topic Re: How to convert JSON array of Name/Value pairs to field/value for event in Getting Data In

How to convert JSON array of Name/Value pairs to field/value for event

jordomo — Tue, 29 Sep 2020 18:38:56 GMT

I am working with JSON data... which looks like this:

{"DN" : "CN=Test Group, OU=Test OU, O=\"Corp.com\"", "sourceId" : "TEST Image", "sequenceId" : 1, "description" : "doing work", "Request" : {"capability" : {"name" : "prod02", "Attributes" : [{"name" : "Model", "value" : "ea123"}, {"name" : "Verson", "value" : "1.2.3"}, {"name" : "Debug_ENABLE", "value" : "FALSE"}, {"name" : "RAM_ENABLE", "value" : "True"}, {"name" : "SW_ID", "value" : "0x0003ed"}]}}, "signatureResponse" : {"statusCode" : "1"}}

same data easier to read:

{
"DN":"CN=Test Group, OU=Test OU, O="Corp.com"",
"sourceId":"TEST Image",
"sequenceId":1,
"description":"doing work",
"Request":{
   "capability":{
       "name":"prod02",
       "Attributes":[
              {
                   "name":"Model",
                   "value":"ea123"
            },
               {
                   "name":"Version",
                   "value":"1.2.3"
            },
               {
                    "name":"Debug_ENABLE",
                    "value":"FALSE"
             },
                {
                    "name":"RAM_ENABLE",
                    "value":"True"
             },
                {
                     "name":"SW_ID",
                     "value":"0x0003ed"
             }
         ]
     }
  },
"signatureResponse":{
"statusCode":"1"
  }
}

When run through spath all the fields get parsed out as expected... except I want the array contents to be individual fields for the event.... right now the array fields have multiple values. So for the event in the image I want/need fields called "Model" "Verson" "RAM_Enabled" etc

Instead of just Request.capability.Attributes{}.name and Request.capability.Attributes{}.value which contain the multiple values.

Re: How to convert JSON array of Name/Value pairs to field/value for event

niketn — Sat, 24 Mar 2018 04:11:02 GMT

[UPDATED ANSWER]

@jordomo, try the following updated answer (hopefully the sequenceId is the unique key for name and value to be correlated):

<yourBaseSearch>
| spath 
| rename Request.capability.Attributes{}.* as * 
| eval kvdata=mvzip(name,value) 
| fields - name value _* 
| mvexpand kvdata 
| eval kvdata=split(kvdata,",") 
| eval name=mvindex(kvdata,0),value=mvindex(kvdata,1) 
| fields - kvdata
| table sequenceId name value
| xyseries sequenceId name value

Following is a run anywhere search example:

| makeresults 
| eval _raw="{\"DN\" : \"CN=Test Group, OU=Test OU, O=\\\"Corp.com\\\"\", \"sourceId\" : \"TEST Image\", \"sequenceId\" : 1, \"description\" : \"doing work\", \"Request\" : {\"capability\" : {\"name\" : \"prod02\", \"Attributes\" : [{\"name\" : \"Model\", \"value\" : \"ea123\"}, {\"name\" : \"Verson\", \"value\" : \"1.2.3\"}, {\"name\" : \"Debug_ENABLE\", \"value\" : \"FALSE\"}, {\"name\" : \"RAM_ENABLE\", \"value\" : \"True\"}, {\"name\" : \"SW_ID\", \"value\" : \"0x0003ed\"}]}}, \"signatureResponse\" : {\"statusCode\" : \"1\"}}" 
| append 
    [| makeresults 
    | eval _raw="{\"DN\" : \"CN=Test Group, OU=Test OU, O=\\\"Corp.com\\\"\", \"sourceId\" : \"TEST Image\", \"sequenceId\" : 2, \"description\" : \"doing work\", \"Request\" : {\"capability\" : {\"name\" : \"prod02\", \"Attributes\" : [{\"name\" : \"Model\", \"value\" : \"ea234\"}, {\"name\" : \"Verson\", \"value\" : \"1.2.1\"}, {\"name\" : \"Debug_ENABLE\", \"value\" : \"TRUE\"}, {\"name\" : \"RAM_ENABLE\", \"value\" : \"False\"}, {\"name\" : \"SW_ID\", \"value\" : \"0x0003ab\"}]}}, \"signatureResponse\" : {\"statusCode\" : \"0\"}}"] 
