Splunk Search

Check lookup table for old/expired entries

JTS911
Explorer

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 



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


Labels (2)
Tags (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

 

View solution in original post

JTS911
Explorer

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Explorer

Thank you for your reply 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

Tags (1)
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...