Need help displaying null results in a table

[jip31]

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

[rmmiller]

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

[jip31]

complex but thanks to your help!

[to4kawa]

Thank you for the name in the comment 🙂

[arjunpkishore5 · Accepted solution]

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

[jip31]

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??

[arjunpkishore5]

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

[to4kawa]

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?

[jip31]

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??

[to4kawa]

 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.