Daily Reminder Email

I used and modified a script found here (I believe) for more than a year and just noticed some strange behavior.  Perhaps someone can figure it out?

I had a few open tickets from 2021 and I continue to get emails for them even though the tickets were CLOSED in the last month (2022).   I suspect it has something to do with calculating YEAR and blowing up other parts of the SQL query but I haven't figured out the cause or the fix.  Has worked great up till now. 

Any ideas?


select HD_TICKET.*,

                        HD_STATUS.NAME AS STATUS_NAME,

                        HD_STATUS.ORDINAL as STATUS_ORDINAL,

                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,

                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,

                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,


                        if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,

                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,

                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,

                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,

                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,

                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,

                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,

                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,

                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,

                        case upper(STATE)

                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)

                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)

                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,

                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,

                        U1.FULL_NAME as OWNER_FULLNAME,

                        U1.EMAIL as OWNER_EMAIL,

                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,

                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,

                        U2.FULL_NAME as SUBMITTER_FULLNAME,

                        U2.EMAIL as SUBMITTER_EMAIL,

                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,

                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,

                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,

                        Q.NAME as QUEUE_NAME

                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)

                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID

                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID

                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID

                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID

                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID

                        where HD_PRIORITY.ID = HD_PRIORITY_ID

                        and HD_STATUS.ID = HD_STATUS_ID

                        and HD_IMPACT.ID = HD_IMPACT_ID

                        and HD_CATEGORY.ID = HD_CATEGORY_ID

                        and ((((  HD_TICKET.ID != '') AND HD_STATUS.NAME != 'Closed') AND (not exists  (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME = '')) ) and HD_TICKET.HD_QUEUE_ID = 1 )

1 Comment   [ + ] Show comment
  • Just to add to the confusion, I just ran the View Query Results and I have no CLOSED tickets in the list. What could cause it to email incorrect tickets other than the query? Bug? - civilnj 1 year ago

Answers (2)

Posted by: barchetta 1 year ago
4th Degree Black Belt

It all come down to:

((((  HD_TICKET.ID != '') AND HD_STATUS.NAME != 'Closed') AND (not exists  (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME = '')) )

My head is spinning on this one. 

Posted by: civilnj 1 year ago
Orange Belt

I almost think it is a bug of some sort because the Preview/View Query Results produces the proper list.  No CLOSED items....

  • You know you could simplify it. It looks like this was originally dont in the Wizard. You could probably yank out like 12 lines depending on what your email is formulated like.

    I have NEVER gotten the preview results to work. always an error (not this script but mine).

    What is the "1" in the select statement (in the where section)? I dont get that one. When I try and run just the where sub select I get an error.

    I can run this script as is in MYSQL.

    It looks like it returns all open tickets in queue 1.

    It might run faster if you change the first line to hd.ticket.id instead of asking to return all the fields. As I said, I think there is a ton you could remove... I have scripts like this too that I should clean up.. but kace sma is on the chopping block so Im leaving it.

    EDIT: in my opinion your result has nothing to do with the date, so not sure why you suspect it is date related. AND the sort_subnitter_name for example gives me the user full name with a "a" in front of it. Weird. This must be a report? - barchetta 1 year ago
    • Since I am pretty incapable of writing these from scratch, I have always come here and tried to cobble together others' work. The reason for the date suspicion is because those seem to be the only tix doing this. Created in 2021 and closed in 2022. Probably coincidence but I really don't see many clues as the script is fairly complex as you noted. - civilnj 1 year ago
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