topic Re: Conditional count distinct if in Splunk Search

Conditional count distinct if

jclemons7 — Tue, 29 Sep 2020 09:22:02 GMT

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

Re: Conditional count distinct if

sideview — Wed, 06 Apr 2016 18:21:08 GMT

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

Re: Conditional count distinct if

somesoni2 — Wed, 06 Apr 2016 18:22:19 GMT

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

Re: Conditional count distinct if

sideview — Wed, 06 Apr 2016 18:25:05 GMT

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.

Re: Conditional count distinct if

jclemons7 — Wed, 06 Apr 2016 18:25:41 GMT

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..