| spath 
| rename Request.capability.Attributes{}.* as * 
| eval kvdata=mvzip(name,value) 
| fields - name value _* 
| mvexpand kvdata 
| eval kvdata=split(kvdata,",") 
| eval name=mvindex(kvdata,0),value=mvindex(kvdata,1) 
| fields - kvdata
| table sequenceId name value
| xyseries sequenceId name value

Re: How to convert JSON array of Name/Value pairs to field/value for event

jordomo — Sat, 24 Mar 2018 14:57:31 GMT

This doesn't do what I was hoping for... it seems to be separating out the "Name" and "Value" and creating additional events. I need the names in the Attributes array to become new fields/columns of this one event. And the values of the Attributes array to be the contents of the new names fields. In the JSON data the attributes are in pairs of name/value.

Re: How to convert JSON array of Name/Value pairs to field/value for event

jordomo — Sat, 24 Mar 2018 14:58:16 GMT

Here is another picture with what I am hoping for...

Re: How to convert JSON array of Name/Value pairs to field/value for event

javiergn — Sat, 24 Mar 2018 15:53:37 GMT

Hi, let me know if this helps:

| your generating search here
| eval req_cap_attr = spath(myJSON, "Request.capability.Attributes{}")
| spath input=myJSON
| fields - Request.capability.Attributes*
| mvexpand req_cap_attr
| rex field=req_cap_attr "\"[^\"]+\"\s*:\s*\"(?<key>[^\"]+)\"\s*\,\s*\"[^\"]+\"\s*:\s*\"(?<value>[^\"]+)\""
| eval {key} = value
 | stats values(*) as * by myJSON
 | fields - myJSON, key, value, req_cap_attr

For instance:

| stats count
| eval myJSON = "
{
 \"DN\":\"CN=Test Group, OU=Test OU, O=Corp.com\",
 \"sourceId\":\"TEST Image\",
 \"sequenceId\":1,
 \"description\":\"doing work\",
 \"Request\":{
    \"capability\":{
        \"name\":\"prod02\",
        \"Attributes\":[
               {
                    \"name\":\"Model\",
                    \"value\":\"ea123\"
             },
                {
                    \"name\":\"Version\",
                    \"value\":\"1.2.3\"
             },
                {
                     \"name\":\"Debug_ENABLE\",
                     \"value\":\"FALSE\"
              },
                 {
                     \"name\":\"RAM_ENABLE\",
                     \"value\":\"True\"
              },
                 {
                      \"name\":\"SW_ID\",
                      \"value\":\"0x0003ed\"
              }
          ]
      }
   },
 \"signatureResponse\":{
 \"statusCode\":\"1\"
   }
 }
"
| eval req_cap_attr = spath(myJSON, "Request.capability.Attributes{}")
| spath input=myJSON
| fields - Request.capability.Attributes*
| mvexpand req_cap_attr
| rex field=req_cap_attr "\"[^\"]+\"\s*:\s*\"(?<key>[^\"]+)\"\s*\,\s*\"[^\"]+\"\s*:\s*\"(?<value>[^\"]+)\""
| eval {key} = value
 | stats values(*) as * by myJSON
 | fields - myJSON, key, value, req_cap_attr

Output:

Thanks,
J

Re: How to convert JSON array of Name/Value pairs to field/value for event

niketn — Sat, 24 Mar 2018 16:05:22 GMT

@jordomom, sorry I missed that in the question, I have updated my answer above. If sequenceId is not unique you can use streamstats to generate sequence number:

<yourBaseSearch>
| spath 
| streamstats count as sequneceId
 <remainingSearchAsAbove>

