Why search not displaying results for those values only if any index/sourcetype is missing logs?

[davinder_kaur]

Hi, 

After reviewing most of the posts and not finding a solution. I finally came here to ask for help related to my query problem. 

I have a lookup table which runs sweeps to check if logs are missing in any particular index/host. My query was working like a charm from last two years, but suddenly, it started to show FPs. 

My query = 

| inputlookup mylookuptable.csv | table index sourcetype host
| join index sourcetype host type=left [| tstats count where index=* sourcetype=* host=* by _time index sourcetype host | stats count by index sourcetype host]
| fillnull value=0
| search count = 0

Can someone please help me understand why its not displaying results for those values only if any index/sourcetype is missing logs?

[VatsalJagani]

@davinder_kaur - Try this query:

| tstats max(_time) as last_event where index=* by index, sourcetype, host
| inputlookup missing_data.csv append=true 
| stats max(last_event) as last_event by environment, index, sourcetype, host
| eval current_time=now(), status = if(last_event < (current_time - 3600), "Missing", "Active")
| appendpipe 
    [| outputlookup missing_data.csv
    | where index="DON'T"]
| where status="Missing"

You need to schedule this every hour.

And that way this query will give you an alert if any data that was coming before suddenly stopped coming. 

Here is wait duration would be 1 hour (3600 seconds) but you can change it if you want.

---------
I hope this will be helpful. An upvote would be appreciated!!!

[PickleRick]

I'm not gonna answer your question as such but I'll point out two strange things about your search.

One is the unnecessary statsing over tstats. Why doing tstats over _time only to stats the results in the next step? You would be ok with just tstatsing over the rest of the fields, without _time . And this actually might be the reason for your subsearch terminating prematurely if it's being processed for too long. The subsearch would then silently fail and you'd get incomplete data.

But another thing is that you actually don't do it this way to have reliable results quickly.

I'd advise the reverse approach - calculate tstats over your indexes, then add "empty" results from your list of metadata and sum.

| tstats count by index sourcetype host 
| append
  [ | inputlookup mylookuptable.csv
    | table index sourcetype host
   | eval count=0 ]
| stats sum(count) as count by index sourcetype host
| where count=0

[davinder_kaur]

hi @VatsalJagani @PickleRick, 

Sorry folks, I worked on it yesterday but didn't get time to reply to you. Unfortunately, it’s still not working for me.

[VatsalJagani]

@davinder_kaur - Can you please give some details on what you mean when you say it's not working? Any errors or what is not working as you expect.