I have the below sample botsv3 sample data set which is sysmon in xml format. I need to convert that into json formatted events. Sample Current XML format - code block :1 <Event xmlns='http://schemas.microsoft.com/win/2004/08/events/event'>
<System>
<Provider Name='Microsoft-Windows-Sysmon' Guid='{5770385F-C22A-43E0-BF4C-06F5698FFBD9}' />
<EventID>1</EventID>
<Version>5</Version>
<Level>4</Level>
<Task>1</Task>
<Opcode>0</Opcode>
<Keywords>0x8000000000000000</Keywords>
<TimeCreated SystemTime='2023-05-21T13:35:45.561534700Z' />
<EventRecordID>36885</EventRecordID>
<Correlation />
<Execution ProcessID='3204' ThreadID='5508' />
<Channel>Microsoft-Windows-Sysmon/Operational</Channel>
<Computer>BGIST-L.froth.ly</Computer>
<Security UserID='S-1-5-18' />
</System>
<EventData>
<Data Name='UtcTime'>2023-05-21 15:17:59.931</Data>
<Data Name='ProcessGuid'>{EBF7A186-1A1C-5B59-0000-0010732E0200}</Data>
<Data Name='ProcessId'>2684</Data>
<Data Name='Image'>C:\Windows\System32\svchost.exe</Data>
<Data Name='FileVersion'>10.0.17134.1 (WinBuild.160101.0800)</Data>
<Data Name='Description'>Host Process for Windows Services</Data>
<Data Name='Product'>Microsoft® Windows® Operating System</Data>
<Data Name='Company'>Microsoft Corporation</Data>
<Data Name='CommandLine'>c:\windows\system32\svchost.exe -k networkservice -p -s CryptSvc</Data>
<Data Name='CurrentDirectory'>C:\Windows\system32\</Data>
<Data Name='User'>NT AUTHORITY\NETWORK SERVICE</Data>
<Data Name='LogonGuid'>{EBF7A186-1A19-5B59-0000-0020E4030000}</Data>
<Data Name='LogonId'>0x3e4</Data>
<Data Name='TerminalSessionId'>0</Data>
<Data Name='IntegrityLevel'>System</Data>
<Data Name='Hashes'>
MD5=32569E403279B3FD2EDB7EBD036273FA,SHA256=C9A28DC8004C3E043CBF8E3A194FDA2B756CE90740DF2175488337281B485F69</Data>
<Data Name='ParentProcessGuid'>{EBF7A186-1A18-5B59-0000-0010CEA80000}</Data>
<Data Name='ParentProcessId'>608</Data>
<Data Name='ParentImage'>C:\Windows\System32\services.exe</Data>
<Data Name='ParentCommandLine'>C:\Windows\system32\services.exe</Data>
</EventData>
</Event>" Expected in JSON format as below. I have only included fields that are actually needed. code block : 2 {
"ID": 1,
"Timestamp": "2023-05-18T05:07:59.940594300Z",
"EventData": {
"FileVersion": "10.0.17134.1 (WinBuild.160101.0800)",
"Company": "Microsoft Corporation",
"TerminalSessionId": 0,
"UtcTime": "2018-08-20 15:18:59.929",
"Product": "Microsoft® Windows® Operating System",
"LogonId": "0x3e7",
"Description": "Find String (QGREP) Utility",
"OriginalFileName": "findstr.exe",
"Hashes": "MD5=BCC8F29B929DABF5489C9BE6587FF66D,SHA256=40F83CE0B6E1C894AB766591574ABD5B6780028C874410F2EC224300DF443C81",
"ParentProcessId": "5428",
"ParentCommandLine": "C:\\Windows\\system32\\cmd.exe /c netstat -nao | findstr /r \"LISTENING\"",
"ProcessGuid": "{EBF7A186-0B7B-5B59-0000-001044A3CD01}",
"ProcessId": "6236",
"Image": "C:\\Windows\\System32\\findstr.exe",
"User": "NT AUTHORITY\\SYSTEM",
"LogonGuid": "{EBF7A186-AB15-5B58-0000-0020E7030000}",
"LogonGuid": "{EBF7A186-AB15-5B58-0000-0020E7030000}",
"IntegrityLevel": "System",
"ParentProcessGuid": "{EBF7A186-0B7B-5B59-0000-0010249FCD01}",
"ParentImage": "C:\\Windows\\System32\\cmd.exe",
"RuleName": "",
"CommandLine": "findstr /r \"LISTENING\"",
"CurrentDirectory": "C:\\Windows\\system32\\"
},
"Hostname": "BGIST-L.froth.ly",
} The key fields are EventID, Computer and entire EventData block What I have tried so far Used | tojson command but it didn't created the nested EventData block. It just extracted all field-value pairs as individual objects then tried the below spl but it has fields hardcoded which is not desirable. We want them to be dynamically added to EventData block. The below SPL also led to lot of fields with null values as not all Event IDs had the same fields for obvious reasons. index=main
| rename EventID as ID Computer as Hostname eventtype as EventType
| fillnull value=""
| eval EventData=json_object("FileVersion", FileVersion, "Company", Company, "TerminalSessionId", TerminalSessionId, "UtcTime", UtcTime, "Product", Product,"LogonId",LogonId,"Description",Description,"Hashes",Hashes,"ParentProcessId",ParentProcessId,"ParentCommandLine",ParentCommandLine,"ProcessGuid",ProcessGuid,"ProcessId",ProcessId,"Image",Image,"User",User,"LogonGuid",LogonGuid,"IntegrityLevel",IntegrityLevel,"ParentProcessGuid",ParentProcessGuid,"ParentImage",ParentImage,"CommandLine",CommandLine,"CurrentDirectory",CurrentDirectory),_raw=json_set_exact(json_object(), "ID", ID, "Hostname", Hostname, "EventData", json_extract(EventData)) I tried the below query as well index=botsv3 sourcetype=xmlwineventlog EventID=1
| spath
| tojson Which gave the following result where Eventdata atleast is still in block but both the fields appear as values in Event.EventData.Data field and values in Event.EventData.Data{@Name}. I need the values in Event.EventData.Data{@Name} field to appear as values of corresponding fields which appear as values in the Event.EventData.Data field and all that as part of EventData nested json block. Basically as shown in code block : 2 Any help on this would be highly appreciated!
... View more