Splunk Search

Need help with nested json and spath

manderson7
Contributor

I've seen a few of the spath topics around, but wasn't able to understand enough to make it work for my data. I have the following json:

{
  "Record": {
    "contentId": "429636",
    "levelId": "57",
    "levelGuid": "3c5b481a-6698-49f5-8111-e43bb7604486",
    "moduleId": "83",
    "parentId": "0",
    "Field": [
      {
        "id": "22811",
        "guid": "6c6bbe96-deab-46ab-b83b-461364a204e0",
        "type": "1",
        "_value": "Need This with 22811 as the field name"
      },
      {
        "id": "22810",
        "guid": "08f66941-8f2f-42ce-87ae-7bec95bb5d3b",
        "type": "1",
        "p": "need this with 22810 as the field name"
      },
      {
        "id": "478",
        "guid": "4e17baea-f624-4d1a-9c8c-83dd18448689",
        "type": "1",
        "p": [
          "Needs to have 478 as field name",
          "Needs to have 478 as field name"
        ]
      },
      {
        "id": "22859",
        "guid": "f45d3578-100e-44aa-b3d3-1526aa080742",
        "type": "3",
        "xmlConvertedValue": "2023-06-16T00:00:00Z",
        "_value": "needs 22859 as field name"
      },
      {
        "id": "482",
        "guid": "a7ae0730-508b-4545-8cdc-fb68fc2e985a",
        "type": "3",
        "xmlConvertedValue": "2023-08-22T00:00:00Z",
        "_value": "needs 482 as field name"
      },
      {
        "id": "22791",
        "guid": "89fb3582-c325-4bc9-812e-0d25e319bc52",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74192",
            "displayName": "Exception Closed",
            "_value": "needs 22791 as field name"
          }
        }
      },
      {
        "id": "22818",
        "guid": "e2388e72-cace-42e6-9364-4f936df1b7f4",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74414",
            "displayName": "Yes",
            "_value": "needs 22818 as field name"
          }
        }
      },
      {
        "id": "22981",
        "guid": "8f8df6e3-8fb8-478b-8aa0-0be02bec24e3",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74550",
            "displayName": "Critical",
            "_value": "needs 22981 as field name"
          }
        }
      },
      {
        "id": "22876",
        "guid": "4cc725ad-d78d-4fc0-a3b2-c2805da8f29a",
        "type": "9",
        "Reference": {
          "id": "256681",
          "_value": "needs 22876 as field name"
        }
      },
      {
        "id": "23445",
        "guid": "f4f262f7-290a-4ffc-af2b-dcccde673dba",
        "type": "9",
        "Reference": {
          "id": "255761",
          "_value": "needs 23445 as field name"
        }
      },
      {
        "id": "1675",
        "guid": "ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b",
        "type": "2"
      },
      {
        "id": "22812",
        "guid": "e563eb9e-6390-406a-ac79-386e1c3006a3",
        "type": "2",
        "_value": "needs 22812 as field name"
      },
      {
        "id": "22863",
        "guid": "a9fe7505-5877-4bdf-aa28-9f6c86af90ae",
        "type": "8",
        "Users": {
          "User": {
            "id": "5117",
            "firstName": "data",
            "middleName": "data",
            "lastName": "data",
            "_value": "needs 22863 as field name"
          }
        }
      },
      {
        "id": "22784",
        "guid": "4466fd31-3ab3-4117-8aa0-40f765d20c10",
        "type": "3",
        "xmlConvertedValue": "2023-07-18T00:00:00Z",
        "_value": "7/18/2023"
      },
      {
        "id": "22786",
        "guid": "d1c7af3e-a350-4e59-9353-132a04a73641",
        "type": "1"
      },
      {
        "id": "2808",
        "guid": "4392ae76-9ee1-45bf-ac31-9e323a518622",
        "type": "1",
        "p": "needs 2808 as field name"
      },
      {
        "id": "22802",
        "guid": "ad7d4268-e386-441d-90b1-2da2fba0d002",
        "type": "1",
        "table": {
          "style": "width: 954px",
          "border": "1",
          "cellspacing": "0",
          "cellpadding": "0",
          "tbody": {
            "tr": {
              "style": "height: 73.05pt",
              "td": {
                "style": "width: 715.5pt",
                "valign": "top",
                "p": "needs 22802 as field name"
              }
            }
          }
        }
      },
      {
        "id": "8031",
        "guid": "fbcfdf2c-2990-41d1-9139-8a1d255688b0",
        "type": "1",
        "table": {
          "style": "width: 954px",
          "border": "1",
          "cellspacing": "0",
          "cellpadding": "0",
          "tbody": {
            "tr": {
              "style": "height: 71.1pt",
              "td": {
                "style": "width: 715.5pt",
                "valign": "top",
                "p": [
                  "needs 8031 as field name",
                  "needs 8031 as field name"
                ]
              }
            }
          }
        }
      },
      {
        "id": "22820",
        "guid": "0f98830d-48b3-497c-b965-55be276037f2",
        "type": "1",
        "p": "needs 22820 as field name"
      },
      {
        "id": "22807",
        "guid": "8aa0d0fa-632d-4dfa-9867-b0cc407fa96b",
        "type": "3"
      },
      {
        "id": "22855",
        "guid": "e55cbc59-ad8d-4831-8e6f-d350046026e9",
        "type": "1"
      },
      {
        "id": "8032",
        "guid": "f916365b-e6eb-4ab9-a4ff-c7812a404854",
        "type": "1",
        "p": "needs 8032 as field name"
      },
      {
        "id": "22792",
        "guid": "8e70c28a-2eec-4e38-b78b-5495c2854b3e",
        "type": "1",
        "_value": "needs 22792 as field name "
      },
      {
        "id": 22793,
        "guid": "ffeaa385-643a-4f04-8a00-c28ddd026b7f",
        "type": "4",
        "ListValues": ""
      },
      {
        "id": "22795",
        "guid": "c46eac60-d86e-4af4-9292-d194a601f8b6",
        "type": "1"
      },
      {
        "id": "22797",
        "guid": "8cd6e398-e565-4034-8db8-2e2ecb2f0b31",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "73060",
            "displayName": "data",
            "_value": "needs 22797 as field name"
          }
        }
      },
      {
        "id": "22799",
        "guid": "20823b18-cb9b-47a3-854d-58f874164b27",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74410",
            "displayName": "Other",
            "_value": "needs 22799 as field name"
          }
        }
      },
      {
        "id": "22798",
        "guid": "5b32be4c-bc40-45b3-add4-1b22162fd882",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74405",
            "displayName": "N/A",
            "_value": "needs 22798 as field name"
          }
        }
      },
      {
        "id": "22800",
        "guid": "6b020db0-780f-4eaf-8381-c122425b71ed",
        "type": "1",
        "p": "needs 22800 as field name"
      },
      {
        "id": "22801",
        "guid": "06334da8-5392-4a9d-a3eb-d4075ee30787",
        "type": "1",
        "p": "needs 22801 as field name"
      },
      {
        "id": "22794",
        "guid": "25da1de8-8e81-4281-8ef3-d82d1dc005ad",
        "type": "4",
        "ListValues": {
          "ListValue": {
            "id": "74398",
            "displayName": "Yes",
            "_value": "needs 22794 as field name"
          }
        }
      },
      {
        "id": "22813",
        "guid": "89760b4f-49be-40ad-8429-89c247e3e95a",
        "type": "1",
        "p": "needs 22813 as field name"
      },
      {
        "id": "22803",
        "guid": "03b6c826-e15c-4356-89e8-b0bd509aaeb5",
        "type": "3",
        "xmlConvertedValue": "2023-06-15T00:00:00Z",
        "_value": "needs 22803 as field name"
      },
      {
        "id": "22804",
        "guid": "d7683f9c-97bb-461a-97df-36ec6596b4fc",
        "type": "1",
        "p": "needs 22804 as field name"
      },
      {
        "id": "22805",
        "guid": "33386a3a-c331-4d8c-9825-166c0a5235c2",
        "type": "3",
        "xmlConvertedValue": "2023-06-15T00:00:00Z",
        "_value": "needs 22805 as field name"
      },
      {
        "id": "22806",
        "guid": "cd486293-9857-475c-9da3-a06f836edb59",
        "type": "1",
        "p": "needs 22806 as field name"
      }
    ]
  }
}

