topic Re: How to write query for creating alert using lookup in Splunk Search

How to write query for creating alert using lookup

Arpmjdr — Wed, 04 Sep 2019 13:49:03 GMT

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

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

Re: How to write query for creating alert using lookup

richgalloway — Wed, 04 Sep 2019 19:19:17 GMT

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

Re: How to write query for creating alert using lookup

DalJeanis — Thu, 05 Sep 2019 15:20:51 GMT

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

Re: How to write query for creating alert using lookup

richgalloway — Thu, 05 Sep 2019 17:03:59 GMT

By concatenate did you mean coalesce?

Re: How to write query for creating alert using lookup

Arpmjdr — Fri, 06 Sep 2019 05:09:34 GMT

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

Re: How to write query for creating alert using lookup

richgalloway — Fri, 06 Sep 2019 12:04:31 GMT

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