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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...