topic Re: Calculating distinct counts with multiple conditions in Splunk Search

Calculating distinct counts with multiple conditions

bhumikajpatel — Fri, 27 Apr 2018 17:21:06 GMT

I am trying to compute distinct counts of a field based on multiple conditions. Can anyone please help with the calc that would help to compute distinct counts?

Example:
ID Index Index(2)
1 CA A
1 NY A
1 NY A
2 CA B
2 CA B
3 CA A
3 NY A
3 NY A

I am trying to get the distinct count of ID by Index(2) where Index(1) = "CA". Any help will be highly appreciated. thanks.

Re: Calculating distinct counts with multiple conditions

elliotproebstel — Fri, 27 Apr 2018 17:32:21 GMT

I think this will fit your needs:

your current base search
| search Index(1)="CA"
| stats dc(ID) AS ID_count BY Index(1)

You could also push that Index(1)="CA" into the base search, presumably.

Re: Calculating distinct counts with multiple conditions

bhumikajpatel — Tue, 29 Sep 2020 19:17:29 GMT

Apologies, I was not clear in my original post...

I need two types of counts.

COUNT(ID), DISTINCT_COUNT(ID) by INDEX(2), and DISTINCT_COUNT(ID) by INDEX(2)but with Index ="CA" as the condition.

Can we have such combined counts? thanks for the help.

Re: Calculating distinct counts with multiple conditions

TISKAR — Fri, 27 Apr 2018 17:58:47 GMT

Can you try this please:

   <yourbasesearch> | stats count(ID) dc(ID) dc(eval(!isnull(ID) AND index="CA")) as val3  by Index(2)

Re: Calculating distinct counts with multiple conditions

somesoni2 — Fri, 27 Apr 2018 18:56:03 GMT

Give this a try

your current search giving fields ID, Index, and Index(2)
| eval CA_ID=if(Index="CA",ID,null())
| stats count(ID) as count dc(ID) as IDs dc(CA_ID) as CA_IDs by "Indexe(2)"

Re: Calculating distinct counts with multiple conditions

bhumikajpatel — Fri, 27 Apr 2018 19:30:30 GMT

Awesome! Many thanks. This worked perfectly... I was using the same but in if condition I was not ID for true value and hence was not getting correct results.

thanks for the help

Re: Calculating distinct counts with multiple conditions

TISKAR — Fri, 27 Apr 2018 20:12:49 GMT

@bhumikajpatel, are you test my proposition?

Re: Calculating distinct counts with multiple conditions

bhumikajpatel — Fri, 27 Apr 2018 20:51:16 GMT

Hi, Yes I did.. and I had tried this approach earlier as well... It gives my count as 1 for instead I needed the distinct count of Ids. Thanks for the help.