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")
