Searching Nested JSON Data

[nkavouris]

Hello Splunkers,

I am New to Splunk and am trying to figure out how to parse nested JSON data spit out by an end-of-line test. 

Here is a sample event:

{"serial_number": "PLACEHOLDER1234", "type": "Test", "result": "Pass", "logs": [{"test_name": "UGC Connect", "result": "Pass"}, 
{"test_name": "Disable UGC USB Comm Watchdog", "result": "Pass"},
{"test_name": "Hardware Rev", "result": "Pass", "received": "4"}, 
{"test_name": "Firmware Rev", "result": "Pass", "received": "1.8.3.99", "expected": "1.8.3.99"},
 {"test_name": "Set Serial Number", "result": "Pass", "received": "1 A S \n", "expected": "1 A S"}, 
{"test_name": "Verify serial number", "result": "Pass", "received": "JC0024EW1482300425", "expected": "JC0024EW1482300425", "reason": "Truncated full serial number: 30913JC0024EW1482300425 to JC0024EW1482300425"}, 
{"test_name": "Thermocouple", "pt1_ugc": "24969.0", "pt1": "25000", "pt2_ugc": "19954.333333333332", "pt2": "20000", "pt3_ugc": "14993.666666666666", "pt3": "15000", "result": "Pass", "tolerance": "1000 deci-mV"}, 
{"test_name": "Cold Junction", "result": "Pass", "ugc_cj": "278", "user_temp": "270", "tolerance": "+ or - 5 C"}, 
{"test_name": "Glow Plug Open and Short", "result": "Pass", "received": "GP Open, Short, and Load verified OK.", "expected": "GP Open, Short, and Load verified OK."}, 
{"test_name": "Glow Plug Power On", "result": "Pass", "received": "User validated Glow Plug Power"}, 
{"test_name": "Glow Plug Measure", "pt1_ugc": "848", "pt1": "2070", "pt1_tolerance": "2070", "pt2_ugc": "5201", "pt2": "5450", "pt2_tolerance": "2800", "result": "Pass"}, 
{"test_name": "Motor Soft Start", "result": "Pass", "received": "Motor Soft Start verified", "expected": "Motor Soft Start verified by operator"}, 
{"test_name": "Motor", "R_rpm_ugc": 1525.0, "R_rpm": 1475, "R_v_ugc": 160.0, "R_v": 155, "R_rpm_t": 150, "R_v_t": 160, "R_name": "AUGER 320 R", "F_rpm_ugc": 1533.3333333333333, "F_rpm": 1475, "F_v_ugc": 164.0, "F_v": 182, "F_rpm_t": 150, "F_v_t": 160, "F_name": "AUGER 320 F", "result": "Pass"}, 
{"test_name": "Fan", "ugc_rpm": 2436.0, "rpm": 2130, "rpm_t": 400, "ugc_v": 653.3333333333334, "v": 630, "v_t": 160, "result": "Pass"}, {"test_name": "RS 485", "result": "Pass", "received": "All devices detected", "expected": "Devices detected: ['P']"},
{"test_name": "Close UGC Port", "result": "Pass"}, 
{"test_name": "DFU Test", "result": "Pass", "received": "Found DFU device"}, 
{"test_name": "Power Cycle", "result": "Pass", "received": "User confirmed power cycle"}, 
{"test_name": "UGC Connect", "result": "Pass"}, 
{"test_name": "Close UGC Port", "result": "Pass"},
{"test_name": "USB Power", "result": "Pass", "received": "USB Power manually verified"}]}

 I want to be able to extract the test data (all key-value pairs) from each test. Ideally would like to create dashboard charts showing response from Motor and Fan tests among others.

Here is a sample search i have been using which allows me to create a table with the serial number, overall test result, individual test name, and individual test result

