I'm not sure if 'unpivot' is the right term, but I'm not sure what the term would be. I'm having trouble searching for help for this problem.
I have this JSON (note the quotes around the brackets for the 'message' value. This is output returned by Ignition SCADA and thats just how it comes. I know use spath on it and that sorts it out.
{
"time": "2018-12-26 14:48:25.554",
"log_level": "INFO",
"logger_name": "testLogger",
"message": "{
"timers": {
"total": {
"start": 1545835678315,
"end": 1545835705554
},
"readUDTParentTypes": {
"start": 1545835698394,
"end": 1545835698571
},
"createAllTags": {
"start": 1545835705500,
"end": 1545835705546
},
"createDS": {
"start": 1545835705546,
"end": 1545835705554
},
"identifyUniqueUDTs": {
"start": 1545835698571,
"end": 1545835705500
}
}",
"totalTime": 27.239,
}"
}
What I'd like to do for each of these events is to create multiple rows, one for each entry in timers, like a field for the timer name, a field for start, and a field for stop. I'm pretty sure this is going to be some mvzip/mvexpand magic, but I haven't worked with that enough to see how it fits together.
Hi @mrogers_t,
I would suggest you to fix extra double quotes (like "
after "message":
) on source instead of splunk to make perfect JSON event. Until you will fix that you can use REGEX with SED to remove that "
at search time.
Please try below query
<yourBaseSearch>
| rex field=_raw mode=sed "s/(?s)(?:(\"message\"\:\s)\"(\{.*\})\"(\,.*\})\")/\1\2\3/"
| spath
| rename message.timers.* AS *
Below is run anywhere search based on sample data which is generating fields like createAllTags.end
, createAllTags.start
etc.
| makeresults
| eval _raw="{
\"time\": \"2018-12-26 14:48:25.554\",
\"log_level\": \"INFO\",
\"logger_name\": \"testLogger\",
\"message\": \"{
\"timers\": {
\"total\": {
\"start\": 1545835678315,
\"end\": 1545835705554
},
\"readUDTParentTypes\": {
\"start\": 1545835698394,
\"end\": 1545835698571
},
\"createAllTags\": {
\"start\": 1545835705500,
\"end\": 1545835705546
},
\"createDS\": {
\"start\": 1545835705546,
\"end\": 1545835705554
},
\"identifyUniqueUDTs\": {
\"start\": 1545835698571,
\"end\": 1545835705500
}
}\",
\"totalTime\": 27.239,
}\"
}"
| rex field=_raw mode=sed "s/(?s)(?:(\"message\"\:\s)\"(\{.*\})\"(\,.*\})\")/\1\2\3/"
| spath
| rename message.timers.* AS *