Sunday, March 16, 2014

Avoiding XPath: Part VI

under construction

So this piece will begin a discussion about grokking Windows Security Event Logs inside PSQL (Postgres).  We recall that to convert your EVTX archived security logs to CSV we need a Powershell function as below:

Function Convert-Logs3 {
[cmdletbinding()]
Param(
$filelist=$NULL
)
$filelist | foreach-object {
Get-WinEvent -Path "$PSItem"| Select RecordID,ID,TimeCreated, Message | export-csv -notypeinformation -path $(write "$PSItem.csv");
[System.gc]::collect();
}
}


Alternatively, you can export the existing ("live") security log to CSV. Once I have the CSV file, I clean up the message field with R 3.0:


setwd("C:/Windows/System32/winevt/Logs/")
# read.csv("Archive-Security-2014-03-04-21-42-06-630.evtx.csv", as.is=TRUE)
d <- read.csv("Archive-Security-2014-03-04-21-42-06-630.evtx.csv", as.is=TRUE)
h <- gsub('\n\t',' ',d$Message,fixed=TRUE)
h <- gsub('\n\n',' ',h,fixed=TRUE)
h <- gsub('\t\t',' ',h,fixed=TRUE)
h <- gsub('\n',' ',h,fixed=TRUE)
h <- gsub('\t',' ',h,fixed=TRUE)
h <- gsub('%%','',h,fixed=TRUE)
d$Message <- h

and then...I write out the file to be imported into Postgres:

write-csv(d, "Archive-Security-2014-03-04-21-42-06-630.csv")

With the row headings, a sample entry is:


"","RecordId","Id","TimeCreated","Message"
"437525",66166003,5158,"2/16/2014 7:42:16 PM","The Windows Filtering Platform has permitted a bind to a local port. Application Information: Process ID: 1280 Application Name: \device\harddiskvolume3\windows\system32\svchost.exe Network Information: Source Address: 0.0.0.0 Source Port: 51986 Protocol: 17 Filter Information: Filter Run-Time ID: 0 Layer Name: Resource Assignment Layer Run-Time ID: 36"
"437526",66166002,5156,"2/16/2014 7:42:16 PM","The Windows Filtering Platform has permitted a connection. Application Information: Process ID: 5072 Application Name: \device\harddiskvolume3\windows\system32\svchost.exe Network Information: Direction: Inbound Source Address: 239.255.255.250 Source Port: 1900 Destination Address: 127.0.0.1 Destination Port: 58710 Protocol: 17 Filter Information: Filter Run-Time ID: 125302 Layer Name: Receive/Accept Layer Run-Time ID: 44"