Report Request: Service Desk Report
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
Answers (1)
Please log in to answer
Posted by:
h2opolo25
9 years ago
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.
Comments:
-
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. - pregiec 9 years ago-
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. - h2opolo25 9 years ago
-
"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. - pregiec 9 years ago -
According to the Time Open question there was a similar question this week. http://www.itninja.com/question/sql-reporting-average-ticket-time-opened - aragorn.2003 9 years ago
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. - nshah 9 years ago