Generate email alerts based on conditions

[hushang999]

Hello,

I'm trying to create searches for that would generate emails for the following conditions:

1 ) If the "transaction_date" field is more than three days after the "term_date" then send out an email

2 ) If the "lastlogon" date is more than one day after the "term date" then send out an email

3 ) Send out a monthly report for showing #1 and #2

So far I'm using the following search to generate the search:

"eventtype=sws_employee_terms transaction_type=Terminate"

[lguinn2]

Assuming that the date fields are stored in Linux epoch time

eventtype=sws_employee_terms transaction_type=Terminate
| where (transaction_date > term_date + (3 * 86400)) OR (lastlogon > term_date + 86400)

Set up the alert to trigger for number of results > 0 and set the trigger action to email.

Run the same search over a 30 days period to see the report. You could also start with this search and then format the report better.

If the date fields are NOT stored in Linux epoch time, use this to convert them

| eval transaction_date = strptime(transaction_date,"%Y-%m-%d %H:%M:%S")
| eval term_date = strptime(term_date,"%Y-%m-%d %H:%M:%S")
| eval lastlogon = strptime(lastlogon,"%Y-%m-%d %H:%M:%S")

Insert these lines after the first line of the original search. Edit the format strings as needed.

[martin_mueller]

For time-related maths you may want to look at relative_time(): http://docs.splunk.com/Documentation/Splunk/6.1.1/SearchReference/CommonEvalFunctions
That'd turn the where row into this:

... | where (transaction_date > relative_time(term_date, "+3d")) OR (lastlogon > relative_time(term_date, "+1d"))