topic Re: Using lookups, determine if search is null. in Splunk Search

Using lookups, determine if search is null.

OldManEd — Wed, 08 May 2019 18:15:16 GMT

I have a lookup table where the columns are formatted as follows:

Location, Vendor, dns_name, host-ip, host-short-name

My search is here:

index=<undex name>

| search [| inputlookup device-list 
          | search Vendor=<Some Vendor Name> 
          | fields host-ip
          | rename host-ip AS dvc
          | format] 

| lookup device-list host-ip AS dvc 

| stats latest(_time) AS last_seen by Vendor Location short-name dvc
| eval time_since = ((now() - last_seen) / 60)
| eval clean_since = round(time_since, 0)

Everything works fine when there are records returned from the "| lookup device-list host-ip AS dvc" section. What I need is a way to determine if no events were returned. If no records were returned, I would like to create a table entry with the data from the lookup table and add some default variable values. I have not been able to figure out a way to accomplish that. My biggest problem is how to determine if no records were returned from the search and keep the lookuptable data.

Re: Using lookups, determine if search is null.

richgalloway — Wed, 08 May 2019 20:14:13 GMT

Try coalesce. It checks if the first argument is null and, if so, applies the second argument.

index=<undex name>
| search [| inputlookup device-list 
           | search Vendor=<Some Vendor Name> 
           | fields host-ip
           | rename host-ip AS dvc
           | format] 
| lookup device-list host-ip AS dvc 
| eval Location=coalesce(Location, "default Location"), Vendor=coalesce(Vendor, "default Vendor"), dns_name=coalesce(dns_name, "default DNS name"), host-short-name=coalesce(host-short-name, "Default short name")
| stats latest(_time) AS last_seen by Vendor Location short-name dvc
| eval time_since = ((now() - last_seen) / 60)
| eval clean_since = round(time_since, 0)

Re: Using lookups, determine if search is null.

OldManEd — Thu, 09 May 2019 13:13:12 GMT

I tried it but it didn't work. The results only display for those records that are returned from the "| lookup device-list host-ip AS dvc" search. If there is no match for host-ip/dvc, nothing is displayed. I need to display some data for ALL host-ip entries.

Re: Using lookups, determine if search is null.

OldManEd — Thu, 09 May 2019 18:50:23 GMT

I didn't realize that my question was so difficult. I will add some additional input here to try and clarify my issue. Lets say I have my lookup table and it looks something like this:

Location, Vendor, dns_name, host-ip, host-short-name
Location-A, Vendor-Bob, hostA.networkA.com, 1.1.1.1, hostA
Location-B, Vendor-Bob, hostB.networkB.com, 2.2.2.2, hostB
Location-C, Vendor-Bob, hostC.networkC.com, 3.3.3.3, hostC

Now, in the code above, I use the host-ip from the lookup table to search an index for records based on the dvc field. As long as there is a match, I get output in a table. In the example below I match on 1.1.1.1 and 3.3.3.3, but not 2.2.2.2. My output table looks like this;

Line added to the code above:

| table Vendor Location short-name dvc clean_since

OUTPUT:
Vendor-Bob    Location-A    hostA    1.1.1.1     4567
Vendor-Bob    Location-C    hostC    3.3.3.3       789

What I need is the output table to include all the host-ips in the lookup table even is there is no match. The "clean_since" field will default to some default value. It should look something like this:

 OUTPUT:
 Vendor-Bob    Location-A    hostA    1.1.1.1     4567
Vendor-Bob     Location-B    hostB    2.2.2.2     10000
 Vendor-Bob    Location-C    hostC    3.3.3.3      789

I hope this clears up some things. I really need some kind of solution soon.

Re: Using lookups, determine if search is null.

OldManEd — Thu, 13 Jun 2019 16:39:55 GMT

OK, no answer yet. Perhaps another way of asking this question is, can I print out the lookup table data, even if the search returns no events?

Re: Using lookups, determine if search is null.

OldManEd — Thu, 20 Jun 2019 19:44:40 GMT

Well, I got it. For those of you that are interested, I rewrote the search using a join. This one was interesting to say the least.

| inputlookup <lookup name>
| search Vendor="<Specific name of Vendor"
| fields dns-name, Location, Vendor, short-name
| rename dns-name AS host
| sort host
| eval lastTime=0

| join type=left host [  | metadata type=hosts
                                        | lookup <lookup name> dns-name AS host ]

| eval secondsAgo = now() - lastTime
| eval durationStr=if(secondsAgo>1560807101,"No Events Ever Seen", tostring(secondsAgo, "duration"))

| rename short-name AS "Server Name",
                  durationStr AS "Time Since Last Event",
                               host AS "DNS Name"

| table "Vendor" "Location" "Server Name" "DNS Name" "Time Since Last Event"

Now, when I run the search I get the following output:

Vendor      Location    Server Name    DNS Name    Time Since Last Event Seen
VendorA    Denver      <hostname>    <dnsname>    01:56:29
VendorA    Chicago     <hostname>    <dnsname>    No Events Ever Seen

Note: The 1560807101 value for the durationStr calculation was just something I pulled out of the air that was large enough to not cause issues. I think I used the difference between when I wrote this and "12/31/1969 at 17:00:00".

Life is good.

Re: Using lookups, determine if search is null.

cchimento — Tue, 05 May 2020 17:09:09 GMT

Hi - if you're still looking, this may be what you're after. I had a similar question a few years ago.

https://answers.splunk.com/answers/331715/how-do-i-get-an-automatic-lookup-to-populate-a-tab.html#answer-331720