Splunk Search

Using lookups, determine if search is null.

OldManEd
Builder

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.

0 Karma
1 Solution

OldManEd
Builder

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?

View solution in original post

0 Karma

cchimento
Path Finder

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#an...

0 Karma

OldManEd
Builder

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?

0 Karma

OldManEd
Builder

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.

0 Karma

OldManEd
Builder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)
---
If this reply helps you, Karma would be appreciated.
0 Karma

OldManEd
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Want a chance to win $500 to the Splunk shop? Take our IT Incident Management Survey!

  Top Trends & Best Practices in Incident ManagementSplunk is partnering up with Constellation Research to ...