topic Re: Multiple JSON Objects in same event in Splunk Search

Multiple JSON Objects in same event

mrlandis3 — Wed, 30 Sep 2020 04:02:50 GMT

The data I am receiving sends multiple JSON objects that have the same keys within them.

EDIT: I've added a sample log. This is a single event that i need to count each DELETE_RETIRED_DEVICE, so 3 in this case. There are no commas between the JSON objects, they are 3 separate objects.

{"connectedCloudName":"","logType":"userAction","version":1,"loggedAt":1580947200024,"actionAt":1580947200024,"device":{"uuid":"","phoneNumber":"","platform":"Android 8.0"},"actor":{"miUserId":9062,"principal":"","email":"-"},"configuration":null,"updatedBlob":null,"certificateDetails":null,"message":null,"spaceName":"Global","spacePath":"/1/","actionType":"DELETE_RETIRED_DEVICE","requestedAt":1580947200024,"completedAt":1580947200024,"reason":"Deleted the retired device successfully","status":"Success","objectId":null,"objectType":null,"objectName":null,"subjectId":"","subjectType":"Smartphone","subjectName":" (Android 8.0 - 12406901520)","subjectOwnerName":null,"requesterName":"misystem","updateRequestId":null,"userInRole":null,"parentId":null,"cookie":null}
{"connectedCloudName":"","logType":"userAction","version":1,"loggedAt":1580947200292,"actionAt":1580947200292,"device":null,"actor":null,"configuration":null,"updatedBlob":null,"certificateDetails":null,"message":null,"spaceName":null,"spacePath":null,"actionType":"SYSTEM_CONFIG_CHANGE","requestedAt":1580947200292,"completedAt":1580947200292,"reason":"Modify Preference lastDeleteRetiredDevicesStatus from Successful, 2020-02-05 00:00:00 UTC to Successful, 2020-02-06 00:00:00 UTC","status":"Success","objectId":null,"objectType":null,"objectName":null,"subjectId":null,"subjectType":"Settings Preferences","subjectName":"System","subjectOwnerName":null,"requesterName":"misystem","updateRequestId":null,"userInRole":null,"parentId":null,"cookie":null}
{"connectedCloudName":"","logType":"userAction","version":1,"loggedAt":1580947200292,"actionAt":1580947200292,"device":null,"actor":null,"configuration":null,"updatedBlob":null,"certificateDetails":null,"message":null,"spaceName":null,"spacePath":null,"actionType":"DELETE_RETIRED_DEVICE","requestedAt":1580947200292,"completedAt":1580947200292,"reason":"Initiated retired device count = 2, deleted retired device count = 2","status":"Success","objectId":null,"objectType":null,"objectName":null,"subjectId":null,"subjectType":null,"subjectName":"misystem (Source - DailyJob, Bulk deletion - 2)","subjectOwnerName":null,"requesterName":"misystem","updateRequestId":null,"userInRole":null,"parentId":null,"cookie":null}
{"connectedCloudName":"","logType":"userAction","version":1,"loggedAt":1580947200011,"actionAt":1580947200011,"device":null,"actor":null,"configuration":null,"updatedBlob":null,"certificateDetails":null,"message":null,"spaceName":null,"spacePath":null,"actionType":"DELETE_RETIRED_DEVICE","requestedAt":1580947200011,"completedAt":1580947200011,"reason":"Initiating bulk deletion of 2 retired device(s)","status":"Initiated","objectId":null,"objectType":null,"objectName":null,"subjectId":null,"subjectType":null,"subjectName":"misystem (Source - DailyJob, Bulk deletion - 2)","subjectOwnerName":null,"requesterName":"misystem","updateRequestId":null,"userInRole":null,"parentId":null,"cookie":null}

Below is the abbreviated objects:
{actionType ... other keys/values}
{actionType ... other keys/values}
{actionType ... other keys/values}

Re: Multiple JSON Objects in same event

vnravikumar — Wed, 05 Feb 2020 16:26:43 GMT

Check this

