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

[swdowiarz]

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

[DavidHourani · Accepted solution]

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

[swdowiarz]

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

[jonydupre]

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.)

[DavidHourani]

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 🙂

[richgalloway]

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

[swdowiarz]

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.