topic Re: Inputlookup search and join in Splunk Search

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

davinder_kaur — Mon, 25 Apr 2022 14:58:55 GMT

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?

Re: Inputlookup search and join

PickleRick — Sat, 23 Apr 2022 05:25:17 GMT

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

Re: Inputlookup search and join

VatsalJagani — Mon, 25 Apr 2022 06:02:01 GMT

@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!!!

Re: Inputlookup search and join

davinder_kaur — Tue, 26 Apr 2022 13:05:41 GMT

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.

Re: Inputlookup search and join

VatsalJagani — Tue, 26 Apr 2022 13:39:05 GMT

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