Splunk Search

Extracting information nested in a JSON-like format

husse_wl
Loves-to-Learn

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

Labels (7)
0 Karma

husse_wl
Loves-to-Learn

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...