topic Re: Distinct Count Where... in Splunk Search

Distinct Count Where...

singhh4 — Thu, 03 Nov 2016 14:38:58 GMT

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

Re: Distinct Count Where...

gwobben — Thu, 03 Nov 2016 14:48:44 GMT

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"

Re: Distinct Count Where...

somesoni2 — Thu, 03 Nov 2016 14:58:49 GMT

What should be the expected output for your sample data?

Re: Distinct Count Where...

singhh4 — Thu, 03 Nov 2016 15:10:03 GMT

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

Re: Distinct Count Where...

singhh4 — Thu, 03 Nov 2016 15:11:10 GMT

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

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

Re: Distinct Count Where...

gwobben — Thu, 03 Nov 2016 15:13:31 GMT

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

Re: Distinct Count Where...

somesoni2 — Thu, 03 Nov 2016 15:57:40 GMT

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

Re: Distinct Count Where...

somesoni2 — Thu, 03 Nov 2016 15:58:49 GMT

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.