Step 4: Filter the data
By now, we’ve got the messages in a table. The first thing we have to do now is filter the messages. This is the part I can’t help you with. You’ll have to decide for yourself whether you’re interested in a message from the virus scanner, or that service such-and-such has been successfully started.
In the main stored proc I’ve included four lines of comment, where you can call a stored procedure that deletes the irrelevant messages.
For example, you may not want to see the daily informational message about the uptime of SQL. If so, delete it with the following code:
DELETE FROM tblLogStaging WHERE source = ‘MSSQLSERVER’ AND eventid = ‘17177’
Or the fact that someone logged in successfully:
DELETE FROM tblApplicationLog WHERE Description LIKE ‘%Login succeeded for user%’
You may not even be interested in the message that login failed for a user. But you will definitely be interested in the following message that login failed for user ‘sa’. So be careful: don’t delete messages too fast.
You’ll have to figure out for yourself which messages can safely be ignored. This comes from experience in looking at your logs.
Step 5: Putting it all together
By now, we’ve got the 4 tables and the important code required. So, let’s create the stored procedures that do all the work. First the inner loop:
CREATE PROCEDURE [dbo].[uspReadEventLogs]
@server char(20)
AS
BEGIN
–Variable declaration
DECLARE @sql varchar(1000)
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF –-some fragments will be NULL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
–Main
–App log
EXEC master..xp_cmdshell ‘copy d:appsdumpevtempty.csv d:appsdumpevtevtlogdump.csv /Y’, no_output
DELETE FROM tblLogStaging
SET @sql = ‘master..xp_cmdshell ”d:appsDumpEvtDumpEvt.exe /logfile=app /outfile=d:appsdumpevtevtlogdump.csv /computer=’ + @server + char(39) + ‘, no_output’
EXEC (@sql)
BULK INSERT tblLogStaging FROM ‘d:appsdumpevtevtlogdump.csv’
WITH (FIELDTERMINATOR = ‘,’)
/*Include filter on any column but description here, e.g. EXEC uspFilterApplicationStaging*/
INSERT INTO tblApplicationLog ([Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], [Description])
SELECT [Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], Fragment0 + Fragment1 + Fragment2 + Fragment3 + Fragment4 + fragment5 + Fragment6
FROM tblLogStaging
/*Include filter on description here */
–Sys log
EXEC master..xp_cmdshell ‘copy d:appsdumpevtempty.csv d:appsdumpevtevtlogdump.csv /Y’, no_output
DELETE FROM tblLogStaging
SET @sql = ‘master..xp_cmdshell ”d:appsDumpEvtDumpEvt.exe /logfile=sys /outfile=d:appsdumpevtevtlogdump.csv /computer=’ + @server + char(39)+ ‘, no_output’
EXEC (@sql)
BULK INSERT tblLogStaging FROM ‘d:appsdumpevtevtlogdump.csv’
WITH (FIELDTERMINATOR = ‘,’)
/*Include filter on any column but description here */
INSERT INTO tblSystemLog ([Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], [Description])
SELECT [Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], Fragment0 + Fragment1 + Fragment2 + Fragment3 + Fragment4 + Fragment5 + Fragment6
FROM tblLogStaging
/*Include filter on description here */
–Clean up
SET CONCAT_NULL_YIELDS_NULL ON
END
Don’t forget to substitute the paths to the DumpEvt directory, if necessary.
As mentioned earlier, in the c.s.v. file, the description of the message is divided into 6 fragments, with a lot of NULL values. We have to concatenate these to form a readable description; for that, we use the set concat_null_yields_null off at the start of the stored proc. Otherwise, concatenating a fragment with an empty (NULL) fragment would result in an empty (NULL) description.
This is also the reason there are two places to filter: the first time for filtering on anything but description, the second time for filtering on description. You may get lots of records, so it is faster to delete first, and then insert into the destination table.
Finally, let’s create the other stored procedure:
CREATE PROC [dbo].[uspCheckAllServers]
AS
–Variable declaration
DECLARE @server char(20)
DECLARE @sql varchar(1000)
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
–Main
DELETE FROM tblApplicationLog
DELETE FROM tblSystemLog
PRINT ”
DECLARE csrJobs CURSOR
FOR SELECT Server FROM tblServers WHERE status = ‘active’ ORDER BY Server
OPEN csrJobs
FETCH NEXT FROM csrJobs INTO @server
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Checking ‘+ @server
EXEC uspReadEventLogs @server
PRINT ‘ ‘
FETCH NEXT FROM csrJobs INTO @server
END
–Clean up
CLOSE csrJobs
DEALLOCATE csrJobs
This is the ‘outer loop’. For each server in tblServer with a status of ‘active’ it will call the main stored proc (the ‘inner loop’). The name of the server will be passed as the variable @server from the outer loop to the inner loop.
Step 6: Use the results
That’s it! Now, you can just run the stored procedure and scroll through the output:
EXEC uspCheckAllServers;
SELECT *
FROM tblApplicationLog
ORDER BY description, time
Personally, I run the stored proc manually every morning. Running it manually ensures you see the latest events, should you happen to perform the check on a different time than you normally would. It also ensures you won’t miss important messages if you can’t check it every day, because the way it is setup now, no history is saved. But you might want to run it more often, or schedule it. That’s up to you.
]]>