index="factory_mtp_events" | 
search sourcetype="placeholder" source="placeholder" serial_number="PLACEHOLDER*"|
spath logs{} output=logs|
stats count by serial_number result logs|
eval _raw=logs|
spath test_name output=test_name |spath result output=test_result|
table serial_number result test_name test_result

How Can I index into the logs{} section and pull out all results dependent on test_name?

So, how can I query for logs{}.test_name="Motor" and have the result yield :

{"test_name": "Motor", "R_rpm_ugc": 1525.0, "R_rpm": 1475, "R_v_ugc": 160.0, "R_v": 155, "R_rpm_t": 150, "R_v_t": 160, "R_name": "AUGER 320 R", "F_rpm_ugc": 1533.3333333333333, "F_rpm": 1475, "F_v_ugc": 164.0, "F_v": 182, "F_rpm_t": 150, "F_v_t": 160, "F_name": "AUGER 320 F", "result": "Pass"},

[dtburrows3 · Accepted solution]

Should be able to do this ustilizing the mvmap() function

example Eval:

| eval
            selective_json=case(
                isnull(logs), null(),
                mvcount(logs)==1, if(spath(logs, "test_name")=="Motor", 'logs', null()),
                mvcount(logs)>1, mvmap(logs, if(spath(logs, "test_name")=="Motor", 'logs', null()))
                )

SPL used to replicate:

| makeresults
    | eval
        _raw="{\"serial_number\": \"PLACEHOLDER1234\", \"type\": \"Test\", \"result\": \"Pass\", \"logs\": [{\"test_name\": \"UGC Connect\", \"result\": \"Pass\"}, {\"test_name\": \"Disable UGC USB Comm Watchdog\", \"result\": \"Pass\"},{\"test_name\": \"Hardware Rev\", \"result\": \"Pass\", \"received\": \"4\"}, {\"test_name\": \"Firmware Rev\", \"result\": \"Pass\", \"received\": \"1.8.3.99\", \"expected\": \"1.8.3.99\"},{\"test_name\": \"Set Serial Number\", \"result\": \"Pass\", \"received\": \"1 A S \n\", \"expected\": \"1 A S\"}, {\"test_name\": \"Verify serial number\", \"result\": \"Pass\", \"received\": \"JC0024EW1482300425\", \"expected\": \"JC0024EW1482300425\", \"reason\": \"Truncated full serial number: 30913JC0024EW1482300425 to JC0024EW1482300425\"}, {\"test_name\": \"Thermocouple\", \"pt1_ugc\": \"24969.0\", \"pt1\": \"25000\", \"pt2_ugc\": \"19954.333333333332\", \"pt2\": \"20000\", \"pt3_ugc\": \"14993.666666666666\", \"pt3\": \"15000\", \"result\": \"Pass\", \"tolerance\": \"1000 deci-mV\"}, {\"test_name\": \"Cold Junction\", \"result\": \"Pass\", \"ugc_cj\": \"278\", \"user_temp\": \"270\", \"tolerance\": \"+ or - 5 C\"}, {\"test_name\": \"Glow Plug Open and Short\", \"result\": \"Pass\", \"received\": \"GP Open, Short, and Load verified OK.\", \"expected\": \"GP Open, Short, and Load verified OK.\"}, {\"test_name\": \"Glow Plug Power On\", \"result\": \"Pass\", \"received\": \"User validated Glow Plug Power\"}, {\"test_name\": \"Glow Plug Measure\", \"pt1_ugc\": \"848\", \"pt1\": \"2070\", \"pt1_tolerance\": \"2070\", \"pt2_ugc\": \"5201\", \"pt2\": \"5450\", \"pt2_tolerance\": \"2800\", \"result\": \"Pass\"}, {\"test_name\": \"Motor Soft Start\", \"result\": \"Pass\", \"received\": \"Motor Soft Start verified\", \"expected\": \"Motor Soft Start verified by operator\"}, {\"test_name\": \"Motor\", \"R_rpm_ugc\": 1525.0, \"R_rpm\": 1475, \"R_v_ugc\": 160.0, \"R_v\": 155, \"R_rpm_t\": 150, \"R_v_t\": 160, \"R_name\": \"AUGER 320 R\", \"F_rpm_ugc\": 1533.3333333333333, \"F_rpm\": 1475, \"F_v_ugc\": 164.0, \"F_v\": 182, \"F_rpm_t\": 150, \"F_v_t\": 160, \"F_name\": \"AUGER 320 F\", \"result\": \"Pass\"}, {\"test_name\": \"Fan\", \"ugc_rpm\": 2436.0, \"rpm\": 2130, \"rpm_t\": 400, \"ugc_v\": 653.3333333333334, \"v\": 630, \"v_t\": 160, \"result\": \"Pass\"}, {\"test_name\": \"RS 485\", \"result\": \"Pass\", \"received\": \"All devices detected\", \"expected\": \"Devices detected: ['P']\"},{\"test_name\": \"Close UGC Port\", \"result\": \"Pass\"}, {\"test_name\": \"DFU Test\", \"result\": \"Pass\", \"received\": \"Found DFU device\"}, {\"test_name\": \"Power Cycle\", \"result\": \"Pass\", \"received\": \"User confirmed power cycle\"}, {\"test_name\": \"UGC Connect\", \"result\": \"Pass\"}, {\"test_name\": \"Close UGC Port\", \"result\": \"Pass\"},{\"test_name\": \"USB Power\", \"result\": \"Pass\", \"received\": \"USB Power manually verified\"}]}"
    | spath logs{} output=logs
    | fields - _raw, _time
    | eval
        selective_json=case(
            isnull(logs), null(),
            mvcount(logs)==1, if(spath(logs, "test_name")=="Motor", 'logs', null()),
            mvcount(logs)>1, mvmap(logs, if(spath(logs, "test_name")=="Motor", 'logs', null()))
            )

