Hi All

I've been asked to create a KACE1000 report on the number of tickets raised by an member of staff (so not the typical "submitter") over the past 30 days which will relate to a KPI.  Any help would be great !
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
This report will show tickets where the submitter is in a specific label that were opened in the past 30 days:
select HD_TICKET.ID, 
HD_TICKET.ID as TICKNUM, 
HD_TICKET.TITLE, 
OWNER.USER_NAME as OWNER_NAME, 
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, 
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, 
HD_STATUS.NAME AS STATUS_NAME, 
HD_STATUS.ORDINAL as STATUS_ORDINAL, 
STATE, 
OWNER.FULL_NAME as OWNER_FULLNAME, 
OWNER.EMAIL as OWNER_EMAIL, 
SUBMITTER.USER_NAME as SUBMITTER_NAME, 
SUBMITTER.FULL_NAME as SUBMITTER_FULLNAME, 
SUBMITTER.EMAIL as SUBMITTER_EMAIL,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, 
HD_PRIORITY, 
HD_STATUS, 
HD_IMPACT, 
HD_CATEGORY)
JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = SUBMITTER.ID
JOIN LABEL SUBMITTER_LABEL on SUBMITTER_LABEL.ID = USER_LABEL_JT.LABEL_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY) and
SUBMITTER_LABEL.NAME = "Label Name Here"
This will pull from all queues. As long as you have the users in a label this should work.
Answered 07/13/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity