Hi All,

I'm currently trying to create a couple reports and I feel that I've gotten close. Presently, I've been working on creating reports that report:

1. Average Time Tickets Open/New Status Per Owner with Overall Time Tickets Open/New Average
2. Average Customer Satisfaction Per Owner with Overall Customer Satisfaction Average
3. Tickets Closed Per Owner Per Last 31 Days with Overall Tickets Closed in Last 31 Days

Although my code is probably incredibly inefficient, wrong, or poorly done, I've still managed to complete the third report which will be a little treat at the end of my post for people to use.

1. I don't know if I'm properly calculating things in this report and I'm having trouble creating a union for the overall average time tickets are open/new status. I feel like I'm pretty far off from where I should be:

SELECT
FULL_NAME AS 'Owner',
AVG(X) AS 'Average Time Tickets Are Open/New Status'
FROM
(
SELECT
T.ID,
U.FULL_NAME,
T.CREATED,
CONCAT(DATEDIFF(NOW(), T.CREATED)) AS X
FROM
HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue')
WHERE
T.OWNER_ID <> 0
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )
AND (S.STATE = 'opened'
OR S.STATE = 'new')
) RAW
GROUP BY FULL_NAME
UNION
SELECT
'Total',
SUM(AVG(Z)) AS 'Average Time Tickets Are Open/New Status'
FROM
(
SELECT
T.ID ,
U.FULL_NAME,
T.CREATED,
CONCAT(DATEDIFF(NOW(), T.CREATED)) AS Z
FROM
HD_TICKET T
JOIN USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID = Q.ID AND Q.NAME ='Queue')
WHERE
T.OWNER_ID <> 0
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )
AND (S.STATE = 'opened'
OR S.STATE = 'new')
) RAW
GROUP BY 'Total'


2. I currently haven't begun to work on integrating an overall customer satisfaction average on this report but I believe it's at least still functional for average customer satisfaction per owner although I could be wrong:

SELECT ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID AND FULL_NAME IN ( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )), 'Unassigned') as 'Owner', AVG(HD_TICKET.SATISFACTION_RATING) as 'Average Satisfaction Rating'
FROM HD_TICKET
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND HD_STATUS.STATE = 'closed'
and HD_TICKET.SATISFACTION_RATING != 0
and ('Owner' != 'Unassigned')
GROUP BY Owner
ORDER BY Owner


3. Here is the last report which I guarantee is working correctly although it is customized specifically for our environment (uses label_id and hd_queue). If anyone notices anything wrong with it, please point it out:

SELECT
X AS 'Owner',
Y AS '# of Tickets Closed in Last 31 Days'
FROM
(SELECT ifnull(FULL_NAME, 'Unassigned') AS X, COUNT(T.ID) AS Y FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) <= 31 AND (T.HD_QUEUE_ID = 1 OR T.HD_QUEUE_ID = 4))
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242
)
GROUP BY FULL_NAME) RAW
UNION
SELECT 'Total', SUM(Z)
FROM
(SELECT ifnull(FULL_NAME, 'Unassigned') AS X, COUNT(T.ID) AS Z FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) <= 31 AND (T.HD_QUEUE_ID = 1 OR T.HD_QUEUE_ID = 4))
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242
)
GROUP BY FULL_NAME) RAW


Thanks for any help offered and any opinions.

Regards
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Good start, here is some feedback:
1) Weekends and holidays are really going to affect this report. It's not an easy problem to solve but you could use a conditional formula to subtract weekends if needed. Or you could fudge the numbers down by 28.5% over a long haul with even distribution. The following code could be a problem:
AND FULL_NAME IN
( SELECT USER.FULL_NAME [blockquote]FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242 )
[/blockquote]AND (S.STATE = 'opened' OR S.STATE = 'new') )
- FULL_NAME is not guaranteed to be unique
- on a long list of users it will be very inefficient because of the subquery
- label id 242 is ambiguous to those re-using it Unless I was only focused on owners currently in the owner labels then I could just assume that an ID in the OWNER_ID column belongs to an owner and simplify. Here is the first part:
SELECT U.FULL_NAME 'Owner',
ROUND(AVG(DATEDIFF(NOW(),T.CREATED)),1) 'Average Time Tickets are Open/ New Status'
FROM
HD_TICKET T JOIN
USER U ON (U.ID = T.OWNER_ID)
JOIN HD_STATUS S ON (S.ID =T.HD_STATUS_ID)
JOIN HD_QUEUE Q ON (T.HD_QUEUE_ID =Q.ID AND Q.NAME like '%')
WHERE
T.OWNER_ID <> 0
AND S.STATE IN ('opened','new')
GROUP BY OWNER_ID
ORDER BY 1,Q.ID
· What is the purpose of the concat?

