How to loop through all the results and send an email to each user

[dkorlat]

Hi,

I'm unable to figure out how to loop through for each row result and send an email.

The first row works correct and sends the user an email that their account is expiring within 30 days, with the below Splunk search.

I've performed a search on Google and found I need to use the map command, but I'm unable to make the below search work with the map command in different areas of the below code.

To recap I'm downloading all the Active Directory users to a lookup table every night with their account details. I now have the lookup table and want to email users when their account is 30 days from expiring. I can only get the first result from my search to work and unable to perform the same function on each row.

| inputlookup ActiveDirectory_Users.csv
| eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%S%Z") | where AccountExpiryEpoch < relative_time(now(),"+30d@d") AND AccountExpiryEpoch >=now()
| eval AccountExpiresTime=strftime(AccountExpiryEpoch ,"%A, %e %B %Y") | eval AccountExpiresDate=strftime(AccountExpiryEpoch ,"%d/%m/%Y %I:%M:%S %p")
| eval UppergivenName = upper(substr(givenName,1,1)).lower(substr(givenName,2)) | eval Uppersn = upper(substr(sn,1,1)).lower(substr(sn,2))
| table AccountExpiresDate, AccountExpiresTime, UppergivenName, Uppersn, mail
| sendemail from="helpdeskemail" to="$result.mail$" bcc=helpdeskemail subject="Account Expiring" message="

Dear $result.UppergivenName$ $result.Uppersn$,

Your account ($result.UppergivenName$ $result.Uppersn$) is due to expire on $result.AccountExpiresTime$.

"

[dkorlat]

I've tried many different combinations and got it working with the map search command.

When I used the map command with sendemail, the variables changed from $result.field$ to $field$, to use the field as a variable. I also had to escape the quotation marks within the map command.

Here is my search query that worked for anyone else facing the same issue.

| inputlookup ActiveDirectory_Users.csv
| eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%S%Z") | where AccountExpiryEpoch < relative_time(now(),"+30d@d") AND AccountExpiryEpoch >=now()
| eval AccountExpiresTime=strftime(AccountExpiryEpoch ,"%A, %e %B %Y") | eval AccountExpiresDate=strftime(AccountExpiryEpoch ,"%d/%m/%Y %I:%M:%S %p")
| eval UppergivenName = upper(substr(givenName,1,1)).lower(substr(givenName,2)) | eval Uppersn = upper(substr(sn,1,1)).lower(substr(sn,2))
| map search="
| sendemail from=\"helpdeskemail\" to=\"$mail$\" bcc=helpdeskemail subject=\"Account Expiring\" message="

Dear $UppergivenName$ $Uppersn$,

Your account ($UppergivenName$ $Uppersn$) is due to expire on $AccountExpiresTime$.

\"
"