| makeresults 
| eval _raw="{\"nameList\":
[{\"name\" : \"Apple\"},
{\"name\" : \"Orange\"},
{\"name\" : \"Orange\"},
{\"name\" : \"Graphs\"},
{\"name\" : \"Apple\"},
{\"name\" : \"Apple\"}]}" 
| append 
    [| makeresults 
    | eval _raw="{\"nameList\":
[{\"name\" : \"Apple\"}]}"] 
| spath path=nameList{}.name output=name 
| stats count by name 
| where name="Apple"

Re: Multiple JSON Objects in same event

mrlandis3 — Wed, 05 Feb 2020 19:02:40 GMT

In your example, you provided a JSON object that had an array of keys. That is not the case for me. I will have multiple JSON objects in a single event. So the event looks like how I posted in my question.
Event 1:
{object 1 keys/values}
{object 2 keys/values}

Event 2:
{object 3 keys/values}

and so on

Re: Multiple JSON Objects in same event

efavreau — Wed, 05 Feb 2020 19:39:54 GMT

@mrlandis3 It seems your question has been asked before a few times. The answers I looked to the most were:
https://answers.splunk.com/answers/762294/parse-nested-json-array-into-splunk-table.html
and
https://answers.splunk.com/answers/366957/how-do-i-get-splunk-to-extract-nested-json-arrays.html

Essentially, when dealing with nested json, they both used a combination of the spath & mvexpand commands. Once you have the key value pairs isolated using those commands, then asking | where key=value1 | stats count or similar, should be fine.

Re: Multiple JSON Objects in same event

to4kawa — Wed, 05 Feb 2020 21:05:51 GMT

| makeresults 
| eval _raw="{\"connectedCloudName\":\"\",\"logType\":\"userAction\",\"version\":1,\"loggedAt\":1580947200024,\"actionAt\":1580947200024,\"device\":{\"uuid\":\"\",\"phoneNumber\":\"\",\"platform\":\"Android 8.0\"},\"actor\":{\"miUserId\":9062,\"principal\":\"\",\"email\":\"-\"},\"configuration\":null,\"updatedBlob\":null,\"certificateDetails\":null,\"message\":null,\"spaceName\":\"Global\",\"spacePath\":\"/1/\",\"actionType\":\"DELETE_RETIRED_DEVICE\",\"requestedAt\":1580947200024,\"completedAt\":1580947200024,\"reason\":\"Deleted the retired device successfully\",\"status\":\"Success\",\"objectId\":null,\"objectType\":null,\"objectName\":null,\"subjectId\":\"\",\"subjectType\":\"Smartphone\",\"subjectName\":\" (Android 8.0 - 12406901520)\",\"subjectOwnerName\":null,\"requesterName\":\"misystem\",\"updateRequestId\":null,\"userInRole\":null,\"parentId\":null,\"cookie\":null}
{\"connectedCloudName\":\"\",\"logType\":\"userAction\",\"version\":1,\"loggedAt\":1580947200292,\"actionAt\":1580947200292,\"device\":null,\"actor\":null,\"configuration\":null,\"updatedBlob\":null,\"certificateDetails\":null,\"message\":null,\"spaceName\":null,\"spacePath\":null,\"actionType\":\"SYSTEM_CONFIG_CHANGE\",\"requestedAt\":1580947200292,\"completedAt\":1580947200292,\"reason\":\"Modify Preference lastDeleteRetiredDevicesStatus from Successful, 2020-02-05 00:00:00 UTC to Successful, 2020-02-06 00:00:00 UTC\",\"status\":\"Success\",\"objectId\":null,\"objectType\":null,\"objectName\":null,\"subjectId\":null,\"subjectType\":\"Settings Preferences\",\"subjectName\":\"System\",\"subjectOwnerName\":null,\"requesterName\":\"misystem\",\"updateRequestId\":null,\"userInRole\":null,\"parentId\":null,\"cookie\":null}
{\"connectedCloudName\":\"\",\"logType\":\"userAction\",\"version\":1,\"loggedAt\":1580947200292,\"actionAt\":1580947200292,\"device\":null,\"actor\":null,\"configuration\":null,\"updatedBlob\":null,\"certificateDetails\":null,\"message\":null,\"spaceName\":null,\"spacePath\":null,\"actionType\":\"DELETE_RETIRED_DEVICE\",\"requestedAt\":1580947200292,\"completedAt\":1580947200292,\"reason\":\"Initiated retired device count = 2, deleted retired device count = 2\",\"status\":\"Success\",\"objectId\":null,\"objectType\":null,\"objectName\":null,\"subjectId\":null,\"subjectType\":null,\"subjectName\":\"misystem (Source - DailyJob, Bulk deletion - 2)\",\"subjectOwnerName\":null,\"requesterName\":\"misystem\",\"updateRequestId\":null,\"userInRole\":null,\"parentId\":null,\"cookie\":null}
{\"connectedCloudName\":\"\",\"logType\":\"userAction\",\"version\":1,\"loggedAt\":1580947200011,\"actionAt\":1580947200011,\"device\":null,\"actor\":null,\"configuration\":null,\"updatedBlob\":null,\"certificateDetails\":null,\"message\":null,\"spaceName\":null,\"spacePath\":null,\"actionType\":\"DELETE_RETIRED_DEVICE\",\"requestedAt\":1580947200011,\"completedAt\":1580947200011,\"reason\":\"Initiating bulk deletion of 2 retired device(s)\",\"status\":\"Initiated\",\"objectId\":null,\"objectType\":null,\"objectName\":null,\"subjectId\":null,\"subjectType\":null,\"subjectName\":\"misystem (Source - DailyJob, Bulk deletion - 2)\",\"subjectOwnerName\":null,\"requesterName\":\"misystem\",\"updateRequestId\":null,\"userInRole\":null,\"parentId\":null,\"cookie\":null}"
| rename COMMENT as "this is your sample. From here, the logic"
| makemv delim="
" _raw
| stats count by _raw
| stats count(eval(searchmatch("DELETE_RETIRED_DEVICE"))) as result

If there is sample log, it is good and clear.

your search
| makemv delim="
" _raw
| stats count by _raw

that's all.

Re: Multiple JSON Objects in same event

mrlandis3 — Thu, 06 Feb 2020 12:25:10 GMT

These do not answer my question. These help with a single JSON object that has nested objects within it in a single event. My logs have multiple JSON objects within a single event.

Re: Multiple JSON Objects in same event

efavreau — Thu, 06 Feb 2020 13:30:25 GMT

Please provide a log sample so we can try things against it. Creating and guessing at a working dummy data sample, sometimes takes more time than solving for it, once we know what we're looking at.

Re: Multiple JSON Objects in same event

mrlandis3 — Thu, 06 Feb 2020 13:38:12 GMT

I've added the sample log, thank you

Re: Multiple JSON Objects in same event

mrlandis3 — Thu, 06 Feb 2020 14:12:46 GMT

this doesn't count multiples of the same value within a single event

Re: Multiple JSON Objects in same event

to4kawa — Thu, 06 Feb 2020 23:23:35 GMT

hi @mrlandis3
thanks for providing sample. check my updated answer.

Re: Multiple JSON Objects in same event

mrlandis3 — Fri, 07 Feb 2020 15:58:24 GMT

unfortunately this does not account for where the value may appear more than once within the same log

Re: Multiple JSON Objects in same event

to4kawa — Fri, 07 Feb 2020 23:11:03 GMT

@mrlandis3
First, my query split connectedCloudName object.
actionType in connectedCloudName appears twice or more?
Looking at your sample, actionType in connectedCloudName is only one.

Re: Multiple JSON Objects in same event

mrlandis3 — Mon, 10 Feb 2020 17:37:08 GMT

Correct, actionType will only appear once. For some reason, the searchmatch is only returning the number of events.

Re: Multiple JSON Objects in same event

to4kawa — Mon, 10 Feb 2020 20:07:57 GMT

| rename COMMENT as "this is your sample. From here, the logic" 
| makemv delim="
 " _raw 
| stats count by _raw 
| spath 
| stats count(eval(actionType="DELETE_RETIRED_DEVICE")) as count

I don't beleave searchmatch can't work.
what's your query? there is strange fields extracted.

Re: Multiple JSON Objects in same event

mrlandis3 — Wed, 12 Feb 2020 14:25:53 GMT

This worked, thank you! There was an extra space copying it in which is why did not work initially.

Re: Multiple JSON Objects in same event

mrlandis3 — Wed, 12 Feb 2020 14:31:11 GMT

Could you explain why the first stats count by _raw is needed?

Re: Multiple JSON Objects in same event

to4kawa — Wed, 12 Feb 2020 14:59:47 GMT

|stats count by _raw ≈ mvexpand _raw

but mvexpand _raw does not work, so I use stats count by _raw