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]

I tried both the solutions but table is generating as blank.

[ITWhisperer]

Please share your full event and the search you are using.

Here is a runanywhere example although it is based on an interpretation of your example which might not be accurate

| makeresults
| fields - _time
| eval _raw="{\"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\\\"}]\"}"
| spath groupByAction
| rex max_match=0 field=groupByAction "(?<group>\{[^\}]+\})"
| mvexpand group
| spath input=group
| table action totalCount

[Biswadeep]

This solution does generate data and I have tried this earlier but the requirement is the values are variable

[ITWhisperer]

What do you mean by that? The solution makes no assumption about the values, i.e. they are variable. The only constants are the names of the fields. If these are variable, then you need to explain this.

[PickleRick]

I suppose @Biswadeep wants something like (ok, the mockup data is the same for all rows but you get the idea):

| makeresults count=10
| streamstats count
| eval _time=_time+count
| eval _raw="{\"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\\\"}]\"}"
| spath groupByAction 
| spath input=groupByAction path={}
| mvexpand {}
| spath input={}
| table _time action totalCount
| xyseries _time action totalCount

[Biswadeep]

Thanks. If the event values change, will the same be updated in the table on refresh without me changing the query?

Yes, field names should be constant.

[ITWhisperer]

Yes, you get a table with action and counts as you requested

[Biswadeep]

Thanks. This does display the results but only thing I am concerned about as of now is if the column values change, will that reflect on the table? The table is supposed to be real-time and not static based on the query.

I cannot validate now as the values have not refreshed yet but if you can please confirm?

[PickleRick]

No. While splunk has something called "real-time search", it should generally not be used in production, especially in a high-volume environment due to its high resource requirements (each realtime search "steals" one CPU from the search-head and each of the indexers for its own).

[Biswadeep]

I just refreshed the data and the event has changed as below, but the table still has the old values.

Other than "real-time search" is there a way to update the dashboard dynamically based on the event values in the JSO

"groupByAction": "[{\"totalCount\": 41117, \"action\": \"update_statistics table\"}, {\"totalCount\": 33793, \"action\": \"reorg index\"}, {\"totalCount\": 22015, \"action\": \"job report\"}, {\"totalCount\": 10252, \"action\": \"reorg table\"}, {\"totalCount\": 8609, \"action\": \"truncate table\"}, {\"totalCount\": 3335, \"action\": \"defrag table\"}, {\"totalCount\": 2628, \"action\": \"add range partitions\"}, {\"totalCount\": 2522, \"action\": \"drop range partitions\"}, {\"totalCount\": 2465, \"action\": \"sp_recompile table\"}, {\"totalCount\": 2227, \"action\": \"update_statistics index\"}]"

[PickleRick]

With a dashboard you can set the refresh interval so that the values in the dashboard are refreshed as the search is being re-run. But that should be used with caution especially if there are many panels in your dashboard, the interval is short and the searches are "heavy".

[Biswadeep]

Thanks. I just refreshed but it only has the predefined values as per the search query and not as per the event data.

eval _raw="\"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\\\"}]\""

The above data is only available in the dashboard and not the latest event data

[ITWhisperer]

The eval _raw is just to set up sample data in line with your example and is not intended for use in your dashboards.

[Biswadeep]

I modified the query which is somehow fetching the latest details from the index but my event has multiple fields and can you advise how can I achieve the same for the other fields as it's only taking groupByAction

Event as below-

It has the fileds - groupByAction, groupByUser, lastOneMonth, lastOneWeek, lastOneDay

"groupByUser": "[{\"requestedBy\": \"rdbmntp\", \"TotalRequests\": 38717}, {\"requestedBy\": \"pstapm\", \"TotalRequests\": 15126}, {\"requestedBy\": \"pirddb\", \"TotalRequests\": 13925}, {\"requestedBy\": \"fiddbtsp\", \"TotalRequests\": 8808}, {\"requestedBy\": \"bkpbs\", \"TotalRequests\": 6513}, {\"requestedBy\": \"arraymgr\", \"TotalRequests\": 5004}, {\"requestedBy\": \"zstapm\", \"TotalRequests\": 4758}, {\"requestedBy\": \"pdspadm\", \"TotalRequests\": 4313}, {\"requestedBy\": \"ptpsadm\", \"TotalRequests\": 3473}, {\"requestedBy\": \"glfinp\", \"TotalRequests\": 3450}]"

[ITWhisperer]

Please share your complete _raw event in a code block </>

[Biswadeep]

<field k="_raw">

