Splunk Search

Multiple JSON Objects in same event

mrlandis3
Path Finder

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}

0 Karma
1 Solution

to4kawa
Ultra Champion
| 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.

View solution in original post

0 Karma

to4kawa
Ultra Champion
| 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.

0 Karma

to4kawa
Ultra Champion
| 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.

0 Karma

mrlandis3
Path Finder

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

0 Karma

mrlandis3
Path Finder

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

0 Karma

mrlandis3
Path Finder

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

0 Karma

to4kawa
Ultra Champion
|stats count by _raw ≈ mvexpand _raw

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

0 Karma

mrlandis3
Path Finder

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

mrlandis3
Path Finder

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

efavreau
Motivator

@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.

###

If this reply helps you, an upvote would be appreciated.
0 Karma

mrlandis3
Path Finder

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.

0 Karma

efavreau
Motivator

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.

###

If this reply helps you, an upvote would be appreciated.
0 Karma

mrlandis3
Path Finder

I've added the sample log, thank you

0 Karma

vnravikumar
Champion

Hi

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"
0 Karma

mrlandis3
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...