Splunk Dev

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

dkorlat
Explorer

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$.

"

Tags (1)
0 Karma

dkorlat
Explorer

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$.

\"
"

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...