Splunk Search

How to compare output of a search to a lookup file?

jtelep
New Member

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!

Tags (3)
0 Karma

chandanghoshCTL
Explorer

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"

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

jtelep
New Member

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%).

0 Karma

jtelep
New Member

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.

0 Karma

lguinn2
Legend

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.

DavidHourani
Super Champion

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!

0 Karma

somesoni2
Revered Legend

What are the no of row in both? Lookup and search?

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!