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.
Hi - if you're still looking, this may be what you're after. I had a similar question a few years ago.
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.
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, 18.104.22.168, hostA Location-B, Vendor-Bob, hostB.networkB.com, 22.214.171.124, hostB Location-C, Vendor-Bob, hostC.networkC.com, 126.96.36.199, 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 188.8.131.52 and 184.108.40.206, but not 220.127.116.11. 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 18.104.22.168 4567 Vendor-Bob Location-C hostC 22.214.171.124 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 126.96.36.199 4567 Vendor-Bob Location-B hostB 188.8.131.52 10000 Vendor-Bob Location-C hostC 184.108.40.206 789
I hope this clears up some things. I really need some kind of solution soon.
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)
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.