Deployment Architecture

How do you unpivot my JSON data?

mrogers_t
New Member

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.

Tags (1)
0 Karma

harsmarvania57
Ultra Champion

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 *
0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...