Querying Sysmon logs

[jnelson]

With the ability to query Windows Event Logs we can also query Sysmon logs as they show up in Event Viewer. This query is an example of what a query would look like, and in this case we are looking at network connections.

select datetime as 'datetime (UTC)', eventid,

  split(split(json_extract(windows_eventlog.data,'$.EventData.RuleName'),',',1),'=',1) as technique,

  split(split(json_extract(windows_eventlog.data,'$.EventData.RuleName'),',',0),'=',1) as technique_id,

  json_extract(windows_eventlog.data,'$.EventData.User') as 'username',

  json_extract(windows_eventlog.data,'$.EventData.Image') as 'process',

  json_extract(windows_eventlog.data,'$.EventData.ProcessId') as 'pid',

  json_extract(windows_eventlog.data,'$.EventData.SourceIp') as 'source_ip',

  json_extract(windows_eventlog.data,'$.EventData.SourcePort') as 'source_port',

  json_extract(windows_eventlog.data,'$.EventData.DestinationIp') as 'destination_ip',

  json_extract(windows_eventlog.data,'$.EventData.DestinationPort') as 'destination_port',

  json_extract(windows_eventlog.data,'$.EventData.Initiated') as 'initiated'

from windows_eventlog

where channel = 'Microsoft-Windows-Sysmon/Operational'

  and eventid ='3';

Here is what the data looks like:

  datetime (UTC) = 2022-01-07T20:30:32.1515030Z

         eventid = 3

       technique = Masquerading

    technique_id = T1036

        username = DESKTOP-030MC74\bit9se

         process = C:\Users\bit9se\AppData\Local\Microsoft\OneDrive\21.230.1107.0004\Microsoft.SharePoint.exe

             pid = 644

       source_ip = 192.168.230.128

     source_port = 49752

  destination_ip = 96.16.113.6

destination_port = 443

       initiated = true