topic Re: Return host name in search even if no results are found in Splunk Search

Return host name in search even if no results are found

jamesmatthews — Mon, 11 Dec 2017 12:50:31 GMT

Hey,

I am very new to Splunk so apologies if this is a very simple question.

Currently Splunk is monitoring application log files and i want to get the volume of log entries in a time frame.

i am currently using this search:
index=myindex sourcetype="mysourcetype" | stats count by host

This works fine but if a host does not log anything in a set time frame it does not appear in the search results.
Is there anyway to have hosts that do not return results appear in the search results but returning a zero?

From looking around on Splunk answers i am guessing its achievable using a lookup but i have not been successful.

thanks in advance.

James M

Re: Return host name in search even if no results are found

kamlesh_vaghela — Mon, 11 Dec 2017 13:42:23 GMT

Can you please try this search?

index=myindex  | eval flag=if(sourcetype="mysourcetype",1,0)  | stats sum(flag) as count by host

Thanks

Re: Return host name in search even if no results are found

harsmarvania57 — Mon, 11 Dec 2017 14:21:54 GMT

Hi @jamesmatthews,

Query which is provided by @kamlesh_vaghela will work when host is ingesting data into myindex in set time frame for other sourcetype not mysourcetype. Let's say host is ingesting in myindex index for only mysourcetype sourcetype in that case you can create lookup table with hostname for which you want to check whether those hosts are sending data to myindex or not for sourcetype mysourcetype

Let's say you have lookup file hostnames.csv with column header hostname, in that case you can try below query

| inputlookup hostnames.csv | fields hostname | rename hostname AS host | join type=outer host [ search index=myindex sourcetype="mysourcetype" | stats count by host ] | where isnull(count)

Re: Return host name in search even if no results are found

gcusello — Mon, 11 Dec 2017 14:30:35 GMT

Hi jamesmatthews,
the easiest way is to create a lookup (e.g. called perimeter.csv) containing all the hosts in your perimeter (one column called "host") and then run a search like this:

index=myindex sourcetype="mysourcetype" 
| eval host=upper(host)
| stats count BY host
| append [  | inputlookup perimeter.csv | eval host=upper(host), count=0 | fields host count ]
| stats sum(count) AS Total BY host

In this way hosts with Total=0 are the missed ones, instead hosts with Total>0 are OK.

You can show host situation in a dashboard alche in graphic mode.
You can also create an alert when Total=0 so you immediately know when there's a problem.

Bye.
Giuseppe