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"


I open up the file in vi and delete the first line of field delimiters. Next I create a schema for Postgres. A more astute Postgres user will transfer that date time format less generically. The Message field is often so much text that it requires the "text" datatype:

CREATE TABLE Events (
Index INT,
RecordID  INT,
ID         INT,
TimeCreated Varchar(80),
Message Text
)
;

COPY Events FROM ’Archive-Security-2014-03-04-21-42-06-630.csv’ (FORMAT CSV);

Select Distinct(ID) from Events ORDER BY ID;
Select ID,COUNT(ID) AS IDcount FROM Events GROUP BY ID  ORDER BY IDCount DESC;


Select ID,COUNT(ID) AS IDcount FROM Events GROUP BY ID  ORDER BY IDCount DESC;
  id  | idcount
------+---------
 5156 |  207448
 5158 |  170988
 5157 |   50158
 4688 |    7308
 5154 |     418
 4907 |     306
 4624 |     239
 4672 |     194
 5441 |      86
 4634 |      78
 5446 |      60
 4776 |      47
 4648 |      41
 4801 |      39
 4800 |      38
 5442 |      14
 5031 |      12
 5444 |      12
 5440 |       8
 4625 |       6
 4905 |       4
 5450 |       4
 5448 |       4
 4904 |       4
 4902 |       2
 4608 |       2
 5449 |       2
 1100 |       1
 1104 |       1
 1101 |       1
 4647 |       1
(31 rows)

to be continued

No comments:

Post a Comment