Re: How to convert JSON array of Name/Value pairs to field/value for event

jordomo — Mon, 26 Mar 2018 16:18:58 GMT

Thanks @javiergn. This is doing what I hoped but I am having a hard time following exactly what is happening here. Any chance you could walk me through each line?

Regardless nice work! Thanks!

Re: How to convert JSON array of Name/Value pairs to field/value for event

javiergn — Tue, 29 Sep 2020 18:42:10 GMT

Sure thing.
Here we go:

| eval req_cap_attr = spath(myJSON, "Request.capability.Attributes{}")

Parse all the attributes in the Request.capability.Attributes and store that in the variable req_cap_attr.

| spath input=myJSON

Now parse the whole JSON file using spath in order to extract the key value pairs automatically.

 | fields - Request.capability.Attributes*

Remove the fields we don't need because we already have their info the req_cap_attr variable.

 | mvexpand req_cap_attr

req_cap_attr is a multivalued variable, therefore we want to expand it into individual events so that we can perform operations against them

 | rex field=req_cap_attr "\"[^\"]+\"\s*:\s*\"(?<key>[^\"]+)\"\s*\,\s*\"[^\"]+\"\s*:\s*\"(?<value>[^\"]+)\""

Now that we have individual events, go an extract the key value pairs from your attributes.

 | eval {key} = value

What this syntax here does is to basically create dynamic field names based on the content of the key field, for instance, if key=Model, the new field will be named "Model" and the value is the content of the value field.

  | stats values(*) as * by myJSON

Now that you have all the data you need in the relevant fields, you can use stats to summarise it in a tabular format, where all the fields and their values are presented in one header row and one values row.

  | fields - myJSON, key, value, req_cap_attr

Do some cleanup and show only the fields you really care about.

Hope that helps. If you want me to elaborate more any particular line let me know.

Thanks,
J

Re: How to convert JSON array of Name/Value pairs to field/value for event

jordomo — Tue, 27 Mar 2018 16:02:57 GMT

Rockstar! Thanks!

Re: How to convert JSON array of Name/Value pairs to field/value for event

dubiza — Mon, 13 May 2019 20:20:00 GMT

@javiergn - This looks almost exactly like what I'm trying to do with some JSON data. I'm trying to understand what you're doing with the search, however, I'm not sure my skills in Splunk are up to the higher level explanation you've given.

I'm trying to go through the Splunk docs on spath to gain a better understanding but not having a great time of it. Can you maybe explain a bit more in detail how spath() is used with eval? What does "myJSON" represent as the first parameter in that first line? How does that related to | spath input=myJSON?

Thanks!

Re: How to convert JSON array of Name/Value pairs to field/value for event

javiergn — Wed, 30 Sep 2020 00:35:06 GMT

Hi @dubiza, sorry for the delay.

Spath can work as a function (within eval): https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/TextFunctions#spath.28X.2CY.29

Or as a command: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath

But the behaviour is pretty much the same.

myJSON is just a random variable name I chose to test the raw json data provided in the question, but this could be your actual event, in which case you would just need to change that in the first spath. Now, that first spath extracts from your raw json (myJSON), based on the path
"Request.capability.Attributes{}" into the new variable req_cap_attr.

The second spath extracts everything from myJSON automatically, creating field names based on the JSON hierarchy. The reason req_cap_attr is extracted separately is because we would like to create new field names based on the values inside the "Request.capability.Attributes{}" branch in a dynamic way by first extracting them with regex:

  | rex field=req_cap_attr "\"[^\"]+\"\s*:\s*\"(?<key>[^\"]+)\"\s*\,\s*\"[^\"]+\"\s*:\s*\"(?<value>[^\"]+)\""

And then dynamically generating those field names with the following syntax:

| eval {key} = value

That syntax above is the one responsible for the field names in the attached screenshot above.

Hope that clarifies a little. It's been more than 1 year since I answered this.

By the way, if you like my answer don't forget to upvote it so that others can find it quicker too.

Thanks,
Javier