Conditional count distinct if

[jclemons7]

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]

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 

[jclemons7]

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

[sideview]

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.

[sideview]

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