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