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!

How to send events & findings from AWS to Splunk using Amazon EventBridge

Amazon EventBridge is a serverless service that uses events to connect application components together, making ...

Exciting News: The AppDynamics Community Joins Splunk!

Hello Splunkers,   I’d like to introduce myself—I’m Ryan, the former AppDynamics Community Manager, and I’m ...

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...