How can I create a historical search to see if a sourcetype didn't come in for certain hosts?

[mpham07]

Hello all,

I currently have a search that checks to see if a sourcetype is coming for specific hosts tagged with a custom tag (example below). The issue is that you'll have to include the current day in the time picker for it to display the "success" message. A user requested to have it as a historical search to display if any host had any "fail" message for not forwarding their "my_srctype" log.

I tried looking into using metadata search but that doesn't narrow down to the custom tag we have.

Was wondering if you guys have any suggestions in creating a new search where it'll display an additional time-date column if the host failed to send the "my_srctype" data if the user want to perform a historical search for previous month etc.? Thank you in advance.

Current search for the panel:

index=my_index custom_tag=tag sourcetype="my_srctype"  
| stats latest(_time) AS Latest by host sourcetype  
| eval check1hour=(now()- Latest)/3600  
| eval status=if(check1hour>1,"fail","success") 
| eval LastIngestionTime=strftime(Latest, "%Y/%m/%d %H:%M:%S %Z")  
| rename LastIngestionTime as "Last Ingestion Time"  
| table host sourcetype "Last Ingestion Time" status  | sort - _time

Example result:

host    sourcetype  Last Ingestion Time    status
host01  my_srctype  2019/12/30 00:00:00 EST   success

[gjanders · Accepted solution]

Could the Broken Hosts App for Splunk or TrackMe or Meta Woot! help here? These all track missing data...

[to4kawa]

| metadata type=hosts index=my_index 
| append 
    [| metadata type=sourcetypes index=my_index]
| append
    [ search index=my_index custom_tag=tag sourcetype="my_srctype"  
      | eval filter=1
      | stats latest(_time) AS lastTime dc(filter) as filter by host sourcetype]
| stats values(*) as * range(lastTime) as range by sourcetype
| where filter=1

How about processing time etc. based on this result?

[aberkow]

We have a concept with our deployment of an "all hosts" csv, which is the current representation of all of our reporting hosts. In this sense, you could do something similar, setting up a csv which represents the most recent sourcetype ingest by host. The base of that search could be something like:

index=my_index custom_tag=tag sourcetype="my_srctype"  
 | stats latest(_time) AS Latest by host sourcetype  
|inputlookup append=t sourceIngestByHost.csv
|stats latest(Latest) as Latest by host, sourcetype
|outputlookup sourceIngestByHost.csv

and then you can use that csv as a reference in the bottom of your search:

index=my_index custom_tag=tag sourcetype="my_srctype"  
| inputlookup sourceIngestByHost.csv append=t
|eval _time=coalesce(_time, Latest)
 | stats latest(_time) AS Latest by host sourcetype  
 | eval check1hour=(now()- Latest)/3600  
 | eval status=if(check1hour>1,"fail","success") 
 | eval LastIngestionTime=strftime(Latest, "%Y/%m/%d %H:%M:%S %Z")  
 | rename LastIngestionTime as "Last Ingestion Time"  
 | table host sourcetype "Last Ingestion Time" status  | sort - _time

The first time you run the top command you'll probably need to run it without the inputlookup command, you should then have a file of host, sourcetype, Latest -> you can pipe this into your other search, coalesce the _time fields so they play nicely in the stats command, and then get the latest time by sourcetype regardless of whether they're in the last day search, as the csv search (which you can run nightly), will have this historical data.

Does this make sense? Hope it helps 🙂

[mpham07]

Hi there aberkow,

So I created the sourceIngestByHost.csv with the SPL below:

index=my_index custom_tag=tag sourcetype="my_srctype"
| stats latest(_time) AS Latest by host sourcetype
| outputlookup sourceIngestByHost.csv

And when I ran the SPL below and then checked the "Latest" column in the "sourceIngestByHost.csv", it doesn't have any data.

index=my_index custom_tag=tag sourcetype="my_srctype"
| stats latest(_time) AS Latest by host sourcetype
| inputlookup append=t sourceIngestByHost.csv
| stats latest(Latest) as Latest by host, sourcetype
| outputlookup sourceIngestByHost.csv

[aberkow]

Oh, yeah in the second one it should be max, not latest. latest only works if you have a _time field still, which we don't have in the csv. Alternatively, change the top search to be stats latest(_time) as _time, then you can move the inputlookup one command higher in the second one and remove one of the stats commands. Your call!

[mpham07]

Thank you for the quick response aberkow, I will definitely try this since it's quickest to implement in our current environment at the moment. I'll post an update once I tested everything out!