topic Re: Extracting information nested in a JSON-like format in Splunk Search

Extracting information nested in a JSON-like format

husse_wl — Wed, 09 Jun 2021 08:22:58 GMT

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

Re: Extracting information nested in a JSON-like format

kamlesh_vaghela — Wed, 09 Jun 2021 09:41:03 GMT

@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.

Re: Extracting information nested in a JSON-like format

husse_wl — Wed, 09 Jun 2021 13:10:37 GMT

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.