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.
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
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
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
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?
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.
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,
Do not use subsearches! Look at my answer, it does it VERY efficiently and in a scalable manner.
you're welcome @shierlawa, also have a look at @woodcock, his solution does it while avoiding sub-searches which you should do whenever possible.