Getting Data In

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

New Member

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.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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).

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

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).

View solution in original post

0 Karma

New Member

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!

0 Karma

New Member

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

0 Karma

SplunkTrust
SplunkTrust

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

New Member

Thanks! It works!

0 Karma

New Member

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Ultra Champion

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.

0 Karma

New Member

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

0 Karma

Ultra Champion

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