Splunk Search

Convert single and multivalue JSON to table

shierlawa
Engager

Hi all,

I haven't been able to work this out and I was hoping someone can help answer. I am looking to create a table using the JSON similar to below:

{
"type": "Data",
"version": "1.0",
"meter": {
"serial": "12345678"
},
"payload": {
"PlantNumber": "12345678"
},
"Configuration": {
"IntervalLength": "PT30M",
"Channels": [
{
"Source": "PositiveTotal",
},
{
"Source": "NegativeTotal",
},
]
},
"Channels": [
{
"Intervals": [
{
"Time": "2019-08-12T01:30:00.000",
"Value": "0"
},
{
"Time": "2019-08-12T02:00:00.000",
"Value": "0"
},
{
"Time": "2019-08-12T02:30:00.000",
"Value": "0"
},
]
},
{
"Intervals": [
{
"Time": "2019-08-12T01:30:00.000",
"Value": "53"
},
{
"Time": "2019-08-12T02:00:00.000",
"Value": "67"
},
{
"Time": "2019-08-12T02:30:00.000",
"Value": "66"
},
]
},
]
}

Ideally my table would look like:

PlantNumber | Source | Time | Value
12345678 | PositiveTotal | 2019-08-12T01:30:00.000 | 0
12345678 | PositiveTotal | 2019-08-12T02:00:00.000 | 0
12345678 | PositiveTotal | 2019-08-12T02:30:00.000 | 0
12345678 | NegativeTotal | 2019-08-12T01:30:00.000 | 53
12345678 | NegativeTotal | 2019-08-12T01:30:00.000 | 67
12345678 | NegativeTotal | 2019-08-12T01:30:00.000 | 66

I've tried combinations of spath and mvzip but I am at a loss of how to generate this table format, any help would be appreciated.

0 Karma
1 Solution

DavidHourani
Super Champion

Hi @shierlawa,

You could use pointers within the spath to get the positive total alone and the negative total alone. Below for the run anywhere search for positive total :

    |makeresults| eval _raw="{
    \"type\": \"Data\",
    \"version\": \"1.0\",
    \"meter\": {
    \"serial\": \"12345678\"
    },
    \"payload\": {
    \"PlantNumber\": \"12345678\"
    },
    \"Configuration\": {
    \"IntervalLength\": \"PT30M\",
    \"Channels\": [
    {
    \"Source\": \"PositiveTotal\"
    },
    {
    \"Source\": \"NegativeTotal\"
    }
    ]
    },
    \"Channels\": [
    {
    \"Intervals\": [
    {
    \"Time\": \"2019-08-12T01:30:00.000\",
    \"Value\": \"0\"
    },
    {
    \"Time\": \"2019-08-12T02:00:00.000\",
    \"Value\": \"0\"
    },
    {
    \"Time\": \"2019-08-12T02:30:00.000\",
    \"Value\": \"0\"
    }
    ]
    },
    {
    \"Intervals\": [
    {
    \"Time\": \"2019-08-12T01:30:00.000\",
    \"Value\": \"53\"
    },
    {
    \"Time\": \"2019-08-12T02:00:00.000\",
    \"Value\": \"67\"
    },
    {
    \"Time\": \"2019-08-12T02:30:00.000\",
    \"Value\": \"66\"
    }
    ]
    }
    ]
    }"  
    |spath input=_raw output=PlantNumber payload{}.PlantNumber
    |spath input=_raw output=Source Configuration.Channels{0}.Source
    |spath input=_raw output=Time Channels{0}.Intervals{}.Time
    |spath input=_raw output=Value Channels{0}.Intervals{}.Value
    | eval temp=mvzip(Value, Time,"###")
    | stats count by temp,PlantNumber,Source
    | rex field=temp "(?<Value>.+)###(?<Time>.+)"
    | table PlantNumber, Source, Time, Value

Let me know if that helps.

PS: You had some errors in your JSON formatting, I fixed them for the run anywhere search.
Cheers,
David

