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!

The Payment Operations Wake-Up Call: Why Financial Institutions Can't Afford ...

The same scenario plays out across financial institutions daily. A payment system fails at 11:30 AM on a busy ...

Make Your Case: A Ready-to-Send Letter for Getting Approval to Attend .conf25

Hello Splunkers, Want to attend .conf25 in Boston this year but not sure how to convince your manager? We've ...

Community Spotlight: A Splunk Expert's Journey

In the world of data analytics, some journeys leave a lasting impact not only on the individual but on the ...