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
Champion

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
Champion

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
Champion

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
Champion

You're welcome 🙂

0 Karma

manjunathmeti
Champion

You're welcome!

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...