topic Re: How to use stats with condition from multiple fields? in Splunk Search

How to use stats with condition from multiple fields?

Utkc137 — Sat, 30 Sep 2023 14:51:26 GMT

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!

Re: How to use stats with condition from multiple fields?

gcusello — Sat, 30 Sep 2023 15:03:38 GMT

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

Re: How to use stats with condition from multiple fields?

Thulasinathan_M — Sat, 30 Sep 2023 15:03:43 GMT

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

Re: How to use stats with condition from multiple fields?

Utkc137 — Sat, 30 Sep 2023 15:09:34 GMT

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

Re: How to use stats with condition from multiple fields?

Utkc137 — Sat, 30 Sep 2023 15:11:02 GMT

Yes, they are individual field values.

Re: How to use stats with condition from multiple fields?

gcusello — Sat, 30 Sep 2023 15:14:48 GMT

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

Re: How to use stats with condition from multiple fields?

Thulasinathan_M — Sat, 30 Sep 2023 15:21:40 GMT

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

Re: How to use stats with condition from multiple fields?

Utkc137 — Sat, 30 Sep 2023 15:22:32 GMT

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.

Re: How to use stats with condition from multiple fields?

gcusello — Sat, 30 Sep 2023 15:24:00 GMT

Hi @Utkc137.

sorry, what's the difference?

Ciao.

Giuseppe

Re: How to use stats with condition from multiple fields?

Utkc137 — Sat, 30 Sep 2023 15:29:49 GMT

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

Re: How to use stats with condition from multiple fields?

gcusello — Sat, 30 Sep 2023 15:29:53 GMT

HI @Utkc137,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

Re: How to use stats with condition from multiple fields?

Thulasinathan_M — Sat, 30 Sep 2023 15:35:53 GMT

Happy that worked for you!! Happy Splunking 🙂