How to show working hours per week in Dell Kace K1000?
Hi,
I was wondering if it's possible to show the working hours per week of a specific user on the K1000.
We are using the Service Desk system and I would like that every technicians gets their total hours per week from every tickets of every week.
Is it possible to do that? By looking at the forum, it seems that it's possible with a script on the K1000. I'm a SQL expert but I understand the basics.
I've found this thread but I didn't succeed to apply it : http://www.itninja.com/blog/view/k1000-report-working-hours-per-week-per-person
If anyone can help it would be really appreciated.
Thank you.
I was wondering if it's possible to show the working hours per week of a specific user on the K1000.
We are using the Service Desk system and I would like that every technicians gets their total hours per week from every tickets of every week.
Is it possible to do that? By looking at the forum, it seems that it's possible with a script on the K1000. I'm a SQL expert but I understand the basics.
I've found this thread but I didn't succeed to apply it : http://www.itninja.com/blog/view/k1000-report-working-hours-per-week-per-person
If anyone can help it would be really appreciated.
Thank you.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
8 years ago
Here is a basic query that will show work on tickets in the past seven days for a given user name:
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(DATEDIFF(W.STOP, W.START))
FROM ORG1.HD_WORK W
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
and USER.USER_NAME = "username"
GROUP BY W.HD_TICKET_ID
Substitute the user's name to get the report for a specific user. If you want all users then you can remove that line. If you don't want to view the data per ticket you can change the GROUP BY statement to USER.ID and get a total per user.
This query assumes that your technicians are entering start and stop hours for their work. If you are instead using adjustment hours then you need to sum those using SUM(W.ADJUSTMENT_HOURS).
Comments:
-
Hi,
Thanks for the reply. When I create the report with what you wrote, it shows me that: http://s14.postimg.org/x1uggz3b3/SSKace.png
It shows 0 hours and the sections are named "Hd Ticket Id" and "Sum(Datediff(W.stop, W.start))"
Sorry for my lack of knowledge, maybe I'm doing something wrong or I didn't understand something.
For now, the user "Kenny Ngo' just has 1 ticket with 2 Work hours lines:
http://s1.postimg.org/x6kqctxpb/SSKace2.png
Thanks again. - dccadieux 8 years ago
Posted by:
chucksteel
8 years ago
Sorry, I was using datediff when I should have used timestampdiff:
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(TIMESTAMPDIFF(HOUR, W.START, W.STOP)) as "Work Hours"
FROM ORG1.HD_WORK W
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY W.HD_TICKET_ID
Also, if you haven't already setup a tool like MySQL Workbench to look at the SQL tables I highly recommend it. It makes building queries like this and debugging them much easier.
