Splunk Search

How to write query for creating alert using lookup

Arpmjdr
Explorer

Hi Splunkers,

I have the events getting ingested as below:

timestamp patch_version

hostname

Now,I want to create one lookup csv named 'PatchDate' which contains columns with values

Host,MaxAge
default,30

Now,I want to implement two logic:

1.For each event received generate the MAXAGE value to be used.

            IF  <hostname> == Host ]
            THEN
                Use the  MaxAge value.
            ELSE
                Use the MaxAge value for ( Host == “default” )
            END-IF
  1. Calculate the DAYSSINCECHANGE for the   Generate current TimeStamp  => (need to write a rex command as field is not extracted)                                                                     
    Calculate Difference between and for event  => DIFFERENCE                  IF DIFFERENCE > 30 THEN It will throw alert.

Kindly help me to build the query.
TIA

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Something like this

Your search that returns a record with these fields 
.... do whatever you need to calculate the timestamp here ... 
| table  _time patch_version hostname
| lookup PatchDate.csv Host as hostname OUTPUT MaxAge
| eval MaxAge=concatenate(MaxAge,30)
| eval DaysSinceChange=round((now()-_time)/86400,0) 
| where DaysSinceChange >= MaxAge

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

Something like this

Your search that returns a record with these fields 
.... do whatever you need to calculate the timestamp here ... 
| table  _time patch_version hostname
| lookup PatchDate.csv Host as hostname OUTPUT MaxAge
| eval MaxAge=concatenate(MaxAge,30)
| eval DaysSinceChange=round((now()-_time)/86400,0) 
| where DaysSinceChange >= MaxAge

richgalloway
SplunkTrust
SplunkTrust

By concatenate did you mean coalesce?

---
If this reply helps you, Karma would be appreciated.
0 Karma

Arpmjdr
Explorer

modified a little but it has served my purpose. btw, I had to use "coalesce". Thanks to both of you @richgalloway and @DalJeanis 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, please accept the answer to help future readers.

---
If this reply helps you, Karma would be appreciated.

richgalloway
SplunkTrust
SplunkTrust

This sounds a lot like a Fiverr task.
We need some example data to determine how to extract the current TimeStamp field.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...