splunk : json spath extract

Question

I have below event message in json format & need to extract the job names with STATUS = Unavailable.

{"Failure":0,"Success":0,"In_Progress":0,"Others":1,"detail":[{"jobA":{"STATUS":"Unavailable"}}]}

{"Failure":0,"Success":1,"In_Progress":0,"Others":1,"detail":[{"jobA":{"STATUS":"SUCCESS","Run":435988393},"jobB":{"STATUS":"Unavailable"}}]}

Any suggestions how can I achieve this with spath ? I am suspecting since values like 0,1 do not have quotes, my spath search is failing

Expected output :
jobA  
jobB

Answer 1

spath works fine for me. The trouble is spath produces fields like "detail{}.jobA.STATUS", which are tricky to work with. One workaround is to use spath to extract the JSON elements then parse the details with rex. Here's a run-anywhere example:

| makeresults | eval data="{\"Failure\":0,\"Success\":0,\"In_Progress\":0,\"Others\":1,\"detail\":[{\"jobA\":{\"STATUS\":\"Unavailable\"}}]}
{\"Failure\":0,\"Success\":1,\"In_Progress\":0,\"Others\":1,\"detail\":[{\"jobA\":{\"STATUS\":\"SUCCESS\",\"Run\":435988393},\"jobB\":{\"STATUS\":\"Unavailable\"}}]}" | eval data=split(data,"
") | mvexpand data | eval _raw=data
```Above just creates test data.  Omit IRL```
```Get the detail element from the events```
| spath path=detail{}
```Parse the details```
| spath input="detail{}"
```Parse the job and status fields as a unit.  We may have more than one.```
| rex field="detail{}" max_match=0 "(?<jobStatus>[^\\\"]+\\\":\{\\\"STATUS\\\":\\\"[^\\\"]+)"
```Create a separate event for each match```
| mvexpand jobStatus
```Parse the job and status values from each match```
| rex field=jobStatus "(?<Job>[^\\\"]+)\\\":\{\\\"STATUS\\\":\\\"(?<Status>[^\\\"]+)"
```Filter for unavailable jobs```
| where Status="Unavailable"
| table Job