Distinct Count Where...

[singhh4]

Hey people,

I'm trying to get multiple "distinct count where..." working but don't know where to start.

The idea is something like

index="servers"
|stats 
     where("Agent1" = "Yes", dc(Hostname))  as "Agent1 Installed"
     where("Agent2" = "Yes", dc(Hostname))  as "Agent2 Installed"
     where("Agent3" = "Yes", dc(Hostname))  as "Agent3 Installed"

The data in the file is set up like this and can't be changed 😞

Patch|Hostname|Agent1|Agent2|Agent3
123  |host1   |yes   |yes   |no
234  |host2   |yes   |no    |no
345  |host3   |no    |no    |no
456  |host1   |no    |yes   |yes
567  |host3   |yes   |yes   |yes

The idea is to get the number of hosts with the Agent installed with all patches.
Any help will be greatly appreciated!

┈┈┈┈┈┈▕▔╲
┈┈┈┈┈┈┈▏▕
┈┈┈┈┈┈┈▏▕▂▂▂
▂▂▂▂▂▂╱┈▕▂▂▂▏
▉▉▉▉▉┈┈┈▕▂▂▂▏
▉▉▉▉▉┈┈┈▕▂▂▂▏
▔▔▔▔▔▔╲▂▕▂▂▂I

[somesoni2]

Give this a try

index="servers" 
| eval Agent=if(Agent1="yes","Agent1","") 
| eval Agent=Agent."#".if(Agent2="yes","Agent2","") 
| eval Agent=Agent."#".if(Agent3="yes","Agent3","") 
| stats dc(Hostname) by Agent

[somesoni2]

What should be the expected output for your sample data?

[singhh4]

 Agent    |Hosts
 agent1  |132
 agent2  |304
 agent3  |247

I just need a count of distinct hosts for an agent where agent = Yes.

[somesoni2]

That would be simpler then. Below line in your question was making it complex/confusing.

The idea is to get the number of hosts with the Agent installed with all patches.

[gwobben]

What is it you would like to count by? Distinct count of "Patch" by Hostname, per agent? Try something like this:

EDIT: Remove the quotes around AgentX, otherwise they'll be interpreted as strings.

index="servers"
| stats 
       dc(eval(if(Agent1 = "Yes", Hostname, NULL)))  as "Agent1 Installed"
       dc(eval(if(Agent2 = "Yes", Hostname, NULL)))  as "Agent2 Installed"
       dc(eval(if(Agent3 = "Yes", Hostname, NULL)))  as "Agent3 Installed"

[singhh4]

Not really counting by anything. I just need a count of distinct hosts for an agent where agent = Yes.

Agent    |Hosts
agent1  |132
agent2  |304
agent3  |247

I tried the search and it gave me 0's

[gwobben]

Have a look at the edit. Furthermore, make sure all the cases match, so "Yes" and "yes" are not the same here. Check your data which you need (same for field names).