topic Re: How to extract JSON data format using extract field in Splunk? in Splunk Search

How to extract JSON data format using extract field in Splunk?

matoulas — Fri, 19 Jun 2020 01:06:32 GMT

Hi,

I have JSON data format that send to Splunk as below:

{ "timestamp": "2020-03-12T18:18:48+00:00", "siteid": "CPM-1600-2-EC-158", "location": "LABRACK1", "powerunit": "1", "outletmetering": "1", "ats": "0", "branchcount": "4", "plugcount": "16", "powerfactor": "100", "powereff": "100", "powerdatacount": "1", "powerdata": [{ "timestamp": "2020-03-12T18:18:48+00:00", "plug1": [{ "plugname": "PaloAlto5220", "voltage": "125.00", "current": "6.00", "branch": "1" }], "plug2": [{ "plugname": "Cisco5220Meraki", "voltage": "125.00", "current": "6.00", "branch": "1" }], "plug3": [{ "plugname": "Outlet_A3", "voltage": "125.00", "current": "1.40", "branch": "2" }] }] }

How do I extract JSON data format using the extract field in Splunk? I want to group the data like these. Can someone please point me to the right way of how to do it.

Re: How to extract JSON data format using extract field in Splunk?

manjunathmeti — Thu, 12 Mar 2020 20:39:12 GMT

hi @matoulas,

Try this:

| makeresults 
| eval _raw="{
 \"timestamp\": \"2020-03-12T18:18:48+00:00\",
 \"siteid\": \"CPM-1600-2-EC-158\",
 \"location\": \"LABRACK1\",
 \"powerunit\": \"1\",
 \"outletmetering\": \"1\",
 \"ats\": \"0\",
 \"branchcount\": \"4\",
 \"plugcount\": \"16\",
 \"powerfactor\": \"100\",
 \"powereff\": \"100\",
 \"powerdatacount\": \"1\",
 \"powerdata\": [{
 \"timestamp\": \"2020-03-12T18:18:48+00:00\",
 \"plug1\": [{
 \"plugname\": \"PaloAlto5220\",
 \"voltage\": \"125.00\",
 \"current\": \"6.00\",
 \"branch\": \"1\"
    }],
 \"plug2\": [{
 \"plugname\": \"Cisco5220Meraki\",
 \"voltage\": \"125.00\",
 \"current\": \"6.00\",
 \"branch\": \"1\"
    }],
 \"plug3\": [{
 \"plugname\": \"Outlet_A3\",
 \"voltage\": \"125.00\",
 \"current\": \"1.40\",
 \"branch\": \"2\"
    }]
 }]
 }" 
| spath 
| spath path=powerdata{} output=powerdata 
| eval powerdata=split(powerdata, "}],") 
| mvexpand powerdata 
| rex field=powerdata "\"(?<plug>plug\d+)\"[^\w]+\"plugname\":\s\"(?<plugname>[\w]+)[^\w]+voltage\":\s\"(?<voltage>[\d\.]+)[^\w]+current.*(?<current>[\d\.]+)[^\w]+branch\":\s\"(?<branch>[\d]+)\""
| table plug, plugname, voltage, current, branch, timestamp, siteid, location, powerunit, outletmetering, ats, branchcount, plugcount, powerfactor, powereff, powerdatacount, powerdata{}.timestamp 
| rename powerdata{}.timestamp as powerdata_timestamp

Re: How to extract JSON data format using extract field in Splunk?

matoulas — Thu, 12 Mar 2020 20:55:26 GMT

Awesome It worked! Thank you so much

Re: How to extract JSON data format using extract field in Splunk?

manjunathmeti — Thu, 12 Mar 2020 20:59:50 GMT

You're welcome!

Re: How to extract JSON data format using extract field in Splunk?

matoulas — Thu, 12 Mar 2020 21:20:47 GMT

How to display or add location, siteid append to the column.

Re: How to extract JSON data format using extract field in Splunk?

matoulas — Thu, 12 Mar 2020 21:49:07 GMT

Another way.

How to add a column to the results table based on an existing field above like loction, siteid and etc...?

Re: How to extract JSON data format using extract field in Splunk?

manjunathmeti — Fri, 13 Mar 2020 06:06:01 GMT

Then it's better to use regex to extract fields and values. I updated answer. Try that.

Re: How to extract JSON data format using extract field in Splunk?

matoulas — Fri, 13 Mar 2020 17:57:44 GMT

It worked! Thanks again for all your help!

Re: How to extract JSON data format using extract field in Splunk?

manjunathmeti — Fri, 13 Mar 2020 18:41:01 GMT

You're welcome 🙂

Re: How to extract JSON data format using extract field in Splunk?

matoulas — Fri, 13 Mar 2020 20:03:52 GMT

One last question? All the column table that were append to it like location, siteid and etc. The result show two data entry for every row. How to make it to not duplicate the data. Thanks in advance!