Avoiding XPath: Part VI

Updated some 1/11/2020 -RMF

So this piece will begin a discussion about grokking Windows Security Event Logs inside rdata.table and Postgres. Use auditpol to set up kernel logging. We recall that to convert your EVTX archived security logs to CSV we need a Powershell function as below:

# Powershell Memory and CPU intensive
Function Convert-Logs3 {
[cmdletbinding()]
Param(
$filelist=$NULL
)
$filelist | foreach-object {
#Note that I am only getting four columns of data
Get-WinEvent -Path "$PSItem"| Select RecordID,ID,TimeCreated, Message | export-csv -notypeinformation -path $(write "$PSItem.csv");
[System.gc]::collect();
}
}

Convert-Logs3 "Archive-Security-2019-12-23-04-25-01-062.evtx"

#R
library(data.table)
d <- fread("Archive-Security-2019-12-23-04-25-01-062.evtx.csv")
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)
h <- gsub('\r','',h,fixed=TRUE)
h <- gsub('\r \r','',h,fixed=TRUE)
d$Message <- h

This produces row headings with data types:

names(d)
[1] "RecordId"    "Id"          "TimeCreated" "Message"
 
d[,sapply(.SD,class)]
   RecordId          Id TimeCreated     Message
  "integer"   "integer" "character" "character" 


# where you can use sample rdata.table queries like this:

d[,.N,.(Id,Message=substr(Message,0,100))][order(-N)]
d[Id == 4688,.(Id,TimeCreated,Message=substr(Message,275,375))]
d[Id == 4672 & !duplicated(substr(TimeCreated,0,11)),
.(Id,TimeCreated,Date=substr(TimeCreated,0,11),Message=substr(Message,400,500))]
d[Id == 4624 & grepl("S-1-0-0",Message),.(Id,Message=substr(Message,200,400))]
d[Id ==  4907,.(Id,TimeCreated,paste0(substr(Message,0,50),substr(Message,250,375)))]

There is probably much more you can do with Security.evtx files with mlr3 or rdata.table

# and then...I write out the file to be imported into Postgres:
write-csv(d, "Archive-Security-2019-12-23-04-25-01-062.csv")


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-2019-12-23-04-25-01-062.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 ID Count 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