/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


How do i display the VALUE name instead of the value ID Kace Reporting

01/17/2020 273 views

CUSTOM_FIELD_VALUE3 as an example returns 9662 in my report

I want to see the name instead luke.smuthe


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,

        HD_CATEGORY.NAME as CATEGORY,

        Q.NAME AS QUEUE_NAME,

        HD_TICKET.CUSTOM_FIELD_VALUE13 as NO_DEVELOPMENT_NEEDED,

        HD_TICKET.CUSTOM_FIELD_VALUE3 as RESOLVED_BY,

        HD_STATUS.NAME as STATUS, 

                   (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 YEAR(HD_TICKET.CREATED) in (2020) and HD_TICKET.HD_QUEUE_ID in (23,33,34,48,50,51,52,53,54,56)

order by OWNER_NAME

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

Assuming 9663 is the user ID, you need to join to the user table like this:

LEFT JOIN USER RESOLVER on RESOLVER.ID = HD_TICKET.CUSTOM_FIELD_VALUE3

That line would go underneath the first join to the USER table.

You can then select user attributes with RESOLVER.FULL_NAME, RESOLVER.EMAIL, etc. 

The updated query looks like this:

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,
        HD_CATEGORY.NAME as CATEGORY,
        Q.NAME AS QUEUE_NAME,
        HD_TICKET.CUSTOM_FIELD_VALUE13 as NO_DEVELOPMENT_NEEDED,
        -- HD_TICKET.CUSTOM_FIELD_VALUE3 as RESOLVED_BY,
        RESOLVER.FULL_NAME as 'Resolved By',
        HD_STATUS.NAME as STATUS, 
                   (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
LEFT JOIN USER RESOLVER on RESOLVER.ID = HD_TICKET.CUSTOM_FIELD_VALUE3
WHERE YEAR(HD_TICKET.CREATED) in (2020) and HD_TICKET.HD_QUEUE_ID in (23,33,34,48,50,51,52,53,54,56)
order by OWNER_NAME

I see that you are using sub-select statements to get user information, which works, but I find joins to be clearer. Generally I create a join to the user table that is aliased to each type of user I want to select attributes, like this:

LEFT JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID


Selecting attributes looks cleaner, at least in my opinion.

SUBMITTER.LDAP_UID as 'Submitter UID',
OWNER.LDAP_UID as 'Owner UID',

etc.



Answered 01/17/2020 by: chucksteel
Red Belt

  • Worked perfectly, Thanks Chucksteel

Don't be a Stranger!

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

Sign up! or login

View more:

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