Splunk Search
Highlighted

How do I write a search to extract these two fields from my sample nested JSON data?

New Member

I have the following sample payload

  {     "time" : "11-23-2016 23:19:15.875
 +0000",    "message" : "CSE Filter skipped these messages:",   "Events" :
 [{
            "header" : {
                "category" : "identity.user.create",
                "timestamp" : "2016-11-23T23:19:06.4908559Z",
                "buffered at" : "2016-11-23T23:19:09.2520382+00:00",
                "rcv at" : 1479943149282,
                "plug" : "1e2OjpYgN"
            },
            "body" : "{"timestamp":"2016-11-23T23:19:06Z","userId":"yank1","userName":"petermixmax","eidmGuid":"yank2","emailId":"petermixmax@gmail.com","emailVerified":false,"profile":{"firstName":"pedro","lastName":"mendoza","countryCode":"ES","language":"en","optIn":false},"contactAddress":{},"consumer":{"serviceKey":"serv-downloads-mx","serviceName":"serv
 Downloads MX"}}"       }, {
            "header" : {
                "category" : "identity.user.updateprofile",
                "timestamp" : "2016-11-23T23:19:08.5282229Z",
                "buffered at" : "2016-11-23T23:19:09.6982154+00:00",
                "rcv at" : 1479943149738,
                "plug" : "asdfasdf"
            },
            "body" : "{"timestamp":"2016-11-23T23:19:08Z","userId":"yank3","userName":"2324234211134","eidmGuid":"yank4","emailId":"asdfasdf@qq.com","emailVerified":false,"profile":{"firstName":"尊敬的用户:官网015107.com注册送188送VIP首存100最高可送388,一倍流水即可出款,详询QQ250136525","lastName":"每天投注一元以上即可参加抢红包,可获得最高红包8888元,快来试试您的运气吧!只需一倍流水","countryCode":"CN","language":"zh_CN","optIn":false},"contactAddress":{}}",
            "time" : "2016-11-23T23:19:08.528Z"         }   ]  }

I would like to extract two fields from an array of hashes which are in
Events > body > userId
and
Events > body > eidmGuid

Below is the testing search I've been using. Couldn't seem to get it to work.

source=data  | spath output=guid path=Events{}.body.eidmGuid  | spath output=oxygen path=Events{}.body.userId | table guid, oxygen

Any recommendations on how to parse these data?

0 Karma
Highlighted

Re: How do I write a search to extract these two fields from my sample nested JSON data?

Motivator

Try this please:

your query to return this event
| rex field=_raw max_match=0 "\"body\".+\"userId\"\:\"(?<userId>[^\"]+)\".+\"eidmGuid\":\"(?<guId>[^\"]+)\""
| eval combo=mvzip(userId, guId, "~")
| mvexpand combo
| rex field=combo "(?<uid>[^~]+)~(?<gId>.*)"
| table uid, gId
0 Karma