/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Kace report showing custom field value in different columns

03/23/2015 2763 views
If HD_TICKET.HD_QUEUE_ID is 1 then HD_TICKET.CUSTOM_FIELD_VALUE4 means one thing but If HD_TICKET.HD_QUEUE_ID is 3 then HD_TICKET.CUSTOM_FIELD_VALUE4 means something else. I want these fields to be separate columns in my report but I don't know MySQL and therefore don't know how to code this. Here is what I have so far:

SELECT HD_TICKET.ID,  O.FULL_NAME AS OWNER_NAME, HD_CATEGORY.NAME AS CATEGORY,  HD_TICKET.CREATED, S.FULL_NAME AS SUBMITTER_NAME, HD_IMPACT.NAME AS IMPACT, HD_TICKET.DUE_DATE,  HD_TICKET.TITLE, O.USER_NAME AS OWNER_USER_NAME,  HD_PRIORITY.NAME AS PRIORITY, Q.NAME AS QUEUE_NAME, HD_TICKET.RESOLUTION, (LEFT(GROUP_CONCAT(DISTINCT COMMENT SEPARATOR '
'),255)) AS SHORT_COMMENT, HD_STATUS.NAME AS STATUS_NAME,  HD_TICKET.CUSTOM_FIELD_VALUE5, HD_TICKET.CUSTOM_FIELD_VALUE4,  HD_TICKET.CUSTOM_FIELD_VALUE3, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_TICKET.CUSTOM_FIELD_VALUE1  FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN MACHINE ON (MACHINE.ID = HD_TICKET.MACHINE_ID) JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER AP ON (AP.ID = HD_TICKET.APPROVER_ID) LEFT JOIN HD_WORK ON (HD_WORK.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN ASSET ON (ASSET.ID = HD_TICKET.ASSET_ID) LEFT JOIN USER ON (USER.ID = ASSET.OWNER_ID) LEFT JOIN ASSET_TYPE ON (ASSET_TYPE.ID = ASSET.ASSET_TYPE_ID) WHERE (HD_TICKET.HD_QUEUE_ID != 2) AND ((HD_STATUS.NAME not like '%closed%'))  GROUP BY HD_TICKET.ID ORDER BY ID
2 Comments   [ + ] Show comments

Comments

  • could you provide us with a screenshot. If QUEUE_ID = 1 there would be a record in the HD_TICKET table and if QUEUE_ID = 3 there would be another record so you should see separate columns.
  • could you provide us with a screenshot. If QUEUE_ID = 1 there would be a record in the HD_TICKET table and if QUEUE_ID = 3 there would be another record so you should see separate columns.

All Answers

0
You can use a case statement to accomplish this:
SELECT ID, HD_QUEUE_ID, CUSTOM_FIELD_VALUE4, 
CASE 
    WHEN HD_QUEUE_ID = 1 THEN CUSTOM_FIELD_VALUE4
END AS "Value From Queue 1",
CASE 
    WHEN HD_QUEUE_ID = 3 THEN CUSTOM_FIELD_VALUE4
END AS "Value from Queue 3"
FROM ORG1.HD_TICKET WHERE HD_QUEUE_ID in (1, 3)

Note that I am only selecting tickets from queues 1 and 3 instead of excluding queue 2, otherwise if you create a fourth queue in the future this query will break. 


Answered 03/24/2015 by: chucksteel
Red Belt

  • Hi chucksteel, i think this isn´t correct, cause you select the same value (CUSTOM_FIELD_VALUE4) in both cases, but you name the column "Value From Queue 1" or "Value From Queue 3", but this is only the column name, and if the column name is set, it can´t be changed anymore.
    • The request was to get values from custom_field_value4 from queues 1 and 3. The complication is those queues contain different data in that custom field so you need to treat it differently depending from which queue the data is coming.
      • Sorry it was my fault. thought it was one case with 2 when conditions.

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