How to use stats with condition from multiple fields?

[Utkc137]

Greetings,

I am struggling with creating a table in splunk which would do the following transformation:

Find the discrete count of id for A, B, and C where value the is 1, by month. 

Currently, I am calculating values for each column individually using eventstats and combining the results.

However, we have a lot of columns (a,b,c,d.....) and thus the SLP does not preform efficiently. 

Looking for a more efficient approach to this.

Thanks in advance!

[Thulasinathan_M · Accepted solution]

Hi,
Please try below:

| stats max(A) as ACnt, max(B) as BCnt, max(C) as CCnt by month, id
| stats sum(ACnt) as ACnt, sum(BCnt) as BCnt, sum(CCnt) as CCnt by month

[Utkc137]

Hey thanks a ton!
Been breaking my head on this issue. 

[Thulasinathan_M]

Happy that worked for you!! Happy Splunking 🙂

[Thulasinathan_M]

Hi,
Are A,B,C,D fields?

[Utkc137]

Yes, they are individual field values. 

[gcusello]

hi @Utkc137,

if the file name having values A, B and C is "id_cnt", you could use the chart command:

<your_search>
| chart count OVER month BY id_cnt

Ciao.

Giuseppe

[Utkc137]

This isn't the case, all columns are individual fields values.

The original log is of the following format:

<date_time> <month> <id> A=1 B=0 C=0 ...

[gcusello]

Hi @Utkc137 ,

you could try stats with eval:

<your_search>
| stats 
   count(eval(A=1)) AS A_id_cnt 
   count(eval(B=1)) AS B_id_cnt 
   count(eval(C=1)) AS C_id_cnt 
   BY month

if it doesn't run, please try also:

<your_search>
| stats 
   count(eval(A="1")) AS A_id_cnt 
   count(eval(B="1")) AS B_id_cnt 
   count(eval(C="1")) AS C_id_cnt 
   BY month

Ciao.

Giuseppe

[Utkc137]

The idea is to find discrete count of id's where A=1, B=1. Not the count of events where  these values are 1.

[gcusello]

HI @Utkc137,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @Utkc137.

sorry, what's the difference?

Ciao.

Giuseppe