Extracting Data from complex JSON

nkavouris — Tue, 04 Jun 2024 14:44:15 GMT

I would like to extract the results of each test within the logs array by distinct count of serial number.

That is, for each serial number i want to extract the test_name and result and plot the pass/fail rates of eachindividual test. The test names are the same in each event with only a different serial_number

For the raw JSON below there would be 12 tests to extract the results from.

How would i go about this in search?

{"serial_number": "VE7515966", "type": "Test", "result": "Pass", "logs": [{"test_name": "UGC Connect", "result": "Pass"}, {"test_name": "Disable UGC USB Comm Watchdog", "result": "Pass"}, {"test_name": "ACR Data Read", "result": "Pass", "received": "ACR model extracted as Test"}, {"test_name": "Thermocouple in Grill", "tc_1": "295", "tc_2": "578", "req_diff": 50, "result": "Pass"}, {"test_name": "Glow Plug in Grill", "m_ugc": "1849", "tol_lower": 0, "tol_upper": 10000, "cond_ugc": "0", "tol_cond": 0, "result": "Pass"}, {"test_name": "Glow Plug Power Toggle", "result": "Pass", "received": "Glow plug power toggled"}, {"test_name": "Glow Plug in Grill", "m_ugc": "2751", "tol_lower": 0, "tol_upper": 10000, "cond_ugc": "0", "tol_cond": 0, "result": "Pass"}, {"test_name": "Motor", "R_rpm_ugc": 3775.0, "R_rpm": 3950, "R_v_ugc": 984.3333333333334, "R_v": 800, "R_rpm_t": 550, "R_v_t": 500, "R_name": "AUGER 640 R", "F_rpm_ugc": 3816.6666666666665, "F_rpm": 3950, "F_v_ugc": 985.3333333333334, "F_v": 800, "F_rpm_t": 550, "F_v_t": 500, "F_name": "AUGER 640 F"}, {"test_name": "Fan", "ugc_rpm": 2117.0, "rpm": 2700, "rpm_t": 800, "ugc_v": 554.3333333333334, "v": 630, "v_t": 200, "result": "Pass"}, {"test_name": "RS 485", "result": "Pass", "received": "All devices detected", "expected": "Devices detected: ['P', 'F']"}, {"test_name": "Close UGC Port", "result": "Pass"}, {"test_name": "Confirm Grill Size", "result": "Pass", "received": "Grill confirmed as Test"}]}

Re: Extracting Data from complex JSON

ITWhisperer — Tue, 04 Jun 2024 14:54:50 GMT

| spath logs{} output=logs | spath serial_number | mvexpand logs | spath input=logs

Re: Extracting Data from complex JSON

yuanliu — Tue, 04 Jun 2024 15:21:28 GMT

As @ITWhisperer shows, the key is mvexpand. Meanwhile, I believe that you already have serial_number, type, outer result, and so on so you don't need to extract them. In addition, you have a whole bunch of logs{}.* fields (including logs{}.result) that you no longer need. You can simplify search to

| fields - logs{}.* | spath logs{} output=logs | mvexpand logs | spath input=logs

One more thing: is the outer result field important? If it is you want to rename outer result first.

Re: Extracting Data from complex JSON

nkavouris — Thu, 13 Jun 2024 18:36:17 GMT

No, outer result is not important as i am looking to create pass/fail charts with the inner results of each corresponding test

topic Extracting Data from complex JSON in Splunk Search

Extracting Data from complex JSON

Re: Extracting Data from complex JSON

Re: Extracting Data from complex JSON

Re: Extracting Data from complex JSON