Import network computer EventLogs to SQL table

/*
==========================================================================
 Name:    Create EventLogs table, procedures.sql        Version:       1.0
 Author:  Mark A. Denner, Baker Robbins & Company
 Creation Date: Dec 28, 2004
 Created for: Baker Robbins & Company
 All rights reserved.  You may modify and share this script but any 
 publication must cite me as the author.
==========================================================================

DESCRIPTION
-----------
Creates the required table and stored procedure to facilitate the import of
Event Log WARNING and FAILURE entries from a target computer into a SQL using Microsoft's LogParser
for monitoring and analysis.

In order to import and analyze a number of computers' Event Logs, you should create a scheduled task
with a series of steps that each execute this procedure against a single system.  Unless you want to 
keep a historical archive of event log messages, you'll want to make the first step 
'TRUNCATE TABLE EventLogs', so that at any time you only have the past five days' logs.

DEPENDENCIES
------------
1) You'll need Microsoft's LogParser v2.1 or later installed in the path of the SQL Server.  
You can get this as part of the IIS Resource Kit Tools installation.  The easiest
way to get this is to do a Google search for "LogParser IIS".  Put LogParser.exe in the system path --
generally the easiest way to do this is to just put in System32.  Otherwise, you can modify the 
stored procedure to run LogParse from a particular location.s

2) LogParser will run via xp_cmdshell, which executes under the same service account that runs 
SQL Server.  Thus, this account must have rights to access a target computer's event log -- this is most 
easily accomplished by having the SQL Server service account run as a domain admin (which is a pretty typical 
configuration), although there are a variety of other ways to accomplish this.  

3) You'll need a database for the EventLogs table.  This database name can be specified as the @TargetDB 
argument to the stored procedure, or you can just use the procedures default database, SysMgmt.  To create 
the SysMgmt database, you can just execute:
CREATE DATABASE SysMgmt

This uses the system defaults for a new database, which may or may not be appropriate.

4) Review the iisTools.chm help file for some documentation about LogParser, and also check out
www.logparser.com for more information.  Some of this stored procedure is based on http://techrepublic.com.com/5100-6329-5034923.html.

5) Remember, if you have a clustered SQL Server then it has to be in the path of ALL possible nodes.  


ARGUMENTS
---------
Note: assuming you've followed the recommendations in this header section, you'll probably only need to specify
@ComputerName for the target computer and perhaps @DaysAgo if you want more or fewer days of Event Log entries.

@ComputerName    --  The name of the machine from which you want to import the Event Logs
@DaysAgo         --  How many days' worth of Event Logs do you want (DEFAULT = 5)
@SQLServer       --  Name of SQL Server containing the TargetDB and TargetTable into which you will import the data.
                     If you do not specify this argument, the SQL Server running the procedure is used.  
@TargetDB        --  Database where target table resides.  (DEFAULT = 'SysMgmt')
@TargetTable     --  Target table to import event log entries to.  Created below.  (DEFAULT = 'EventLogs')


RETURNS
-------
Returns the return code of LogParser.  I haven't ever found what the return codes are for LogParser, but 1 is a
successful run, 5 means access denied (i.e., the SQL Service account doesn't have permissions to query the
event log on the target machine).  1722 indicates the target server could not be found.  

UPDATE HISTORY
--------------
12/29/05MADReleased
*/

-- REMEMBER TO SWITCH TO THE DATABASE WHERE YOU WANT THE TABLE AND PROCEDURE TO RESIDE!!

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EventLogs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EventLogs]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[proc_GetEventLogs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[proc_GetEventLogs]
GO

CREATE TABLE EventLogs(
      EventLog varchar(256),
      RecordNumber int,
      TimeGenerated datetime,
      EventID int,
      EventType int,
      EventTypeName varchar(256),
      EventCategory int,
      SourceName varchar(256),
      ComputerName varchar(256),
      Message varchar(256),
)
CREATE INDEX IX_EventLogs_ComputerName ON EventLogs(ComputerName)
CREATE INDEX IX_EventLogs_TimeGenerated ON EventLogs(TimeGenerated DESC)

GO

CREATE PROCEDURE proc_GetEventLogs
@ComputerName VarChar(32),
@DaysAgo Int = 5,
@SQLServer VarChar(32) = NULL,
@TargetDB VarChar(32) = 'SysMgmt',
@TargetTable VarChar(32) = 'EventLogs'
AS
DECLARE @LPQuery VarChar(512), @Cmd VarChar(1024), @RC Int
IF @SQLServer IS NULL SELECT @SQLServer = @@SERVERNAME
--First build up the query that LogParser executes (LogParser has a rudimentary SQL engine):
SELECT @LPQuery = Char(34) + 'SELECT EventLog, RecordNumber, TimeGenerated, EventID, EventType, EventTypeName, EventCategory, SourceName, ComputerName, Message'
SELECT @LPQuery = @LPQuery + ' FROM \\' + @ComputerName + '\System, \\' + @ComputerName + '\Application, \\' + @ComputerName + '\Security TO ' + @TargetTable
--EventType = 4 is informational.  You can remove this WHERE clause if you want to get everything.
SELECT @LPQuery = @LPQuery + ' WHERE EventType <> 4 AND TimeGenerated > SUB(System_TimeStamp(), TimeStamp(''' + LTrim(Str(@DaysAgo)) + ''', ''d''))' + Char(34)
--Now build up the command that we'll execute via xp_cmdshell:
SELECT @Cmd = 'LogParser ' + @LPQuery + ' -o:SQL -server:' + @SQLServer + ' -driver:"SQL Server" -database:' + @TargetDB
--Debug:
PRINT @Cmd
EXEC @RC = master..xp_cmdshell @Cmd
RETURN @RC
GO
/*
--Examples
TRUNCATE TABLE EventLogs
DECLARE @RC Int
-- Assuming you use the default configuration, you only really have to specify two arguments: the target server and the number of days of event logs you want
EXEC @RC = proc_GetEventLogs 'NYLAB01', 5
RAISERROR('proc_GetEventLogs returned %d...', 10, 1, @RC)

--Some very basic queries
SELECT ComputerName, COUNT(ComputerName) CountOfErrors
FROM EventLogs 
GROUP BY ComputerName

SELECT ComputerName, TimeGenerated, SourceName, Message, EventTypeName FROM EventLogs
ORDER BY ComputerName, TimeGenerated DESC

SELECT TOP 100 ComputerName, TimeGenerated, SourceName, Message, EventTypeName FROM EventLogs
ORDER BY TimeGenerated DESC

*/