Extract Fields from JSON

[felipesodre]

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 : [[email protected]]: {\"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.

[richgalloway]

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+)"

[felipesodre]

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?

Screen Shot 2020-07-10 at 12.49.06 PM.png

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?

[richgalloway · Accepted solution]

You mean like this?

| makeresults 
| eval _raw="{\"\": \"INFO : 2020-07-09T01:11:08Z : [[email protected]]: {\"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

[felipesodre]

It just worked perfectly.

Thank you so much, appreciate it!!!!