topic Re: How to parse a JSON array into Splunk table? in Splunk Search

How to parse a JSON array into Splunk table?

bshega — Tue, 07 Mar 2017 19:58:24 GMT

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:

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.

Re: How to parse a JSON array into Splunk table?

somesoni2 — Tue, 07 Mar 2017 20:42:24 GMT

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?

Re: How to parse a JSON array into Splunk table?

bshega — Tue, 07 Mar 2017 20:43:44 GMT

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"

Re: How to parse a JSON array into Splunk table?

bshega — Tue, 07 Mar 2017 20:53:08 GMT

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]

Re: How to parse a JSON array into Splunk table?

somesoni2 — Tue, 07 Mar 2017 21:12:41 GMT

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"

Re: How to parse a JSON array into Splunk table?

niketn — Tue, 07 Mar 2017 21:14:59 GMT

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-valuefieldextractionsatsearch-time

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

Re: How to parse a JSON array into Splunk table?

bshega — Wed, 08 Mar 2017 13:04:03 GMT

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!

Re: How to parse a JSON array into Splunk table?

niketn — Thu, 09 Mar 2017 10:25:56 GMT

@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.