[nkavouris]

this function works well, thank you!

Using it with eval _raw allows me to easily get the data i want using SPATH.

eval _raw=selective_json|
spath R_rpm_ugc output=reverseRPM|
table serial_number test_name selective_json reverseRPM

 Would you be able to explain how this function works? As a splunk novice I am unsure how the selective_json field is created?

[dtburrows3]

mvmap() function loops through all values in multivalued field and applies an operation to each individual entry.

Combining this functionality with if() conditions you can get pretty slick with working with MV fields.

So for this situation you can see that we are looping through entries in the MV field `logs`

So step one it it looks at the value...

{"test_name": "Disable UGC USB Comm Watchdog", "result": "Pass"}

We then apply the if() function to check a specific condition for this individual entry in our loop

if(spath(logs, "test_name")=="Motor", 'logs', null())

 
spath is checking the value of key "test_name" in this entry and if it is equal to "Motor" then we are taking that individual entry (json_object) and dropping it into a new field named "selective_json"

If there were multiple entries matching this criteria then selective_json would also be a multivalued field but but would be filtered down to only include the json_objects whose "test_name"=="Motor".


MVMap function is great but I have found very odd bug with it sometimes. From what I can tell, without checking if the field is actually mutivalued before using mvmap against it there can be strange behavior when jumping between events. To get around that I have found that nesting it in a case() function to first check if the values of an field in each event is null, single_value, or multi_value to determine which operation to use.

[nkavouris]

Is there a way to include multiple conditions? for example:

if(spath(logs, "test_name")=="Motor", 'logs', null()),
if(spath(logs, "result")=="Pass", 'logs', null())

In this case only using the data from the logs where test_name="Motor" and result="pass" 

[dtburrows3]

Should be able to fit both conditions into one if() logic statement

if((spath(logs, "test_name")=="Motor" OR spath(logs, "test_name")=="Pass"), 'logs', null())

or maybe even something like this.

if(spath(logs, "test_name") IN ("Motor", "Pass"), 'logs', null())