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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...