topic Re: Noob Question - Parsing JSON in Splunk Search

Noob Question - Parsing JSON

ikoth — Fri, 26 Jul 2024 14:46:04 GMT

Hi,

complete Splunk beginner here, so sorry it this is a stupid question.

I'm trying to chart some data that I'm pulling from an MQTT broker. The Splunk MQTT Modular Input app is doing its thing and data is arriving every 5 minutes.

Using the most basic query ( source="mqtt://MeteoMQTT" ) gives these results:

Fri Jul 26 15:24:46 BST 2024 name=mqtt_msg_received event_id= topic=meteobridge msg={"meteoTemp":17.9,"meteoHumidity":64,"meteoRainlasthour":0,"meteoWindSpeed":6.04,"meteoWindDirection":"SW","meteolunarPercent":67.3}

What I really want to do though is to break out the values from the most recent data poll into separate "elements" that can then be added to a dashboard.

I tried using the spath command:

source="mqtt://MeteoMQTT" | spath output=meteoTemp path=meteoTemp

But that just returned the whole object again.

So, how can i parse out the different values (meteoTemp, meteoHumidity, meteoRainlasthour, etc), so that i can add their most recent values as individual dashboard elements please?

TIA.

Re: Noob Question - Parsing JSON

yuanliu — Fri, 26 Jul 2024 21:33:01 GMT

Just extract the content of "msg" into a new field, then apply spath

| rex "msg=(?<msg>.+)" | spath input=msg

Here is the output from your sample data

meteoHumidity	meteoRainlasthour	meteoTemp	meteoWindDirection	meteoWindSpeed	meteolunaPercent	msg
64	0	17.9	SW	6.04	67.3	{"meteoTemp":17.9,"meteoHumidity":64,"meteoRainlasthour":0,"meteoWindSpeed":6.04,"meteoWindDirection":"SW","meteolunarPercent":67.3}

This is an emulation for you to play with and compare with real data.

| makeresults | eval _raw = "Fri Jul 26 15:24:46 BST 2024 name=mqtt_msg_received event_id= topic=meteobridge msg={\"meteoTemp\":17.9,\"meteoHumidity\":64,\"meteoRainlasthour\":0,\"meteoWindSpeed\":6.04,\"meteoWindDirection\":\"SW\",\"meteolunarPercent\":67.3}" ``` data emulation above ```

Re: Noob Question - Parsing JSON

ikoth — Fri, 26 Jul 2024 19:10:46 GMT

Thanks for the quick reply. That has helped in that it's extracted the "msg data" section from the headers, but I'm still unsure of how to parse each individual value ("meteoTemp", or "meteolunarPercent" for example) into separate objects so they can represented by separate and different "widgets" on a dashboard. Sticking with those same two examples, I ultimately want to plot temperature on a line chart, but show lunarPercent as a single value

Thanks.

Re: Noob Question - Parsing JSON

yuanliu — Fri, 26 Jul 2024 21:34:27 GMT

headers, but I'm still unsure of how to parse each individual value ("meteoTemp", or "meteolunarPercent" for example) into separate objects so they can represented by separate and

I am confused. Have you viewed my sample output? meteoTemp and meteolunarPercent are extracted by spath, and tabulated in my example. You can plot them however you want. For example,

source="mqtt://MeteoMQTT" | rex "msg=(?<msg>.+)" | spath input=msg | timechart avg(meteoTemp) as avgMeteoTemp max(meteolunaPercent) as maxMeteolunaPercent

If you do not get those fields, you need to play with my emulation and carefully compare with your raw data and post data that is representative of the actual data structure.

Re: Noob Question - Parsing JSON

ikoth — Sat, 27 Jul 2024 09:40:45 GMT

Thank you! I'd forgotten / didn't realise I could chain terms together in searches. Your last example triggered the lightbulb.

Your help is much appreciated!"