/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Need Advanced MySQL query for Custom Rule "where email is like a list"

03/16/2018 680 views

I am trying to put together a custom rule that will allow me to create a list of "Do Not Email" addresses so I can accept new ticket from these addresses, but never send them responses.  These addresses are my AV, Backup, Monitoring services, etc.  Many services come with the noreply@company.com address that gets ignored, but these services send an email to you that strips the subject so I know I will end up getting a loop.

To help with this, I am creating a custom asset type called "Do Not Email" where it is just a list of usernames and email addresses.  I am then comparing the email address on a new ticket to the list of names in the ASSET_DATA_10201 to see if there is a match:

SELECT
  HD_TICKET.ID,
  HD_TICKET.TITLE,
  USER.ID AS ID1,
  USER.USER_NAME,
  USER.EMAIL
FROM
  HD_TICKET
  INNER JOIN USER ON USER.ID = HD_TICKET.SUBMITTER_ID
WHERE
  USER.EMAIL IN (SELECT
      ASSET_DATA_10201.FIELD_10002
    FROM
      ASSET_DATA_10201)

This works great for exact matches, but I also want to expand this for @domain.com matches.  I want to put @company-xyz.com in my asset list so that if the senders email comes from bob@company-xyz.com I also get a match, but I am not sure how to accomplish this in the query.

0 Comments   [ + ] Show comments

Comments


All Answers

0
I want to put @company-xyz.com in my asset list
Probably the wrong approach. Instead, use SQL wildcard(s) in your query.
Answered 03/20/2018 by: VBScab
Red Belt

  • But I do not want to have the team editing the query every time they need to add a domain.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