The Baking DBA - select * from flour: [Powershell] EVENTLOG_TRACKING - Capturing multiple servers' Event Logs to a database

Wow, that's what I call timing. TSQL Tuesday happens to be covering something I just finished building!
I'm usually not much for the explanatory posts. I typically try and just solve a problem, then post it here.

(yes, there's code, glorious already-in-production code)

TL;DR - store classic Application/System event logs, minus the crap, in a database. Fast - 2 minutes for 90 servers.

Problem: we had no insight into our servers' event logs. Our in-house application would throw errors, and we needed a process that could rip through the server logs and send emails to the systems team when there were problems.

Solution, 5 years ago: I banged something together in a hurry, using Microsoft's LogParser app, to capture event logs to a database. Why? Yes, I'm the DBA, but I've always been the "special teams" - need to take hex dumps from an Linux app's log and convert them to a format we can query off of? I'm your guy. Sed, tcsh, Shell Scripts, Perl (so very rusty these days), DOS Batch files, Un*x, Powershell, etc. And nobody else had time, so I wrote one using the quickest path I could. And it worked! Worked well, even. And so it ran in the background for 5 years...

Fast forward to: now. The fact that I'd been saving them saved the day (both at the time, and recently), but I saw that it needed improving. Looking at what I wrote... I don't shudder, but it needed a rewrite. Yes, the systems folk now have tools... that only save for a week. So yet again... DBA to the rescue!

(Of course, now that I write this - I wonder if I should've just built a process to save from that tools' table...)

So how should we overhaul this? LogParser hasn't seen any love in years, every time I log on the box I need to clear a bunch of "LogParser has crashed" messages, and while event logs have changed some I still only need the "classic" logs. And it's slow. Ye gods, it's slow. 4 hours. Partly because it's running in serial (it's a loop that executes, then goes to the next), partly because LogParser is dated and partially because.... um, it's slow, no idea why, it shouldn't be.

So, I rewrote it, using PowerShell! (and it only takes 2 minutes for 90 servers!)

Several ~~days~~ later I've got it working, and have learned some important things I'll share with you.

Every version has peccadillos. Get-WinEvent doesn't work with -filterhashtable in 2008 (but works in 2008R2). Get-eventlog only does classic logs. Win32_NTLogEvent is faster, but requires WMI permissions. Fields differ. One of the event codes is different between the different versions (so an event may be 3 from one method, and 4 from another)
Filtering is weird. Everything I read says that server-side filtering over slow pipes would be faster (makes sense), and that only Get-WinEvent does server-side filtering... but in every test I've done, over multiple environments, the oldest implementation (gwmi -query on Win32_NTLogEvent) is the fastest. If everything's on the same network it's close, but over the WAN it's apparent.
That being said, if you want the newer style of logs, you must use Get-Winevent - neither get-eventlog nor gwmi capture them. *Sad Trombone*
WMI uses its own time implementation, which means you need to pay attention when setting dates.
FilterXML is a beating, but is required for good filtering on SQL 2008 with get-winevent.
Windows Remoting is awesome - if your systems people will set it up. Then, you can run the commands remotely, and ensure the filtering occurs on the server. Setting it up (more so on older boxes) is a pain.
The permissions required for WMI are NOT the same as those required for get-eventlog *sigh*.
Being able to say (get-date).second and get back the current second is pretty cool.

One catch on my code, and I have no idea why it is: sometimes, even running in serial, a server won't be inserted. I suspect it's some weird interaction with BULK INSERT, write-datatable and FULLTEXT, like maybe it's running the SP before the data has finished committing. So, I have an additional job step that runs one last time over, once it's all done.

One other oddity - because of the way logs work, clusters have issues. The logs show up on both, and the computername shows up as the listener name. And then you see the computername of the physical boxes.... it's a pain. I'll have to fix. Later.

TL;DR - store classic Application/System event logs, minus the crap, in a database.

Lower-level overview:

Grab a list of servers from our central repository and combine that with our Eventlog view (each month gets its own tables, combined into a view) to figure out what we need to get, aka what time and record number we care about, for each log (app and sys). Alas, the Security Log has…. issues, not the least of which is the amount of growth/size, but also the way it’s generated means that querying it is beyond slow.

Truncate our staging table. At the end of the process, there may be left over records. If we can’t get them in the database we need to look at it, but we’ll always have some due to our cluster. (Long story)

Now, we run the next part in parallel. I’ve got it running 8 threads at once, so it doesn’t overwhelm the ~~database~~ network. The way it’s structured, the "master" job will stop everything after a number of seconds (defined at the end of the block). When our systems people got the permissions working properly, you would see the job run for the full time, then forcibly end. Which means that it didn’t finish all of the threads. So when it started up again, it would try again, get as far as it could, then stop again. Fortunately, that’s pretty far, and after a half-dozen of the 30-minute runs, it was caught up. Now it takes 1.5 minutes for 70+ servers. Not too shabby.

And for each server...

Get the version of the server we’re querying. I did that originally because it says online that Win-Getevent is sooo much quicker, server-side filtering, etc, etc. LIES. In my testing the closest I ever got was “not any slower”, but most of the time gwmi was 4 times faster (although I saw 40 times) than either get-eventlog or get-winevent. So it all uses the same path, but I left in there in case we needed it. Theoretically we could get it down even faster, using remoting… but setting it up on servers is decidedly Non-Trivial and increases your Windows Surface Area. (However, you are MORE than welcome to do so - expect a possible order-of-magnitude speed boost.)
Now, use GWMI (get-wmiobject) and a query, built off of the logs, to get new records. Uses the --query parameter, and including a timegenerated/eventlog/recordnumber. Now that I think about it, it’s possible that one event gets written before a different event from a different subsystem, so we could conceivably lose events. I’ll have to look at that.
Next, take the datatable results, and insert them into a view. We use a view because adding stuff like ID (an identity column) and insert_datetime rapidly becomes Non Trivial. But inserting through the view fixes that. (Which is a different set of jobs; one that creates a new monthly table and modify the view)
Finally, run the eventlog_blacklist_removal Stored Procedure. That one deserves its own little writeup. But practically, it looks at a blacklist table and deletes anything from our blacklist. It's fast because of fulltext indexing. Why do all that? There’s far too much stuff we don’t care about.

Hope this helps you!

######============CODE=============#####

Creating the monthly table and views (set this up as a monthly job, probably on the 15th or so, so that you have plenty of time to fix if it borks

	SET QUOTED_IDENTIFIER ON
	SET ANSI_NULLS ON
	GO

	CREATE PROCEDURE [dbo].[EventLog_Monthly_Table_View_Maintenance]
	AS
	--mdb 2015/05/06 adding insert_datetime so that we can tell when the heck rows are added, for troubleshooting
	DECLARE @sql VARCHAR(8000)
	SELECT @sql =
	'CREATE TABLE [dbo].[EventLog_' + CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112) + '] (
	[EventLog] [varchar](255) NOT NULL,
	[RecordNumber] [int] NOT NULL,
	[TimeGenerated] [datetime] NOT NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) NULL,
	[SourceName] [varchar](255) NULL,
	[Strings] [varchar](255) NULL,
	[ComputerName] [varchar](255) NOT NULL,
	[SID] [varchar](255) NULL,
	[Message] [varchar](255) NULL,
	insert_datetime datetime,
	PRIMARY KEY CLUSTERED
	(
	[TimeGenerated] ASC,
	[ComputerName] ASC,
	[RecordNumber] ASC,
	[EventLog] ASC
	)
	)
	'
	EXEC (@sql)

	SELECT @sql = 'ALTER TABLE [dbo].[EventLog_'+ CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112) + '] WITH CHECK ADD CHECK ([TimeGenerated]>='''
	+ CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE() )),101)
	+ ''' AND [TimeGenerated]< '''
	+ CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,2,GETDATE() )),101)
	+ ''')'
	EXEC (@sql)



	SELECT @sql = 'alter view EventLog_Details as '

	SELECT @sql = @sql + CHAR(13)
	+ 'select EventLog,
	RecordNumber,
	TimeGenerated,
	TimeWritten,
	EventID,
	EventType,
	EventTypeName,
	EventCategory,
	EventCategoryName,
	SourceName,
	Strings,
	ComputerName,
	SID,
	[Message],
	insert_datetime
	from ' + table_name + CHAR(13)
	+ 'union all'
	FROM INFORMATION_SCHEMA.tables
	WHERE table_NAME LIKE 'EventLog_20[0-9][0-9][0-9][0-9]'
	AND table_name NOT LIKE 'EventLog_'+ CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112)
	ORDER BY TABLE_NAME

	SELECT @sql = @sql + CHAR(13) + 'SELECT * FROM EventLog_'+ CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112)

	EXEC (@sql)

	GO

view raw EventLog_Monthly_Table_View_Maintenance.sql hosted with ❤ by GitHub

The powershell:

	#Note this is JUST the capture portion; there's a database component as well
	# the 4 scripts below are obviously prereqs, from Hey Scripting Guy and PoshCode
	clear
	. C:\powershell_scripts\invoke-sqlcmd2.ps1;
	. C:\powershell_scripts\split-job.ps1;
	. C:\powershell_scripts\write-datatable.ps1;
	. C:\powershell_scripts\out-datatable.ps1;

	$server_repository = 'yourreposerverhere'
	$database_repository = 'yourrepodbhere'

	write-host (get-date)

	########################
	#Get the list of servers and Event Logs we need to connect. For now, just App & System (table = EventLogs_To_Get)
	# We have to use the tables because the serverlog table's "last" date is when it last ran, not when it was actually done.
	########################
	$serverlist = invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "
	SELECT a.Server_Name,
	a.EventLog,
	isnull(MAX(TimeGenerated),DATEADD(dd, DATEDIFF(dd, 0, GetDate()-days_back_to_get), 0)) AS max_timegenerated,
	ISNULL(MAX(RecordNumber),minimum_recordnumber) AS max_recordnumber
	FROM
	(
	SELECT Server_Name, EventLog, days_back_to_get, minimum_recordnumber FROM EventLog_Servers
	CROSS JOIN EventLogs_To_Get
	WHERE Is_Active = 1
	--ORDER BY Server_Name
	) a LEFT OUTER JOIN [EventLog_Details] WITH (NOLOCK)
	ON EventLog_Details.EventLog = a.EventLog
	AND EventLog_Details.ComputerName= a.Server_Name
	AND TimeGenerated >=GETDATE()-15
	GROUP BY a.Server_Name, a.EventLog, days_back_to_get, minimum_recordnumber
	ORDER BY a.Server_Name"

	$serverlist


	##############################
	# Truncate the staging table #
	##############################
	#have to do it here because we can't have multiples all hitting the same table and truncating it
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "truncate table EventLog_Staged_PoSH"


	###############################
	# FOR EACH SERVER (SPLIT-JOB) #
	###############################

	#now use Split-Job (from POSHcode) to split into a number of Runspaces ("hosted runspaces")
	#variables need to be inside the split-job due to scope issues, though there's a parameter (see end) that will import them.
	# Need a way to trap for inside errors, since any failure causes an invisible total failure
	#$serverlist\| %{ #alternate version for testing

	$serverlist\|Split-Job {%{
	#because the scope is separate in here, you need to re-dot-source and reinit your parameters.
	. C:\powershell_scripts\invoke-sqlcmd2.ps1;
	. C:\powershell_scripts\write-datatable.ps1;
	. C:\powershell_scripts\out-datatable.ps1;
	#setting these here because the datatypes are screwy if passed directly. (due to the datatable)
	$computername = $_.server_name
	$eventlog = $_.EventLog
	$recordnumber = $_.max_recordnumber

	try
	{

	############################
	# Clear the staging table ##
	############################
	#Doing it again in case we somehow dupe.
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "delete from EventLog_Staged_PoSH where computername = '$computername' and eventlog = '$eventlog'"


	#################################################
	# get OS version so we know which method to use #
	#################################################
	# not currently used; GWMI is fastest of the methods, in my testing

	# http://stackoverflow.com/questions/27513886/substring-to-get-text-after-second-period-in-powershell
	#get version of OS via WMI - lets us know if we can use get-winevent, get-eventlog, etc. Annoyingly, GWMI is currently fastest.
	[version]$full_version = (get-wmiobject -class win32_operatingsystem -ComputerName $computername\|select version).version

	#now set up a string we can compare against
	[string]$usable_version = "$($full_version.Major).$($full_version.Minor)"

	####################################################
	## Windows Server 5 (Windows Server 2003, 2003R2) ##
	####################################################

	#Unsure if we event _want_ get-winevent for now - in testing, using filterxpath on a 6.0 box took 31 seconds, but 9 using gwmi
	#even if I swapped the order to ensure evenness, gwmi is simply faster, at least with 6.0, which can't use hashtables.
	if(($full_version.Major -eq "5") -or ($full_version.Major -eq "6")) #doing it this way until I can get the get-winevent added
	{

	#for this path, which includes WS 2003, we HAVE to go old-school. Specifically, GWMI, which in our testing was much faster than get-eventlog
	$time = [System.Management.ManagementDateTimeConverter]::ToDmtfDateTime($_.max_timegenerated)

	#pull across the min number of fields you need
	#Does filtering on RecordNumber speed things up? No, but doesn't seem to slow it down either. Needs more testing!
	$EventQuery = @"
	select
	Logfile, RecordNumber, Timegenerated, TimeWritten, EventCode, EventType, Type, Category, CategoryString, SourceName, InsertionStrings, ComputerName, User, Message
	from Win32_NTlogEvent
	where timewritten >='$time'
	and LogFile = '$eventlog'
	and RecordNumber > $recordnumber
	"@ #this line must be un-recessed

	$EventQuery

	#logging the time the eventlog pull starts
	$computername + ", " + $eventlog + " pull beginning at " + (get-date)
	$LogResults = gwmi -computername $computername -Query $EventQuery \|
	select @{Label='EventLog';Expression={$_.Logfile}}, `
	RecordNumber, `
	@{LABEL="TimeGenerated"; EXPRESSION = {$_.convertToDateTime($_.TimeGenerated)}}, `
	@{LABEL="TimeWritten"; EXPRESSION = {$_.convertToDateTime($_.TimeWritten)}}, `
	@{Label='EventID';Expression={$_.EventCode}}, `
	#eventType DOES NOT MATCH existing! One has diff values than other. is 4 = information, 2= warning, 1 = error, 5 = audit failure
	EventType, `
	@{Label='EventTypeName';Expression={$_.Type}}, `
	@{Label='EventCategory';Expression={$_.Category}}, `
	@{Label='EventCategoryName';Expression={$_.CategoryString}}, ` #"None" in old, blank here
	SourceName, ` #"Perflib" in old, Microsoft-Windows-Perflib here
	@{name='Strings';Expression={ $_.InsertionStrings -join '\|'}}, ` #Appears to match!
	@{name='ComputerName';Expression={$_.ComputerName -replace "\..*",""}}, ` #remove the domain name (.blah.com)
	@{Label='SID';Expression={$_.User}}, `
	@{Label='Message';Expression={$_.Message}} \|out-datatable

	#17s/18s with just filter on timewritten and logfile (two tests, mem-pr-repl-01)
	#18s/18s including recordnumber (two tests, mem-pr-repl-01)

	#Why no results on the console?
	#It's due to runspace (aka split-job)

	#logging the time the eventlog pull ends
	$computername + ", " + $eventlog + " pull ending at " + (get-date)
	}

	#Write to the table, so that we can act upon it using the SP
	Write-DataTable -ServerInstance $server_repository -Database $database_repository -TableName EventLog_Staged_PoSH_insert -Data $LogResults

	#logging the time the eventlog write finishes
	$computername + ", " + $eventlog + " write to staged ending at " + (get-date)
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "EXEC Eventlog_Blacklist_Removal @ComputerName = '$computername', @EventLog = '$eventlog'"

	#logging the time the blacklist SP ends
	$computername + ", " + $eventlog + " blacklist removal ending at " + (get-date)
	}
	catch
	{
	#throw $_.Exception.Message #doesn't remove enough crap
	Write-Error $_.Exception.Message #-ErrorAction Stop I dont want to stop it, just throw an error.
	}

	#Now, run a (new for this posh version) stored procedure to get it into EventLog_YYYYMM

	}
	} -MaxDuration 3200 -MaxPipelines 8 -Variable server_repository, database_repository -NoProgress
	#the variable above allows the split-job to read from outside the scope and put it inside
	#max of 10 minutes because some will actually take that long; may need to set to 15-20!.
	#as you run it initially, it will take the full MaxDuration to get everything downloaded. After several times, will settle down.
	#Perms needed – add whatever credential you’re using to “Distributed COM users group”
	# and add user to permissions on the wmi namespace “CIMV2” on each server with permissions “Allow” on “Remote Enabled”

	write-host (get-date)

view raw EventLog_Capture.ps1 hosted with ❤ by GitHub

Stored procedures (probably have to run these after the tables are all built):
Eventlog_Blacklist_Removal - this runs after the insert, and its job is to delete the crap records (as per the blacklist table) and insert into the partitioned view.

	SET QUOTED_IDENTIFIER ON
	SET ANSI_NULLS ON
	GO
	CREATE PROCEDURE Eventlog_Blacklist_Removal
	@ComputerName sysname, @EventLog VARCHAR (255)
	AS
	SET NOCOUNT on
	/*
	--Uses log parser to scan the event logs
	1.00 Michael Bourgon 2010/02/05 still need to be fixed: TRDB issues, "Error reading event log: The remote procedure call failed."

	2.00 mdb 2015/04/29 The all-new, powershell-driven approach. Used GWMI and get-WinEvent to pull logs, using remoting if possible.

	V2, unsure if this applies:
	--while it can run in Chicago (run on VI-01 so that it can reach the TRDBs and APs)
	-- another problem then occurs: it can't insert into the partitioned view. Cursor error of some sort.

	--V2, This still applies:
	--There are two jobs: one that runs the log reader, and one that runs monthly that creates the new tables and alters the view.
	-- you should only need last month and this month; new servers are only polled for the past two weeks.
	-- So provided it even just runs weekly, you should be fine.

	2015/04/30 mdb 2.01 moving update logic, trying to figure out the dupes.
	2015/05/04 mdb 2.02 dupes were due to stragglers (still unexplained) and the cluster. Added Straggler re-run
	also: adding insert_datetime everywhere.
	2015/05/07 mdb 2.03 changing size of fields "strings" to 1000, "message" to 1500"

	-----------------------
	--RUN ON FTW-SV-DB-03--
	-----------------------
	---------------
	--create tables
	---------------

	--create table EventLog_EventBlacklist (Event_Name VARCHAR(255), DESCRIPTION VARCHAR(50), is_active BIT)
	--INSERT INTO eventlog_eventblacklist (name, is_active) VALUES ('The McAfee McShield service entered',1)
	--INSERT INTO eventlog_servers (SERVER, is_active) VALUES ('FTW-SV-DB-03', 1)
	--DECLARE @blacklist TABLE (NAME VARCHAR(255)
	--SELECT NAME FROM EventLog_EventBlacklist WHERE is_active IS NULL OR is_active = 1
	--you'll need to create a primary key on the EventLog_Staging table named PK__EventLog_Staged__RecordNumber
	-- that's needed for the fulltext below

	-- note that the ID is now the PK. Needed so we can run in parallel.
	drop table [dbo].[EventLog_Staged_PoSH]
	CREATE TABLE [dbo].[EventLog_Staged_PoSH]
	(
	ID INT IDENTITY,
	[EventLog] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[RecordNumber] [bigint] NOT NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SourceName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Strings] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ComputerName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Message] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]
	GO
	--View so that we can insert into here and have it auto-pick-up the IDENTITY
	CREATE VIEW [EventLog_Staged_PoSH_Insert]
	AS
	SELECT EventLog,
	RecordNumber,
	TimeGenerated,
	TimeWritten,
	EventID,
	EventType,
	EventTypeName,
	EventCategory,
	EventCategoryName,
	SourceName,
	Strings,
	ComputerName,
	SID,
	Message FROM EventLog_Staged_PoSH

	ALTER TABLE [dbo].[EventLog_Staged_PoSH] ADD CONSTRAINT [PK__EventLog_Staged_PoSH__RecordNumber] PRIMARY KEY CLUSTERED (ID) ON [PRIMARY]
	GO
	CREATE FULLTEXT INDEX ON [EventLog_Staged_PoSH] KEY INDEX [PK__EventLog_Staged_PoSH__RecordNumber] ON [Eventlog_Blacklist]
	with change_tracking AUTO --change_tracking AUTO is the default.
	GO
	ALTER FULLTEXT INDEX ON [dbo].[EventLog_Staged_PoSH] ADD (Message LANGUAGE 1033)
	GO
	create index ncidx__EventLog_Staged_PoSH__computername__eventlog on EventLog_Staged_PoSH (Computername, EventLog)
	--needed since we'll have multiple computers in there at once.

	create table EventLog_Staged_Errors (ID bigint identity primary key, ErrorState int, ErrorSeverity int, errormessage nvarchar(4000))
	--trying to figure out leftovers
	create table EventLog_Blacklist_Calls (ID bigint identity primary key, computername varchar(255), eventlog varchar(255), insert_datetime datetime default(getdate()))
	------------
	--fulltext--
	------------
	--mdb 2015/04/29 - below are original comments from V1.

	--sp_fulltext_database 'enable'
	--CREATE FULLTEXT CATALOG Eventlog_Blacklist AS default
	--We create the index on Staged since that's the table we're filtering (in order to populate the Daily table).
	-- Plus it keeps things small(er).
	--mdb 2015/04/29 the above block for Eventlog_Staged_PoSH seems to handle all of the below. Saving here until i'm sure.
	--CREATE FULLTEXT INDEX ON dbo.EventLog_Staged ([Message])
	--KEY INDEX PK__EventLog_Staged__RecordNumber WITH Change_Tracking AUTO

	*/
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	DECLARE @blacklist_message varchar(255), @min int, @max int
	DECLARE @blacklist TABLE (id INT IDENTITY, event_name VARCHAR(255))
	IF object_id('tempdb..#Errors') IS NOT NULL
	DROP TABLE #Errors
	CREATE TABLE #Errors (resultant VARCHAR(255))
	IF object_id('tempdb..#Full_Errors') IS NOT NULL
	DROP TABLE #Full_Errors
	CREATE TABLE #Full_Errors (id INT IDENTITY, server_name sysname, resultant VARCHAR(255))

	DECLARE @max_recordnumber BIGINT, @insert_datetime DATETIME
	DECLARE @waitcounter TINYINT = 1
	BEGIN TRY

	----had issues where it would skip result sets... I think possibly because it runs before it's committed
	----doing the above part so that it doesn't have to wait 1 second if it doesn't need to.
	--WHILE (@waitcounter <= 5 AND @max_recordnumber IS NULL)
	--begin
	-- SELECT @max_recordnumber = MAX(RecordNumber) , @insert_datetime = MAX(insert_datetime)
	-- FROM EventLog_Staged_PoSH
	-- WHERE eventlog = @EventLog
	-- AND ComputerName = @ComputerName
	-- INSERT INTO EventLog_Blacklist_Calls (computername, eventlog, max_recordnumber, inserted_dt_staging) VALUES (@ComputerName, @EventLog, @max_recordnumber, @insert_datetime)

	-- WAITFOR DELAY '00:00:01'
	-- SET @waitcounter = @waitcounter + 1
	--END

	SELECT @max_recordnumber = MAX(RecordNumber)
	FROM EventLog_Staged_PoSH
	WHERE eventlog = @EventLog
	AND ComputerName = @ComputerName


	--updating the table is at the end, in case it crashes.

	-------------------------------------------------------------------------------------------
	--Blacklist removal code. Uses the EventLog_Blacklist table to determine what to delete.--
	-------------------------------------------------------------------------------------------
	--Doing this to allow inclusion/exclusion of "all servers" from blacklist.
	-- If the servername is null, it assumes it's for all servers, but you can explicitly forbid a server from using it.
	DELETE FROM @blacklist
	INSERT INTO @blacklist (event_name)
	--get "all server" events
	SELECT event_name FROM dbo.EventLog_EventBlacklist
	--but exclude when we've set is_active
	WHERE
	is_active = 1
	AND Server_Name IS NULL
	AND NOT EXISTS
	(
	SELECT * FROM EventLog_EventBlacklist whitelist
	WHERE whitelist.Event_Name = EventLog_EventBlacklist.Event_Name
	AND whitelist.Server_Name = @computername
	AND is_active = 0
	)
	UNION ALL
	--and include all explicit blacklist entries for that server
	SELECT event_name
	FROM dbo.EventLog_EventBlacklist
	WHERE Server_Name = @computername AND is_active = 1

	--dealing with regexp
	UPDATE @blacklist SET event_name = REPLACE(event_name, '[','[[]')

	SELECT @min = NULL, @max = NULL

	SELECT @min = MIN(id), @max = MAX(id) FROM @blacklist

	WHILE @min <= @max
	BEGIN
	SELECT @blacklist_message = event_name FROM @blacklist WHERE id = @min

	DELETE FROM dbo.EventLog_Staged_PoSH WHERE message LIKE '%'+@blacklist_message+'%'
	AND computername = @ComputerName AND EventLog = @EventLog

	SET @min = @min + 1
	END

	-------------------------------
	--Insert into permanent table--
	-------------------------------



	INSERT INTO EventLog_Details
	(EventLog ,
	RecordNumber ,
	TimeGenerated ,
	TimeWritten ,
	EventID ,
	EventType ,
	EventTypeName ,
	EventCategory ,
	EventCategoryName ,
	SourceName ,
	Strings ,
	ComputerName ,
	[SID] ,
	[Message],
	insert_datetime )
	--changing the field length here because trying "width" via custom table in PoSH caused "illegal key width" error
	SELECT EventLog ,
	RecordNumber ,
	TimeGenerated ,
	TimeWritten ,
	EventID ,
	EventType ,
	EventTypeName ,
	EventCategory ,
	EventCategoryName ,
	SourceName ,
	LEFT(Strings,1000) ,
	ComputerName ,
	[SID] ,
	LEFT([Message],1500),
	insert_datetime
	FROM dbo.EventLog_Staged_PoSH
	WHERE ComputerName = @ComputerName
	AND EventLog = @EventLog
	END TRY
	BEGIN CATCH
	PRINT 'Error Occurred on Insert'

	SELECT @ErrorMessage = ERROR_MESSAGE(),
	@ErrorSeverity = ERROR_SEVERITY(),
	@ErrorState = ERROR_STATE();

	INSERT INTO EventLog_Staged_Errors (errorstate, errorseverity, errormessage, computername, eventlog)
	VALUES (@errorstate, @ErrorSeverity, @ErrorMessage, @computername, @eventlog)

	IF @@TRANCOUNT > 0
	ROLLBACK TRANSACTION;
	PRINT 'Error Severity: ' + CONVERT(VARCHAR(30), @ErrorSeverity)
	PRINT 'Error State: ' + CONVERT(VARCHAR(30), @ErrorState)
	PRINT 'Error Severity: ' + @ErrorMessage
	--commented this out because I don't want it dy.
	--RAISERROR (@ErrorMessage, -- Message text.
	-- @ErrorSeverity, -- Severity.
	-- @ErrorState -- State.
	-- );


	END CATCH

	--trying to keep the staging table small
	IF @@ERROR = 0 AND @ErrorState IS null
	BEGIN
	DELETE FROM EventLog_Staged_PoSH
	WHERE ComputerName = @ComputerName
	AND EventLog = @EventLog

	--if a null set is returned (because there are no new events) then we need to make sure it doesn't get set to null

	IF @max_recordnumber IS NOT NULL AND @eventlog = 'Application'
	UPDATE EventLog_Servers
	SET Application_RecordCount = @max_recordnumber,
	Application_Last_Inserted = GETDATE()
	WHERE Server_Name = @computername
	ELSE
	IF @max_recordnumber IS NOT NULL AND @EventLog = 'System'
	UPDATE EventLog_Servers
	SET System_RecordCount = @max_recordnumber,
	System_Last_Inserted = GETDATE()
	WHERE Server_Name = @computername
	END



	GO

view raw Eventlog_Blacklist_Removal.sql hosted with ❤ by GitHub

EventLog_Blacklist_Stragglers - for some reason, sometimes a servers logs aren't cleared. As per the job, this step runs second, and its whole purpose is to remove any stragglers that weren't properly processed originally.

	SET QUOTED_IDENTIFIER ON
	SET ANSI_NULLS ON
	GO

	CREATE PROCEDURE EventLog_Blacklist_Stragglers
	AS
	--mdb 2015/04/30 for some reason we're seeing cases where the stored procedure is called, but isn't doing anything.
	-- while running single-threaded fixes it, it also takes several (4x) times longer.
	-- We could also simply load everything in first, then go through and filter. However, that causes other
	-- issues, like space bloat. Hence, this.
	DECLARE @straggler_logs TABLE (id int IDENTITY PRIMARY KEY, computername varchar(255), eventlog varchar(255))
	INSERT INTO @straggler_logs (computername, eventlog)
	SELECT DISTINCT computername, eventlog FROM EventLog_Staged_PoSH

	declare @min INT, @max INT, @computername varchar(255), @eventlog varchar(255)
	SELECT @min = MIN(id), @max = MAX(id) FROM @straggler_logs
	while @min <= @max
	BEGIN
	print @min
	SELECT @computername = computername, @eventlog = eventlog FROM @straggler_logs WHERE id = @min
	EXEC Eventlog_Blacklist_Removal @ComputerName = @computername, @EventLog = @eventlog
	set @min = @min+1
	END
	GO

view raw EventLog_Blacklist_Stragglers.sql hosted with ❤ by GitHub

Job:
The job that runs all this:

	USE [msdb]
	GO

	/**** Object: Job [EventLog Capture [Priority 2]] Script Date: 5/12/2015 4:20:29 PM ****/
	BEGIN TRANSACTION
	DECLARE @ReturnCode INT
	SELECT @ReturnCode = 0
	/**** Object: JobCategory [[Uncategorized (Local)]] Script Date: 5/12/2015 4:20:30 PM ****/
	IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
	BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	END

	DECLARE @jobId BINARY(16)
	EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'EventLog Capture',
	@enabled=1,
	@notify_level_eventlog=0,
	@notify_level_email=0,
	@notify_level_netsend=0,
	@notify_level_page=0,
	@delete_level=0,
	@description=N'mdb 2015/04/29 using the repository for database (until I can move over to this box), but script runs from here.

	For now:
	look back a day+ (yesterday at midnight) at the longest, and get all event logs and save into eventlog_tracking.dbo.eventlog_yyyymm',
	@category_name=N'[Uncategorized (Local)]',
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	/**** Object: Step [run c powershell_scripts eventlog_capture] Script Date: 5/12/2015 4:20:31 PM ****/
	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run c powershell_scripts eventlog_capture',
	@step_id=1,
	@cmdexec_success_code=0,
	@on_success_action=3,
	@on_success_step_id=0,
	@on_fail_action=2,
	@on_fail_step_id=0,
	@retry_attempts=0,
	@retry_interval=0,
	@os_run_priority=0, @subsystem=N'CmdExec',
	@command=N'powershell "& c:\powershell_scripts\eventlog_capture.ps1"',
	@output_file_name=N'c:\sql_log\eventlog_capture.txt',
	@flags=0
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	/**** Object: Step [run straggler cleanup SP on repodb] Script Date: 5/12/2015 4:20:31 PM ****/
	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run straggler cleanup SP on reposerver',
	@step_id=2,
	@cmdexec_success_code=0,
	@on_success_action=1,
	@on_success_step_id=0,
	@on_fail_action=2,
	@on_fail_step_id=0,
	@retry_attempts=0,
	@retry_interval=0,
	@os_run_priority=0, @subsystem=N'CmdExec',
	@command=N'sqlcmd -Q "exec EventLog_Blacklist_Stragglers" -S reposerver -E -d eventlog_tracking',
	@output_file_name=N'c:\sql_log\eventlog_capture.txt',
	@flags=2
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every 10 minutes',
	@enabled=1,
	@freq_type=4,
	@freq_interval=1,
	@freq_subday_type=4,
	@freq_subday_interval=10,
	@freq_relative_interval=0,
	@freq_recurrence_factor=0,
	@active_start_date=20150429,
	@active_end_date=99991231,
	@active_start_time=0,
	@active_end_time=235959,
	@schedule_uid=N'93334e64-e791-4ee5-83a7-71f9a82f6fda'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	COMMIT TRANSACTION
	GOTO EndSave
	QuitWithRollback:
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
	EndSave:

	GO

view raw job_eventlog_capture_posh.sql hosted with ❤ by GitHub

	#Note this is JUST the capture portion; there's a database component as well
	# the 4 scripts below are obviously prereqs, from Hey Scripting Guy and PoshCode
	clear
	. C:\powershell_scripts\invoke-sqlcmd2.ps1;
	. C:\powershell_scripts\split-job.ps1;
	. C:\powershell_scripts\write-datatable.ps1;
	. C:\powershell_scripts\out-datatable.ps1;

	$server_repository = 'yourreposerverhere'
	$database_repository = 'yourrepodbhere'

	write-host (get-date)

	########################
	#Get the list of servers and Event Logs we need to connect. For now, just App & System (table = EventLogs_To_Get)
	# We have to use the tables because the serverlog table's "last" date is when it last ran, not when it was actually done.
	########################
	$serverlist = invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "
	SELECT a.Server_Name,
	a.EventLog,
	isnull(MAX(TimeGenerated),DATEADD(dd, DATEDIFF(dd, 0, GetDate()-days_back_to_get), 0)) AS max_timegenerated,
	ISNULL(MAX(RecordNumber),minimum_recordnumber) AS max_recordnumber
	FROM
	(
	SELECT Server_Name, EventLog, days_back_to_get, minimum_recordnumber FROM EventLog_Servers
	CROSS JOIN EventLogs_To_Get
	WHERE Is_Active = 1
	--ORDER BY Server_Name
	) a LEFT OUTER JOIN [EventLog_Details] WITH (NOLOCK)
	ON EventLog_Details.EventLog = a.EventLog
	AND EventLog_Details.ComputerName= a.Server_Name
	AND TimeGenerated >=GETDATE()-15
	GROUP BY a.Server_Name, a.EventLog, days_back_to_get, minimum_recordnumber
	ORDER BY a.Server_Name"

	$serverlist


	##############################
	# Truncate the staging table #
	##############################
	#have to do it here because we can't have multiples all hitting the same table and truncating it
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "truncate table EventLog_Staged_PoSH"


	###############################
	# FOR EACH SERVER (SPLIT-JOB) #
	###############################

	#now use Split-Job (from POSHcode) to split into a number of Runspaces ("hosted runspaces")
	#variables need to be inside the split-job due to scope issues, though there's a parameter (see end) that will import them.
	# Need a way to trap for inside errors, since any failure causes an invisible total failure
	#$serverlist\| %{ #alternate version for testing

	$serverlist\|Split-Job {%{
	#because the scope is separate in here, you need to re-dot-source and reinit your parameters.
	. C:\powershell_scripts\invoke-sqlcmd2.ps1;
	. C:\powershell_scripts\write-datatable.ps1;
	. C:\powershell_scripts\out-datatable.ps1;
	#setting these here because the datatypes are screwy if passed directly. (due to the datatable)
	$computername = $_.server_name
	$eventlog = $_.EventLog
	$recordnumber = $_.max_recordnumber

	try
	{

	############################
	# Clear the staging table ##
	############################
	#Doing it again in case we somehow dupe.
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "delete from EventLog_Staged_PoSH where computername = '$computername' and eventlog = '$eventlog'"


	#################################################
	# get OS version so we know which method to use #
	#################################################
	# not currently used; GWMI is fastest of the methods, in my testing

	# http://stackoverflow.com/questions/27513886/substring-to-get-text-after-second-period-in-powershell
	#get version of OS via WMI - lets us know if we can use get-winevent, get-eventlog, etc. Annoyingly, GWMI is currently fastest.
	[version]$full_version = (get-wmiobject -class win32_operatingsystem -ComputerName $computername\|select version).version

	#now set up a string we can compare against
	[string]$usable_version = "$($full_version.Major).$($full_version.Minor)"

	####################################################
	## Windows Server 5 (Windows Server 2003, 2003R2) ##
	####################################################

	#Unsure if we event _want_ get-winevent for now - in testing, using filterxpath on a 6.0 box took 31 seconds, but 9 using gwmi
	#even if I swapped the order to ensure evenness, gwmi is simply faster, at least with 6.0, which can't use hashtables.
	if(($full_version.Major -eq "5") -or ($full_version.Major -eq "6")) #doing it this way until I can get the get-winevent added
	{

	#for this path, which includes WS 2003, we HAVE to go old-school. Specifically, GWMI, which in our testing was much faster than get-eventlog
	$time = [System.Management.ManagementDateTimeConverter]::ToDmtfDateTime($_.max_timegenerated)

	#pull across the min number of fields you need
	#Does filtering on RecordNumber speed things up? No, but doesn't seem to slow it down either. Needs more testing!
	$EventQuery = @"
	select
	Logfile, RecordNumber, Timegenerated, TimeWritten, EventCode, EventType, Type, Category, CategoryString, SourceName, InsertionStrings, ComputerName, User, Message
	from Win32_NTlogEvent
	where timewritten >='$time'
	and LogFile = '$eventlog'
	and RecordNumber > $recordnumber
	"@ #this line must be un-recessed

	$EventQuery

	#logging the time the eventlog pull starts
	$computername + ", " + $eventlog + " pull beginning at " + (get-date)
	$LogResults = gwmi -computername $computername -Query $EventQuery \|
	select @{Label='EventLog';Expression={$_.Logfile}}, `
	RecordNumber, `
	@{LABEL="TimeGenerated"; EXPRESSION = {$_.convertToDateTime($_.TimeGenerated)}}, `
	@{LABEL="TimeWritten"; EXPRESSION = {$_.convertToDateTime($_.TimeWritten)}}, `
	@{Label='EventID';Expression={$_.EventCode}}, `
	#eventType DOES NOT MATCH existing! One has diff values than other. is 4 = information, 2= warning, 1 = error, 5 = audit failure
	EventType, `
	@{Label='EventTypeName';Expression={$_.Type}}, `
	@{Label='EventCategory';Expression={$_.Category}}, `
	@{Label='EventCategoryName';Expression={$_.CategoryString}}, ` #"None" in old, blank here
	SourceName, ` #"Perflib" in old, Microsoft-Windows-Perflib here
	@{name='Strings';Expression={ $_.InsertionStrings -join '\|'}}, ` #Appears to match!
	@{name='ComputerName';Expression={$_.ComputerName -replace "\..*",""}}, ` #remove the domain name (.blah.com)
	@{Label='SID';Expression={$_.User}}, `
	@{Label='Message';Expression={$_.Message}} \|out-datatable

	#17s/18s with just filter on timewritten and logfile (two tests, mem-pr-repl-01)
	#18s/18s including recordnumber (two tests, mem-pr-repl-01)

	#Why no results on the console?
	#It's due to runspace (aka split-job)

	#logging the time the eventlog pull ends
	$computername + ", " + $eventlog + " pull ending at " + (get-date)
	}

	#Write to the table, so that we can act upon it using the SP
	Write-DataTable -ServerInstance $server_repository -Database $database_repository -TableName EventLog_Staged_PoSH_insert -Data $LogResults

	#logging the time the eventlog write finishes
	$computername + ", " + $eventlog + " write to staged ending at " + (get-date)
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "EXEC Eventlog_Blacklist_Removal @ComputerName = '$computername', @EventLog = '$eventlog'"

	#logging the time the blacklist SP ends
	$computername + ", " + $eventlog + " blacklist removal ending at " + (get-date)
	}
	catch
	{
	#throw $_.Exception.Message #doesn't remove enough crap
	Write-Error $_.Exception.Message #-ErrorAction Stop I dont want to stop it, just throw an error.
	}

	#Now, run a (new for this posh version) stored procedure to get it into EventLog_YYYYMM

	}
	} -MaxDuration 3200 -MaxPipelines 8 -Variable server_repository, database_repository -NoProgress
	#the variable above allows the split-job to read from outside the scope and put it inside
	#max of 10 minutes because some will actually take that long; may need to set to 15-20!.
	#as you run it initially, it will take the full MaxDuration to get everything downloaded. After several times, will settle down.
	#Perms needed – add whatever credential you’re using to “Distributed COM users group”
	# and add user to permissions on the wmi namespace “CIMV2” on each server with permissions “Allow” on “Remote Enabled”

	write-host (get-date)

view raw EventLog_Capture.ps1 hosted with ❤ by GitHub

Finally, because it's so long: the tables & views involved.
Don't forget: you have to issue: "sp_fulltext_database 'enable' " first, and make sure fulltext is enabled Otherwise it's SLOW.

	USE [EventLog_Tracking]
	GO
	/**** Object: FullTextCatalog [Eventlog_Blacklist] Script Date: 5/12/2015 4:02:38 PM ****/
	CREATE FULLTEXT CATALOG [Eventlog_Blacklist]WITH ACCENT_SENSITIVITY = ON
	AS DEFAULT

	GO
	/**** Object: Table [dbo].[EventLog_Blacklist_Calls] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLog_Blacklist_Calls](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[computername] [varchar](255) NULL,
	[eventlog] [varchar](255) NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLog___inser__56B7027F] DEFAULT (getdate()),
	[max_recordnumber] [bigint] NULL,
	[inserted_dt_staging] [datetime] NULL,
	CONSTRAINT [PK__EventLog__3214EC2754CEBA0D] PRIMARY KEY CLUSTERED
	(
	[ID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: Table [dbo].[EventLog_Daily] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLog_Daily](
	[EventLog] [varchar](255) NULL,
	[RecordNumber] [int] NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) NULL,
	[SourceName] [varchar](255) NULL,
	[Strings] [varchar](255) NULL,
	[ComputerName] [varchar](255) NULL,
	[SID] [varchar](255) NULL,
	[Message] [varchar](255) NULL
	) ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: Table [dbo].[EventLog_EventBlacklist] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLog_EventBlacklist](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Event_Name] [varchar](255) NOT NULL,
	[Server_Name] [sysname] NULL,
	[Event_Description] [varchar](50) NULL,
	[is_active] [bit] NULL,
	CONSTRAINT [PK__EventLog_EventBl__07020F21] PRIMARY KEY CLUSTERED
	(
	[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: Table [dbo].[EventLog_Servers] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLog_Servers](
	[Server_Name] [varchar](30) NOT NULL,
	[Is_Active] [bit] NULL,
	[Application_RecordCount] [int] NULL,
	[System_RecordCount] [int] NULL,
	[Application_Last_Inserted] [smalldatetime] NULL,
	[System_Last_Inserted] [smalldatetime] NULL,
	CONSTRAINT [PK__EventLog_Servers__0CBAE877] PRIMARY KEY CLUSTERED
	(
	[Server_Name] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: Table [dbo].[EventLog_Staged_Errors] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLog_Staged_Errors](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[ErrorState] [int] NULL,
	[ErrorSeverity] [int] NULL,
	[errormessage] [nvarchar](4000) NULL,
	[computername] [varchar](255) NULL,
	[eventlog] [varchar](255) NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLog___inser__7C729713] DEFAULT (getdate()),
	CONSTRAINT [PK__EventLog__3214EC271E72A95C] PRIMARY KEY CLUSTERED
	(
	[ID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: Table [dbo].[EventLog_Staged_PoSH] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLog_Staged_PoSH](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EventLog] [varchar](255) NOT NULL,
	[RecordNumber] [bigint] NOT NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) NULL,
	[SourceName] [varchar](255) NULL,
	[Strings] [varchar](max) NULL,
	[ComputerName] [varchar](255) NOT NULL,
	[SID] [varchar](255) NULL,
	[Message] [varchar](max) NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLog___inser__575555A6] DEFAULT (getdate()),
	CONSTRAINT [PK__EventLog_Staged_PoSH__RecordNumber] PRIMARY KEY CLUSTERED
	(
	[ID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: Table [dbo].[EventLogs_To_Get] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[EventLogs_To_Get](
	[tinyint] [int] IDENTITY(1,1) NOT NULL,
	[EventLog] [varchar](30) NULL,
	[days_back_to_get] [tinyint] NULL,
	[minimum_recordnumber] [bigint] NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLogs__inser__39F9FCE9] DEFAULT (getdate()),
	CONSTRAINT [PK__EventLog__8EC24DE83811B477] PRIMARY KEY CLUSTERED
	(
	[tinyint] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	GO
	SET ANSI_PADDING OFF
	GO
	/**** Object: View [dbo].[EventLog_Details] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO

	CREATE VIEW [dbo].[EventLog_Details] AS
	SELECT EventLog,
	RecordNumber,
	TimeGenerated,
	TimeWritten,
	EventID,
	EventType,
	EventTypeName,
	EventCategory,
	EventCategoryName,
	SourceName,
	Strings,
	ComputerName,
	SID,
	Message, insert_datetime FROM EventLog_201504
	UNION ALL
	SELECT EventLog,
	RecordNumber,
	TimeGenerated,
	TimeWritten,
	EventID,
	EventType,
	EventTypeName,
	EventCategory,
	EventCategoryName,
	SourceName,
	Strings,
	ComputerName,
	SID,
	Message, insert_datetime FROM EventLog_201505


	GO
	/**** Object: View [dbo].[EventLog_Staged_PoSH_Insert] Script Date: 5/12/2015 4:02:38 PM ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE VIEW [dbo].[EventLog_Staged_PoSH_Insert]
	AS
	SELECT EventLog,
	RecordNumber,
	TimeGenerated,
	TimeWritten,
	EventID,
	EventType,
	EventTypeName,
	EventCategory,
	EventCategoryName,
	SourceName,
	Strings,
	ComputerName,
	SID,
	Message FROM EventLog_Staged_PoSH

	GO
	SET ANSI_PADDING ON

	GO
	/**** Object: Index [ncidx__EventLog_Staged_PoSH__computername__eventlog] Script Date: 5/12/2015 4:02:38 PM ****/
	CREATE NONCLUSTERED INDEX [ncidx__EventLog_Staged_PoSH__computername__eventlog] ON [dbo].[EventLog_Staged_PoSH]
	(
	[ComputerName] ASC,
	[EventLog] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	/**** Object: FullTextIndex Script Date: 5/12/2015 4:02:38 PM ****/
	CREATE FULLTEXT INDEX ON [dbo].[EventLog_Staged_PoSH](
	[Message] LANGUAGE 'English')
	KEY INDEX [PK__EventLog_Staged_PoSH__RecordNumber]ON ([Eventlog_Blacklist], FILEGROUP [PRIMARY])
	WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)


	GO

view raw EventLog_Capture_tables_views.sql hosted with ❤ by GitHub

Finally, some default records for the tables:
EventLogs_To_Get:

SELECT N'1' AS [tinyint], N'Application' AS [EventLog], N'1' AS [days_back_to_get], N'1' AS [minimum_recordnumber], N'2015-04-28 16:36:34.470' AS [insert_datetime] UNION ALL
SELECT N'2' AS [tinyint], N'System' AS [EventLog], N'1' AS [days_back_to_get], N'1' AS [minimum_recordnumber], N'2015-04-28 16:36:40.277' AS [insert_datetime] ) t;

Some Blacklist filter samples: remember this uses fulltext indexing. You could probably get fancier on the match. Not doing that yet.

The WinHTTP Web Proxy Auto-Discovery Service service
WinHttpAutoProxySvc
Database backed up. Database
The system uptime is
Configuration option % changed from 1 to 1. Run the RECONFIGURE statement to install.
CHECKDB for database % finished without errors
Package % finished successfully.
Package % started.

The Baking DBA - select * from flour

Tuesday, May 12, 2015

[Powershell] EVENTLOG_TRACKING - Capturing multiple servers' Event Logs to a database

No comments:

Blog Archive