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
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
What should be the expected output for your sample data?
Agent |Hosts
agent1 |132
agent2 |304
agent3 |247
I just need a count of distinct hosts for an agent where agent = Yes.
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.
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"
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
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).