topic Re: Extract Fields from JSON in Splunk Search

Extract Fields from JSON

felipesodre — Thu, 09 Jul 2020 20:37:29 GMT

Hi Everyone. Thanks in advance for any help.

I am trying to extract some fields (Status, RecordsPurged) from a JSON on the following _raw text:

{"": "INFO : 2020-07-09T01:11:08Z : [database@test.com]: {\"Purging_Results_Test\": {\"NewPurging\": 1, \"Status\":\"Successful\", \"VacuumEnabled\": true, \"RecordsPurged\": 6646, \"StartTime\":\"8-Jul-2020 18:03:07\", \"EndTime\":\"8-Jul-2020 18:11:08\", \"Duration(min)\":8.02}}"}

Any Ideas that might help me out?

Thank you so much.

Re: Extract Fields from JSON

richgalloway — Thu, 09 Jul 2020 21:32:32 GMT

Normally, one uses spath to parse JSON, but it doesn't like your sample text. So rex will do, instead.

... | rex "Status\":\"(?<Status>\w+)" | rex "RecordsPurged\": (?<RecordsPurged>\d+)"

Re: Extract Fields from JSON

felipesodre — Fri, 10 Jul 2020 15:26:17 GMT

Hi, sorry for the late reply and thanks for your help.

For some reason your code works for some fields and others don't. Please see the image attached.

Any ideas that might help?

Is it possible to get the JSON of Purging_Results: {all content in here} using regex and then navigate to the fields using a spath?

Re: Extract Fields from JSON

richgalloway — Fri, 10 Jul 2020 16:55:57 GMT

You mean like this?

| makeresults | eval _raw="{\"\": \"INFO : 2020-07-09T01:11:08Z : [database@test.com]: {\"Purging_Results_Test\": {\"NewPurging\": 1, \"Status\":\"Successful\", \"VacuumEnabled\": true, \"RecordsPurged\": 6646, \"StartTime\":\"8-Jul-2020 18:03:07\", \"EndTime\":\"8-Jul-2020 18:11:08\", \"Duration(min)\":8.02}}\"}" | rex "Purging_Results_Test\": (?<PurgingResultsTest>.*?})" | spath input=PurgingResultsTest

Re: Extract Fields from JSON

felipesodre — Fri, 10 Jul 2020 17:13:15 GMT

It just worked perfectly.

Thank you so much, appreciate it!!!!