/build/static/layout/Breadcrumb_cap_w.png

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.

0 Comments   [ + ] Show comments

Answers (2)

Posted by: chucksteel 8 years ago
Red Belt
2
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
Red Belt
0
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

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