How do you unpivot my JSON data?

mrogers_t — Wed, 26 Dec 2018 17:32:01 GMT

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.

Re: How do you unpivot my JSON data?

harsmarvania57 — Thu, 27 Dec 2018 10:17:17 GMT

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 *

topic Re: How do you unpivot my JSON data? in Deployment Architecture

How do you unpivot my JSON data?

Re: How do you unpivot my JSON data?