topic Re: How to create a dynamic table from JSON events? in Dashboards & Visualizations

How to create a dynamic table from JSON events

Biswadeep — Fri, 25 Aug 2023 10:02:28 GMT

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

Re: How to create a dynamic table from JSON events

gcusello — Fri, 25 Aug 2023 10:07:31 GMT

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

Re: How to create a dynamic table from JSON events

ITWhisperer — Fri, 25 Aug 2023 10:17:52 GMT

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

Re: How to create a dynamic table from JSON events

Biswadeep — Fri, 25 Aug 2023 11:37:40 GMT

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

Re: How to create a dynamic table from JSON events

ITWhisperer — Fri, 25 Aug 2023 11:42:24 GMT

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

Re: How to create a dynamic table from JSON events

Biswadeep — Fri, 25 Aug 2023 11:48:31 GMT

Event:

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?

Re: How to create a dynamic table from JSON events

Biswadeep — Fri, 25 Aug 2023 11:54:01 GMT

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

Re: How to create a dynamic table from JSON events

ITWhisperer — Fri, 25 Aug 2023 11:54:23 GMT

| 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

Re: How to create a dynamic table from JSON events

ITWhisperer — Fri, 25 Aug 2023 11:57:04 GMT

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.

Re: How to create a dynamic table from JSON events

Biswadeep — Fri, 25 Aug 2023 12:02:39 GMT

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.

Re: How to create a dynamic table from JSON events

PickleRick — Fri, 25 Aug 2023 13:17:36 GMT

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

Re: How to create a dynamic table from JSON events

ITWhisperer — Fri, 25 Aug 2023 13:31:49 GMT

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

Re: How to create a dynamic table from JSON events

Biswadeep — Mon, 28 Aug 2023 05:57:26 GMT

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?

Re: How to create a dynamic table from JSON events

PickleRick — Mon, 28 Aug 2023 07:22:29 GMT

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

RE : How to create a dynamic table from JSON events

Biswadeep — Mon, 28 Aug 2023 08:29:32 GMT

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

Re: RE : How to create a dynamic table from JSON events

PickleRick — Mon, 28 Aug 2023 08:33:44 GMT

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

Re: RE : How to create a dynamic table from JSON events

Biswadeep — Mon, 28 Aug 2023 08:46:11 GMT

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

Re: RE : How to create a dynamic table from JSON events

ITWhisperer — Mon, 28 Aug 2023 10:11:37 GMT

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

How to create a dynamic table from JSON events?

Biswadeep — Mon, 28 Aug 2023 10:49:43 GMT

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

Re: How to create a dynamic table from JSON events?

ITWhisperer — Mon, 28 Aug 2023 10:55:48 GMT

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