Splunk Search

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

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

Influencer

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

Influencer

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

Path Finder

Awesome It worked! Thank you so much

0 Karma

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

Path Finder

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

0 Karma

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

Influencer

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

0 Karma

Path Finder

It worked! Thanks again for all your help!

0 Karma

Influencer

You're welcome 🙂

0 Karma

Influencer

You're welcome!

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!