Splunk Search

Need help displaying null results in a table

jip31
Motivator

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

arjunpkishore5
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

rmmiller
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

jip31
Motivator

complex but thanks to your help!

0 Karma

to4kawa
Ultra Champion

Thank you for the name in the comment 🙂

0 Karma

arjunpkishore5
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
0 Karma

jip31
Motivator

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

arjunpkishore5
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

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

jip31
Motivator

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

to4kawa
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...