Email each user returned in a search and including values in the email

[Callumfranks]

I am trying to create an email notification for users which have a password expiring soon. I then want to include the number of days until it expires.

I have the below search. It returns the users which are due to expire. It then emails every user which it returns which has an email address assigned to it in AD. I cannot seem to find a way to include how many days until it expires.

| ldapsearch domain=default search="(&(objectclass=user)(!(objectclass=computer)))" attrs="displayName, whenCreated, pwdLastSet, mail"
| dedup displayName
| eval DateLastChanged=strptime(pwdLastSet, "%Y-%m-%dT%H:%M:%S.%N")
| where DateLastChanged < relative_time(now(),"-69d@d")
| table DateLastChanged, displayName, mail
| outputcsv TempEmailFile.csv
| stats values(mail) AS email, values(DateLastChanged) as DateLastChanged
| mvexpand email 
| map search="|inputcsv TempEmailFile.csv | where mail=\"$email$\" 
| fields - mail
| sendemail
   sendresults=false inline=true
   server="0.0.0.0"
   from="senderemail"
   to=\"$email$\"
   subject=\"Password Reminder\"
   message=\"Your password is due to expire in ??? Days.\""

If anyone can think of a way to make it better to make it include the days until it expires, i would be very grateful

[manjunathmeti]

Hi @Callumfranks,

You should have two days variables, one to determine when password is expired and one to determine when to start sending alerts. Example: Password will be expired after 69 days and an alert needs to be sent each day starting from 10th day before password is expired. Try this query and check if this works for you.

| ldapsearch domain=default search="(&(objectclass=user)(!(objectclass=computer)))" attrs="displayName, whenCreated, pwdLastSet, mail"
 | dedup displayName
 | eval DateLastChanged=strptime(pwdLastSet, "%Y-%m-%dT%H:%M:%S.%N")
 | eval password_age = floor((now() - DateLastChanged)/(60*60*24))
 | where password_age > (69 - 10)
 | eval message = if(password_age > 69, "Your password is expired.", "Your password is due to expire in ".(69-password_age)." Day(s).")
 | table DateLastChanged, displayName, mail, message

Instead of using map and sendemail commands, save above query as an alert and configure "Send email" alert action like below. You need set Trigger to For each result in Trigger conditions to send email per row in the result.

[Callumfranks]

Hi @manjunathmeti

Thank you for the answer. This seems to be working perfect.

I have a couple of additional questions which you might be able to assist with.

What is the (60*60*24) for in the following line:

| eval password_age = floor((now() - DateLastChanged)/(60*60*24))

Also, can you think of any nice way to email every other day? i.e 10 days left, 8 days left, 6 days left, 4 days left etc. Just to prevent the spam of emails. The only way I could think of was to create 5 different alerts and in each of those alerts just include a | where on the password_age.

Thanks
Callum