How to find results present in index and not in lookup table?

[Taruchit]

Hi All,

I have enquired this problem earlier in older threads, however, could not get a working answer, thus, created a new thread to get wider visibility and response. 

Resources in hand: -

I have a lookup table which has many fields. I have two fields to consider: - index, host
I have a list of indexes for which results need to be fetched. 

Requirement: -

I need to fetch list of those hosts for each index value whose records are fetched in index but not fetched from lookup table. For fetching the events from index, I need to get the list of index values from lookup table.

I tried the below however, I am getting hosts which are fetched in both index and lookup table: -

|tstats fillnull_value="unknown" count AS event_count
WHERE
[
|inputlookup table1
|stats count BY index
|foreach index
[eval <<FIELD>>=replace(replace(lower(trim(<<FIELD>>)),"\s+",""),"\t+","")]
|eval search_str="(index=".index.")"
|stats values(search_str) AS search_str
|eval to_return=mvjoin(search_str," OR ")
|return $to_return
]
BY index, host
|search NOT
(
[
|inputlookup table1
|stats count BY index, host
]
)

Thus, I need your help to resolve the issue.

Thank you

[Taruchit]

Hi @richgalloway and @PickleRick,

Thank you for sharing your inputs. 

I observed that even though I was using tstats command, it was taking a long time to load the results, considering the long list of index values and their respective hosts.  And I had to build a dashboard to give the results to end users. 

To overcome the slowness of fetching results on the dashboard, I did following: -

1. Created a new lookup table.

2. Built a report with the tstats command and added the results to the new lookup table.

3. Scheduled the report to run after every 5 minutes and refresh the lookup table periodically.

4. Use the lookup table to fetch and display the results on the dashboard. 

Please share your views on the above approach and if you have suggestions to improve the same.

Thank you

[richgalloway]

Sometimes the tstats command takes a while.

Your workaround is fine, but there's no need for another lookup table.  Schedule a search to run and output results using a table command.  Splunk will save the results as it normally does.  Have the dashboard invoke the search using the loadjob command.  Splunk will load the results of the most recent run of the saved search.

[Taruchit]

Thank you for sharing your inputs. 

[richgalloway · Accepted solution]

The first subsearch appears to be doing a lot of unnecessary work that could be replaced by the format command.

The second subsearch is telling Splunk to look for the index, host, and count fields, but the main search has only index and host.  

Try this search

| tstats fillnull_value="unknown" count AS event_count
WHERE
[
  | inputlookup table1
  | fields index
  | format
]
BY index, host
|search NOT
[
  | inputlookup table1
  | fields index, host
  | format
]

[PickleRick]

You can approach it from a different side completely.

Instead of building a condition for your search, you can just search across all data (it's tstats so it should be reasonably quick) and filter out the "bad" entries

| tstats count where index IN ("your","set","of","indexes","or","*") host IN ("another","set","or","*") by host index
| lookup yourlookup index host OUTPUT index as found_index
| where NOT index=found_index

 But you can still build your search by excluding from tstats directly

| tstats count where
   [ | inputlookup yourlookup
     | table index ] NOT
   [ | inputlookup yourlookup
      | table host index ]
BY host index