Parse JSON object map and mvexpand to separate events

[bowesmana]

I'm struggling with parsing this JSON. This query shows the part of a larger JSON element (response.rules).

| makeresults
| eval _raw="{\"CLEANSE_001\":{\"start\":1608151620347,\"nodes\":[{\"start\":1608151620347,\"name\":\"Cleanse In\",\"end\":1608151620576}],\"end\":1608151620576},\"ENRICH_001\":{\"start\":1608151620376,\"nodes\":[{\"start\":1608151620376,\"name\":\"Resolve Providers\",\"end\":1608151620378,\"status\":{\"errorCode\":0}}]},\"ROUTER_001\":{\"start\":1608151620408,\"nodes\":[{\"nodeId\":\"48290e0c.13b5\",\"startTime\":1608151620408,\"endTime\":1608151620564,\"action\":0,\"status\":null,\"name\":\"Modality Router\",\"type\":\"router\"}]},\"COMMON_001\":{\"start\":1608151620428,\"nodes\":[{\"nodeId\":\"471341df.4d01e\",\"startTime\":1608151620428,\"endTime\":1608151620431,\"action\":1,\"status\":null,\"name\":\"Check Blacklist\",\"type\":\"bank-accounts\"},{\"nodeId\":\"c53c6260.e6a06\",\"startTime\":1608151620434,\"endTime\":1608151620436,\"action\":0,\"status\":null,\"name\":\"Complete Processing\",\"type\":\"finalise\"}],\"end\":1608151620436},\"RULE_001\":{\"start\":1608151620467,\"nodes\":[{\"nodeId\":\"1f79b359.f41a6d\",\"startTime\":1608151620467,\"endTime\":1608151620471,\"action\":1,\"status\":null,\"name\":\"Is Item Whitelisted\",\"type\":\"item-codes\"},{\"nodeId\":\"5f220c75.7d7314\",\"startTime\":1608151620474,\"endTime\":1608151620514,\"action\":0,\"status\":{\"message\":\"History match found\",\"criteria\":{\"fund\":\"AAA\",\"policyId\":\"12345678X\"}},\"name\":\"Does History Exist\",\"type\":\"history-filter\"},{\"nodeId\":\"b362caa7.30b868\",\"startTime\":1608151620517,\"endTime\":1608151620519,\"action\":0,\"status\":null,\"type\":\"audit\"},{\"nodeId\":\"f23d5572.add938\",\"startTime\":1608151620529,\"endTime\":1608151620535,\"action\":0,\"status\":null,\"name\":\"Conditional Item Approval\",\"type\":\"conditional\"},{\"nodeId\":\"5231d7f2.4d6be8\",\"startTime\":1608151620538,\"endTime\":1608151620541,\"action\":0,\"status\":null,\"name\":\"Finalise Rule\",\"type\":\"finalise\"}],\"end\":1608151620541},\"RULE_003\":{\"start\":1608151620548,\"nodes\":[{\"nodeId\":\"d23183a9.2ac66\",\"startTime\":1608151620548,\"endTime\":1608151620551,\"action\":1,\"status\":null,\"name\":\"MyName\",\"type\":\"items\"},{\"nodeId\":\"684920f.3e1fee\",\"startTime\":1608151620553,\"endTime\":1608151620554,\"action\":0,\"status\":null,\"name\":\"Finalise Rule\",\"type\":\"finalise\"}],\"end\":1608151620554}}"
| rex field=_raw max_match=0 "(?<rule>[A-Z]*_\d+)"

Note the JSON is what is shown in the query result, not the escaped JSON above.

So, what I want is to be able to mvexpand the 6 'rules' shown by the rex extraction, i.e. 

CLEANSE_001

ENRICH_001

ROUTER_001

COMMON_001

RULE_001

RULE_003

so that there is a field called rule with that name in and then the JSON for that particular rule which I can then spath out to its component parts to do stuff with the data.

As this is a JSON representation of a map type of object, where the keys are the rule name, I don't know if it's possible to extract this with spath. The data is this sort of representation, where there can be any number of properties inside the rule and the nodes array objects can contain those shown plus other elements. 

{
  "rule1": {
             start:1
             end:55
             nodes: [
             {
               name:A
               start:1
               end:17
             },
             {
               name:B
               start:17
               end:42
             }
             ]
           },
  "rule2": {
             start:42
             end:55
             nodes: [
             {
               name:C
               start:42
               end:55
             }
             ]
           }
}

