Splunk Search

Calculating distinct counts with multiple conditions

bhumikajpatel
Explorer

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.

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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)"
0 Karma

bhumikajpatel
Explorer

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

0 Karma

TISKAR
Builder

Can you try this please:

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

TISKAR
Builder

@bhumikajpatel, are you test my proposition?

0 Karma

bhumikajpatel
Explorer

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.

0 Karma

elliotproebstel
Champion

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.

0 Karma

bhumikajpatel
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...

Splunk AppDynamics Agents Webinar Series

Mark your calendars! On June 24th at 12PM PST, we’re going live with the second session of our Splunk ...

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...