Querying KQL is extremely powerful but sometimes when querying KQL there is a lack of contextual information. A common example of this is when searching Windows Event Log IDs. Generally, Microsoft do provide the Event Log Description, however, I have encountered in some organisations where they do not have Windows Active Directory deployed, despite having a sizeable corporate Windows estate; they used Samba instead.
As such, Samba does not provide the Windows Event Log Descriptions. To provide this contextualisation, we can leverage the externaldata function within Kusto Query Language (KQL).
externaldata (EventLogData:string) [@"<https://raw.githubusercontent.com/ezaspy/windows-event-id-list-csv/master/windows-event-id.csv>"] with (format="txt", ignoreFirstRecord=true) //reading each line as a string
| project EventLogData=parse_csv(EventLogData) // parsing string as csv
| project EventLogID=tostring(EventLogData[0]) // splitting the csv into columns
| join SecurityEvent on $left.EventLogID == $right.EventID | where EventID != ""
After invoking the function externaldata we define the variable name and the type with EventLogData and string, respectively. We now reference the name of the file we want to read, within the [].
Following this we type with and the format to read this data as plaintext using format=”txt”.
Given that it is a csv file, we typically have a header in the first line to denote the field names of the column values. This is invoked using ignoreFirstRecord=true.
After this, we leverage | project to output the variable EventLogData comprised of parse_csv(EventLogData) which converts the plain text we previously read as a csv format in Sentinel.
Every line is now interpreted as a single entry in an array - similar to a list in python - hence the use of EventLogData[0] which is then fed into the variable EventLogID as a string (using tostring function).
We can then perform a lookup which compares the values of one dataset against the value of another and return all fields form both if the comparison matches.
If, like when I tried to leverage the original repository https://github.com/PerryvandenHondel/windows-event-id-list-csv, the query returned blank - no results.
I viewed the file in the browser and it seemed syntactically correct…
https://raw.githubusercontent.com/PerryvandenHondel/windows-event-id-list-csv/master/windows-event-id.csv
… but I still received blank results.
I downloaded a copy of the file and opened in a Terminal.
ezaspy@Computer Downloads % head -n 5 windows-event-id.csv
??EVENT_ID,EVENT_DESCRIPTION,EVENT_SOURCE
1100,The event logging service has shut down,Windows
1101,Audit events have been dropped by the transport.,Windows
1102,The audit log was cleared,Windows
1104,The security Log is now full,Windows
ezaspy@Computer Downloads %
Notice the first few characters shown as ??. This is the issue. Thus, I forked the repository, removed these characters and used this as my reference file.