topic Re: Converting json map into table in Splunk Dev

Converting json map into table

queryaslan — Wed, 01 Sep 2021 09:18:33 GMT

Hi,

I'm trying out the new Splunk dashboard and the goal is to plot users' database document count over time.

The log contains a JSON map with the top 100 users with most documents. Since user doc-count differs over time the keys will also differ...

"userDocuments": {
"userA":1836,
"userD":1197,
"userB":606,
"userZ":108062,
"userE":972,
"userC":931
}

I'm having a hard time creating a simple table like this

User	Count
userA	1836
userD	1197
userB	606
userZ	108062
userE	972
userC	931

Any input for a query/changing data structure?

Re: Converting json map into table

kamlesh_vaghela — Wed, 01 Sep 2021 11:47:13 GMT

@queryaslan

Can you please try this?

My Sample Search :

| makeresults | eval _raw="{\"userDocuments\": {\"userA\":1836,\"userD\":1197,\"userB\":606,\"userZ\":108062,\"userE\":972,\"userC\":931}}" |rex field=_raw "\"(?<Users>[^\"]+)\"\s*\:\s*(?<Count>\d+)" max_match=0 | eval t=mvzip(Users,Count) |stats count by t | eval Users=mvindex(split(t,","),0),Count=mvindex(split(t,","),1) | search Users="user*" | table Users Count

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Converting json map into table

queryaslan — Wed, 01 Sep 2021 11:39:48 GMT

@kamlesh_vaghela Thank you! The only problem I have is that have another map in the log so it will parse values from the map into the table. But I guess i could solve the by looking for "user" as a prefix in the regex or if you have a smarter solution?

Re: Converting json map into table

kamlesh_vaghela — Wed, 01 Sep 2021 11:47:54 GMT

@queryaslan

Just add

| search Users="user*"

Please check my updated answer.

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Converting json map into table

queryaslan — Mon, 06 Sep 2021 14:03:05 GMT

@kamlesh_vaghela sorry for bothering you but how would you add a timestamp column for the table?

Re: Converting json map into table

kamlesh_vaghela — Tue, 07 Sep 2021 04:27:26 GMT

@queryaslan

try this.