View solution in original post

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval _raw="{
\"type\": \"Data\",
\"version\": \"1.0\",
\"meter\": {
\"serial\": \"12345678\"
},
\"payload\": {
\"PlantNumber\": \"12345678\"
},
\"Configuration\": {
\"IntervalLength\": \"PT30M\",
\"Channels\": [
{
\"Source\": \"PositiveTotal\",
},
{
\"Source\": \"NegativeTotal\",
},
]
},
\"Channels\": [
{
\"Intervals\": [
{
\"Time\": \"2019-08-12T01:30:00.000\",
\"Value\": \"0\"
},
{
\"Time\": \"2019-08-12T02:00:00.000\",
\"Value\": \"0\"
},
{
\"Time\": \"2019-08-12T02:30:00.000\",
\"Value\": \"0\"
},
]
},
{
\"Intervals\": [
{
\"Time\": \"2019-08-12T01:30:00.000\",
\"Value\": \"53\"
},
{
\"Time\": \"2019-08-12T02:00:00.000\",
\"Value\": \"67\"
},
{
\"Time\": \"2019-08-12T02:30:00.000\",
\"Value\": \"66\"
},
]
},
]
}"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| spath
| rex max_match=2 "(?ms)\"Intervals\":\s*\[[\r\n\s]+(?<Intervals>.*?)\][\r\n\s]+"
| rex max_match=2 "(?ms)\{[\r\n\s]+\"Source\":\s*\"(?<Source>[^\"]+)"
| eval raw = mvzip(Source, Intervals, "=")
| rename meter.serial AS PlantNumber
| table raw PlantNumber
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Source>[^=]+)"
| rex max_match=3 "[\r\n\s]+\"Time\":\s*\"(?<Time>[^\"]+)\",[\r\n\s]+\"Value\":\s*\"(?<Value>[^\"]+)"
| eval TimeAndValue = mvzip(Time, Value, "=")
| fields - _raw Time Value
| mvexpand TimeAndValue
| rex field=TimeAndValue "^(?<Time>[^=]+)=(?<Value>.*)$"
| eval {Source} = Value
| fields - Source Value TimeAndValue
| eval _time = strptime(Time, "%Y-%m-%dT%H:%M:%S.%3N")
| fields - Time
| stats values(*) AS * BY _time
| table PlantNumber _time NegativeTotal PositiveTotal
0 Karma

DavidHourani
Super Champion

Hi @shierlawa,

You could use pointers within the spath to get the positive total alone and the negative total alone. Below for the run anywhere search for positive total :

    |makeresults| eval _raw="{
    \"type\": \"Data\",
    \"version\": \"1.0\",
    \"meter\": {
    \"serial\": \"12345678\"
    },
    \"payload\": {
    \"PlantNumber\": \"12345678\"
    },
    \"Configuration\": {
    \"IntervalLength\": \"PT30M\",
    \"Channels\": [
    {
    \"Source\": \"PositiveTotal\"
    },
    {
    \"Source\": \"NegativeTotal\"
    }
    ]
    },
    \"Channels\": [
    {
    \"Intervals\": [
    {
    \"Time\": \"2019-08-12T01:30:00.000\",
    \"Value\": \"0\"
    },
    {
    \"Time\": \"2019-08-12T02:00:00.000\",
    \"Value\": \"0\"
    },
    {
    \"Time\": \"2019-08-12T02:30:00.000\",
    \"Value\": \"0\"
    }
    ]
    },
    {
    \"Intervals\": [
    {
    \"Time\": \"2019-08-12T01:30:00.000\",
    \"Value\": \"53\"
    },
    {
    \"Time\": \"2019-08-12T02:00:00.000\",
    \"Value\": \"67\"
    },
    {
    \"Time\": \"2019-08-12T02:30:00.000\",
    \"Value\": \"66\"
    }
    ]
    }
    ]
    }"  
    |spath input=_raw output=PlantNumber payload{}.PlantNumber
    |spath input=_raw output=Source Configuration.Channels{0}.Source
    |spath input=_raw output=Time Channels{0}.Intervals{}.Time
    |spath input=_raw output=Value Channels{0}.Intervals{}.Value
    | eval temp=mvzip(Value, Time,"###")
    | stats count by temp,PlantNumber,Source
    | rex field=temp "(?<Value>.+)###(?<Time>.+)"
    | table PlantNumber, Source, Time, Value

Let me know if that helps.

PS: You had some errors in your JSON formatting, I fixed them for the run anywhere search.
Cheers,
David

shierlawa
Engager

Thanks, this is what I was after but couldn't quite get.

The JSONs have a varying amount of channels (not just positive and negative) which is why I was looking to have them combined into one table. The example above only has one channel alone in the resulting table, can the search return all channels and corresponding values and times?

0 Karma

DavidHourani
Super Champion

Yes you can, something like this Channels{}.Intervals{}.Value would call all values within all intervals within all channels. You simply have to use empty brackets.

Let me know how that works out for you.

0 Karma

shierlawa
Engager

Unfortunately that isn't generating the table I was hoping. I was looking for the order of the Source Configuration.Channels{}.Source to correspond with the iteration order of the Channels{}.Intervals{}.Value an Time. This generates all distinct value and time results for each Source.

However, I can use your original search to generate a dashboard with sub-searches and achieve the result I was looking for, so thank you,

0 Karma

woodcock
Esteemed Legend

Do not use subsearches! Look at my answer, it does it VERY efficiently and in a scalable manner.

0 Karma

DavidHourani
Super Champion

you're welcome @shierlawa, also have a look at @woodcock, his solution does it while avoiding sub-searches which you should do whenever possible.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...