topic Re: How to get sum of field based on it's value? in Splunk Search

How to get sum of field based on it's value?

swdowiarz — Fri, 20 Sep 2019 13:01:38 GMT

Hi,

I would be grateful for any help.

In my fields we are having two fields which are: data.user_id and data.config.offlineGDTS

data.config.offlineGDTS can have value true or false

My questions is: How can I have statistics for each user how many events he has for data.config.offlineGDTS=true and data.config.offlineGDTS=false ?
Basically I would like to have in one row: user_id, sum(data.config.offlineGDTS=true), sum(data.config.offlineGDTS=false)

What I've tried looks like this:

index=cs_engineering sourcetype=pizza_app data.offlineGDTS=* | stats 
count(eval(data.offlineGDTS="true")) as ONLINE_GDT
count((data.offlineGDTS="false")) as OFFLINE_GDT
by data.user_id

Re: How to get sum of field based on it's value?

richgalloway — Fri, 20 Sep 2019 14:01:50 GMT

The second count is missing an eval. What do you get from that query?

Re: How to get sum of field based on it's value?

swdowiarz — Wed, 30 Sep 2020 02:14:29 GMT

Oh yes, I missed that one.
With this query I'm getting the look of a table as I wanted with three columns( user_id, ONLINE_GDT, OFFLINE_GDT ), but for each row(user_id) the data are not being count and all the values for GDTs are 0.

Re: How to get sum of field based on it's value?

DavidHourani — Sun, 22 Sep 2019 10:27:52 GMT

Hi @swdowiarz,

Try this, could be that the . is causing some problems with the eval as it could be interpreted as a concatenation :

 index=cs_engineering sourcetype=pizza_app data.offlineGDTS=* 
|rename data.offlineGDTS as NewofflineGDTS
| stats  count(eval(NewofflineGDTS="true")) as ONLINE_GDT count(eval(NewofflineGDTS="false")) as OFFLINE_GDT
 by data.user_id

Let me know if that helps.

Cheers,
David

Re: How to get sum of field based on it's value?

swdowiarz — Mon, 23 Sep 2019 08:03:29 GMT

yeah! it is working as it should be, thanks!
However, do you know how to present those counts in percentage?

Re: How to get sum of field based on it's value?

DavidHourani — Mon, 23 Sep 2019 08:22:16 GMT

you're welcome !

Yeah for the percentage you just need the total count. Something like this should do the trick :

| stats count(NewofflineGDTS)  as total count(eval(NewofflineGDTS="true")) as ONLINE_GDT count(eval(NewofflineGDTS="false")) as OFFLINE_GDT

You can then use that total with an eval to make a percentage 🙂

Re: How to get sum of field based on it's value?

jonydupre — Mon, 23 Sep 2019 08:51:59 GMT

You could try the top function, it orders your results by amount and automaticly adds percentage to it, but it might not be best practice. Not sure if you can add percetage on it's own. Maybe try "showperc=true" or something?

(I just started with Splunk, so I might be wrong.)