topic Re: How to generate a single search that uses inputlookup and join on multiple fields? in Splunk Search

How to generate a single search that uses inputlookup and join on multiple fields?

pcordel — Mon, 24 Oct 2016 21:16:41 GMT

I have a list of hosts that submit logs periodically. I need Splunk to generate an alert if the last time it received a log from a host on this list is older than a configurable value per host.

The list of hosts was created Excel, saved as a CSV, uploaded successfully into the Lookup Editor and is called criticalhosts.csv. Below is the contents (I've used example data)

IP        Hostname   FQDN                   MaxTimeoutInSeconds Function     Description
10.0.0.1    host1       host1.corpdomain.com    900                 webserver   Old Webserver
10.0.0.2    host2                               3600                   webserver    New Webserver
10.0.0.3                 host3.corpdomain.com   86400                 mailserver   Mailserver
10.0.0.4                                         300                    appserver   Appserver

The data represents real hosts. Within my domain, DNS is not necessarily well-maintained and does not always have entries for existing hosts. As such, the "host" value in the logs is sometimes IP, sometimes hostname or sometimes FQDN. While I agree this should be standardized, that's not the world I currently inhabit.

The MaxTimeoutInSeconds value is the maximum amount of time in seconds allowable for the lastTime entry for a particular host.

So, if I wanted to keep it simple and use a fixed time rather than the MaxTimeoutInSeconds of the lookup table, say at least 15 minutes old(900 seconds) but younger than 30 days old (2592000 seconds) I'd use the following.

| metadata type=hosts
| eval lastEventAgeInSeconds = (now() - lastTime)
| search lastEventAgeInSeconds > 900 lastEventAgeInSeconds < 2592000

So far, so good. Now, to use that data and find all log entries matching an IP in my lookup table and display them in a human format I'd use the following.

| metadata type=hosts
| eval lastEventAgeInSeconds = (now() - lastTime)
| search lastEventAgeInSeconds > 900 lastEventAgeInSeconds < 2592000 
| join [|inputlookup criticalhosts.csv | eval host=IP]
| convert ctime(lastTime) 
| table host Hostname FQND lastEventAgeInSeconds lastTime Function Description

That gives me a very usable subset of what I want to know. Now, I could create another search to find all log entries matching a hostname in my lookup table my changing the inputlookup statement like so.

| metadata type=hosts
| eval lastEventAgeInSeconds = (now() - lastTime)
| search lastEventAgeInSeconds > 900 lastEventAgeInSeconds < 2592000 
| join [|inputlookup criticalhosts.csv | eval host=Hostname]
| convert ctime(lastTime) 
| table host Hostname FQND lastEventAgeInSeconds lastTime Function Description

But that requires a separate search for matching IP, hostname or FQDN. And that's not mentioning the use of the MaxTimeoutInSeconds value in the lookup table.

==== HERE'S THE QUESTION ===
How do I have a single search that matches against IP, hostname or FQDN in addition to using the MaxTimeoutInSeconds values in my lookup table instead of fixed timeouts?

Re: How to generate a single search that uses inputlookup and join on multiple fields?

sundareshr — Mon, 24 Oct 2016 22:00:49 GMT

How about something like this

 | metadata type=hosts
 | eval lastEventAgeInSeconds = (now() - lastTime)
 | lookup criticalhosts.csv Hostname as host 
 | where lastEventAgeInSeconds >  MaxTimeoutInSeconds
 | convert ctime(lastTime) 
 | table host Hostname FQND lastEventAgeInSeconds lastTime Function Description

Re: How to generate a single search that uses inputlookup and join on multiple fields?

pcordel — Mon, 24 Oct 2016 22:08:43 GMT

Doesn't the lookup in your line 3

| lookup criticalhosts.csv Hostname as host

do the same thing as

| join [|inputlookup criticalhosts.csv | eval host=Hostname]

Re: How to generate a single search that uses inputlookup and join on multiple fields?

sundareshr — Mon, 24 Oct 2016 22:22:49 GMT

lookup command adds selected fields from the lookup file to matching events from your index, whereas inputlookup append=t will append all data from the lookup. Here's more documentation on lookup

http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Lookup

Re: How to generate a single search that uses inputlookup and join on multiple fields?

pcordel — Mon, 24 Oct 2016 22:58:16 GMT

I see that lookup works in the way you describe. However, I don't see how it addresses my end question. I see how your query matches my lookup table to my log entries if the log entry's "host" value is a Hostname. If the log entry's "host" value is an IP or an FQDN, that host won't match the Hostname value in the lookup table.

Re: How to generate a single search that uses inputlookup and join on multiple fields?

sundareshr — Mon, 24 Oct 2016 23:13:53 GMT

Just repeat the lookup for each field that needs to be match.

Re: How to generate a single search that uses inputlookup and join on multiple fields?

pcordel — Fri, 28 Oct 2016 20:04:58 GMT

As it turns out, I had better success reversing the process a bit.

| inputlookup criticalhosts.csv
| join type=inner IP[| metadata type=hosts | eval IP=host]
| join type=left Hostname[| metadata type=hosts | eval Hostname=host]
| join type=left Hostname[| metadata type=hosts | eval FQDN=host]
| eval lastEventAgeInSeconds = (now() - lastTime)
| where (lastEventAgeInSeconds > MaxTimeoutInSeconds) AND (lastEventAgeInSeconds < 2592000)
| convert ctime(lastTime) 
| table IP Hostname FQDN Function Description MaxTimeoutInSeconds lastEventAgeInSeconds lastTime

Re: How to generate a single search that uses inputlookup and join on multiple fields?

aaraneta_splunk — Fri, 28 Oct 2016 21:33:48 GMT

Hi @pcordel - Is this a working solution that solved your question? If yes, please don't forget to resolve this post by clicking "Accept" below your answer. Thanks.