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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...