Check lookup table for old/expired entries

[JTS911]

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 
----------------------------------------------------- 

| inputlookup host_lookup
| eval host=lower(host)
| join host type=left
[| 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]

| eval Action=case(eventTime>200, "Keep Host", isnull(eventTime) , "Remove from Lookup")
| fields Action host LastEventTime 

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

[JTS911]

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.

[yuanliu · Accepted solution]

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")

[JTS911]

Thank you for your reply 

[yuanliu]

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.