Splunk Search

Search to determine what is missing in Lookup table

tmurray3
Path Finder

I have a lookup tabled defined with two columns Host and Source. I am trying to do a search to determine which hosts/source are not receiving data within the last X mins.

I can't figure out the query to determine what hosts / sources in the lookup table are NOT receiving data.

For example, the lookup table has two entires:

host              source
tx8ziuci0110    /hosting/logs/eat-customer-prod-1/access
tx8ziuci0145    /hosting/logs/eat-customer-prod-2/access

If I run the query:

index=iam_eat earliest=-1m | dedup host,source | table host,source

and the results returned are:

Host               Source
tx8ziuci0110       /hosting/logs/eat-customer-prod-1/access

What I really want is to compare the query results to the lookup table and display the hosts which are missing from the lookup table, in this example:

Host               Source
tx8ziuci0145       /hosting/logs/eat-customer-prod-2/access

Thanks in advance for your help!!!

Tags (3)

chrisfrigo
Path Finder

|inputlookup EATSourceHostLookup | search NOT [ search index=iam_eat earliest=-1m host=* source=* ] | dedup host,source|fields host,source

0 Karma

tmurray3
Path Finder

Thanks for the info. I tried to reverse the second approach as suggested but was unable to get the query to run. I keep getting an error. Here is the query I was trying to run:

 |inputlookup EATSourceHostLookup NOT [ search index=iam_eat earliest=-1m host=* source=* ] | dedup host,source|fields host,source

I get the error "Error in 'inputlookup' command: Invalid argument: 'NOT'

I believe it is see the NOT as a parameter to the inputlookup command.

0 Karma

Ayn
Legend

Ah, my bad, I was simplifying things a bit. I'll update my post to give you a working solution.

0 Karma

Ayn
Legend

I can think of a couple of approaches.

First, you could use set diff to compare the set of hosts from the lookup table with the set of host from your search. (the fields - _* at the end of the first subsearch is to remove the _* fields that are otherwise returned from the subsearch along with host and source.)

| set diff [search index=iam_eat earliest=-1m | dedup host,source | fields host,source | fields - _*] [|inputlookup yourlookupfile.csv | dedup host,source | fields host,source]

The potential problem with this approach is that while you will get the difference between those two sets, you will get no information regarding where that difference was found. So, you will not see if a host/source pair shows up in the results because it was found in the lookup but not in the search or the other way around.

Second, you could use a subsearch that gets all the host/source pairs from the lookup table and then returns that to the outer search, and then use the outer search to search for any host/pairs that were not returned from the lookup table (i.e. they're missing).

index=iam_eat earliest=-1m NOT [| inputlookup yourlookuptable.csv | dedup host,source | fields host,source]

This has the potential advantage over the first approach of that you can choose which direction you want to check the difference, just switch place of the two searches if you want to check which events are in the lookup table but not in the index for the timespan you're searching.

EDIT: Almost true! One does not simply switch place of the two events, you will need to add a search command that can receive the results from the subsearch. Otherwise the subsearch will return a number of search filters that inputlookup does not know how to handle. This is how you could implement the reverse of the search above.

|inputlookup EATSourceHostLookup | search NOT [ search index=iam_eat earliest=-1m host=* source=* ] | dedup host,source|fields host,source

sansay
Contributor

Excellent!
I was able to solve my attempt to verify that a process runs using this technique.

Thank you very much.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...