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