How to use inputlookup to check if hosts in a CSV have been sending events to Splunk, then use eval to output "yes" or "no"?

[sbattista09]

I want to inputlookup a CSV and search the hosts in the CSV to see if they have been reporting into Splunk, and then table a report that will have the host names from the CSV with an added column that displays "yes" or "no". Not sure how I can use the eval statement to do something like eval if count is 0=no if >0=yes

[Stevelim]

You might want to use a case statement instead:

input Lookup search | eval Results =case(count == 0, "Yes", count >= 0, "No")

You can also refer to this quick reference:

• https://www.splunk.com/content/dam/splunk2/pdfs/solution-guides/splunk-quick-reference-guide.pdf

[gcusello]

Try this:

| inputlookup HostList.csv 
| eval count=0 
| eval host=upper(host) 
| append [ 
|metasearch index=main latest=-7d
| eval host=upper(host) 
| stats count by host
] 
| stats sum(count) AS Total by host 
| where Total=0
| table host

after you can use eval to show the status or rangemap (see the dashboard example "Table Iconset (Rangemap)" in "Splunk 6.x Dashboard Examples".

Bye.
Giuseppe

[ryanoconnor · Accepted solution]

Something like this would get you most of the way there. I think. This would display a table of the host, the last time it reported, and then if it is reporting or not.

| inputlookup servers.csv |  join type=left host [|metadata type=hosts ] | table host lastTime | eval reporting=case(isnull(lastTime), "no", 1=1, "yes") |  eval time=strftime(lastTime,"%b %d %T %Y %Z")  | fields - lastTime

[sbattista09]

is there a way to format the lastTime field so that it is more human readable?

[ryanoconnor]

Definitely, I just modified the search for you

[gfreitas]

You can use an eval like that

| eval existing_field=if(count == "0", "No", "Yes")

Another option if the field might exist and might not:

| eval existing_field=if(isnull(field), "No", "Yes")