Splunk Search

Conditional count distinct if

jclemons7
Path Finder

Hello all,

I'm looking to do a "count distinct value if record type = foobar" type of scenario. Hopefully, I'll be able to articulate what I'm trying to do here.

record type A:

record: person
name: bob
id: 123456
sex: m
state: tx
hp: 555-123-1234
dept: finance



record: person
name: jane
id: 7949191
sex: f
state: ca
hp: 555-456-7890
dept: marketing



record: person
name: jane
id: 7949191
sex: f
state: ca
hp: 555-456-7890
dept: marketing

record type B:

record: computer
computername: mycomputer
type: pc
ram: 4GB
ip: 1.1.1.1
dept: finance





record: computer
computername: mylaptop
type: laptop
ram: 4GB
ip: 2.2.2.2
dept: finance





record: computer
computername: theserver
type: server
ram: 16GB
ip: 10.0.0.1
dept: marketing

stats if(record="computer",dc(computername)) as dc_computer if(record="person",dc(id)) as dc_people by dept

desired result:

dept        dc_computer dc_people   
finance         2           1                       
marketing         1         2                       

somesoni2
SplunkTrust
SplunkTrust

Give this a try. There is no id field for computer and there is no computername field for person record, it can be simply done by this

your base search | stats dc(computername) as dc_computer dc(id) as dc_people by dept 
0 Karma

jclemons7
Path Finder

ugh.. sorry, in my "real" data.. the field I need to count distinct values from is actually the same name. I think my dummy data is not accurate..

0 Karma

sideview
SplunkTrust
SplunkTrust

I like this answer but it does rely on the additional assumption that the computername is also unique. iow there might be some "my laptop" computers out there. or just null values which wouldn't get counted.

0 Karma

sideview
SplunkTrust
SplunkTrust

I recommend breaking this sort of thing into an eval statement and then a separate stats command just to make it more comprehensible.

Also in this case you can make a separate "record_type" field and then use that with the chart command to do it a little more compactly.

| eval record_type=case(record="computer","computer",  record="person","person")
| chart dc(id) over department by record_type

However, there is another way, and this I think is more the way you were headed -- to create separate id fields and then use stats

| eval person_id=if(record="person",id,null()
| eval computer_id=if(record="computer",id,null())
| stats dc(person_id) dc(computer_id) over department 
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...