2) Looks like you are focused on current owners of the queue. Here is how you can incorporate that:
SELECT IFNULL(USER.FULL_NAME,'Unassigned') OWNER,
ROUND(AVG(HD_TICKET.SATISFACTION_RATING),1) as 'Average Satisfaction Rating',
FROM HD_TICKET
LEFT JOIN USER ON OWNER_ID=USER.ID
LEFT JOIN HD_STATUS on HD_STATUS_ID= HD_STATUS.ID
LEFT JOIN HD_QUEUE_OWNER_LABEL_JTQO ON QO.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
LEFT JOIN LABEL QL ONQL.ID=QO.LABEL_ID and QL.NAME 'LABELNAMEHERE' /*********/
LEFT JOIN USER_LABEL_JT OL ONOL.USER_ID=OWNER_ID and OL.LABEL_ID=QL.ID
WHERE (HD_TICKET.HD_QUEUE_ID =1)
AND HD_STATUS.STATE = 'closed'
and HD_TICKET.SATISFACTION_RATING!= 0
GROUP BY OWNER_ID, HD_TICKET.HD_QUEUE_ID
ORDER BY 1
Answered 10/06/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
GillySpy,

Thanks for all the input, I really appreciate it! I will be sure to test your queries come Monday and will work on creating UNION's in order to add 'Overall' rows to the reports.

My use of the following select statement SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242
was my method of pulling owner's names from the label we have slapped to everyone in IT. This method allowed owners with zero closed tickets per month or zero satisfaction rating to still show up in reports, although I do understand the report becoming useless for future IT employees if label id's change, and do admit that using that type of select statement is only a result of my own naivety.
Answered 10/08/2011 by: corbins
Senior Yellow Belt

Please log in to comment
0
This method allowed owners with zero closed tickets per month or zero satisfaction rating to still show up in reports...
That's fair and works great for most, but if we need to avoid sub-queries (for efficiency) then I would start with USER table and (FULL INNER) JOIN in the label tables then LEFT JOIN on the ticket table and still skip the sub-query. Here's first one I did modified -- mostly just rearranged to incorporate your caveat:
SELECT DISTINCT U.FULL_NAME 'Owner',
IFNULL( ROUND(AVG(DATEDIFF(NOW(),IF(S.STATE IS NULL, NULL,T.CREATED))),1), 'n/a') 'Average Time Tickets are Open/ New Status'
FROM
USER U
JOIN USER_LABEL_JT OL ON OL.USER_ID=U.ID
JOIN LABEL L ON L.ID=OL.LABEL_ID and L.NAME LIKE '%' /*label name here */
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.LABEL_ID=OL.LABEL_ID
JOIN HD_QUEUE Q ON Q.ID=QOL.HD_QUEUE_ID and Q.NAME LIKE '%' /* queue name here */
LEFT JOIN HD_TICKET T ON T.OWNER_ID=U.ID and Q.ID=T.HD_QUEUE_ID and T.OWNER_ID<>0
LEFT JOIN HD_STATUS S ON (S.ID =T.HD_STATUS_ID) and S.STATE IN ('opened','new')
GROUP BY U.ID
ORDER BY 1,Q.ID
Answered 10/08/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
GillySpy,

After some work, I've outfitted the statement you made for #2 with a couple changes and a UNION for overall average:

SELECT DISTINCT USER.FULL_NAME 'Owner',
IFNULL (ROUND(AVG(HD_TICKET.SATISFACTION_RATING),1), 'n/a') as 'Average Satisfaction Rating'
FROM HD_TICKET
LEFT JOIN USER ON OWNER_ID=USER.ID
LEFT JOIN HD_STATUS on HD_STATUS_ID=HD_STATUS.ID
LEFT JOIN HD_QUEUE_OWNER_LABEL_JT QO ON QO.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
LEFT JOIN LABEL QL ON QL.ID=QO.LABEL_ID and QL.NAME LIKE 'IT Staff'
LEFT JOIN USER_LABEL_JT OL ON OL.USER_ID=OWNER_ID and OL.LABEL_ID=QL.ID
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.LABEL_ID=OL.LABEL_ID
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND HD_STATUS.STATE = 'closed'
AND HD_TICKET.SATISFACTION_RATING != 0
GROUP BY OWNER_ID
UNION
SELECT 'Overall',
ROUND(AVG(SATISFACTION_RATING),1) AS 'Average Satisfaction Rating'
FROM HD_TICKET
WHERE SATISFACTION_RATING != 0
GROUP BY 'Overall'


