/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Customized Dell Kace Service Desk Report

06/19/2015 1948 views

Hi All

Can someone assist me in an SQL script to pull the following.  This would pull from a a queue called IT Helpdesk.  The following would need to be done:

Ticket ID

Title

Time Closed

Category

Submitter User Name

Hours worked (with a total)

Would need the last 30 days for each month so far in 2015 and then one for a scheduled time reporting for the last 7 days. 

I appreciate anyones help with this.

2 Comments   [ + ] Show comments

Comments

  • To clarify, you're basically wanting a list of all tickets that fit two types of criteria (as below), correct?

    1. One report for each month of 2015 including all tickets created that month.

    &

    2. A dynamic report that can be run at any time showing all tickets created in the last 7 days.

    Also, what other criteria would apply? Are you only wanting closed tickets? Only tickets remaining open? Etc..
  • I am guessing that you want closed calls as you need the close date. You will need to get a custom report written to give you what you want, but in the meantime below is some reporting code that will hopefully help.

    select W.STOP as DATE, CONCAT(CAST(T.ID AS CHAR), " - ", T.TITLE) as TICKET, U.FULL_NAME, substring(W.NOTE,1,1200) as NOTE,
    format((time_to_sec(timediff(stop, start)))/3600.0 + ADJUSTMENT_HOURS,2) as HOURS_WORKED
    from (HD_WORK W, HD_TICKET T, USER U)
    where W.HD_TICKET_ID = T.ID
    and isnull(W.VOIDED_BY)
    and W.USER_ID = U.ID
    and W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
    order by U.FULL_NAME, TICKET, W.STOP

    You will be able to change the interval to '7 Day' for your weekly report

Be the first to answer this question

Don't be a Stranger!

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

Sign up! or login

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