topic Re: Need help displaying null results in a table in Splunk Search

Need help displaying null results in a table

jip31 — Fri, 01 Nov 2019 15:20:07 GMT

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

Re: Need help displaying null results in a table

to4kawa — Fri, 01 Nov 2019 15:31:34 GMT

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?

Re: Need help displaying null results in a table

arjunpkishore5 — Fri, 01 Nov 2019 17:10:22 GMT

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

Re: Need help displaying null results in a table

rmmiller — Fri, 01 Nov 2019 17:46:36 GMT

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

Re: Need help displaying null results in a table

to4kawa — Fri, 01 Nov 2019 19:58:04 GMT

Thank you for the name in the comment 🙂

Re: Need help displaying null results in a table

jip31 — Sat, 02 Nov 2019 06:04:08 GMT

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

Re: Need help displaying null results in a table

jip31 — Sat, 02 Nov 2019 06:32:59 GMT

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

Re: Need help displaying null results in a table

jip31 — Sat, 02 Nov 2019 06:33:25 GMT

complex but thanks to your help!

Re: Need help displaying null results in a table

to4kawa — Sat, 02 Nov 2019 06:51:05 GMT

 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.

Re: Need help displaying null results in a table

arjunpkishore5 — Sat, 02 Nov 2019 11:43:11 GMT

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