How to Splunk query to split values of multiple dictionary within a list

[vinodkumarK]

Query is to retrieve failed test case matching with exception message. Out of 6 failed test case, one test as exception and rest of them are skipped with message 'Test was skipped'.

Below data of one event. 

{
  "suite_build_id": "20241015.12",
  "suite_build_name": "pipeline_name",
  "unit_test_name_failed": [
    {
      "message": "Failed to save the shipping address. An unexpected error occurred. Please try again later or contact HP Support for assistance.",
      "test_rail_name": "test_printer_order_placement_magento",
      "test_result": "fail"
    },
    {
      "message": "Test was skipped",
      "test_rail_name": "test_updation_of_access_token",
    },
    {
      "message": "Test was skipped",
      "test_name": "test_printer_and_user_details",
      "test_rail_name": "test_printer_and_user_details",
    }
  ]
}

 Now, I want to display result to show test_rail_name and exception_message which matches exception.

Below is the query that I tried.

index="eqt-e2e" suite_build_name="pipeline-name" suite_build_number="20241015.12"
| mvexpand unit_test_name_failed{}.message
| mvexpand unit_test_name_failed{}.test_rail_name
| search unit_test_name_failed{}.message="Failed to save the shipping address. An unexpected error occurred. Please try again later or contact HP Support for assistance."
| table suite_build_number, suite_build_start_time, unit_test_name_failed{}.test_rail_name, unit_test_name_failed{}.message
| rename suite_build_number AS "Pipeline Number", suite_build_start_time AS "Pipeline Date", unit_test_name_failed{}.test_rail_name AS "Test Name", unit_test_name_failed{}.message AS "Exception Message"

In the result, it should have been 1 event, but retrieve 6 events. 

I understand, mvexpand works only on one multivalue fields, and here I have 2 multivalue fields.

Let me know if there is any solution on retrieving the data.

[ITWhisperer]

mvexpand on message creates 3 events from one. mvexpand on test_rail_name creates 2 events from each of these - hence 6 events.

| spath unit_test_name_failed{} output=unit_test_name_failed
| mvexpand unit_test_name_failed
| spath input=unit_test_name_failed
| where message="Failed to save the shipping address. An unexpected error occurred. Please try again later or contact HP Support for assistance."

[vinodkumarK]

Thanks for the response. Appreciate it.

I tried your query,  and it didn't match my result. 

Below is the result of your query.

I want to display a row, with test_rail_name as 'test_printer_order_placement_magento' and message as 'Failed to save the shipping address. An unexpected error occurred. Please try again later or contact HP Support for assistance.'

[ITWhisperer]

What do you get from this

| spath unit_test_name_failed{} output=unit_test_name_failed
| mvexpand unit_test_name_failed
| table unit_test_name_failed

[vinodkumarK]

Here.

[ITWhisperer · Accepted solution]

| spath unit_test_name_failed{} output=unit_test_name_failed
| mvexpand unit_test_name_failed
| spath input=unit_test_name_failed
| where message="Failed to save the shipping address. An unexpected error occurred. Please try again later or contact HP Support for assistance."
| table message test_rail_name

[vinodkumarK]

This is how data is shown If I just display the list.