Splunk Search

Need help displaying null results in a table

Contributor

Hi,

With the search below, I would like to be able to display in my table the host which have also "No SPLUNK Agent"
Actually, I have only the "SPLUNK Agent is present"
How I can do this, please?

[| inputlookup host.csv 
    | table host]   index=toto sourcetype="winhostmon" Type=Service Name=SplunkForwarder
| stats latest(Name) as "SPLUNK Service" by host 
| eval "SPLUNK agent status"=if(isnotnull("SPLUNK Service"),"SPLUNK Agent is present", "No SPLUNK Agent") 
| search "SPLUNK agent status"="No SPLUNK Agent" 
| rename host as Hostname
0 Karma
1 Solution

Motivator

You already are filtering to only those Hosts which have a Name value. Remove that. and if my guess about what you're trying to achieve is right, you need to move that to the if statement

index=toto sourcetype="winhostmon" Type=Service [| inputlookup host.csv 
     | table host] 
 | stats latest(Name) as Name by host 
 | eval "SPLUNK agent status"=if(Name=="SplunkForwarder","SPLUNK Agent is present", "No SPLUNK Agent") 
 | search "SPLUNK agent status"="No SPLUNK Agent" 
 | rename host as Hostname

View solution in original post

0 Karma

Contributor

I think this is what you're after:

  [| inputlookup host.csv 
      | table host]     index=toto sourcetype="winhostmon" Type=Service 
| stats values(Name) AS "Names" by host
| eval "SPLUNK agent status" = if(isnull(mvfind(Names,"SplunkForwarder")),"No SPLUNK Agent","SPLUNK Agent is present")
| search "SPLUNK agent status" = "No SPLUNK Agent"
| rename host AS Hostname

If you don't want the Names column, add in this line before the rename at the bottom:

| fields - Names

Test query:

| makeresults count=20
| eval raw=split("SplunkForwarder,SMTP_Server,WWW_Publishing,Server,Workstation",",")
| eval Name=mvindex(raw,random()%4)
| eval alphabet=split("abcdefg","")
| eval host=mvindex(alphabet,random()%7)
| table host Name
`comment("Mocked-up sample data with credit to to4kawa")`
| stats values(Name) AS "Names" by host
| eval "SPLUNK agent status" = if(isnull(mvfind(Names,"SplunkForwarder")),"No SPLUNK Agent","SPLUNK Agent is present")
| search "SPLUNK agent status" = "No SPLUNK Agent"
| rename host AS Hostname

Hope that helps!
rmmiller

0 Karma

Contributor

complex but thanks to your help!

0 Karma

Ultra Champion

Thank you for the name in the comment 🙂

0 Karma

Motivator

You already are filtering to only those Hosts which have a Name value. Remove that. and if my guess about what you're trying to achieve is right, you need to move that to the if statement

index=toto sourcetype="winhostmon" Type=Service [| inputlookup host.csv 
     | table host] 
 | stats latest(Name) as Name by host 
 | eval "SPLUNK agent status"=if(Name=="SplunkForwarder","SPLUNK Agent is present", "No SPLUNK Agent") 
 | search "SPLUNK agent status"="No SPLUNK Agent" 
 | rename host as Hostname

View solution in original post

0 Karma

Contributor

it seems to work thanks
last question : I want to count the number of hosts with a "No SPLUNK Agent" status
what is the better way to do this please??

0 Karma

Motivator

@jip31 I you just want the total count, you can add | stats count(host) as total to the end of the query posted above.
If you need the total as an additional column, add | eventstats count(host) as total to the end of the query posted above

Cheers.

0 Karma

Ultra Champion

index=toto sourcetype="winhostmon" Type=Service Name=SplunkForwarder
By this search,
Name = SplunkForwarder
We are searching only for those that are.
Therefore, the next if statement can only be true.

 [| inputlookup host.csv 
     | table host]     index=toto sourcetype="winhostmon" Type=Service 
 | stats latest(Name) as "SPLUNK Service" by host 
 | eval "SPLUNK agent status"=if(("SPLUNK Service"!="","SPLUNK Agent is present", "No SPLUNK Agent") 
 | stats list(host) as Hostname by "SPLUNK agent status"

How about this?

Contributor

hi, same problem
the events corresponding to "SPLUNK Agent is present" are well displayed but the events corresponding to "No SPLUNK Agent" are not...
I remind just a thing : "No SPLUNK Agent" means that SPLUNK agent is not installed as a consequence the host corresponding in host.csv dont obviously generate events...
So why your code doesnt let to display "No SPLUNK Agent" events??

0 Karma

Ultra Champion
 index=toto sourcetype="winhostmon" Type=Service  Name=""

Check this result.

If this query returns results, the previous query should be fine.

If it does not return, there is no terminal that does not contain an agent.

0 Karma