Getting Data In

Multi field combination for JSON file question

darkelfaxe
New Member

I'm totally new to splunk, I have this JSON file already indexed:

{"EventType":2,"EventData":{"Values":[{"Status":1,"Name":"BOT1"},{"Status":0,"Name":"BOT2"},{"Status":0,"Name":"BOT3"},{"Status":1,"Name":"BOT4"}],"Subject":"Resource Online Status","Source":"Dashboard"}}

I need to create a table which contains the Values in separate columns like this:

ID STATUS RESOURCE
1 1 BOT1
2 0 BOT2
3 0 BOT3
4 1 BOT4

I'm trying the following:
index="main" resource online Status | table "EventData.Values{}.Name" "EventData.Values{}.Status" | sort -_time asc | head 1
But it gives me this:

ID EventData.Values{}.Name EventData.Values{}.Status
1 BOT1 BOT2 BOT3 BOT4 1 0 0 1

How can I combine the two columns to generate the desired format?

Thank you!

Tags (2)
0 Karma

niketn
Legend

@darkelfaxe, if your issue is that you are getting a multi-value results from your JSON, try adding the following to your existing query

index="main" resource online Status
| head 1
| fields "EventData.Values{}.Name" "EventData.Values{}.Status"
| rename "EventData.Values{}.*" as "*"
| eval EventData=mvzip(Name,Status)
| fields EventData
| mvexpand EventData
| makemv EventData delim=","
| eval RESOURCE=mvindex(EventData,0),STATUS=mvindex(EventData,1)
| streamstats count as ID
| fields ID STATUS RESOURCE

Please try out and confirm.
Following is a run anywhere example based on the sample data provided:

| makeresults
| eval _raw="{\"EventType\":2,\"EventData\":{\"Values\":[{\"Status\":1,\"Name\":\"BOT1\"},{\"Status\":0,\"Name\":\"BOT2\"},{\"Status\":0,\"Name\":\"BOT3\"},{\"Status\":1,\"Name\":\"BOT4\"}],\"Subject\":\"Resource Online Status\",\"Source\":\"Dashboard\"}}"
| spath
| head 1
| table "EventData.Values{}.Name" "EventData.Values{}.Status"
| rename "EventData.Values{}.*" as "*"
| eval EventData=mvzip(Name,Status)
| fields EventData
| mvexpand EventData
| makemv EventData delim=","
| eval RESOURCE=mvindex(EventData,0),STATUS=mvindex(EventData,1)
| streamstats count as ID
| fields ID STATUS RESOURCE

PS:
1. table is a transforming command, you should avoid use fields instead.
2. Splunk events are sorted in reverse chronological order by default i.e. sort - _time just consumes performance if you are only interested in latest event.
3. head 1 should be first pipe after index search as it pulls only one event from indexer. This should improve performance of your search.

Refer to Writing Better Searches Splunk documentation to better understand above points.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...