topic How to create a table showing the sum of values across dictionaries in multiple queries? in Splunk Search

How to create a table showing the sum of values across dictionaries in multiple queries?

beaverjustin1 — Thu, 11 May 2023 05:03:49 GMT

If I have queries with dictionaries containing events as the key and frequencies as the value:

line.Data = {"eventOne": 4, "eventThree" : 2}; line.Data = {"eventOne": 2, "eventTwo" : 3}

How can I create a table that shows the sum of the different events:

eventOne: 6

eventTwo: 3

eventThree: 2

Re: How to create a table showing the sum of values across dictionaries in multiple queries?

TrangCIC81 — Thu, 11 May 2023 05:32:22 GMT

<your base search> | stats sum(*) as * by _time | transpose

Replace <your base search> with the search that produces the line.Data field containing the dictionaries.
Use the stats command with the sum(*) function to calculate the sum of all values in each event category for each _time value. This will create a table with columns _time, eventOne, eventTwo, and eventThree.
Use the transpose command to switch the rows and columns of the table so that the event categories become rows and the _time values become columns.

Let me know if it works.

Re: How to create a table showing the sum of values across dictionaries in multiple queries?

ITWhisperer — Thu, 11 May 2023 05:58:06 GMT

Here is a runanywhere example showing how you could approach this.

| makeresults | fields - _time | eval line.Data = split("{\"eventOne\": 4, \"eventThree\" : 2};{\"eventOne\": 2, \"eventTwo\" : 3}",";") | mvexpand line.Data ``` the lines above create sample events, one event per line.Data ``` | spath input=line.Data | untable line.Data event count | stats sum(count) as count by event