How to create a dynamic table from JSON events

[Biswadeep]

Hi,

I have an index that returns logging events in JSON format. I want to create a tabular dashboard which will dynamically update the JSON key-value pairs in rows and columns for visualization purposes. Any help would be highly appreciated.

index=log-1696-nonprod-c laas_appId=tsproid_qa.sytsTaskRunner laas_logId=CC6F5AA6-3813-11EE-AD8F-237241A57196

Event(the values change and it's dynamic): 

"groupByAction": "[{\"totalCount\": 40591, \"action\": \"update_statistics table\"}, {\"totalCount\": 33724, \"action\": \"reorg index\"}, {\"totalCount\": 22015, \"action\": \"job report\"}, {\"totalCount\": 10236, \"action\": \"reorg table\"}, {\"totalCount\": 7389, \"action\": \"truncate table\"}, {\"totalCount\": 3291, \"action\": \"defrag table\"}, {\"totalCount\": 2291, \"action\": \"sp_recompile table\"}, {\"totalCount\": 2172, \"action\": \"add range partitions\"}, {\"totalCount\": 2088, \"action\": \"update_statistics index\"}, {\"totalCount\": 2069, \"action\": \"drop range partitions\"}]"

Table should have "totalCount" and "action" as columns

[Biswadeep]

Can someone please help to fetch the other fields like groupByUser?

[ITWhisperer]

Have you tried my suggestion on your actual events? (You don't need to include the lines which attempt to set up sample data based on the example you posted.)

[Biswadeep]

Thank you so much. Finally I have my intended dashboard ready. Many thanks for your help 🙂

[Biswadeep]

I am not looking for makeresults as that will be hard coded.

[ITWhisperer]

| spath groupByAction
| rex max_match=0 field=groupByAction "(?<group>\{[^\}]+\})"
| mvexpand group
| spath input=group
| table action totalCount

[gcusello]

Hi @Biswadeep,

these seems to be json logs so you sgould use a sourcetype for json logs, or at least use the "INDEXED_EXTRACTIONS = json" option in the props.conf so all the fields will be extracted and you can use them also in a table.

Otherwise you could use the spath command (https://community.splunk.com/t5/Splunk-Enterprise/spath-command/m-p/518343😞

index=log-1696-nonprod-c laas_appId=tsproid_qa.sytsTaskRunner laas_logId=CC6F5AA6-3813-11EE-AD8F-237241A57196
| spath
| table field1 field2 etc...

Ciao.

Giuseppe