NT Event log analysis (for DBA's)

Have ever looked "deep" into your NT Event logs ? - most of we DBA's will say NO. Here I am describe a simple and little easy way to do that trick. Using this trick you are going to generate a report of type of errors that your server logging into the logs and you will woundering what is all this junk on my server.

We are going to use SQL Server to do this, as it is our strenght !

First pick your box to analyze and export the NT Event logs CSV format. Why CSV - because we are going to import that into a SQL Server and do a "deep" analysis of the issues that are going on you server (sort of a health report). Here are the steps for your convience

Open Event viewer
Right click on the "Application Log"
Choose "Save Log File As" option from the pop up menu
Save dialogue will appear
Type the filename and select file type as "CSV (Comma delimited) (*.csv)"

Simple isn't ? (this is not all that I wanted to share in this article). Similary you an save other logs too if you are interested.

Ok, once we have the NT Event logs saved in CSV format out next step is to load them into a SQL Server table, so that we can query, slice and dice the data in this log(s). We are going to import the CSV file into SQL Server using a linked server. Why linked server, because its easy :), here is the simple command to create a linked server,

EXEC sp_addlinkedserver EventLogs, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\MyEventLogs\',
NULL,
'Text'

"C:\MyEventLogs" is the folder where my all the eventlogs are stored, "EventLogs" is my linked server name. Refer to SQL Books online for specifics of sp_addlinkedserver command, if you are interested.

After linked server is created, run the following command,

EXEC sp_tables_ex EventLogs

This will display a recordset and if you look at the column "TABLE_NAME", you will see that all the CSV files under the folder "C:\MyEventLogs\" will be listed. Lets assume that you NT Eventlogs were save a file named "PefCounter.csv" the file will be listed as "PefCounter#csv". Now let query this "PefCounter#csv" table. Here is a simple query,

SELECT * FROM EventLogs...PefCounter#csv

Was't is simple and neat idea !

Now don't run your analytics directly on the file directly (linked server) it will be slow. Instead import it into a table. Below is the table structure that I use and it mostly works all the time, I mean the datatypes, come take a look,


IF OBJECT_ID('EventLog_BeforeQ2') > 0
DROP TABLE [dbo].[EventLog_BeforeQ2]
GO

CREATE TABLE [dbo].[EventLog_BeforeQ2](
[LogDate] [datetime] NULL,
[LogTime] [nvarchar](255) NULL,
[LogSource] [nvarchar](255) NULL,
[LogType] [nvarchar](255) NULL,
[LogCategory] varchar(5) NULL,
[LogEvent] [INT] NULL,
[LogUser] [nvarchar](255) NULL,
[LogComputer] [nvarchar](255) NULL,
[LogErrDesc] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SELECT * INTO [dbo].[EventLog_BeforeQ2] FROM EventLogs...PefCounter#csv

At this point your data is sitting in EventLog_BeforeQ2 table. You can run any query again this table now to do any kind of analysis that you want, but my intention of writing this article was about looking "deep" into the event logs. So, first thing I do is

SELECT DATEDIFF(DD, MIN(LogDate), MAX(logDate)) FROM EventLog_BeforeQ2

This will tell how many days worth of logs you have in your table. Try the below query for more detials,

SELECT LogEvent, COUNT(LogEvent)[Count] FROM [EventLog_BeforeQ2]
WHERE LogType= 'Error'
GROUP BY LogEvent
ORDER BY COUNT(LogEvent) DESC

Below is the output that I see on my logs.

LogEvent Count
----------- -----------
6043 2813
4019 823
6013 528
1 381
318 303
17052 217
4045 122

You can also try to find out what specific applications/services are logging most of the erorrs using following query,

SELECT LogSource, COUNT(LogEvent)[Count] FROM [EventLog_BeforeQ2]
WHERE LogType= 'Error'
GROUP BY LogSource
ORDER BY COUNT(LogEvent) DESC

This is how I target to improve my SQL Server's Maintenance, Monitoring and Performance. I will leave rest of the creativity of log analysis to the readers of this article.