Converting json map into table

[queryaslan]

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?

[queryaslan]

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

| 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) | eval time=0 | foreach User [eval time=_time]| table Users Count total _time | sort by -Count

[kamlesh_vaghela]

@queryaslan 

try this.

|rex field=_raw "\"(?<Users>[^\"]+)\"\s*\:\s*(?<Count>\d+)" max_match=0
| eval t=mvzip(Users,Count) |mvexpand t | eval Users=mvindex(split(t,","),0),Count=mvindex(split(t,","),1) | search Users="user*"  | table _time Users Count

KV 

[queryaslan]

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

[kamlesh_vaghela]

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

[kamlesh_vaghela]

@queryaslan 

Can you please try this?

YOUR_SEARCH
|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

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.