topic Re: Searching Nested JSON Data in Splunk Search

Searching Nested JSON Data

nkavouris — Tue, 28 Nov 2023 16:54:36 GMT

Using SPL and Splunk Search, I would like to search the logs array for each separate test_name and results and create a table with the results

my current query looks something like:

index="factory_mtp_events" | spath logs{}.test_name | search "logs{}.test_name"="Sample Test1"

{ 
   logs: [ 
     { 
       result: Pass
       test_name: Sample Test1
     { 
       result: Pass
       test_name: Sample Test2
     }
     { 
       received: 4
       result: Pass
       test_name: Sample Test3
     }
     { 
       expected: sample
       received: sample
       result: Pass
       test_name: Sample Test4
     }
     { 
       expected: 1 A S
       received: 1 A S 

       result: Pass
       test_name: Sample Test5
     }
     {
       expected: 1
       reason: Sample Reason
       received: 1
       result: Pass
       test_name: Sample Test6
     }
     { 
       pt1: 25000
       pt1_recieved: 25012.666666666668
       pt2: 20000
       pt2_recieved: 25015.333333333332
       pt3: 15000
       pt3_recieved: 25017.0
       result: Fail
       test_name: Sample Test7
     }
     {
       result: Pass
       test_name: Sample Test8
       tolerance: + or - 5 C
       recieved_cj: 239
       user_temp: 250
     }
     { 
       expected: Open, Short, and Load verified OK.
       pt1: 2
       pt1_recieved: 0
       pt2: 1
       pt2_received: 0
       result: Fail
       test_name: Sample Test9
     }
     {
       pt1: 2070
       pt1_tolerance: 2070
       pt1_received: 540
       pt2: 5450
       pt2_tolerance: 2800
       pt2_received: 538
       result: Fail
       test_name: Sample Test10
     }
     { 
       expected: Soft Start verified by operator
       received: Soft Start verified
       result: Pass
       test_name: Sample Test11
     }
     { 
       F_name: AUGER 320 F
       F_rpm: 1475
       F_rpm_t: 150
       F_rpm_received: 1500
       F_v: 182
       F_v_t: 160
       F_v_received: 173
       R_name: AUGER 320 R
       R_rpm: 1475
       R_rpm_t: 150
       R_rpm_received: 1450
       R_v: 155
       R_v_t: 160
       R_v_ugc: 154.66666666666666
       result: Pass
       test_name: Sample Test12
     }
     { 
       result: Pass
       rpm: 2130
       rpm_t: 400
       test_name: Sample Test13
       received_rpm: 2126.6666666666665
       received_v: 615.6666666666666
       v: 630
       v_t: 160
     }
 ]
   result: Fail
   serial_number: XXXXXXXXXXXsample
   type: Test

What is the purpose of the brackets after logs? I assume regex must be used to get the result from each test? How do I pull results from each test into a table containing the results of every separate log?

I would like the table for each test to look something like:

** Sample Test1**

Expected Actual Serial No.

X	X	XXXXXXXsample
Y	Z	XXXXXX2sample

Re: Searching Nested JSON Data

PickleRick — Tue, 28 Nov 2023 16:30:41 GMT

1. The brackets are just part of field's name. Nothing more, nothing less.

2. Working with regex over structured data is... risky.

3. Extract the "logs" part. You should get a multivalued field of json-formatted objects. Do mvexpand to split it into separate results. Then do spath. Otherwise you'd just get huge multivalued blobs of data - Splunk doesn't play the "json structure" game so if you just flatten your json, you'll get all values of "the same" field compressed into a single multivalued field.

Re: Searching Nested JSON Data

nkavouris — Tue, 28 Nov 2023 17:53:02 GMT

"Do mvexpand to split it into separate results. Then do spath" Need more detail please

Is there a way to see what the mvexpand returns? feels like debugging queries is next to impossible

when spath-ing the mv results what exactly am inputting for?

index="factory_mtp_events" |
spath "logs{}" output=logs | 
mvexpand logs | 
spath input=logs.test_name|

Re: Searching Nested JSON Data

PickleRick — Tue, 28 Nov 2023 18:56:55 GMT

Usualy debugging involves just adding commands one by one and seeing if they yield the result you expect.

So just remove the last spath and see if you have separate "bundle" in each row. Then just do

| spath input=logs

Re: Searching Nested JSON Data

yuanliu — Thu, 30 Nov 2023 01:24:24 GMT

The problem is Splunk always flattens arrays. The trick is to preserve logs{} as a vector before mvexpand.

index="factory_mtp_events" | spath path=logs{} ``` alternative syntax: | spath logs{} ``` | mvexpand logs{} | search test_name="Sample Test1"