How to write query for creating alert using lookup

[Arpmjdr]

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

[DalJeanis · Accepted solution]

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]

By concatenate did you mean coalesce?

[Arpmjdr]

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

[richgalloway]

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

[richgalloway]

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