Splunk Search

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

matoulas
Path Finder

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.

alt text

Labels (1)
Tags (1)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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
0 Karma

matoulas
Path Finder

Awesome It worked! Thank you so much

0 Karma

matoulas
Path Finder

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!

0 Karma

matoulas
Path Finder

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

0 Karma

matoulas
Path Finder

Another way.

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

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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

0 Karma

matoulas
Path Finder

It worked! Thanks again for all your help!

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

You're welcome 🙂

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

You're welcome!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...