ITNinja

...one more easy one. I want to include custom field 14 (renamed to Time Spent) in the report, what is the syntax?

Transam,

This is pretty easy.

You're going to want to join in HD_TICKET_CHANGE and filter where DESCRIPTION LIKE '%TICKET CREATED%'.

The user id is the person who opened the ticket.

Give the SQL a shot, and well help with the rest.

OK, with only a few minutes of time I tried to just add Custom Field 14 to my query. Seemed simple, right? From looking at other posts I thought the syntax would simply be to add HD_TICKET.CUSTOM_FIELD_VALUE14, but no. Sorry to be dense. Also, what is the tool to look at the database? I might just have to roll up my sleeves on the other one.

CUSTOM_FIELD_VALUE13 is CUSTOM_14 -- for this field the numbering starts at 0 in the db.

Like Rip Van Winkel, slept on this for 5 months.
So I got the HD_TICKET_CHANGE.USER_ID to return a name by stealing some other code and using your instructions, but i don't trust it. In some instances it returns a User rather than an Admin. Users would not be the on changing a ticket after it is opened, only Admins would do that. so i think I have something wrong...would you mind reviewing the attached code?

The line in question is the last subselect before the FROM. I tried OWNER and SUBMITTER but apparently they are not in the CHANGE table, and I do not have access to the database to see what is there.
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_OPENED, '%m-%d %H:%i') as TIME_OPENED,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
(TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) AS MIN_TO_CLOSE,
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < 120,'Yes','No') AS 2_HOUR_SLA,
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < 1440,'Yes','No') AS 24_HOUR_SLA,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_TICKET.RESOLUTION as RESOLUTION,
HD_TICKET.CUSTOM_FIELD_VALUE13 AS TIME_SPENT,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
(select FULL_NAME from USER where HD_TICKET_CHANGE.USER_ID = USER.ID) as CHANGE1_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.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 HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
left join HD_TICKET_CHANGE on HD_TICKET.ID = HD_TICKET_CHANGE.ID
where DESCRIPTION LIKE '%TICKET CREATED%' and HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL


Seems to be a problem with the join as well. Untill I added this Join there were nearly 300 records, after there are 72.

I don't see any joins on the users.

The query here kind of demonstrates joining user three times -- user, submitter and updater:
http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=613&artlang=en

Here's an excerpt:
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

OK. I took a stab, it was in the query, with left join HD_TICKET_CHANGE on HD_TICKET.ID = HD_TICKET_CHANGE.ID ...close :)

What I am after is the first Service Desk person to touch the ticket. There are 2 scenaios: 1) a phoned in ticket where the Service Desk Staff creates the ticket manually; 2) an emailed in ticket where the ticket is created automatically. In both cases a default user is assigned, but they are different. What I think I want is the first Service Desk Staff that updates the ticket, not our Default "Service Desk" Owner for emails or our Default "Service Admin" for phone calls. I am also guessing I want this to come from the Updater table? Any help on the query?

No go. Still only get 60 some odd records and the Updated name matches the Change name, which the name is not possible. No way a user could change a ticket. In case you care...

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_OPENED, '%m-%d %H:%i') as TIME_OPENED,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
(TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) AS MIN_TO_CLOSE,
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < 120,'Yes','No') AS 2_HOUR_SLA,
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < 1440,'Yes','No') AS 24_HOUR_SLA,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_TICKET.RESOLUTION as RESOLUTION,
HD_TICKET.CUSTOM_FIELD_VALUE13 AS TIME_SPENT,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_TICKET.CUSTOM_FIELD_VALUE6 AS PURPOSE,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
IF(UPDATER.FULL_NAME=',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.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 HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = HD_TICKET.ID
left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL


ok, so you are interested in the group of people that are owners but do not include the users "Service Desk" nor "Service Admin". What about owners that have been demoted or removed from the owner label for other reasons? What do you want do in that case?

Assuming, you are interested in only currently owners of the queue (excluding "Service Desk" nor "Service Admin") then here's an attempt. If the assumption is wrong then you'll need a label that represents all the users you want to track.
select T.ID TICKID,
C.ID CHGID,
C.USER_ID,
FIRSTTOUCH.ID FIRSTID,
IFNULL(OWNER.USER_NAME,'no owner') CURRENT_OWNER,
IFNULL(FIRSTTOUCH.USER_NAME,'no touch yet') FIRST_OWNER,
C.TIMESTAMP
from HD_TICKET T
join HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
join USER FIRSTTOUCH ON FIRSTTOUCH.ID=C.USER_ID
left join USER OWNER ON T.OWNER_ID=OWNER.ID
join ( select MIN(C.ID) CID, C.HD_TICKET_ID TID FROM HD_TICKET_CHANGE C
JOIN HD_TICKET T ON T.ID=C.HD_TICKET_ID
join HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=T.HD_QUEUE_ID
JOIN USER_LABEL_JT OL ON OL.LABEL_ID=QOL.LABEL_ID and OL.USER_ID=C.USER_ID
JOIN USER ON USER.ID=OL.USER_ID and USER.NAME NOT IN ('Service Desk','Service Admin')
WHERE GROUP BY T.ID ) C2
ON C2.CID=C.ID
GROUP BY FIRSTTOUCH.ID,C.ID
Note: that if you have a lot of tickets this will be an expensive query. If you have a lot of tickets then it may be better to have a rule that populates a hidden custom field with the change id when the first eligible owner updates the ticket.