Hello,

Can someone help me make a service desk report with the following fields?

Ticket ID
Issue
Users Affected
Submitter
Owner
Priority
Status
Resolution
Time Opened
Time closed
Time to close

I can make this report through the wizard expect fields like time to close are not included. Is there a way to view the SQL for a report if it was generated through the wizard? Any suggestions would help.
2 Comments   [ + ] Show Comments

Comments

  • If you did it using only a single topic yes. There should be an Edit SQL button towards the bottom where you can convert it.

    If you used a subtopic then no.

    Every think you listed should be there. Even if it was a custom field you created. Users affected is a custom field i'm assuming. Time to close may require the SQL route unless it to was a custom field.
  • I believe I am good on this report currently other than one issue. The custom field I have created labeled as "User(s) Affected" shows up as "Custom Field Value0" in the output if I run the report as an SQL report. My question is how would I get the "Custom Field Value0" to actually display "User(s) Affected" in the report?
Please log in to comment

Answers

0
When you create a rule it will give you the code at the end in two separate sections: Select SQL and Update SQL.
Create the report the way you want then post the code here and let us know what you want changed and we can take a look at it. 
Answered 03/26/2015 by: h2opolo25
Red Belt

  • Ok so I was able to find the SQL for the report. Not sure how I missed it.

    So, the field I actually want to display is "Time Open," which doesn't seem to be an available field in the wizard. "Time Opened" is available, but it is not the same thing. I was able to find a part of the SQL for "Time Open" from one of the other pre-configured reports:

    CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
    TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
    TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
    DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
    SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
    '%kh %im')) AS TIME_OPEN,

    The only problem I seem to having at the moment, is when I copy the SQL for the report that was made in the Wizard, the custom field that I have labeled as "User(s) Affected" shows up as "Custom Field Value0" in the outcome. If the report is run from the wizard this field displays properly. So below is the report I want to run, however, I would like the Custom Field Value to actually display "User(s) Affected."

    SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE0, O.FULL_NAME AS OWNER_NAME, HD_PRIORITY.NAME AS PRIORITY, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CREATED, CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
    TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
    TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
    DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
    SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
    '%kh %im')) AS TIME_OPEN,
    HD_TICKET.TIME_CLOSED FROM HD_TICKET LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_TICKET.APPROVAL = '')) ORDER BY CREATED

    Any help would be greatly appreciated. Thanks.
    • That's the way KACE stores custom fields. It's normal. Your SQL code above show's Time Opened. Do you mean when the ticket was created? If so then add this line in there before the "FROM" statement...."HD_TICKET.CREATED". Make sure to place the commas in the proper place just like the other part of the code above do.
      • "Time Opened" shows up properly on the report, which gives the time the ticket was actually opened.

        I am trying to use a custom field of "Time Open" or Ticket Duration" that gives the time that the actual ticket was opened from the creation of a ticket to the closing of the ticket.
      • According to the Time Open question there was a similar question this week. http://www.itninja.com/question/sql-reporting-average-ticket-time-opened
Please log in to comment
Answer this question or Comment on this question for clarity