Forwarded Events & Log Parser
I would like to be able to query an event log using Log Parser (or simular tool) and store the query into an SQL Database - simular to this post: http://myitforum.com/cs2/blogs/maikkoster/archive/2012/09/28/compare-active-directory-computer-accounts-with-configuration-manager-resources.aspx The events I want are in the ForwardedEvents log file but when I run the query against the database I don't get anything returned. Does anyone have any ideas how I can query this event log on a Windows Server 2008 R2 server? Cheers Tom
September 30th, 2012 9:16pm

Thanks but the tools you have given me are focused around the IIS stuff - LogParser isn't just used for IIS. In my question I've asked how to use it in the ForwardedEvents log.
Free Windows Admin Tool Kit Click here and download it now
October 1st, 2012 9:42am

Hi, can you query other event log such as system event log and application event log? Or try to save the forwarded event log to another specific log for query agian? To avoid if any corruption event log, you can also try to cleanup all the forwarded event logs and try to query the new logs agian? I think there is no diffrent on the format between forwarded event log file and other eveng log file.Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
October 16th, 2012 5:57pm

Hi, just following up to check if you have any updates on this issue.Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2012 4:46am

Hi Kevin, Actually no, I haven't got any further with this. I have tried getting examples of Application, System and Security logs to be saved into an SQL table but I can't get that to work either. I'm using Server 2008 R2 with an SQL Server 2008 database and seen a few exmaples online but none of them seem to work. I've played around with variations of the commands but none of them take... I forget which sites I've looked at but if people have examples I'll be more than willing to try them out. One complication is that some of the data is within the "Details tab" and not within the normal fields in an event...
October 25th, 2012 9:51am

Hi Tom, just checking if you are still working on this issue. i am not sure if the event log database will match the SQL database. maybe need some help on the translation and permission setting.Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2012 6:38pm

Hi Kevin, Sadly I haven't found a solution to it. I am looking for a tool to export a range of events (eg from today) to an SQL database, not really taking them directly out of the built-in DB where they are stored today within the OS. It seems to be possible to take a few of the "easy" events which can be seen in the UI, but there is more detailed held in the "XML view" which is what I am really after to export in any way possible... So I am still looking for help on this if anyone has a solution :)
November 19th, 2012 1:16pm

Maybe you can refer to this workaround: save the logs as .csv file, then it is essy to import it to a SQL database. Regarding the centralized management, you can check if Microsoft System Center can achieve you goal.Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Free Windows Admin Tool Kit Click here and download it now
December 5th, 2012 4:04pm

Maybe you can refer to this workaround: save the logs as .csv file, then it is essy to import it to a SQL database. Regarding the centralized management, you can check if Microsoft System Center can achieve you goal.Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
December 5th, 2012 4:04pm

just check if you have any updates on this issue.Please remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2013 3:14am

