Splunk Search

Distinct Count Where...

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

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

Revered Legend

What should be the expected output for your sample data?

0 Karma

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

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

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"

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

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!