topic Re: Calculating total in row in Splunk Search

Calculating total in row

khanyag1 — Thu, 27 Feb 2020 14:20:48 GMT

Hi,

I need help adding b+ c together to get a total, I will then calculate a percentage using a/combined b+c. Is this possible?

| stats dc(Users) as UsersCount by Label app
| stats sum(UsersCount) by Label

Label sum(UsersCount)
1 a 14
2 b 2
3 c 19

Re: Calculating total in row

adonio — Thu, 27 Feb 2020 20:32:15 GMT

little clumsy solution, but maybe itll work for you
try it anywhere

| makeresults count=1
| eval data="1 a 14;;;2 b 2;;;3 c 19"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<label>[^\s]+)\s(?<app>[^\s]+)\s(?<tot_users>[^\s]+)"
| table label app tot_users
| rename COMMENT as "above generates fake data, below is solution"
| chart max(tot_users) as total over label by app
| stats values(*) as *
| mvexpand label
| eval b_plus_c = b + c
| eval percent = round(a / b_plus_c * 100, 2)

hope it helps

Re: Calculating total in row

somesoni2 — Thu, 27 Feb 2020 20:55:30 GMT

Try this (assuming field Label has values "a" "b" "c". If there are different values, update the column name in the query)

..your base earch
| chart dc(Users) as UsersCount by app Label | fields - app
| stats sum(*) as *
| eval total=b+c
| eval percentage=round*a*100/total,2)

Re: Calculating total in row

khanyag1 — Wed, 11 Mar 2020 13:42:37 GMT

Thank you! I will give this a try

Re: Calculating total in row

khanyag1 — Wed, 11 Mar 2020 13:42:50 GMT

Thank you! I will try this

Re: Calculating total in row

woodcock — Wed, 11 Mar 2020 14:43:51 GMT

What you are doing is NOT MATHEMATICALLY CORRECT!!!; you cannot sum distinct counts; think about it...
So do this:

| stats dc(Users) AS UsersCount BY Label
| eval foo="bar"
| xyseries foo Label UsersCount
| eval AplusB = A + B
| untable foo Label UsersCount
| fields - foo
| eval AplusB = if(Label = "AplusB", UsersCount, null())
| eventstats first(AplusB) AS AplusB

Re: Calculating total in row

khanyag1 — Thu, 12 Mar 2020 13:12:37 GMT

If my Label displays three labels, how do I extract the labels so that I can add them? Thanks for your response!

Re: Calculating total in row

woodcock — Thu, 19 Mar 2020 15:51:51 GMT

NO, NO, NO!!! It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! DO NOT DEPLOY THIS. Although this answer does what you asked, you should not be asking this. It is as wrong as can possibly be. This is bad math, and will lead to WRONG DECISIONS!!!!

Re: Calculating total in row

woodcock — Thu, 19 Mar 2020 15:52:08 GMT

NO, NO, NO!!! It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! DO NOT DEPLOY THIS. Although this answer does what you asked, you should not be asking this. It is as wrong as can possibly be. This is bad math, and will lead to WRONG DECISIONS!!!!

Re: Calculating total in row

woodcock — Thu, 19 Mar 2020 15:53:09 GMT

Keep in mind that this answer was for EDUCATIONAL PURPOSES ONLY. It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! DO NOT DEPLOY THIS. Although this answer does what you asked, you should not be asking this. It is as wrong as can possibly be. This is bad math, and will lead to WRONG DECISIONS!!!! I am working on a solution that will work. Stand by.

Re: Calculating total in row

woodcock — Thu, 19 Mar 2020 16:16:49 GMT

It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! You need to preserve the actual values all the way through to the very end like this:

... | stats values(Users) AS Users BY Label 
| eval foo="bar" 
| xyseries foo Label Users 
| eval AplusB = mvappend(A, B)
| foreach * [ eval "<<FIELD>>"=mvjoin('<<FIELD>>', ":::") ]
| untable foo Label Users
| fields - foo 
| eval AplusB = if(Label = "AplusB", Users, null()) 
| eventstats first(AplusB) AS AplusB
| foreach Users AplusB [ eval "<<FIELD>>" = mvcount(split('<<FIELD>>', ":::")) ]

Here is a run-anywhere example:

index=_* 
| rename component AS Users, log_level AS Label 
| replace "INFO" WITH "A", "ERROR" WITH "B"
| stats values(Users) AS Users BY Label 
| eval foo="bar" 
| xyseries foo Label Users 
| eval AplusB = mvappend(A, B)
| foreach * [ eval "<<FIELD>>"=mvjoin('<<FIELD>>', ":::") ]
| untable foo Label Users
| fields - foo 
| eval AplusB = if(Label = "AplusB", Users, null()) 
| eventstats first(AplusB) AS AplusB
| foreach Users AplusB [ eval "<<FIELD>>" = mvcount(split('<<FIELD>>', ":::")) ]

Re: Calculating total in row

woodcock — Thu, 19 Mar 2020 16:31:06 GMT

See my other answer. It does it the right way keeping the values, not the counts.