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 : [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.

[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?

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 : [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

[felipesodre]

It just worked perfectly.

Thank you so much, appreciate it!!!!