topic Re: How to parse a json tree into a table? in Splunk Search

How to parse a json tree into a table?

bradyguy — Thu, 13 Nov 2014 22:25:43 GMT

the following seach string basically pulls out the JSON puts it in a variable called data and then runs it through spath. I then output two elements from the json. What I get is a couple of multivalue fields but the second table shows what I want.

index=fulfillment com.x.y.u.i.segment.SegmentJoinDataWorkflow category | rex ".*SegmentJoinDataWorkflow: (?.*)" | spath input=data | rename segments{}.customSegmentName AS customSegmentName, segments{}.expression{}.$and{}.$and{}.$or{}.category as category | table _time,customSegmentName,category


2014-11-13 14:42:56     Test117182801LVRPublSD2Adv                     5256
                        Test117182802LVRPublSD2Adv                     5257
                                                                       5987
                                                                       5000
                                                                       5256
                                                                       5257
                                                                       5987
                                                                       5000

2nd table Basically taking the json tree structure and flatten it out into multiple events
Any help on how to do this would be appreciated. I have tried a number of things with the mv[commands] mvexpand,mvzip, mvappend, makemv etc..

2014-11-13 14:42:56    Test117182801LVRPublSD2Adv                    5256
2014-11-13 14:42:56    Test117182801LVRPublSD2Adv                    5257
2014-11-13 14:42:56    Test117182801LVRPublSD2Adv                    5987
2014-11-13 14:42:56    Test117182801LVRPublSD2Adv                    5000
**2014-11-13 14:42:56  Test117182802LVRPublSD2Adv                    5256
2014-11-13 14:42:56    Test117182802LVRPublSD2Adv                    5257
2014-11-13 14:42:56    Test117182802LVRPublSD2Adv                    5987
2014-11-13 14:42:56    Test117182802LVRPublSD2Adv                    5000**

My JSON:

{ "accountInfo" : { "policyType" : "basic",
      "shareAccountId" : "1063216919"
    },
  "adId" : "40283453",
  "isRequired" : "true",
  "manualApproval" : [ "no" ],
  "notification" : { "DATALOADER" : { "failureUrl" : "http://batch/status/",
          "successUrl" : "http://status/"
        },
      "EMAIL" : { "email" : [ "" ] }
    },
  "notifyUser" : [ "onAll" ],
  "packageId" : "1171824434543",
  "packageName" : "Test11718244DataApp",
  "PartnerId" : "5034534",
  "segments" : [ { "id_count" : 112,
        "controlPercent" : 20,
        "count" : "123",
        "customSegmentId" : "1171824401",
        "customSegmentName" : "Test1171824401Dataloader",
        "expression" : [ { "$and" : [ { "$and" : [ { "$or" : [ { "category" : "5256" },
                              { "category" : "5257" }
                            ] } ] },
                  { "$and" : [ { "$or" : [ { "category" : "5987" },
                              { "category" : "5000" }
                            ] } ] }
                ] } ],
        "locations" : [ "" ],
        "total_ashid_count" : 100
      },
      { "id_count" : 112,
        "controlPercent" : 20,
        "count" : "123",
        "customSegmentId" : "1171824402",
        "customSegmentName" : "Test1171824402Dataloader",
        "expression" : [ { "$and" : [ { "$and" : [ { "$or" : [ { "category" : "5256" },
                              { "category" : "5257" }
                            ] } ] },
                  { "$and" : [ { "$or" : [ { "category" : "5987" },
                              { "category" : "5000" }
                            ] } ] }
                ] } ],
        "locations" : [ "" ],
        "total_id_count" : 100
      }
    ],
  "username" : "",
}

Re: How to parse a json tree into a table?

jrodman — Fri, 14 Nov 2014 16:03:53 GMT

I tried to put the tables back together, and quoted the JSON while at it.

Re: How to parse a json tree into a table?

jrodman — Fri, 14 Nov 2014 16:09:27 GMT

I don't know the BEST solution for this kind of problem, but one approach is to attack it in two passes.

In pass one, you extract each segment as a blob of json in a field. You then have a multivalue field of segments, and can use mvexpand to get two results, one with each segment.

At this point you can use spath again to pull out the list of expressions as multivalue fields, process them as neededed and mvexpand again to get a full table.

Note that mvexpand is written such that it will duplicate all the information in all the fields, so if for example your original item has hundreds of kilobytes of json you might want to trim the original event with |fields to toss away _raw before performing your |mvexpand action.

Re: How to parse a json tree into a table?

dominiquevocat — Mon, 28 Sep 2020 19:50:49 GMT

Um, i have a simpler json that looks like this:

{
"qos-definition": [
{
"bool": "false",
"description": "Active Directory Connect Response",
"hasMax": "false",
"name": "QOS_AD_CONNECT_RESPONSE",
"qosDefId": "28",
"qosGroup": "QOS_APPLICATION",
"type": "0",
"unit": "ms",
"unitShort": "ms"
},
{
"bool": "false",
"description": "Active Directory Replication Age",
"hasMax": "false",
"name": "QOS_AD_REPLICATION_AGE",
"qosDefId": "29",
"qosGroup": "QOS_APPLICATION",
"type": "0",
"unit": "s",
"unitShort": "s"
},
...

I have in a field "result" (which i get from a custom commando that just gets it from a RESTful Webservice) and then i split it and use extract. Maybe this helps a little:

| eval results=split(results,"{") | mvexpand results| rename results AS _raw| eval _raw = substr(_raw, 1, len(_raw)-2) | extract pairdelim=",", kvdelim=":" | fields - _raw

extract seems to work on _raw but split and mvexpand don't seem to like _raw. The substr() is to get rid of trailing "}," but that is more of a nice coincidence with the json in my case.

Hope this helps a little?

Re: How to parse a json tree into a table?

sk8asd123 — Fri, 04 Dec 2015 23:11:51 GMT

Can you edit props.conf to process json files?

http://docs.splunk.com/Documentation/Splunk/latest/Admin/Propsconf?utm_source=answers&utm_medium=in-answer&utm_term=props.conf&utm_campaign=refdoc

and then something like:

search | rex "(?\{.*\})" |  spath input=json_input  |table fields