Getting Data In

How to convert JSON array of Key/Value pairs to Column/Value?

mlevsh
Builder

Lets say we have Json data in the following format ( using 2 events as an example)

Event 1)

Time    Event 5/19/19 2:26:06.730 PM    
{ [-] 
   account_id: 1234567890 
   created_time: 2019-05-16T15:00:45.000Z 
   region: us-1
   tags: [ [-] 
     { [-] 
       Key: environment 
       Value: env1 
     } 
     { [-] 
       Key: data_class 
       Value: Class1 
     } 
     { [-] 
       Key: built_by 
       Value: group1
     } 
     { [-] 
       Key: description 
       Value: anything 
     } 
     { [-] 
       Key: application_id 
       Value: abc 
     } 
}

Event 2:

5/19/19 2:26:06.620 PM  
{ [-] 
   account_id: 1234567891 
   created_time: 2019-05-13T21:31:02.710Z 
   region: us-1 
   tags: [ [-] 
     { [-] 
       Key: environment 
       Value: env2 
     } 
     { [-] 
       Key: data_class 
       Value: Class1 
     } 
     { [-] 
       Key: built_by 
       Value: group1
     } 
     { [-] 
       Key: description 
       Value: anything 
     } 
     { [-] 
       Key: application_id 
       Value: def 
     } 
   ] 

The objective to display it in the following format: each Key in Key/Value pair needs to be a Column header and each Value needs to belong to corresponding Header(Key) , for example the above data needs to be in the following format

Environment       data_class      build_by      description      application_id
env1              Class1          group1      anything            abc
env2              Class2          group1      anything            def

Any ideas will be appreciated!

Tags (2)
0 Karma

to4kawa
Ultra Champion

Sample query:

| makeresults 
| eval _raw="{\"account_id\": \"1234567890\",\"created_time\": \"2019-05-16T15:00:45.000Z\",\"region\": \"us-1\",\"tags\": [ {\"Key\": \"environment\",\"Value\": \"env1\" }, {\"Key\" :\"data_class\",\"Value\": \"Class1\"},{\"Key\": \"built_by\",\"Value\": \"group1\" },{\"Key\": \"description\",\"Value\": \"anything\" },{\"Key\":\"application_id\",\"Value\": \"abc\" }] }" 
| spath
| eval _raw=mvzip('tags{}.Key','tags{}.Value',"=")
| kv
| fields - _* tags*

Recommend:

your_search
| spath
| eval _raw=mvzip('tags{}.Key','tags{}.Value',"=")
| kv
| fields - _* tags*
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@mlevsh

Can you please try this?

index=YOUR_INDEX | rename tags{}.Key as Keys, tags{}.Value as Values | eval temp=mvzip(Keys,Values) | mvexpand temp | eval Key=mvindex(split(temp,","),0),Value=mvindex(split(temp,","),1) | table account_id Key Value | eval {Key} = Value | stats values(*) as * by account_id | fields - Key, Value

My Sample Search:

| makeresults | eval _raw="{\"account_id\": \"1234567890\",\"created_time\": \"2019-05-16T15:00:45.000Z\",\"region\": \"us-1\",\"tags\": [ {\"Key\": \"environment\",\"Value\": \"env1\" }, {\"Key\" :\"data_class\",\"Value\": \"Class1\"},{\"Key\": \"built_by\",\"Value\": \"group1\" },{\"Key\": \"description\",\"Value\": \"anything\" },{\"Key\":\"application_id\",\"Value\": \"abc\" }] }" | kv | rename tags{}.Key as Keys, tags{}.Value as Values | eval temp=mvzip(Keys,Values) | mvexpand temp | eval Key=mvindex(split(temp,","),0),Value=mvindex(split(temp,","),1) | table account_id Key Value | eval {Key} = Value | stats values(*) as * by account_id | fields - Key, Value

Thanks

0 Karma

koshyk
Super Champion

Your payload is not pure JSON, but a mixed message of timestamp && JSON
So there are two steps
1. Do at indextime for extracting timestamp and line breaker etc.
2. Do at searchtime for pure JSON message using REPORT-

The link https://answers.splunk.com/answers/117121/extract-json-data-within-the-logs-json-mixed-with.html will explain you how to do it using props & transforms.

Alternatively, if you Just wanted to do at Search time, you can use SPATH command. Feed the pure JSON into spath command http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/spath

0 Karma

mlevsh
Builder

@koshyk,
We are running Slunk Cloud and cannot easily make any modifications on Indexers or Search heads

We did tried to use spath command, but were not successful so far

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...