topic Re: How to create an alert using sendemail to send only particular set of results to repective email ID in Splunk Enterprise

How to create an alert using sendemail to send only particular set of results to repective email ID

Jithu1717 — Fri, 11 Sep 2020 00:02:58 GMT

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

Re: How to create an alert using sendemail to send only particular set of results to repective email ID

yeahnah — Fri, 11 Sep 2020 01:07:19 GMT

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

Hope this helps. If it solves your issue then please mark the post as solved.

Re: How to create an alert using sendemail to send only particular set of results to repective email ID

Jithu1717 — Mon, 14 Sep 2020 19:45:56 GMT

@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

Re: How to create an alert using sendemail to send only particular set of results to repective email ID

yeahnah — Mon, 14 Sep 2020 20:30:01 GMT

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)

Or is it to do with the attachment that is send with the email?

Re: How to create an alert using sendemail to send only particular set of results to repective email ID

marycordova — Tue, 15 Sep 2020 03:02:19 GMT

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

Re: How to create an alert using sendemail to send only particular set of results to repective email ID

Jithu1717 — Tue, 15 Sep 2020 20:30:51 GMT

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

Re: How to create an alert using sendemail to send only particular set of results to repective email ID

marycordova — Wed, 16 Sep 2020 01:26:14 GMT

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 ]