Splunk Search

Distinct Count Where...

singhh4
Path Finder

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

0 Karma

somesoni2
Revered Legend

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
0 Karma

somesoni2
Revered Legend

What should be the expected output for your sample data?

0 Karma

singhh4
Path Finder
 Agent    |Hosts
 agent1  |132
 agent2  |304
 agent3  |247

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

0 Karma

somesoni2
Revered Legend

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.

0 Karma

gwobben
Communicator

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
Path Finder

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

0 Karma

gwobben
Communicator

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

0 Karma