Hello!
We have a requierment to create an alert for one of the cloud application data. The following fields are like account name, account id etc should be sent to the repective RemediationContactEmail id. we are able to create an alert with all the above with csv attachment by using command sendemail However we observed that for particular set of results , if the recipients are same , in that case they will be receiving email for each results.
For example We tried below sample query to make some sample event sets using makeresults :
| makeresults
| eval id="12345"
| eval Account_ID=1234567
| eval Remediation_Contact_Email="abc123@xyz.com"
| append
[| makeresults
| eval id="67890"
| eval Account_ID=4567895
| eval Remediation_Contact_Email="abc123@xyz.com" ]
| append
[| makeresults
| eval id="13579"
| eval Account_ID=6785432
| eval Remediation_Contact_Email="abc123@xyz.com" ]
| map
[ makeresults
| eval id="$id$"
| eval Account_ID=$Account_ID$
| eval Remediation_Contact_Email="$Remediation_Contact_Email$"
| fields - _time
| sendemail to=$Remediation_Contact_Email$ subject="Test Sendemail" message="
Hello,
There is an alert for your account
id : $id$
account id : $Account_ID$
Regards,
xyz Security Operation Team" maxinputs=10000 sendcsv=true inline=true format=csv priority=1 ]
Here the recipient "abc123@xyz.com" received 3 different emails for each result with attachments as shown in the bellow screenshot.
Any help or guidance will be much appreciated here to group all the relevant results in data set with respect to remidiation contact email id and send their results in single attachment. We tried to group it using stats command however the attachment doesn’t look good as it will have a single row with all results for that particular email.we have more number of RemediationContactEmail id for each Account group in data set so if there are any 10 alerts triggered for one respective RemediationContactEmail id all the 10 alerts should be consolidated and grouped from data set then send it to that particullar recepient as one attachment rather than sending 10 different emails
This is as good as it gets:
index=index sourcetype=sourcetype status="open" reason="NEW_ALERT" u_account_contact_email!="" Translated_Severity IN (4,5) NOT "resource.data.tags{}.value"="exception"
| dedup id
| eval _time=round(('lastSeen'/1000),0)
| eval limit=round(relative_time(now(),"-13h@h"),0)
| where _time>limit
| eval remediator=rtrim(ltrim(lower('u_account_contact_email')))
| eval account="Account ID " + 'resource.accountId'
| eval policy="Policy " + 'policy.name'
| eval resource="resource(s) " + 'resource.name'
| eval target=mvzip('account','resource'," ")
| eval recommendation=if('policy.recommendation'=="" or isnull('policy.recommendation'),"No Recommendations Available",'policy.recommendation')
| stats values(target) as target by remediator policy recommendation
| eval target=mvjoin('target',"
")
| eval remediation=mvzip('recommendation','target',"
___________________________________________________
")
| eval violation=mvzip('policy','remediation',"
___________________________________________________
")
| stats values(violation) as violation by remediator
| eval violation=mvjoin('violation',"
#############################################################################
")
| table violation remediator
| map
[| makeresults
| eval violation=$violation$
| eval remediator=$remediator$
| table violation remediator
| sendemail to=$remediator$ from="infosec@company.com" subject="New Alerts for your Cloud Environments" content_type=html format=table inline=true sendresults=true ]
try this app, I think it does all the things necessary, the sendemail+map has display limitations that can't really be overcome
https://splunkbase.splunk.com/app/1794/#/details
@marycordova Thank you! This app have been tested already. Using this we are unable to add any csv or pdf attachment to email also the email body is not in a proper format.
Hi @Jithu1717
The following should suit your use case (if you don;t mind the id and account id being on the same line in email body) ...
| makeresults
| eval id="12345"
| eval Account_ID=1234567
| eval Remediation_Contact_Email="abc123@xyz.com"
| append
[| makeresults
| eval id="67890"
| eval Account_ID=4567895
| eval Remediation_Contact_Email="abc123@xyz.com" ]
| append
[| makeresults
| eval id="13579"
| eval Account_ID=6785432
| eval Remediation_Contact_Email="abc123@xyz.com" ]
| eval msg="id: " . id . " account id: " . Account_ID . "|"
| stats values(msg) AS msg BY Remediation_Contact_Email
| map
[ makeresults
| eval msg=ltrim(split($msg$, "|"))
| eval Remediation_Contact_Email="$Remediation_Contact_Email$"
| fields - _time
| sendemail to=$Remediation_Contact_Email$ subject="Test Sendemail" message="
Hello,
There is an alert for your account(s)
$msg$
Regards,
xyz Security Operation Team" maxinputs=10000 sendcsv=true inline=true format=csv priority=1
]
Hope this helps. If it solves your issue then please mark the post as solved.
@yeahnah Thanks very much for your quick responce!
Actully its adding all the values into a single row, we have more number values to be added while sending attachment to the owners so giving all the values in single row is bit difficult for them to understand
we are expecting the output something like this in attachment :
id | AccountName | AccountID | AccountGroup | Policy | Policyseverity | SLA Status | Account Type | Remediation_Contact_Email |
123 | prod | 1234 | Application | S3 - Not encrypted | 5 | NA | abc123@xyz.com | |
778 | qa | 5678 | Security | VPN Voilation | 5 | NA | abc123@xyz.com | |
889 | test | 9876 | cloud | EC2 out of memory | 5 | NA | abc123@xyz.com | |
243 | dev | 54321 | All | 5 | Within SLA | Non-Prod | abc123@xyz.com |
Hi @Jithu1717
I must admit to being a bit confused as to what you're actually asking for. If it is just to split the email body content into multi-line output per id, then something like the following will do it (adjust as per your fields etc)
| makeresults
| eval id="12345"
| eval Account_ID=1234567
| eval Remediation_Contact_Email="abc123@xyz.com"
| append
[| makeresults
| eval id="67890"
| eval Account_ID=4567895
| eval Remediation_Contact_Email="abc123@xyz.com" ]
| append
[| makeresults
| eval id="13579"
| eval ,Account_ID=6785432
| eval Remediation_Contact_Email="abc123@xyz.com" ]
| eval msg="id: " . id . ",account id: " . Account_ID . ",next_field: ... , ... , |"
| stats values(msg) AS msg BY Remediation_Contact_Email
| map
[ makeresults
| eval msg=ltrim(split($msg$, "|"))
| mvexpand msg
| eval msg=split(msg, ",")
| eval Remediation_Contact_Email="$Remediation_Contact_Email$"
| stats list(msg) BY Remediation_Contact_Email
| sendmail ... ]
Or is it to do with the attachment that is send with the email?