topic Re: Create table with sums for columns in Splunk Search

Create table with sums for columns

RubenAcon — Thu, 07 Mar 2024 23:51:33 GMT

Hi, we have a log that contains the amount of times any specific message has been sent by the user in every session. This log contains the user's ID (data.entity-id), the message ID (data.message-code), message name (data.message-name) and the total amount of times it was sent during each session (data.total-received).

I'm trying to create a table where the 1st column shows the User's ID (data.entity-id), then each column shows the sum of the total amount of times each message type (data.total-received) was received. Ideally I would be able to create a dashboard were I can have a list of the data.message-code's I want to be used as columns.

Example data:

data: { entity-id: 1 message-code: 445 message-name: handshake total-received: 10 } data: { entity-id: 1 message-code: 269 message-name: switchPlayer total-received: 20 } data: { entity-id: 1 message-code: 269 message-name: switchPlayer total-received: 22 } data: { entity-id: 2 message-code: 445 message-name: handshake total-received: 12 } data: { entity-id: 2 message-code: 269 message-name: switchPlayer total-received: 25 } data: { entity-id: 2 message-code: 269 message-name: switchPlayer total-received: 30 }

Ideally the table would look like this:

Entity-id | handshake | switchPlayer
    1     |    10     |     42
    2     |    12     |     55

Is this possible? What would be the best way to store the message-code in a dashboard?

Thanks!

Re: Create table with sums for columns

ITWhisperer — Thu, 07 Mar 2024 23:04:47 GMT

Assuming these are separate events and that you have or can extract the fields from your event data, try something like this

| chart sum('total-received') as total over 'entity-id' by 'message-name'

Re: Create table with sums for columns

bowesmana — Thu, 07 Mar 2024 23:11:32 GMT

Assuming these are separate events and the fields are auto extracted JSON fields, then this statement will give you your table

... your search ... | chart sum("data.total-received") over data.entity-id by data.message-name

As for having a dropdown where you can choose the message codes you want to display, have a multiselect input and use a populating search that does

... your search ... | stats count by data.message-name

and use the field for label/field for value settings to assign the name.

You will have to work out the tokenisation to that in your search for the data to show in the table you can filter out the ones you want.

Also, you could use a single base search to drive the population of the dropdown as well as the results for the table, which would improve your dashboard load times, but I'll leave that as an exercise for you to play with.

Re: Create table with sums for columns

yuanliu — Thu, 07 Mar 2024 23:20:26 GMT

If I read your samples right, each of that "data" block is its own event. Is this correct? (By the way, you would help volunteers and yourself greatly if you post sample/mock data in raw text format which is JSON compliant; Splunk's beautified display is not.) In that case, Splunk would have given you three fields of your interest: data.entity_id, data.message-name, and data.total-received. Do you get these?

Assuming both assumptions are correct, xyseries is your friend, like this

| stats sum(data.total-received) as subtotal by data.message-name data.entity-id | xyseries "data.entity-id" "data.message-name" subtotal

Your mock events give you

data-entity-id	handshake	switchPlayer
1	10	42
2	12	55

Note, I reconstructed JSON compliant events as the following:

{ "data": { "entity-id": 1, "message-code": 445, "message-name": "handshake", "total-received": 10 } } { "data": { "entity-id": 1, "message-code": 269, "message-name": "switchPlayer", "total-received": 20 } } { "data": { "entity-id": 1, "message-code": 269, "message-name": "switchPlayer", "total-received": 22 } } { "data": { "entity-id": 2, "message-code": 445, "message-name": "handshake", "total-received": 12 } } { "data": { "entity-id": 2, "message-code": 269, "message-name": "switchPlayer", "total-received": 25 } } { "data": { "entity-id": 2, "message-code": 269, "message-name": "switchPlayer", "total-received": 30 } }

This is an emulation you can play with and compare with real data

| makeresults | eval data = split("{ \"data\": { \"entity-id\": 1, \"message-code\": 445, \"message-name\": \"handshake\", \"total-received\": 10 } } { \"data\": { \"entity-id\": 1, \"message-code\": 269, \"message-name\": \"switchPlayer\", \"total-received\": 20 } } { \"data\": { \"entity-id\": 1, \"message-code\": 269, \"message-name\": \"switchPlayer\", \"total-received\": 22 } } { \"data\": { \"entity-id\": 2, \"message-code\": 445, \"message-name\": \"handshake\", \"total-received\": 12 } } { \"data\": { \"entity-id\": 2, \"message-code\": 269, \"message-name\": \"switchPlayer\", \"total-received\": 25 } } { \"data\": { \"entity-id\": 2, \"message-code\": 269, \"message-name\": \"switchPlayer\", \"total-received\": 30 } }", " ") | mvexpand data | rename data AS _raw | spath ``` data emulation above ```