Surprisingly this does not seem to be an overly popular thing to attempt as finding information on it was quite difficult.
At windowsecurity.com I found an article that explained how to setup Log Subscriptions, a feature which Microsoft has included in their operating systems beginning with Vista. In summary it had me:
1. open an elevated command prompt (run as administrator)
2. on the central aggregator machine
2a. run the command "winrm qc -q"
2b. run the command "wecutil qc /q"
2c. open up the event viewer and create a new subscription under "Subscriptions"
3. on each client machine being subscribed to
3a. run the command "winrm qc -q"
Note: There are patches available for windows 2003 and windows XP that must be installed before they are able to participate as a subscriber or subscribed to machine.
The account used to setup the subscription on the aggregator machine must be added to the "Event Log Readers" group on the client machine. Using a domain admin account avoids this requirement.
Some logs, the Security log in particular, require extra permissions to subscribe to it. Even though the machine is accessed by a domain admin account the log is still being read by the local "Network Service" built-in account, so that account must also be added to the "Event Log Readers" local group on every client machine. I was made aware of this by a post on Microsoft's Technet website.
WINDOWS XP and 2003 NOTE:
WinRM has really come into its own in Windows Vista and later. However, it can be installed on windows XP and 2003 as well by downloading the patch from Microsoft Here, and the KB for it is Here.
Once installed it should be noted that the new "server" may be listening on port 80, which means you would need to setup a separate subscription on the aggregator machine for all such installs and change the default port it queries to port 80.
Also, there is no "Event Log Readers" group on the older OSs. In order to allow the security logs to be read on these older machines the registry needs to be modified for windows 2003 or the service needs to be run as Local System for windows XP as detailed here.
Another note for older machines: there are new logs that older machines do not support. If you try and create a subscription for logs that the older machines can't handle, the older machines will likely throw error 0x6 and not return any data at all.
STAGE 2
Of course the log viewer is pretty limited in storage and is certainly designed with reporting speed in mind. Which meant I needed to find some way to save the data elsewhere. The logical choice for me was Microsoft SQL Server since that is what my company uses.
After several days of pain and suffering learning Power Shell for the first time, I came up with the following script that was able to extract the data I cared about most from the ForwardedEvents log, upload it to SQL, and delete the log.
$WriteTableName = "RawLogs"
$ColumnsForQuery = "Level, EntryDate, Source, EventID, TaskCategory, LogName, Computer, TargetName, Message"
$ParamNames = "@Level, @EntryDate, @Source, @EventID, @TaskCategory, @LogName, @Computer, @TargetName, @Message"
$WriteConnectionString = "server=servername;Trusted_Connection=Yes;Database=EventLogs; Connection Timeout=120"
$WriteConn = New-Object System.Data.SqlClient.SqlConnection
$WriteConn.ConnectionString = $WriteConnectionString
$WriteConn.Open() | Out-Null
[string]$SQLQuery = ("INSERT INTO {0} ({1}) VALUES ({2})" -f
$WriteTableName,
$ColumnsForQuery,
$ParamNames
)
#CREATE TABLE [dbo].[RawLogs] (
# [Level] [varchar] (100) NULL ,
# [EntryDate] [varchar] (100) NULL ,
# [Source] [varchar] (500) NULL ,
# [EventID] [varchar] (100) NULL ,
# [TaskCategory] [varchar] (500) NULL ,
# [LogName] [varchar] (500) NULL ,
# [Computer] [varchar] (500) NULL ,
# [TargetName] [varchar] (500) NULL ,
# [Message] [varchar] (max) NULL
#) ON [PRIMARY]
#GO
# $yesterday = (Get-Date) - (New-TimeSpan -day 1)
#Get-WinEvent -logname "ForwardedEvents" | where {$_.timecreated -ge $yesterday} |
#Format-Table TimeCreated, ID, ProviderName, LevelDisplayName, Message -AutoSize -Wrap | out-file C:\ForwardedEvents.txt
# $events = Get-WinEvent -logname "ForwardedEvents" -MaxEvents 5 | where {$_.timecreated -ge $yesterday}
#time calculation = miliseconds * seconds * minutes * hours = 1000*60*60*12 = 12 hours
$query = '*[System[TimeCreated[timediff(@SystemTime) <= 43200000]]]' #43200000]]]'
[xml]$xmlevents = wevtutil qe ForwardedEvents /q:$query /e:Events
#$xmlevents.Events.Event | %{ $_.System } | select Computer | export-csv 'C:\ForwardedEvents.txt' -NoTypeInformation
#$xmlevents.Events.Event | select @{Name="EventID"; Expression={$_.System.EventID}},@{Name="Computer"; Expression={$_.System.Computer}},@{Name="Message"; Expression={$_.RenderingInfo.Message}} | export-csv 'C:\ForwardedEvents.txt' -NoTypeInformation
#@{Name="TargetName"; Expression={ $_.EventData.InnerXml.substring($_.EventData.InnerXml.indexOf('TargetUserName'),$_.EventData.InnerXml.indexOf('TargetUserName')+20) }},
$DataImport = $xmlevents.Events.Event | select @{Name="Level"; Expression={$_.System.Level}},
@{Name="EntryDate"; Expression={$_.System.TimeCreated.SystemTime}},
@{Name="Source"; Expression={$_.System.Provider.Name}},
@{Name="EventID"; Expression={$_.System.EventID}},
@{Name="TaskCategory"; Expression={$_.RenderingInfo.Task}},
@{Name="LogName"; Expression={$_.RenderingInfo.Channel}},
@{Name="Computer"; Expression={$_.System.Computer}},
@{Name="TargetName"; Expression={ $_.EventData.InnerXml.substring($_.EventData.InnerXml.indexOf('>', $_.EventData.InnerXml.indexOf('TargetUserName'))+1,$_.EventData.InnerXml.indexOf('<', $_.EventData.InnerXml.indexOf('TargetUserName'))-($_.EventData.InnerXml.indexOf('>', $_.EventData.InnerXml.indexOf('TargetUserName'))+1)) }},
@{Name="Message"; Expression={$_.RenderingInfo.Message}}
wevtutil.exe cl ForwardedEvents # erase the event log
#$DataImport
#Exit
ForEach($Obj in $DataImport)
{
$writeCmd = new-object System.Data.SqlClient.SqlCommand
$writecmd.Connection = $WriteConn
If ($Obj -ne $Null)
{
If ($Obj.Level -ne $Null -and $Obj.Level.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@Level", $Obj.Level) | out-null }
else { $writeCmd.Parameters.AddWithValue("@Level", [DBNull]::Value) | out-null }
If ($Obj.EntryDate -ne $Null -and $Obj.EntryDate.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@EntryDate", $Obj.EntryDate) | out-null }
else { $writeCmd.Parameters.AddWithValue("@EntryDate", [DBNull]::Value) | out-null }
If ($Obj.Source -ne $Null -and $Obj.Source.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@Source", $Obj.Source) | out-null }
else { $writeCmd.Parameters.AddWithValue("@Source", [DBNull]::Value) | out-null }
If ($Obj.EventID -ne $Null -and $Obj.EventID.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@EventID", $Obj.EventID) | out-null }
else { $writeCmd.Parameters.AddWithValue("@EventID", [DBNull]::Value) | out-null }
If ($Obj.TaskCategory -ne $Null -and $Obj.TaskCategory.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@TaskCategory", $Obj.TaskCategory) | out-null }
else { $writeCmd.Parameters.AddWithValue("@TaskCategory", [DBNull]::Value) | out-null }
If ($Obj.LogName -ne $Null -and $Obj.LogName.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@LogName", $Obj.LogName) | out-null }
else { $writeCmd.Parameters.AddWithValue("@LogName", [DBNull]::Value) | out-null }
If ($Obj.Computer -ne $Null -and $Obj.Computer.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@Computer", $Obj.Computer) | out-null }
else { $writeCmd.Parameters.AddWithValue("@Computer", [DBNull]::Value) | out-null }
If ($Obj.TargetName -ne $Null -and $Obj.TargetName.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@TargetName", $Obj.TargetName) | out-null }
else { $writeCmd.Parameters.AddWithValue("@TargetName", [DBNull]::Value) | out-null }
If ($Obj.Message -ne $Null -and $Obj.Message.GetType().ToString() -ne "System.Xml.XmlElement") { $writeCmd.Parameters.AddWithValue("@Message", $Obj.Message) | out-null }
else { $writeCmd.Parameters.AddWithValue("@Message", [DBNull]::Value) | out-null }
$writecmd.CommandText = $SQLQuery
$Null = $writecmd.ExecuteNonQuery()
}
}
$WriteConn.close()
This script will lose any events that come in between when the data is loaded into Power Shells memory and the next line where the log is truncated. It also doesn't recover any data lost due to SQL upload errors.
It is also important to note that this script must be run with elevated permissions in the task scheduler, otherwise it will fail to clear the event log on each run.
VMWARE NOTE:
I did notice an odd problem on windows 7 machines that were running VMWare player. When I tried to enable WinRM I got the error:
WinRM firewall exception will not work since one of the network connection types on this machine is set to Public
Unfortunately, on a domain connected machine, this setting can not be easily modified; fortunately I found a powershell script here that was able to do the trick for me:
$nlm = [Activator]::CreateInstance([Type]::GetTypeFromCLSID([Guid]"{DCB00C01-570F-4A9B-8D69-199FDBA5723B}"))
$connections = $nlm.getnetworkconnections()
$connections |foreach {
if ($_.getnetwork().getcategory() -eq 0)
{
$_.getnetwork().setcategory(1)
}
}
No comments:
Post a Comment