How can I get a list of host names that do not ingest for the last 24 hrs?

[ytaointra]

I need to get a list of host names that does not ingest for certain source for the last 24hrs compare with the same search criteria based on the 24hr period one day before. Here is my search:

index=* sourcetype=* source="*services.log" earliest=-2d latest=-1d|dedup host|fields host|search NOT [search index=* sourcetype=* source="*services.log" earliest=-24h latest=now|dedup host|fields host]|stats count by host sourcetype source

However, it doesn't seem to be working.

[gjanders]

You have the answers but I'd thought I'd add a couple more:
Broken Hosts App for Splunk
Meta Woot!

[somesoni2 · Accepted solution]

Give this a try

| tstats max(_time) as lastReportedOn WHERE index=* sourcetype=* source=*services.log earliest=-2d by host sourcetype source
| where lastReportedOn<relative_time(now(),"-24h")
| convert ctime(lastReportedOn)

Basically check, from 2 days worth of data, when was the last time a host reported with that source. Then we compare (and filter) to show hosts which have not reported in last 24 hrs (or last reported before 24 hrs from now).

[ytaointra]

Thanks! I don't have exact scenario to test at this point. However, with this search what is the output I would expect ? In other words, how can I create alert when some hosts are missing for the last 24hrs compare to the one in between last 48hrs- last 24hrs. I don't care if I get more hosts within the last 24hrs. Thanks!

[ytaointra]

Thanks. However, the above search seems not working as expected. I modify the search as following to compare the last two days result with last 1 hour. When search separately, I got different results ( 2days: 54 hosts, 1h: 50 hosts). However, when I use the search, I don't get anything comes out.

tstats max(_time) as lastReportedOn WHERE index=* sourcetype=* source="*services.log" OR source="*service.log" earliest=-2d by host sourcetype source| where lastReportedOn

[somesoni2]

Try running just this and see if the lastReportedOn is showing correct values (manually check if it's value is within last one Hr or not

| tstats max(_time) as lastReportedOn WHERE index=* sourcetype=* source=*services.log earliest=-2d by host sourcetype source
 | convert ctime(lastReportedOn)

[ytaointra]

Thanks! It works!

[ytaointra]

tstats max(_time) as lastReportedOn WHERE index=* sourcetype=* source="*services.log" OR source="*service.log" earliest=-2d by host sourcetype source| where lastReportedOn

[somesoni2]

The output should be 4 columns, host sourcetype source and lastReportedOn, where all combination of host/sourcetype/source will listed which have lastReportedOn field older than 24 hrs from the time the search has ran.

[FrankVl]

For performance reasons, you'd want to do this type of stuff using tstats (or metadata, but since you want to filter by source, that is not possible).

| tstats count where source="services.log" by _time,host
| bin _time span=1d

That gives you the count of events per host, for that specific source, by day (just set the timepicker to look for the last 2 days). Then you can do some smart stuff to find hosts that do have a count for yesterday, but not for today.

A very basic approach can be to add the following, to find hosts that have only 1 entry. Which means that either today or yesterday they did not report.

| eventstats count as daycount by host
| where daycount = 1

To filter specifically for those that did report yesterday, but not today, you'll need to get a bit more creative.

[ytaointra]

Can you please post the entire query? Not very clear about your answer. Thanks.

[FrankVl]

I've refined it a bit more, but basically just glue the two pieces I mentioned together:

| tstats count where source="services.log" earliest=-1d@d latest=now() by _time,host span=1d
| eventstats count as daycount by host
| where daycount = 1