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
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
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
Hi
Can you please try this search?
index=myindex | eval flag=if(sourcetype="mysourcetype",1,0) | stats sum(flag) as count by host
Thanks
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)