Extracting information nested in a JSON-like format

[husse_wl]

Hello,

I'm designing some searches from O365 logs that have a complicated field called "Data", depending on the workload. I have 2 cases about this.

In the first case,  with the Workload of SecurityCompliance, the field Data comes like this:

{"etype":"MaliciousUrl",
"aii":"90224859-1bce-4d99-a94c-08d92a7c3325",
"eid":"http://someURL.com/uiEWUIWER8",
"tid":"672rerce-0ccd-4867-8090-e8bb889999ae0",
"ts":"2021-06-09T02:50:41.0000000Z",
"te":"2021-06-09T02:50:41.0000000Z",
"trc":"xxxx@yyyy.com",
"tdc":"1",
"at":"2021-06-09T02:50:41.0000000Z",
"dm":"Office ATP Safe Links",
"ot":"Not Applicable",
"od":"Not Applicable",
"md":"2021-06-08T12:52:04.3356492Z",
"lon":"MaliciousUrlClick"}

And what I've tried for extracting the field "etype", for example, is:

MY SEARCH | spath Data output=Data | table Data.etype
MY SEARCH | spath Data  | table Data{}.etype
MY SEARCH | spath | table Data.etype

And that kind of things... Didn't work

And in the 2nd case, with the Workload=AirInvestigation. I have kind of the same data structure but with lots of lines, like 190 lines (impossible to copy here without messing it up), and with array fields, grouping items with [ ]  instead of { }  and that kind of things.

If you could help me, I would thank you so much.

Thanks for your assistance and have a nice day.

Regards

[husse_wl]

Hi Kamlesh, thank you for your answer.

The problem is that the field values can change so establishing a variable with every values is non-viable.

[kamlesh_vaghela]

@husse_wl 

If you have single JSON data then you can try this.

| makeresults | eval _raw="
{\"etype\":\"MaliciousUrl\",
\"aii\":\"90224859-1bce-4d99-a94c-08d92a7c3325\",
\"eid\":\"http://someURL.com/uiEWUIWER8\",
\"tid\":\"672rerce-0ccd-4867-8090-e8bb889999ae0\",
\"ts\":\"2021-06-09T02:50:41.0000000Z\",
\"te\":\"2021-06-09T02:50:41.0000000Z\",
\"trc\":\"xxxx@yyyy.com\",
\"tdc\":\"1\",
\"at\":\"2021-06-09T02:50:41.0000000Z\",
\"dm\":\"Office ATP Safe Links\",
\"ot\":\"Not Applicable\",
\"od\":\"Not Applicable\",
\"md\":\"2021-06-08T12:52:04.3356492Z\",
\"lon\":\"MaliciousUrlClick\"}" | spath | table etype

If you have arrays of JSON data then use this.

| makeresults | eval _raw="[{\"etype\": \"MaliciousUrl\",\"aii\": \"90224859-1bce-4d99-a94c-08d92a7c3325\",\"eid\": \"http://someURL.com/uiEWUIWER8\",\"tid\": \"672rerce-0ccd-4867-8090-e8bb889999ae0\",\"ts\": \"2021-06-09T02:50:41.0000000Z\",\"te\": \"2021-06-09T02:50:41.0000000Z\",\"trc\": \"xxxx@yyyy.com\",\"tdc\": \"1\",\"at\": \"2021-06-09T02:50:41.0000000Z\",\"dm\": \"Office ATP Safe Links\",\"ot\": \"Not Applicable\",\"od\": \"Not Applicable\",\"md\": \"2021-06-08T12:52:04.3356492Z\",\"lon\": \"MaliciousUrlClick\"},{\"etype\": \"MaliciousUrl\",\"aii\": \"90224859-1bce-4d99-a94c-08d92a7c3325\",\"eid\": \"http://someURL.com/uiEWUIWER8\",\"tid\": \"672rerce-0ccd-4867-8090-e8bb889999ae0\",\"ts\": \"2021-06-09T02:50:41.0000000Z\",\"te\": \"2021-06-09T02:50:41.0000000Z\",\"trc\": \"xxxx@yyyy.com\",\"tdc\": \"1\",\"at\": \"2021-06-09T02:50:41.0000000Z\",\"dm\": \"Office ATP Safe Links\",\"ot\": \"Not Applicable\",\"od\": \"Not Applicable\",\"md\": \"2021-06-08T12:52:04.3356492Z\",\"lon\": \"MaliciousUrlClick\"}]" | spath path={} output=data | mvexpand data | rename data as _raw | spath | table etype

In case you have different type of data then share samples with us along with expected output.

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.