topic Re: distinct_count (dc) threshold in Splunk Search

distinct_count (dc) threshold

yannquique — Mon, 02 Mar 2020 10:17:22 GMT

I'm trying to eliminate results below a threshold with dc and it's not working. I only want to show versions that have at least 10 users. Here's some of what I've tried:

Syntax error
index = data | timechart span=1w dc(userid) as Users by version where Users > 10
Only get 1 result back for a version "OTHER"
index = data | timechart span=1w dc(userid) as Users by version where dc > 10
No results
index = data | timechart span=1w dc(userid) as Users by version | where Users > 10
index = data | timechart span=1w dc(userid) as Users by version | where version > 10
Nothing is filtered (count and dc are clearly different values)
index = data | timechart span=1w dc(userid) as Users by version where Users > 10 where count > 10

Thanks

Re: distinct_count (dc) threshold

richgalloway — Mon, 02 Mar 2020 16:04:16 GMT

index = data | timechart span=1w dc(userid) as Users by version | where Users > 10 should work. Are you sure you have a field called 'userid' in the 'data' index?
What do you get from index = data | timechart span=1w dc(userid) as Users by version?

Re: distinct_count (dc) threshold

yannquique — Tue, 03 Mar 2020 09:09:47 GMT

Thanks for the reply! Without the where clause, I get all the data I expect plus the values that don't interest me:
_time:2020-03-03
v3.1:210
v3.2:1719
v3.3.0:7

When I add the where clause I get "No results found"

Re: distinct_count (dc) threshold

yannquique — Tue, 03 Mar 2020 09:12:10 GMT

If it's important: I'm using Splunk Enterprise v7.3.3

Re: distinct_count (dc) threshold

njohnson7 — Tue, 03 Mar 2020 10:22:43 GMT

@yannquique

I believe When you do a timechart, all you see there is your values split by versions for different time buckets and there is no field named Users for Splunk to apply that mathematical condition you specify in the where clause and hence it is returning no results.

Could you try this and see?

index = data 
| bucket span=1w _time  
| stats dc(userid) as Users by _time version 
| where Users > 10 
| xyseries _time, version, Users 
| fillnull value=0

Re: distinct_count (dc) threshold

yannquique — Tue, 03 Mar 2020 12:20:35 GMT

That works great! Thank you.
I'm very impressed with your splunk query knowledge. I'm going to have to play around with all those commands to try and understand how it works. It's not simple and you got it first time.

Thanks again