expiry notification use case

[avi123]

Hi All,

I have designed a splunk query:

| inputlookup Expiry_details_list.csv
| lookup SupportTeamEmails.csv Application_name OUTPUT Owner_Email_address Ops_Leads_Email_address Escalation_Contacts_Email_address
| eval Expiry_Date = strptime(Expiry_date, "%m/%d/%Y")
| eval Current_Time = now()
| eval Expiry_Date_Timestamp = strftime(Expiry_Date, "%Y/%m/%d %H:%M:%S")
| eval Days_until_expiry = round((Expiry_Date - Current_Time) / 86400, 0)
| eval alert_type = case(
Days_until_expiry <= 7, "Owner",
Days_until_expiry <= 15, "Support",
Days_until_expiry < 1, "Expired",
Days_until_expiry > 15, "Others",
true(), "None")
| search alert_type != "None"
| eval email_list = case(
alert_type == "Owner", Escalation_Contacts_Email_address,
alert_type == "Support", Ops_Leads_Email_address,
alert_type == "Expired", mvappend(Owner_Email_address, Ops_Leads_Email_address, Escalation_Contacts_Email_address),
true(), "None")
| eval email_list = split(mvjoin(email_list, ","), ",")
| eval cc_email_list = case(
alert_type == "Owner", Owner_Email_address,
alert_type == "Support", Owner_Email_address,
true(), "None")
| eval cc_email_list = split(mvjoin(cc_email_list, ","), ",")
| dedup Application_name Environment email_list
| eval email_recipient = mvdedup(email_list)
| eval email_recipient = mvjoin(email_recipient, ",")
| eval email_cc = mvdedup(cc_email_list)
| eval email_cc = mvjoin(email_cc, ",")
| table Application_name, Environment, Type, Sub_Type, Expiry_Date_Timestamp, Days_until_expiry, email_recipient, email_cc
| fields - alert_type, Owner_Email_address, Ops_Leads_Email_address, Escalation_Contacts_Email_address

Now this is returning output as provided in the attached file, what I am expecting is in email_list it should return only Escalation_contacts_email_address and in cc_email_list it should merge the email address of Owner_Email_address and Ops_Leads_Email_address seperated by a comma when the alert_type == "Expired"

How do I get this using splunk query?

[defection-io]

Hi @avi123 ,

I'm not 100% sure if I understood the requirements, but I'm giving it a shot here. Let me know if this works for you:

| inputlookup Expiry_details_list.csv 
| lookup SupportTeamEmails.csv Application_name OUTPUT Owner_Email_address Ops_Leads_Email_address Escalation_Contacts_Email_address 
| eval Expiry_Date = strptime(Expiry_date, "%m/%d/%Y") 
| eval Current_Time = now() 
| eval Expiry_Date_Timestamp = strftime(Expiry_Date, "%Y/%m/%d %H:%M:%S") 
| eval Days_until_expiry = round((Expiry_Date - Current_Time) / 86400, 0) 
| eval alert_type = case(
    Days_until_expiry <= 7, "Owner",
    Days_until_expiry <= 15, "Support",
    Days_until_expiry < 1, "Expired",
    Days_until_expiry > 15, "Others",
    true(), "None") 
| search alert_type != "None" 
| eval email_list = case(
    alert_type == "Owner", Escalation_Contacts_Email_address,
    alert_type == "Support", Ops_Leads_Email_address,
    alert_type == "Expired", Escalation_Contacts_Email_address,
    true(), "None") 
| eval cc_email_list = case(
    alert_type == "Owner", Owner_Email_address,
    alert_type == "Support", Owner_Email_address,
    alert_type == "Expired", mvappend(Owner_Email_address, Ops_Leads_Email_address),
    true(), "None") 
| eval email_list = split(mvjoin(email_list, ","), ",") 
| eval cc_email_list = split(mvjoin(cc_email_list, ","), ",") 
| dedup Application_name Environment email_list 
| eval email_recipient = mvdedup(email_list) 
| eval email_recipient = mvjoin(email_recipient, ",") 
| eval email_cc = mvdedup(cc_email_list) 
| eval email_cc = mvjoin(email_cc, ",") 
| table Application_name, Environment, Type, Sub_Type, Expiry_Date_Timestamp, Days_until_expiry, email_recipient, email_cc 
| fields - alert_type, Owner_Email_address, Ops_Leads_Email_address, Escalation_Contacts_Email_address