/build/static/layout/Breadcrumb_cap_w.png

Could someone help me eliminate unnecessary SQL lines from wizard generated result?

Using the ticket rule wizard, I created a rule to have an email sent to me when a user creates a new ticket. While I'm getting data sent to me successfully, it's more than what I need and not human readable. I'm looking to only capture the: Id, Title, Summary, Created, Status, and Name of Person, which I can read easily.

The following is the SQL that the wizard produced. Could someone help me eliminate the lines that aren't necessary to accomplish what I outlined above?

Thank you,

Scott

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, STATE, 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() - unix_timestamp(HD_TICKET.TIME_OPENED) else unix_timestamp() - 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(HD_TICKET.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_STATUS.NAME like '%new%') and HD_TICKET.HD_QUEUE_ID = 9 )

0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: smaccallum 2 years ago
Senior White Belt
1

IgorAngelini,


This is excellent!


Thank you,


Scott
Posted by: IgorAngelini 2 years ago
Second Degree Blue Belt
0

Top Answer

Try:

SELECT HD_TICKET.ID AS ID,
       HD_TICKET.TITLE AS Title,
       HD_TICKET.SUMMARY AS Summary,
       HD_TICKET.CREATED AS Created,
       HD_STATUS.NAME AS Status,
       USER.FULL_NAME AS User
       
FROM HD_TICKET
       
LEFT JOIN USER
    ON HD_TICKET.SUBMITTER_ID = USER.ID
    
LEFT JOIN HD_STATUS
    ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID


WHERE HD_STATUS.NAME like '%new%'
and HD_TICKET.HD_QUEUE_ID = 9
Posted by: smaccallum 2 years ago
Senior White Belt
0

IgorAngelini,

The one issue that I have left to resolve is for the email to be only be sent when the ticket is created and not when the ticket is manipulated in anyway. How should the SQL be modified for this?

Thank you,


Scott


Comments:
  • I think adding an "AND HD_TICKET.CREATED > NOW() - INTERVAL 2 MINUTE" to the end should do it, change the time as you wish, I think 2 min should more than enough to send the email but not too much to send a duplicate when someone modifies it, give it a try and mess about with the timing. - IgorAngelini 2 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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