Avoiding XPath: Part I

Typically a Security Event Log entry contains a Message entry that is resistant to anything but an XPath query:

Index         : 65597968
TimeGenerated : 1/29/2014 1:32:01 PM
EventID       : 5158
Message       : The Windows Filtering Platform has permitted a bind to a local port.

                Application Information:
                    Process ID:        200
                    Application Name:    \device\harddiskvolume3\program files (x86)\google\chrome\application\chrome.exe

                Network Information:
                    Source Address:        ::
                    Source Port:        49837
                    Protocol:        17

                Filter Information:
                    Filter Run-Time ID:    0
                    Layer Name:        %%14608
                    Layer Run-Time ID:    38

We can shelve the XPath query by using a combination of Powershell and R to dump the Message field to a single line text entry in a CSV.  Using .NET interfaces to dump the first 1000 Security log entries:

#Powershell
# Using [System.Diagnostics.EventLog] for Powershell 3.0
# Clearing variable types
rv -ea 0 i;
rv -ea 0 var
$var=@("a","b","c","d","e"); foreach ($i in $var) {rv -ea 0 $i}
 #Creating $a specific to the 'GetEventLogs()'
 # method for [System.Diagnostics.EventLog]
 $a=[System.Diagnostics.EventLog]::GetEventLogs()
 # Creating $a as generic to the .NET class; Querying active
 # Eventlog for a local(or remote?)computer name:
 $a=[System.Diagnostics.EventLog]
 # Creating $B as the result of machine specific
 #'GetEventLogs()' query
 $b=$a::GetEventLogs("rmfvpc")
 # Choose the Security Log
 $C = $B | Where Log -eq Security
  # Returns select sorted information last 1000 entries
 $d=($c[0].get_Entries())[0..999]| Select Index,TimeGenerated,EventID,Message
 $d | export-csv Security.csv  -NoTypeInformation

Using R to strip all the returns and tabs and add all the Message Contents to a field in an R Dataframe. This also returns an SQL suitable CSV file:

# R
d <- read.csv("Security.csv")
#d$Message
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
d$Message[2]

[1] "The Windows Filtering Platform has permitted a bind to a local port. Application Information: Process ID: 200 Application Name: \\device\\harddiskvolume3\\program files (x86)\\google\\chrome\\application\\chrome.exe Network Information: Source Address: :: Source Port: 49837 Protocol: 17 Filter Information: Filter Run-Time ID: 0 Layer Name: 14608 Layer Run-Time ID: 38"

write.csv(d, "Security_rev.csv")


# Updating this R interface with data.table and lubridate and R 3.5

# R with data.table and lubridate
library(data.table)
library(lubridate)

d <- fread("Security.csv")
#d$Message
h <- gsub('\n\t',' ',d$Message,fixed=TRUE)
h <- gsub('\n\n',' ',h,fixed=TRUE)
h <- gsub('\t\t',' ',h,fixed=TRUE)
h <- gsub('\r\r',' ',h,fixed=TRUE)
h <- gsub('\n',' ',h,fixed=TRUE)
h <- gsub('\t',' ',h,fixed=TRUE)
h <- gsub('\r',' ',h,fixed=TRUE)
h <- gsub('%%','',h,fixed=TRUE)
h <- gsub('"','',h,fixed=TRUE)
d$Message <- h

d[,.(Time=mdy_hms(TimeGenerated),Message=substr(Message,1,200))]

d[,.N,.(Message=substr(Message,1,40))][order(-N)]
                                     Message    N
 1:                                          9367
 2: The Windows Filtering Platform has permi  536
 3: An account was successfully logged on.     37
 4: Special privileges assigned to new logon   34
 5: A new process has been created.    Creat   11
 6: A security-enabled local group membershi    5
 7: A logon was attempted using explicit cre    3
 8: A primary token was assigned to process.    1
 9: Boot Configuration Data loaded.    Subje    1
10: Windows is starting up.    This event is    1
11: The Per-user audit policy table was crea    1
12: The Windows Firewall Driver started succ    1
13: The Windows Firewall service started suc    1

Now back to Powershell to query for specific terms as opposed to EventIDs:

# Powershell
$e = import-csv Security_rev.csv
 $e | ? {$_.Message -match "Destination Port: 443"} | ft -auto -wrap | more

H1  Index    TimeGenerated        EventID Message
--  -----    -------------        ------- -------
6   65597972 1/29/2014 1:32:01 PM 5156    The Windows Filtering Platform has permitted a connection. Application Information: Process ID: 200 Application Name: \device\harddiskvolume3\program files(x86)\google\chrome\application\chrome.exe Network Information: Direction: 14593 Source Address: 192.168.0.11 Source Port: 16559 Destination Address: 173.194.33.22 Destination Port: 443 Protocol: 6 Filter Information: Filter Run-Time ID: 159420 Layer Name: 14611 Layer Run-Time ID: 48

12  65597978 1/29/2014 1:32:01 PM 5156    The Windows Filtering Platform has permitted a connection. Application Information: Process ID: 200 Application Name: \device\harddiskvolume3\program files (x86)\google\chrome\application\chrome.exe Network Information: Direction: 14593 Source Address: 192.168.0.11 Source Port: 16560 Destination Address: 74.125.28.106 Destination Port: 443 Protocol: 6 Filter Information: Filter Run-Time ID: 159420 Layer Name: 14611 Layer Run-Time ID: 48
....

Some Extras:

# Powershell Returns First and Last Events and days of log
 $lc = $($c[0].Entries.count - 1)
 $c[0].get_Entries()[0,$($c[0].Entries.count - $lc)]
 # Creates a DateTime variable;Returns number of days
 # between first and last events
 ($c[0].get_Entries()[0,$lc]).TimeGenerated
 $TG=($c[0].get_Entries()[0,$lc]).TimeGenerated
 $TG  | gm -s
 $TG[1]-$TG[0]
 ($TG[1]-$TG[0]).Days

# Some Search Terms for EventIDs 515X
Application Information:
        Process ID:
        Application Name:
Network Information:
        Source Address:
        Source Port:
        Protocol:
Filter Information:
        Filter Run-Time ID:
        Layer Name:
        Layer Run-Time ID:

#Some Further R queries and Charts

library(data.table)
e <- data.table(d)
setkey(e,Index)

e[EventID=="4688"]
f <- data.frame(e[EventID=="4688"])
nrow(f)

e[EventID=="5157"]
h <- data.frame(e[EventID=="5157"])
nrow(h)

plot(f[2])
plot(h[2])