How to search for devices/servers not reporting to Splunk?

[waJesu]

I have two queries I want to merge and I need expert help. The first one returns reporting devices as good and non-reporting devices as missing. The second one returns the missing devices with a heartbeat but not sending logs. Help me come up with one query that would show results for Good, Heartbeat and Missing:

| tstats latest(_time) as latest where index="*" earliest=-5d by host
| eval recent = if(latest > relative_time(now(),"-15m"),"Good","Missing"), realLatest = strftime(latest,"%c")

| tstats latest(_time) as latest where index="_*" earliest=-5d by host
| eval recent = if(latest > relative_time(now(),"-15m"),"Heartbeat","Missing"), realLatest = strftime(latest,"%c")

[richgalloway]

See if this helps.

| tstats latest(_time) as latest where (index="*" OR index=_internal) earliest=-5d by host
| eval recent = case(index!=_internal AND latest > relative_time(now(),"-15m"),"Good", 
                     index!=_internal AND latest <= relative_time(now(),"-15m"), "Missing", 
                     index=_internal AND latest > relative_time(now(),"-15m"), "Heartbeat", 
                     1==1, "Missing"), 
       realLatest = strftime(latest,"%c")

Standard disclaimer: don't use index=* in production.

[waJesu]

This is the error I am getting after running the query:

[richgalloway]

I dropped a comma in my reply.  It's fixed.

[waJesu]

Thank you. I think you forgot to attach the corrected query.

[richgalloway]

Didn't forget.  The correction is in the original query.

[waJesu]

I am not sure why it's returning "Missing only even on devices that are reporting. Maybe the query needs a tweak?

[richgalloway]

Query has been tweaked.

[waJesu]

Maybe I am missing something. It's still returning "Missing" for everything.

[richgalloway]

Please have a look at the case function and verify the logic there meets your business requirements.

[waJesu]

I think it does. We want the query to return devices sending logs as Good, those not reporting as Missing and those missing yet have a heartbeat as Heartbeat. That's what the case function is saying. I am actually surprised I am not getting expected results.

[waJesu]

Good morning.  Any new thoughts as to why my results are showing "Missing" only even for devices/servers I know to be reporting? Anything to tweak the query somehow?

[waJesu]

Good morning.  Any new thoughts as to why my results are showing "Missing" only even for devices/servers I know to be reporting? Anything to tweak the query somehow?