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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...