/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How do i add submitter email to a report.....

09/15/2020 78 views

see my query below, i added the line highlighted to try to get the submitter email in my report but i continue to get errors, what am i missing ? help please




select HD_TICKET.ID, 

       HD_TICKET.TITLE ,

DATE_FORMAT(HD_TICKET.CREATED, '%m-%d-%y %H:%i') as TIME_OPENED,       

DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d-%y %H:%i') as TIME_CLOSED,

        DATEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED) as DAYS_OPEN,

        HD_TICKET.CUSTOM_FIELD_VALUE0 as SEVERITY,

        HD_CATEGORY.NAME as CATEGORY,

        Q.NAME AS QUEUE_NAME,

        HD_TICKET.CUSTOM_FIELD_VALUE14 as DIRECT_CONTACT,

        HD_TICKET.CUSTOM_FIELD_VALUE3 as RESOLVED_BY,

        HD_TICKET.CUSTOM_FIELD_VALUE9 as DEVICE,

HD_TICKET.CUSTOM_FIELD_VALUE5 as LOCATION,

        HD_ TICKET.SUBMITTER_EMAIL as EMAIL,

        HD_STATUS.NAME as STATUS, 

                   USER.LOCATION_ID,

        (select LDAP_UID from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,

(Select LDAP_UID from USER where HD_TICKET.OWNER_ID = USER.ID) as OWNER_NAME,

 ABS(DATEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED)) + 1

     - ABS(DATEDIFF(ADDDATE(HD_TICKET.TIME_CLOSED, INTERVAL 1 - DAYOFWEEK(HD_TICKET.TIME_CLOSED)DAY),

                    ADDDATE(HD_TICKET.CREATED, INTERVAL 1 - DAYOFWEEK(HD_TICKET.CREATED) DAY))) / 7 * 2

     - (DAYOFWEEK(IF(HD_TICKET.CREATED < HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED)) = 1)

     - (DAYOFWEEK(IF(HD_TICKET.CREATED > HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED)) = 7) - 1 AS DAYS_OPEN_NO_WKND

from HD_TICKET

left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID

JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID

left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID

left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID

left join USER on HD_TICKET.SUBMITTER_ID = USER.ID

WHERE MONTH(HD_TICKET.CREATED) > 5 and YEAR(HD_TICKET.CREATED) = 2020

order by OWNER_NAME

1 Comment   [ + ] Show comment

Comments

  • HD_ TICKET.SUBMITTER_EMAIL as EMAIL,

    It doesnt show highlighted... but this is what i added and causes an error

All Answers

0

The HD_TICKET table doesn't contain a column for SUBMITTER_EMAIL. You want to select the EMAIL column from the USER table. In this particular query, the USER table is being joined with this statement:

left join USER on HD_TICKET.SUBMITTER_ID = USER.ID

This means that you can select columns referencing the submitter like this:

USER.EMAIL

Personally, I prefer to join to the user table and use an alias to make it more meaningful, like this:

left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

This makes the select clause of the query more clear, because the columns are then selected like this:

SUBMITTER.EMAIL

I generally avoid sub-select statements, where possible and prefer using a join. Again, I find it to be clearer. In the case of your query, that means I would eliminate these:

(select LDAP_UID from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
(Select LDAP_UID from USER where HD_TICKET.OWNER_ID = USER.ID) as OWNER_NAME,

We already have a join to the USER table for the submitter, so there's no need for the sub-select statement anyway. Let's add a join to the USER table referencing the owner.

left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

The final result is this query:

 SUBMITTER.LOCATION_ID,
 SUBMITTER.LDAP_UID as SUBMITTER_NAME,
OWNER.LDAP_UID as OWNER_NAME,
 ABS(DATEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED)) + 1
     - ABS(DATEDIFF(ADDDATE(HD_TICKET.TIME_CLOSED, INTERVAL 1 - DAYOFWEEK(HD_TICKET.TIME_CLOSED)DAY),
                    ADDDATE(HD_TICKET.CREATED, INTERVAL 1 - DAYOFWEEK(HD_TICKET.CREATED) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(HD_TICKET.CREATED < HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED)) = 1)
     - (DAYOFWEEK(IF(HD_TICKET.CREATED > HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED)) = 7) - 1 AS DAYS_OPEN_NO_WKND
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
WHERE MONTH(HD_TICKET.CREATED) > 5 and YEAR(HD_TICKET.CREATED) = 2020
order by OWNER_NAME




Answered 09/16/2020 by: chucksteel
Red Belt

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