Splunk Enterprise

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

Jithu1717
Loves-to-Learn Lots

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.

send email.PNG

 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

Labels (1)
Tags (1)
0 Karma

marycordova
SplunkTrust
SplunkTrust

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 ]
@marycordova
0 Karma

marycordova
SplunkTrust
SplunkTrust

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

Jithu1717
Loves-to-Learn Lots

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

0 Karma

yeahnah
Communicator

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.

0 Karma

Jithu1717
Loves-to-Learn Lots

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

idAccountNameAccountIDAccountGroupPolicyPolicyseveritySLA StatusAccount TypeRemediation_Contact_Email
123prod1234ApplicationS3 - Not encrypted5NA abc123@xyz.com
778qa5678SecurityVPN Voilation5NA abc123@xyz.com
889test9876cloudEC2 out of memory 5NA abc123@xyz.com
243dev54321All 5Within SLANon-Prodabc123@xyz.com

 

 

0 Karma

yeahnah
Communicator

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?

0 Karma