and have been able to extract id, (some) p data and _value data from Record.Field{} using:

| spath path=Record.Field{} output=Field 
| mvexpand Field 
| spath input=Field 
| rename id AS Field_id, value AS Field_value, p AS Field_p


, but have been unable get any other data out. The p values that I can get out are single value only.
In particular, I need to get the multi-value fields for ListValues{}.ListValue out. In addition, I need to map the values in _value and p to the top ID field in that array. I think the code sample provided above explains what's needed.
I know I can do a

|eval {id}=value


but it's complicated when there are so many more fields other than value, or complicated when the fields are nested.

Can someone help with this?

Labels (2)
Tags (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Is this what you are looking for?

ListValuesListValues.ListValue._valueListValues.ListValue.displayNameListValues.ListValue.idRecord.contentIdRecord.levelGuidRecord.levelIdRecord.moduleIdRecord.parentIdReference._valueReference.idUsers.User._valueUsers.User.firstNameUsers.User.idUsers.User.lastNameUsers.User.middleNameguididptable.bordertable.cellpaddingtable.cellspacingtable.styletable.tbody.tr.styletable.tbody.tr.td.ptable.tbody.tr.td.styletbody.tr.td.valigntypevaluexmlConvertedValue
......                            
 needs 22981 as field nameCritical745504296363c5b481a-6698-49f5-8111-e43bb760448657830       8f8df6e3-8fb8-478b-8aa0-0be02bec24e322981         4  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830needs 22876 as field name256681     4cc725ad-d78d-4fc0-a3b2-c2805da8f29a22876         9  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830needs 23445 as field name255761     f4f262f7-290a-4ffc-af2b-dcccde673dba23445         9  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830       ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b1675         2  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830       e563eb9e-6390-406a-ac79-386e1c3006a322812         2needs 22812 as field name 
    4296363c5b481a-6698-49f5-8111-e43bb760448657830  needs 22863 as field namedata5117datadataa9fe7505-5877-4bdf-aa28-9f6c86af90ae22863         8  
    4296363c5b481a-6698-49f5-8111-e43bb760448657830       4466fd31-3ab3-4117-8aa0-40f765d20c1022784         37/18/20232023-07-18T00:00:00Z
......                            

Just repeat the same array flatten method

 

 

 

| fields - Record.Field{}.*
| spath path=Record.Field{}
| mvexpand Record.Field{}
| spath input=Record.Field{}
| mvexpand p{}
| mvexpand table.tbody.tr.td.p{}
| eval p = coalesce(p, 'p{}'), table.tbody.tr.td.p = coalesce(table.tbody.tr.td.p, 'table.tbody.tr.td.p{}')
| fields - Record.Field{} p{} table.tbody.tr.td.p{}

 

 

 

Here's an emulation you can play with and compare with your data

 

 

 

| makeresults
| eval _raw = "{
  \"Record\": {
    \"contentId\": \"429636\",
    \"levelId\": \"57\",
    \"levelGuid\": \"3c5b481a-6698-49f5-8111-e43bb7604486\",
    \"moduleId\": \"83\",
    \"parentId\": \"0\",
    \"Field\": [
      {
        \"id\": \"22811\",
        \"guid\": \"6c6bbe96-deab-46ab-b83b-461364a204e0\",
        \"type\": \"1\",
        \"_value\": \"Need This with 22811 as the field name\"
      },
      {
        \"id\": \"22810\",
        \"guid\": \"08f66941-8f2f-42ce-87ae-7bec95bb5d3b\",
        \"type\": \"1\",
        \"p\": \"need this with 22810 as the field name\"
      },
      {
        \"id\": \"478\",
        \"guid\": \"4e17baea-f624-4d1a-9c8c-83dd18448689\",
        \"type\": \"1\",
        \"p\": [
          \"Needs to have 478 as field name\",
          \"Needs to have 478 as field name\"
        ]
      },
      {
        \"id\": \"22859\",
        \"guid\": \"f45d3578-100e-44aa-b3d3-1526aa080742\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-06-16T00:00:00Z\",
        \"_value\": \"needs 22859 as field name\"
      },
      {
        \"id\": \"482\",
        \"guid\": \"a7ae0730-508b-4545-8cdc-fb68fc2e985a\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-08-22T00:00:00Z\",
        \"_value\": \"needs 482 as field name\"
      },
      {
        \"id\": \"22791\",
        \"guid\": \"89fb3582-c325-4bc9-812e-0d25e319bc52\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74192\",
            \"displayName\": \"Exception Closed\",
            \"_value\": \"needs 22791 as field name\"
          }
        }
      },
      {
        \"id\": \"22818\",
        \"guid\": \"e2388e72-cace-42e6-9364-4f936df1b7f4\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74414\",
            \"displayName\": \"Yes\",
            \"_value\": \"needs 22818 as field name\"
          }
        }
      },
      {
        \"id\": \"22981\",
        \"guid\": \"8f8df6e3-8fb8-478b-8aa0-0be02bec24e3\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74550\",
            \"displayName\": \"Critical\",
            \"_value\": \"needs 22981 as field name\"
          }
        }
      },
      {
        \"id\": \"22876\",
        \"guid\": \"4cc725ad-d78d-4fc0-a3b2-c2805da8f29a\",
        \"type\": \"9\",
        \"Reference\": {
          \"id\": \"256681\",
          \"_value\": \"needs 22876 as field name\"
        }
      },
      {
        \"id\": \"23445\",
        \"guid\": \"f4f262f7-290a-4ffc-af2b-dcccde673dba\",
        \"type\": \"9\",
        \"Reference\": {
          \"id\": \"255761\",
          \"_value\": \"needs 23445 as field name\"
        }
      },
      {
        \"id\": \"1675\",
        \"guid\": \"ea8f9a24-3d35-49f9-b74e-e3b9e48f8b3b\",
        \"type\": \"2\"
      },
      {
        \"id\": \"22812\",
        \"guid\": \"e563eb9e-6390-406a-ac79-386e1c3006a3\",
        \"type\": \"2\",
        \"_value\": \"needs 22812 as field name\"
      },
      {
        \"id\": \"22863\",
        \"guid\": \"a9fe7505-5877-4bdf-aa28-9f6c86af90ae\",
        \"type\": \"8\",
        \"Users\": {
          \"User\": {
            \"id\": \"5117\",
            \"firstName\": \"data\",
            \"middleName\": \"data\",
            \"lastName\": \"data\",
            \"_value\": \"needs 22863 as field name\"
          }
        }
      },
      {
        \"id\": \"22784\",
        \"guid\": \"4466fd31-3ab3-4117-8aa0-40f765d20c10\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-07-18T00:00:00Z\",
        \"_value\": \"7/18/2023\"
      },
      {
        \"id\": \"22786\",
        \"guid\": \"d1c7af3e-a350-4e59-9353-132a04a73641\",
        \"type\": \"1\"
      },
      {
        \"id\": \"2808\",
        \"guid\": \"4392ae76-9ee1-45bf-ac31-9e323a518622\",
        \"type\": \"1\",
        \"p\": \"needs 2808 as field name\"
      },
      {
        \"id\": \"22802\",
        \"guid\": \"ad7d4268-e386-441d-90b1-2da2fba0d002\",
        \"type\": \"1\",
        \"table\": {
          \"style\": \"width: 954px\",
          \"border\": \"1\",
          \"cellspacing\": \"0\",
          \"cellpadding\": \"0\",
          \"tbody\": {
            \"tr\": {
              \"style\": \"height: 73.05pt\",
              \"td\": {
                \"style\": \"width: 715.5pt\",
                \"valign\": \"top\",
                \"p\": \"needs 22802 as field name\"
              }
            }
          }
        }
      },
      {
        \"id\": \"8031\",
        \"guid\": \"fbcfdf2c-2990-41d1-9139-8a1d255688b0\",
        \"type\": \"1\",
        \"table\": {
          \"style\": \"width: 954px\",
          \"border\": \"1\",
          \"cellspacing\": \"0\",
          \"cellpadding\": \"0\",
          \"tbody\": {
            \"tr\": {
              \"style\": \"height: 71.1pt\",
              \"td\": {
                \"style\": \"width: 715.5pt\",
                \"valign\": \"top\",
                \"p\": [
                  \"needs 8031 as field name\",
                  \"needs 8031 as field name\"
                ]
              }
            }
          }
        }
      },
      {
        \"id\": \"22820\",
        \"guid\": \"0f98830d-48b3-497c-b965-55be276037f2\",
        \"type\": \"1\",
        \"p\": \"needs 22820 as field name\"
      },
      {
        \"id\": \"22807\",
        \"guid\": \"8aa0d0fa-632d-4dfa-9867-b0cc407fa96b\",
        \"type\": \"3\"
      },
      {
        \"id\": \"22855\",
        \"guid\": \"e55cbc59-ad8d-4831-8e6f-d350046026e9\",
        \"type\": \"1\"
      },
      {
        \"id\": \"8032\",
        \"guid\": \"f916365b-e6eb-4ab9-a4ff-c7812a404854\",
        \"type\": \"1\",
        \"p\": \"needs 8032 as field name\"
      },
      {
        \"id\": \"22792\",
        \"guid\": \"8e70c28a-2eec-4e38-b78b-5495c2854b3e\",
        \"type\": \"1\",
        \"_value\": \"needs 22792 as field name \"
      },
      {
        \"id\": 22793,
        \"guid\": \"ffeaa385-643a-4f04-8a00-c28ddd026b7f\",
        \"type\": \"4\",
        \"ListValues\": \"\"
      },
      {
        \"id\": \"22795\",
        \"guid\": \"c46eac60-d86e-4af4-9292-d194a601f8b6\",
        \"type\": \"1\"
      },
      {
        \"id\": \"22797\",
        \"guid\": \"8cd6e398-e565-4034-8db8-2e2ecb2f0b31\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"73060\",
            \"displayName\": \"data\",
            \"_value\": \"needs 22797 as field name\"
          }
        }
      },
      {
        \"id\": \"22799\",
        \"guid\": \"20823b18-cb9b-47a3-854d-58f874164b27\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74410\",
            \"displayName\": \"Other\",
            \"_value\": \"needs 22799 as field name\"
          }
        }
      },
      {
        \"id\": \"22798\",
        \"guid\": \"5b32be4c-bc40-45b3-add4-1b22162fd882\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74405\",
            \"displayName\": \"N/A\",
            \"_value\": \"needs 22798 as field name\"
          }
        }
      },
      {
        \"id\": \"22800\",
        \"guid\": \"6b020db0-780f-4eaf-8381-c122425b71ed\",
        \"type\": \"1\",
        \"p\": \"needs 22800 as field name\"
      },
      {
        \"id\": \"22801\",
        \"guid\": \"06334da8-5392-4a9d-a3eb-d4075ee30787\",
        \"type\": \"1\",
        \"p\": \"needs 22801 as field name\"
      },
      {
        \"id\": \"22794\",
        \"guid\": \"25da1de8-8e81-4281-8ef3-d82d1dc005ad\",
        \"type\": \"4\",
        \"ListValues\": {
          \"ListValue\": {
            \"id\": \"74398\",
            \"displayName\": \"Yes\",
            \"_value\": \"needs 22794 as field name\"
          }
        }
      },
      {
        \"id\": \"22813\",
        \"guid\": \"89760b4f-49be-40ad-8429-89c247e3e95a\",
        \"type\": \"1\",
        \"p\": \"needs 22813 as field name\"
      },
      {
        \"id\": \"22803\",
        \"guid\": \"03b6c826-e15c-4356-89e8-b0bd509aaeb5\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-06-15T00:00:00Z\",
        \"_value\": \"needs 22803 as field name\"
      },
      {
        \"id\": \"22804\",
        \"guid\": \"d7683f9c-97bb-461a-97df-36ec6596b4fc\",
        \"type\": \"1\",
        \"p\": \"needs 22804 as field name\"
      },
      {
        \"id\": \"22805\",
        \"guid\": \"33386a3a-c331-4d8c-9825-166c0a5235c2\",
        \"type\": \"3\",
        \"xmlConvertedValue\": \"2023-06-15T00:00:00Z\",
        \"_value\": \"needs 22805 as field name\"
      },
      {
        \"id\": \"22806\",
        \"guid\": \"cd486293-9857-475c-9da3-a06f836edb59\",
        \"type\": \"1\",
        \"p\": \"needs 22806 as field name\"
      }
    ]
  }
}"
| spath
``` emulation above ```

 

 

 

 

 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

To get p which takes either the single or mv field, use

| eval p_values=coalesce(p, 'p{}')

I don't understand the ListValues.Listvalue part - these are not an array, so only single value fields in your example. Can you give an example of what in your example data should get mapped in the _value and p cases to the top array?

 

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...