<v xml:space="preserve" trunc="0"> "groupByAction": "[{\"totalCount\": 41117, \"action\": \"update_statistics table\"}, {\"totalCount\": 33793, \"action\": \"reorg index\"}, {\"totalCount\": 22015, \"action\": \"job report\"}, {\"totalCount\": 10252, \"action\": \"reorg table\"}, {\"totalCount\": 8609, \"action\": \"truncate table\"}, {\"totalCount\": 3335, \"action\": \"defrag table\"}, {\"totalCount\": 2628, \"action\": \"add range partitions\"}, {\"totalCount\": 2522, \"action\": \"drop range partitions\"}, {\"totalCount\": 2465, \"action\": \"sp_recompile table\"}, {\"totalCount\": 2227, \"action\": \"update_statistics index\"}]"</v>

</field>

<field k="_raw">

<v xml:space="preserve" trunc="0"> "groupByUser": "[{\"requestedBy\": \"rdbmntp\", \"TotalRequests\": 38717}, {\"requestedBy\": \"pstapm\", \"TotalRequests\": 15126}, {\"requestedBy\": \"pirddb\", \"TotalRequests\": 13925}, {\"requestedBy\": \"fiddbtsp\", \"TotalRequests\": 8808}, {\"requestedBy\": \"bkpbs\", \"TotalRequests\": 6513}, {\"requestedBy\": \"arraymgr\", \"TotalRequests\": 5004}, {\"requestedBy\": \"zstapm\", \"TotalRequests\": 4758}, {\"requestedBy\": \"pdspadm\", \"TotalRequests\": 4313}, {\"requestedBy\": \"ptpsadm\", \"TotalRequests\": 3473}, {\"requestedBy\": \"glfinp\", \"TotalRequests\": 3450}]",</v>

</field>

<field k="_raw">

<v xml:space="preserve" trunc="0"> "lastOneMonth": "[{\"requestStatus\": \"Failed\", \"Total Count\": 384}, {\"requestStatus\": \"Succeeded\", \"Total Count\": 3801}, {\"requestStatus\": \"Errors\", \"Total Count\": 540}, {\"requestStatus\": \"Killed\", \"Total Count\": 1}]",</v>

</field>

<field k="_raw">

<v xml:space="preserve" trunc="0"> "lastOneWeek": "[{\"requestStatus\": \"Failed\", \"Total Count\": 384}, {\"requestStatus\": \"Succeeded\", \"Total Count\": 3801}, {\"requestStatus\": \"Errors\", \"Total Count\": 540}, {\"requestStatus\": \"Killed\", \"Total Count\": 1}]",</v>

</field>

[Biswadeep]

Event:

"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\"}]"

Search:

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

However, the event values are dynamic and are fed into splunk by another application which might change and I wish to view the same data in the table. It should not be a hardwired table. Can you please help?

[ITWhisperer]

| makeresults
| fields - _time
| eval _raw="\"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\\\"}]\""
``` The line above creates the event as you have shared ```
``` Convert the _raw to compliant JSON ```
| eval _raw="{"._raw."}"
``` Extract the groupByAction field - this resolves the escaped double quotes ```
| spath groupByAction
``` Extract the groups into a multi-valued field ```
| rex max_match=0 field=groupByAction "(?<group>\{[^\}]+\})"
``` Expand the multi-value field ```
| mvexpand group
``` Extract the fields from the group ```
| spath input=group
``` Output the table ```
| table action totalCount

[ITWhisperer]

