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!
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*
@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
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
@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