I don't want this to be split up into several rows at index time, this is simply a search to query a single event to show processing of a particular entity through each rule.

Is it possible to spath the data in a way to get each rule into its own event or will it have to be rex'ed out using some clever regex?

I can spath _raw on this data, but then I get all the extracted fields with the rule 'name' as the top level of the field name. 

what I want is 6 events with

rule_name=CLEANSE_001 etc + rule_data=JSON object that I can spath 

[to4kawa · Accepted solution]

index=_internal | head 1 | fields _raw
| eval _raw="{\"CLEANSE_001\":{\"start\":1608151620347,\"nodes\":[{\"start\":1608151620347,\"name\":\"Cleanse In\",\"end\":1608151620576}],\"end\":1608151620576},\"ENRICH_001\":{\"start\":1608151620376,\"nodes\":[{\"start\":1608151620376,\"name\":\"Resolve Providers\",\"end\":1608151620378,\"status\":{\"errorCode\":0}}]},\"ROUTER_001\":{\"start\":1608151620408,\"nodes\":[{\"nodeId\":\"48290e0c.13b5\",\"startTime\":1608151620408,\"endTime\":1608151620564,\"action\":0,\"status\":null,\"name\":\"Modality Router\",\"type\":\"router\"}]},\"COMMON_001\":{\"start\":1608151620428,\"nodes\":[{\"nodeId\":\"471341df.4d01e\",\"startTime\":1608151620428,\"endTime\":1608151620431,\"action\":1,\"status\":null,\"name\":\"Check Blacklist\",\"type\":\"bank-accounts\"},{\"nodeId\":\"c53c6260.e6a06\",\"startTime\":1608151620434,\"endTime\":1608151620436,\"action\":0,\"status\":null,\"name\":\"Complete Processing\",\"type\":\"finalise\"}],\"end\":1608151620436},\"RULE_001\":{\"start\":1608151620467,\"nodes\":[{\"nodeId\":\"1f79b359.f41a6d\",\"startTime\":1608151620467,\"endTime\":1608151620471,\"action\":1,\"status\":null,\"name\":\"Is Item Whitelisted\",\"type\":\"item-codes\"},{\"nodeId\":\"5f220c75.7d7314\",\"startTime\":1608151620474,\"endTime\":1608151620514,\"action\":0,\"status\":{\"message\":\"History match found\",\"criteria\":{\"fund\":\"AAA\",\"policyId\":\"12345678X\"}},\"name\":\"Does History Exist\",\"type\":\"history-filter\"},{\"nodeId\":\"b362caa7.30b868\",\"startTime\":1608151620517,\"endTime\":1608151620519,\"action\":0,\"status\":null,\"type\":\"audit\"},{\"nodeId\":\"f23d5572.add938\",\"startTime\":1608151620529,\"endTime\":1608151620535,\"action\":0,\"status\":null,\"name\":\"Conditional Item Approval\",\"type\":\"conditional\"},{\"nodeId\":\"5231d7f2.4d6be8\",\"startTime\":1608151620538,\"endTime\":1608151620541,\"action\":0,\"status\":null,\"name\":\"Finalise Rule\",\"type\":\"finalise\"}],\"end\":1608151620541},\"RULE_003\":{\"start\":1608151620548,\"nodes\":[{\"nodeId\":\"d23183a9.2ac66\",\"startTime\":1608151620548,\"endTime\":1608151620551,\"action\":1,\"status\":null,\"name\":\"MyName\",\"type\":\"items\"},{\"nodeId\":\"684920f.3e1fee\",\"startTime\":1608151620553,\"endTime\":1608151620554,\"action\":0,\"status\":null,\"name\":\"Finalise Rule\",\"type\":\"finalise\"}],\"end\":1608151620554}}"
| rex max_match=0 "(?<rules>[A-Z]+_\d+\":.*?((?=,\"[A-Z]+)|(?=}$)))"
| stats count by rules
| rex field=rules "(?<rule>[\w_]+)\":(?<json>.*)"
| spath input=json

how about this?

[bowesmana]

Thanks @to4kawa I have never managed to get my head around regex lookahead/behind, but that works a treat.

I figured it was not possible directly with spath, which in my opinion, is a deficiency in Splunk's JSON parser. I wonder if SPL2 has better support.