Splunk Search

Parse JSON object map and mvexpand to separate events

bowesmana
SplunkTrust
SplunkTrust

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
Ultra Champion
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
Ultra Champion
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
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...