Alert using calendar arithmetic

[Branden]

Hi. We a Dashboard that informs us of the date a service account password was changed/reset. Passwords need to be reset no later than every 700 days, but we'd like Splunk to alert us 6 months prior to that deadline.

Our date field looks like this:

  2019-11-15T15:27:42.416732Z

From that date, we need to alert when that account password is 520 days old (700 days minus 6 months/180 days). So in the above example, we need to receive an alert on April 18, 2021, to remind us to schedule a password reset for that account.

We are getting the account name date of the last password set from Active Directory, in case that matters.

In other words, is there a way to do "Calendar arithmetic" in Splunk? I have no idea where to even begin with this.

Thank you!

[richgalloway]

Timestamp math is easy. The trick is to convert the timestamp into integer (epoch) form first.

... | eval epoch=strptime(accountPassword, "%Y-%m-%dT%H:%M:%S.%5N%Z")
| eval alertEpoch=epoch + (86400 * 520)
| eval alertTime = strftime(alertEpoch, "%Y-%m-%dT%H:%M:%S.%5N%Z")

[Branden]

I can actually calculate the epoch time six months in the future from the date of the password set:

  | eval pwDate=strptime(pwdLastSet,"%Y-%m-%dT%H:%M:%S.%3N") | eval sixMonthsAhead=relative_time(pwDate, "+520d@d") | table cn, pwdLastSet, sixMonthsAhead

But I can't seem to get an alert to trigger once that date arrives...