Splunk Search

How to sum two rows from a table?

na206b
Explorer

I have the following table that I would like to summarize as total logins and total token creations
by creating a new table with two rows  showing  CLIENT_LOGIN + LOGIN  and
CODE_TO_TOKEN + REFRESH_TOKEN

how do I sum two rows?   Thanks

CLIENT_LOGIN 81392
CLIENT_LOGIN_ERROR 290
CODE_TO_TOKEN 2984
CODE_TO_TOKEN_ERROR 13
CUSTOM_REQUIRED_ACTION_ERROR 3
INTROSPECT_TOKEN 33
LOGIN 10559
LOGIN_ERROR 1240
LOGOUT 2
REFRESH_TOKEN 51
REFRESH_TOKEN_ERROR 126
Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Or you could replace the last two lines of my example with 

| eval type=if(match(Field, "LOGIN"), 1, 0)
| stats values(Field) as Fields sum(Count) as Total by type
| fields - type
| eval Fields = mvjoin(Fields, " + ")

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

One way to do it

| makeresults
| eval _raw="CLIENT_LOGIN	81392
CLIENT_LOGIN_ERROR	290
CODE_TO_TOKEN	2984
CODE_TO_TOKEN_ERROR	13
CUSTOM_REQUIRED_ACTION_ERROR	3
INTROSPECT_TOKEN	33
LOGIN	10559
LOGIN_ERROR	1240
LOGOUT	2
REFRESH_TOKEN	51
REFRESH_TOKEN_ERROR	126"
| multikv noheader=t
| rename Column_1 as Field, Column_2 as Count
| table Field Count
| where match(Field, "^(CLIENT_LOGIN|LOGIN|CODE_TO_TOKEN|REFRESH_TOKEN)$")
| stats sum(eval(if(match(Field, "^(CLIENT_LOGIN|LOGIN)$"), Count, null()))) as Logins sum(eval(if(match(Field, "^(CODE_TO_TOKEN|REFRESH_TOKEN)$"), Count, null()))) as Tokens
| transpose

From the |where statement - the rest is just setting up your example data.

 

0 Karma

na206b
Explorer

that stats sum commands are not returning any data.  I don't think they are properly summing up the data.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Or you could replace the last two lines of my example with 

| eval type=if(match(Field, "LOGIN"), 1, 0)
| stats values(Field) as Fields sum(Count) as Total by type
| fields - type
| eval Fields = mvjoin(Fields, " + ")

na206b
Explorer

Hey!! this did it wow thanks for the Splunk FU magic!  

*Only one small typo I had to change "sum(Count)" to   "sum(count)"  

Thanks again!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...