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!

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...