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
... View more