Sometimes I go into these simple explorations that make me learn something new that gets me off the routine. SQL Server is always on the agenda and that passion will never die. This exploration was based on the fact when I was talking to someone about some of the super cool stuff people work on – I heard a DBA complain to me that he was working on a “Windows Server Core” machine and he had no UI. I was pleasantly taken aback and got back search on the internet about. I also got to know “SQL Server” can be installed on these server machines. It was fascinating for me to hear such things and I wondered, how can someone work on such machines.
I thought, let me see if we can use some sort of scripting to work on things I take for granted when troubleshooting some error messages. I always look at opening up the Windows Event Viewer to watch through the errors. Now I thought it would be great not to open up Event Viewer and yet be able to query. That was surely a challenge and I wanted to learn something new and interesting to share.
This blog is at the moment a rudimentary shot at going the scripting route using Powershell. Here are some commands that I used. Let me progress from few simple commands to little interesting, complex queries I figured out to write:
1) List the event viewer logs on a given system. This is simple because I need to know what can be queried at any point in time.
get-eventlog -list
As you can see I have about some 27k+ messages and this is a great place to make our query.
2) Let us next try to select the newest 50 messages of application log:
Get-EventLog -LogName Application -newest 50
The next, I was wondering if we can dump on data based on a date value. This gets me to the next query that was written.
3) Gathering logs after a particular date, we can also use “before” to select the messages prior to given dates as shown below:
Get-EventLog -LogName Application -after 1/10/2016
I wanted to make the query less complex by searching on a specific Event type and Event Source. Since I work with SQL Server and the source had to be MSSQLSERVER (default instance name).
4) Selecting only the messages which are logged as “information” for a source like “MSSQLSERVER” and using a clip to basically copy the output to the clipboard:
Get-EventLog -logname application -EntryType information -newest 50 -source *MSSQLSERVER* | clip
When I was writing the above query, I was little clueless to what are valid source types we can use. So I made a query to identify the source names.
5) Find the relevant source to be used in a query:
Get-EventLog -logname "Application" | Select-Object Source -unique
As you can see, if we know how to play around with Powershell and know the query we need – we can always find nice and easy way to get the data. How many SQL DBA’s who read this blog have already tried playing around with PowerShell? What have you been doing with it? Please let me know via the comments below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
i am first time using powershell very usefull
Yes. It is.
Thanks Pinal. Am trying to Bulk insert this output data of Event log into a Db but I’m having hard time inserting this data into table.
1. Using below query to pull the event log data:
Get-WinEvent -FilterHashTable @{LogName=”CustomEventLogName”;StartTime=(get-date).AddHours(-24)} -ComputerName “MyComputer”| Select-Object TimeCreated, LevelDisplayName, Message | export-csv -path C:\temp\EvntLogPBI_3.csv
Wokrs fine – I get all the data.
2. Trying to bulk insert using:
BULK INSERT GeneralEvents
FROM ‘C:\temp\EvntLogPBI_3.csv’
WITH
(
FIRSTROW = 3,
CODEPAGE = ‘RAW’,
DATAFILETYPE = ‘CHAR’,
FORMATFILE=’C:\temp\level_3.fmt’
)
It throws below exception for all rows in that column:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (TimeCreated).
Here is my table structure:
USE [EventCollections]
GO
CREATE TABLE [dbo].[GeneralEvents](
[TimeCreated] [datetime] NULL,
[LevelDisplayName] [varchar](255) NULL,
[Message] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Format file level_3.fmt includes:
11.0
3
1 SQLCHAR 0 24 “,” 1 TimeCreated SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 255 “,” 2 LevelDisplayName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 “\r\n” 3 Message SQL_Latin1_General_CP1_CI_AS
Hi Pinal, is there a way to write the output of event log records from powershell back into sql server?
how to automate powershell call in sql job to read event log, write event log to file?