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!!!
|inputlookup EATSourceHostLookup | search NOT [ search index=iam_eat earliest=-1m host=* source=* ] | dedup host,source|fields host,source
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.
Ah, my bad, I was simplifying things a bit. I'll update my post to give you a working solution.
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
Excellent!
I was able to solve my attempt to verify that a process runs using this technique.
Thank you very much.