Splunk Search

How to parse a JSON array into Splunk table?

New Member

I have been searching for how to do this and I haven't really come across anything that matches my use case.

I have the following object in Splunk:

NewRecordingEvent

I am creating a table to display all of the data and everything is working except for the outlet_states the field is just blank for all of them.

How can I parse these so that they show up? I'd also eventually like to be able to make them like Outlet 1: Off, Outlet 2: Off, etc...

Any help is greatly appreciated.

0 Karma

Legend

If you search with spath output=outlet_states path=object.outlet_states you should get a multivalue result. If you want to get individual you will have to use spath output=outlet1 path=object.outlet_states{0} and similar commands.

Which one do you want to use?

Refer to Splunk Documentation on spath, which should have examples for both.http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath

You can also enable automatic Key Value field extraction by setting KV_MODE=json in props.conf
Refer to the documentation: https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Automatickey-valuefieldextractionsatse...

In either case if you want to convert "false" to "off" you can use replace command.

For example your first query can be changed to

 <yourBaseSearch>
| spath output=outlet_states path=object.outlet_states |
| replace "false" with "off" in outlet_states 

Similarly your second option to

 <yourBaseSearch>
| spath output=outlet_states1 path=object.outlet_states{0} |
| replace "false" with "off" in outlet_states1 
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

New Member

Ok, this is great information. so how do I display the values if I do the spath output=outlet_states path=object.outlet_states right now they are just coming up blank if I do table outlet_states, Thanks!

0 Karma

Legend

@bshega... Will it be possible for you to copy paste a JSON mocked data sample as a code block? Use the 1010 button while pasting the code so the Splunk Answer does not interpret as special characters.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Revered Legend

The json array fields should be appearing as multivalued fields when you table them. Are you not seeing them? What's the search you're using?

0 Karma

New Member

I think I see I have to do something like spath output=outlet1 path=object.outlet_states{0} I thought maybe I could have just gotten an array in the field like [true, true, false, false]

0 Karma

Revered Legend

Can you try this

index="iot-production" trigger="NewRecordingEvent" 
| spath 
| rename object.* as *
| convert timeformat="%m-%d-%Y %l:%M %p" ctime(_time) AS created_at_fmt 
| search serial_number=1004039 
| table name serial_number created_at_fmt current_temperature mode target_temperature preset_greeting preset_title outlet_states timer_length active_preset rssi 
| rename name as "Name", serial_number as "Serial Number", created_at_fmt as "Time", current_temperature as "Current Temperature", mode as "Mode", target_temperature as "Target Temperature", preset_greeting as "Preset Greeting", preset_title as "Preset Title", outlet_states as "Outlet States", timer_length as "Timer Length", active_preset as "Active Preset", rssi as "RSSI"
0 Karma

New Member

This is the query that I'm using

index="iot-production" trigger="NewRecordingEvent" | spath output=name path=object.name | spath output=serial_number path=object.serial_number | spath output=action_time path=object.created_at | convert timeformat="%m-%d-%Y %l:%M %p" ctime(_time) AS created_at_fmt | spath output=current_temperature path=object.current_temperature | spath output=mode path=object.mode | spath output=target_temperature path=object.target_temperature | spath output=preset_greeting path=object.preset_greeting | spath output=preset_title path=object.preset_title | spath output=outlet_states path=object.outlet_states | spath output=timer_length path=object.timer_length | spath output=active_preset path=object.active_preset | spath output=rssi path=object.rssi | search serial_number=1004039 | table name serial_number created_at_fmt current_temperature mode target_temperature preset_greeting preset_title outlet_states timer_length active_preset rssi | rename name as "Name", serial_number as "Serial Number", created_at_fmt as "Time", current_temperature as "Current Temperature", mode as "Mode", target_temperature as "Target Temperature", preset_greeting as "Preset Greeting", preset_title as "Preset Title", outlet_states as "Outlet States", timer_length as "Timer Length", active_preset as "Active Preset", rssi as "RSSI"
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!