and from my testing, it seems to work perfectly! Thanks for the input on this and please let me know if you see any glaring errors.



For #1: when testing your statement and trying to discern what was being outputted, I was having a hard time trying to understand. I'm getting a report comparable to the following example:

1 White, John 59.5
2 Smith, John 198.5
3 Brown, John 183.6

Are the numbers outputted in hours? If so, then I'm very confused as we have someone with an average of 355.5 but the only ticket they've ever closed was left open/new for only 6d. Please elucidate as to what might be happening.



I've also been working on re-doing #3 that I posted in my original post as it seems very inefficient. This is what I've got so far but the number created by the COUNT in the second select statement is not producing the correct amount (~625 tickets have been closed in the past 31 days and it's counting ~635). Any idea why?

SELECT
X AS 'Owner',
Y AS '# of Tickets Closed in Last 31 Days'
FROM
(SELECT ifnull(FULL_NAME, 'Unassigned') AS X, COUNT(T.ID) AS Y FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) <= 31 AND (T.HD_QUEUE_ID = 1 OR T.HD_QUEUE_ID = 4))
AND FULL_NAME IN
( SELECT USER.FULL_NAME
FROM USER_LABEL_JT, USER
WHERE USER_ID=USER.ID
AND LABEL_ID = 242
)
GROUP BY FULL_NAME) RAW
UNION
SELECT 'Total', COUNT(T.ID) AS '# of Tickets Closed in Last 31 Days'
FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) <= 31 AND (T.HD_QUEUE_ID = 1 OR T.HD_QUEUE_ID = 4))
GROUP BY 'Total'


Regards
Answered 10/10/2011 by: corbins
Senior Yellow Belt

Please log in to comment
0
Sorry about that. Since you wanted results even if an owner never had a ticket I was using join elimination only. Since we're using join elimination I had to exclude NULL values from any outer joined results from the calculations. I fixed it above. Basically changed

T.CREATED

to

IF(S.STATE IS NULL, NULL,T.CREATED)
Answered 10/10/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
The statement you just edited is now giving me 0.0 for owners that I know have closed 100's of tickets:

1 John 0.3
2 Ben 1.5
3 Jake 0.0
4 Rob 0.0
Answered 10/10/2011 by: corbins
Senior Yellow Belt

Please log in to comment
0
No offense, but that's irrelevant because I was answering part 1 which is excluding all closed results.
I'm having trouble creating a union for the overall average time tickets are open/new status

By the way, the only valid states are opened, closed and stalled so when you say STATE IN ('New','Opened') are you trying to examine two different states or two different statuses? Either way a modification will likely need to be made since the new status has a stated of stalled.
Answered 10/10/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Essentially, I'm only trying to measure the average amount of time per owner a ticket sits around before it is closed. Maybe we do a datediff between the exact time the ticket was created and the ticket was closed, and skip the states that might be occurring on the ticket? I can only imagine this being problematic when tickets are marked as 'waiting for customer' since that will raise averages even though the owner is purposefully waiting for a response. If possible, it would be nice to be able to remove the amount of time a ticket sits around in a state that ISNT new/open from the calculation of the average, but it seems like that may be too complicated within the scope of kace reporting.
Answered 10/11/2011 by: corbins
Senior Yellow Belt

Please log in to comment
0
If you only care about the time it takes between open and closed though then your approach is good. I gave you the time between open and now (for non-closed) so closed is very similar.

But i f you are saying that you're interested in...if a ticket opens at X and closes at Y but sat around for A=Y-X+Z then you want to measure Z? and A?
Take a look at this thread:
http://itninja.com/question/servers-installation-help03&mpage=1&key=&#83412
Answered 10/11/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I don't think this specific report is important enough to warrant a complicated approach, so I think your first option would be alright. I can settle with a report that is still mostly accurate. Please let me know where we could go from here.

I would still be interested in testing the rule base you created just for the sake of seeing what it's like and helping out though.
Answered 10/11/2011 by: corbins
Senior Yellow Belt

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