I was able to successfully import Forwarded Events into SQL Server on Windows 2012 using the following code: SQL: CREATE DATABASE EventCollections GO USE EventCollections GO -- the table name loosely relates to the name of my Win Event Subscription name CREATE TABLE [dbo].[GeneralEvents]( [Id] [int] NULL, [LevelDisplayName] [varchar](50) NULL, [LogName] [varchar](50) NULL, [MachineName] [varchar](255) NULL, [Message] [varchar](max) NULL, [ProviderName] [varchar](255) NULL, [RecordID] [bigint] NULL, [TaskDisplayName] [varchar](50) NULL, [TimeCreated] [smalldatetime] NULL ) -- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents] ( [RecordID] ASC, [MachineName] ASC, [LogName] ASC ) WITH (IGNORE_DUP_KEY = ON) GO PowerShell initial import: $events = Get-WinEvent ForwardedEvents | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated $connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;" $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString $bulkCopy.DestinationTableName = "GeneralEvents" $dt = New-Object "System.Data.DataTable" # build the datatable $cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name foreach ($col in $cols) {$null = $dt.Columns.Add($col)} foreach ($event in $events) { $row = $dt.NewRow() foreach ($col in $cols) { $row.Item($col) = $event.$col } $dt.Rows.Add($row) } # Write to the database! $bulkCopy.WriteToServer($dt) And PowerShell recurring hourly import: # While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes. # This allows the script to run for 5 minutes without any missing any events. Because we setup the # table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database. $xml = @' <QueryList> <Query Id="0" Path="ForwardedEvents"> <Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) &lt;= 3900000]]]</Select> </Query> </QueryList> '@ $events = Get-WinEvent -FilterXml $xml | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated $connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;" $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString $bulkCopy.DestinationTableName = "GeneralEvents" $dt = New-Object "System.Data.DataTable" # build the datatable $cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name foreach ($col in $cols) {$null = $dt.Columns.Add($col)} foreach ($event in $events) { $row = $dt.NewRow() foreach ($col in $cols) { $row.Item($col) = $event.$col } $dt.Rows.Add($row) } # Write to the database! $bulkCopy.WriteToServer($dt) I tried this same code on Windows Server 2008 R2 and had far more NULLs returning from Get-WinEvent. This is a known issue, but seems to only impact some users under some circumstances. I wrote up a blog post detailing this a little more if you'd like additional insight. If you do end up getting this to work on Win2k8 R2, please let me know! I'd like to know what .NET version you're at, which Service Pack you're at, etc. Chrissy LeMaire, MCITP, C|EH Blog: http://blog.netnerds.net Twitter: @cl
March 20th, 2013 7:43pm

I was able to successfully import Forwarded Events into SQL Server on Windows 2012 using the following code: SQL: CREATE DATABASE EventCollections GO USE EventCollections GO -- the table name loosely relates to the name of my Win Event Subscription name CREATE TABLE [dbo].[GeneralEvents]( [Id] [int] NULL, [LevelDisplayName] [varchar](50) NULL, [LogName] [varchar](50) NULL, [MachineName] [varchar](255) NULL, [Message] [varchar](max) NULL, [ProviderName] [varchar](255) NULL, [RecordID] [bigint] NULL, [TaskDisplayName] [varchar](50) NULL, [TimeCreated] [smalldatetime] NULL ) -- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents] ( [RecordID] ASC, [MachineName] ASC, [LogName] ASC ) WITH (IGNORE_DUP_KEY = ON) GO PowerShell initial import: $events = Get-WinEvent ForwardedEvents | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated $connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;" $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString $bulkCopy.DestinationTableName = "GeneralEvents" $dt = New-Object "System.Data.DataTable" # build the datatable $cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name foreach ($col in $cols) {$null = $dt.Columns.Add($col)} foreach ($event in $events) { $row = $dt.NewRow() foreach ($col in $cols) { $row.Item($col) = $event.$col } $dt.Rows.Add($row) } # Write to the database! $bulkCopy.WriteToServer($dt) And PowerShell recurring hourly import: # While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes. # This allows the script to run for 5 minutes without any missing any events. Because we setup the # table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database. $xml = @' <QueryList> <Query Id="0" Path="ForwardedEvents"> <Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) &lt;= 3900000]]]</Select> </Query> </QueryList> '@ $events = Get-WinEvent -FilterXml $xml | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated $connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;" $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString $bulkCopy.DestinationTableName = "GeneralEvents" $dt = New-Object "System.Data.DataTable" # build the datatable $cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name foreach ($col in $cols) {$null = $dt.Columns.Add($col)} foreach ($event in $events) { $row = $dt.NewRow() foreach ($col in $cols) { $row.Item($col) = $event.$col } $dt.Rows.Add($row) } # Write to the database! $bulkCopy.WriteToServer($dt) I tried this same code on Windows Server 2008 R2 and had far more NULLs returning from Get-WinEvent. This is a known issue, but seems to only impact some users under some circumstances. I wrote up a blog post detailing this a little more if you'd like additional insight. If you do end up getting this to work on Win2k8 R2, please let me know! I'd like to know what .NET version you're at, which Service Pack you're at, etc. Chrissy LeMaire, MCITP, C|EH Blog: http://blog.netnerds.net Twitter: @cl
Free Windows Admin Tool Kit Click here and download it now
March 22nd, 2013 12:46pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics