So I've got a lookup table full of hostnames that I want to compare to a search that returns only the active hosts so I can figure out which of those hosts are active and which aren't. Basically if the resultant hostname from the lookup is not in the results of the search I want it printed and counted so I can get an idea as to what hosts aren't currently active when compared to the lookup file.
I can't seem to find an answer to this anywhere.
Help!
i need to do same thing like DavidHourani asked and thank to lguinn2
index=cg| rex "^\S+\s+(?<cgSYSlogIPAddress>\S+)" | stats count by cgSYSlogIPAddress | eval active_host="T" | append [ inputlookup cgIP | eval active_host="F" ] | dedup cgSYSlogIPAddress |where active_host="F"
Let me rephrase: You have a lookup table that contains more unique hosts than your search result; you want to output hosts that are not found in your search result. If this is the case, this pseudo search will show you the hosts that do not show in your active search:
| set diff
[ inputlookup thingamajig | fields hostname ]
[ activesearch |fields hostname | fields - _* ]
Here, "thingamajig" is the lookup table containing all hosts in field hostname
; "activesearch" is a search that will return all active hosts as field hostname
. No need to pipe into stats
.
Caveat: If "activesearch" comes back with a hostname that is not in "thingamajig", it will also show in results. (Worse, if this extraneous hostname is contained in more than one event, it will be repeated more than once unless you do stats in search.)
If all you need is a gauge, you could then count the inactive, then compare to total as counted in inputlookup:
| set diff
[ inputlookup thingamajig | fields hostname ]
[ activesearch |fields hostname | fields - _* ]
| stats count as Active
| join [ inputlookup thingamajig | stats dc(hostname) as Total ]
| stats values(eval(1 - Active/Total)) as Ratio
The way set
works, it is important that you count Total after it.
Awesome and by piping that to stats count I can get the total number of matches that don't equal the hostname so now I've got two variables (sort of) which leads to my next question actually in two parts. The first, how can I turn the output of each search into a variable (i.e. X and Y) The second, how can I compare the two? My end goal is to create a dashboard gauge that shows the a percentage of servers that have reported in "X" against the total number of servers in the list "Y" (where "Y" would be 100%).
OK so my search looks like this:
index= | rex "\sFrom\s \"(?[^\"]+)\""| stats count by hostname
My field is also named hostname in the lookup table. My lookup is called thingamajig. What I am trying to accomplish is this, my lookup table contains about 20 hostnames. The search extracts the field "From" and calls it hostname then lists how many times each hostname is hit upon. What I am hoping is that I can then cross reference the output of that search (i.e. list of hostnames it gets) against the lookup table to find out how many hostnames listed in the lookup table it didn't match as that is the data I am looking for.
Try this:
yoursearchhere
| dedup host
| eval active_host="T"
| append [ inputlookup yourlookupname | eval active_host="F" ]
| stats count(eval(active_host=="T")) as active by host
| where active==0
This assumes that the field is named host
in the lookup table. I just used yourlookupname
because you didn't give the name of your lookup.
Hello,
Thank you for your answer, it was very useful 🙂 could this be done in real time searches too ? Because it works fine when I'm running a normal search but with RT the append doesn't seem to work.
Any idea how i can get it to work for RT ?
Thanks in advance!
What are the no of row in both? Lookup and search?