topic Re: How to find results present in index and not in lookup table? in Splunk Search

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

Taruchit — Mon, 19 Dec 2022 19:16:48 GMT

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: -

Thus, I need your help to resolve the issue.

Thank you

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

PickleRick — Mon, 19 Dec 2022 22:13:34 GMT

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

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

richgalloway — Mon, 19 Dec 2022 22:03:11 GMT

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

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

Taruchit — Tue, 20 Dec 2022 18:26:54 GMT

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

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

richgalloway — Tue, 20 Dec 2022 21:36:08 GMT

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.

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

Taruchit — Thu, 22 Dec 2022 20:18:34 GMT

Thank you for sharing your inputs.