Splunk Search

Parse JSON object map and mvexpand to separate events

bowesmana
Champion

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 

Labels (3)
Tags (2)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust
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?

View solution in original post

to4kawa
SplunkTrust
SplunkTrust
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?

View solution in original post

bowesmana
Champion

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.

 

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!