| makeresults
| fields - _time
| eval _raw="<field k=\"_raw\">
<v xml:space=\"preserve\" trunc=\"0\"> \"groupByAction\": \"[{\\\"totalCount\\\": 41117, \\\"action\\\": \\\"update_statistics table\\\"}, {\\\"totalCount\\\": 33793, \\\"action\\\": \\\"reorg index\\\"}, {\\\"totalCount\\\": 22015, \\\"action\\\": \\\"job report\\\"}, {\\\"totalCount\\\": 10252, \\\"action\\\": \\\"reorg table\\\"}, {\\\"totalCount\\\": 8609, \\\"action\\\": \\\"truncate table\\\"}, {\\\"totalCount\\\": 3335, \\\"action\\\": \\\"defrag table\\\"}, {\\\"totalCount\\\": 2628, \\\"action\\\": \\\"add range partitions\\\"}, {\\\"totalCount\\\": 2522, \\\"action\\\": \\\"drop range partitions\\\"}, {\\\"totalCount\\\": 2465, \\\"action\\\": \\\"sp_recompile table\\\"}, {\\\"totalCount\\\": 2227, \\\"action\\\": \\\"update_statistics index\\\"}]\"</v>
</field>|<field k=\"_raw\">
<v xml:space=\"preserve\" trunc=\"0\"> \"groupByUser\": \"[{\\\"requestedBy\\\": \\\"rdbmntp\\\", \\\"TotalRequests\\\": 38717}, {\\\"requestedBy\\\": \\\"pstapm\\\", \\\"TotalRequests\\\": 15126}, {\\\"requestedBy\\\": \\\"pirddb\\\", \\\"TotalRequests\\\": 13925}, {\\\"requestedBy\\\": \\\"fiddbtsp\\\", \\\"TotalRequests\\\": 8808}, {\\\"requestedBy\\\": \\\"bkpbs\\\", \\\"TotalRequests\\\": 6513}, {\\\"requestedBy\\\": \\\"arraymgr\\\", \\\"TotalRequests\\\": 5004}, {\\\"requestedBy\\\": \\\"zstapm\\\", \\\"TotalRequests\\\": 4758}, {\\\"requestedBy\\\": \\\"pdspadm\\\", \\\"TotalRequests\\\": 4313}, {\\\"requestedBy\\\": \\\"ptpsadm\\\", \\\"TotalRequests\\\": 3473}, {\\\"requestedBy\\\": \\\"glfinp\\\", \\\"TotalRequests\\\": 3450}]\",</v>
</field>|<field k=\"_raw\">
<v xml:space=\"preserve\" trunc=\"0\"> \"lastOneMonth\": \"[{\\\"requestStatus\\\": \\\"Failed\\\", \\\"Total Count\\\": 384}, {\\\"requestStatus\\\": \\\"Succeeded\\\", \\\"Total Count\\\": 3801}, {\\\"requestStatus\\\": \\\"Errors\\\", \\\"Total Count\\\": 540}, {\\\"requestStatus\\\": \\\"Killed\\\", \\\"Total Count\\\": 1}]\",</v>
</field>|<field k=\"_raw\">
<v xml:space=\"preserve\" trunc=\"0\"> \"lastOneWeek\": \"[{\\\"requestStatus\\\": \\\"Failed\\\", \\\"Total Count\\\": 384}, {\\\"requestStatus\\\": \\\"Succeeded\\\", \\\"Total Count\\\": 3801}, {\\\"requestStatus\\\": \\\"Errors\\\", \\\"Total Count\\\": 540}, {\\\"requestStatus\\\": \\\"Killed\\\", \\\"Total Count\\\": 1}]\",</v>
</field>"
| eval event=split(_raw,"|")
| mvexpand event
| eval _raw=event
| fields _raw
| spath field.v output=v
``` The lines above creates the event as you have shared ```
``` Convert the _raw to compliant JSON ```
| eval _raw="{".v."}"
``` Extract the groupByAction field - this resolves the escaped double quotes ```
| spath groupByAction
| spath groupByUser
| spath lastOneMonth
| spath lastOneWeek
``` Extract the groups into a multi-valued field ```
| rex max_match=0 field=groupByAction "(?<group>\{[^\}]+\})"
| rex max_match=0 field=groupByUser "(?<group>\{[^\}]+\})"
| rex max_match=0 field=lastOneMonth "(?<group>\{[^\}]+\})"
| rex max_match=0 field=lastOneWeek "(?<group>\{[^\}]+\})"
``` Expand the multi-value field ```
| mvexpand group
``` Extract the fields from the group ```
| spath input=group
| spath input=requestedBy
| spath input=requestStatus
| spath input=group
``` Output the table ```
| table action totalCount requestedBy TotalRequests requestStatus "Total Count"

[Biswadeep]

This solution isn't reading the log file. The below modified query works for me but the similar query doesn't work for other fields like groupByUser etc. even after updating spath. Please if you can advise and see if something needs to be adapted in the below. This is the most ideal query for but need to read other fields as well.

index=log-1696-nonprod-c laas_appId=tsproid_qa.sytsTaskRunner laas_file="/tmp/usage_snapshot.json" | head 1
| fields - _time
``` Convert the _raw to compliant JSON ```
| eval _raw="{"._raw."}"
``` Extract the groupByAction field - this resolves the escaped double quotes ```
| spath groupByAction
``` Extract the groups into a multi-valued field ```
| rex max_match=0 field=groupByAction "(?<group>\{[^\}]+\})"
``` Expand the multi-value field ```
| mvexpand group
``` Extract the fields from the group ```
| spath input=group
``` Output the table ```
| table action totalCount