topic Re: Check lookup table for old/expired entries in Splunk Search

Check lookup table for old/expired entries

JTS911 — Tue, 03 Oct 2023 21:10:12 GMT

Hi All

I need to do some lookup table maintenance and would like to know which hosts are not being monitored but still in the lookup table

My problem is I have host fields that has an "*", I.E. host=saps* that are valid and are being monitored

Here is my SPL
-----------------------------------------------------

-----------------------------------------------------

Re: Check lookup table for old/expired entries

yuanliu — Tue, 03 Oct 2023 17:51:54 GMT

Given that some lookup entries contain wildcard, it is reasonable to assume that your lookup is defined with match_type WILDCARD(host). In the following I will make some simplifying assumptions because I do not know the significance of comparing eventTime or LastEventTime: All you want to compare is with events in your search window. If your search window is past 7 days, I assume that you want to keep entries that one or more events match in the past week, and that you want to drop any table entries with zero match during this same period. But if eventTime is important, I'm sure you can adapt the solution to meet your needs.

The key here is to utilize lookup; specifically, allow lookup to perform wildcard matches.

| metasearch (index=os_* OR index=perfmon_*) | dedup host | eval host=lower(host) ```| eval eventTime=_time | convert timeformat="%Y/%m/%d %H:%M:%S" ctime(eventTime) AS LastEventTime | fields host eventTime LastEventTime index ^^^ the above is not calculated or used ``` | lookup host_lookup output host AS matchhost | append [inputlookup host_lookup | rename host AS tablehost] | eventstats values(matchhost) as matchhost | eval Action = if(tablehost IN matchhost, "Keep Host", "Remove from Lookup") | fields Action tablehost

(Obviously you do not need to rename tablehost. It just makes the intent obvious.) Hope this helps.

Re: Check lookup table for old/expired entries

JTS911 — Thu, 05 Oct 2023 11:33:19 GMT

Thanks for your reply, I appreciate it.

I intentionally first got all <hosts> from lookup table to reduce the search footprint.
I get this error:
Error in 'lookup' command: Must specify one or more lookup fields.

Re: Check lookup table for old/expired entries

yuanliu — Thu, 05 Oct 2023 17:06:39 GMT

That was my omission. (Syntax is explained in lookup.) I assume that you are trying to match "host" field in the following. (Also, you need to control letter case in the table to all-lower case.

| metasearch (index=os_* OR index=perfmon_*) | dedup host | eval host=lower(host) ```| eval eventTime=_time | convert timeformat="%Y/%m/%d %H:%M:%S" ctime(eventTime) AS LastEventTime | fields host eventTime LastEventTime index ^^^ the above is not calculated or used ``` | lookup host_lookup host output host AS matchhost | append [inputlookup host_lookup | rename host AS tablehost] | eventstats values(matchhost) as matchhost | eval Action = if(tablehost IN matchhost, "Keep Host", "Remove from Lookup")

Re: Check lookup table for old/expired entries

JTS911 — Fri, 06 Oct 2023 10:25:22 GMT

Thank you for your reply