how to backdate an exisitng Kace report
I have a report based on service desk that I need to run as if it was 1/2/2015. Is this even possible?
4 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
aragorn.2003
8 years ago
If you use the provided SQL statement you can add a where condition based on the HD_TICKER.CREATED date.
WHERE HD_TICKET.CREATED <= '2015-02-01 00:00:00'
Comments:
-
or WHERE HD_TICKET.CREATED = '2015-02-01 00:00:00' if you want tickets that are opened on this day. - aragorn.2003 8 years ago
Posted by:
chucksteel
8 years ago
Top Answer
To find tickets that were opened on a specific date you want to look for tickets that were created before or on that date, (DATE(CREATED) <= "2015-01-02") and closed after or on that date (DATE(TIME_CLOSED) >= "2015-01-02").
This basic query returns tickets that were open on 2015-01-02:
SELECT ID, TITLE, MODIFIED, CREATED, TIME_CLOSED FROM HD_TICKET
WHERE DATE(CREATED) <= "2015-01-02"
AND DATE(TIME_CLOSED) >= "2015-01-02"
UNION
SELECT HD_TICKET.ID, HD_TICKET.TITLE AS Issue_Summary, HD_TICKET.CUSTOM_FIELD_VALUE2 AS Dept, HD_TICKET.CUSTOM_FIELD_VALUE1 AS Dept_Priority, HD_TICKET.CUSTOM_FIELD_VALUE3 AS Overall_Priority, S.FULL_NAME AS Customer, O.FULL_NAME AS Assigned_To, HD_TICKET.CREATED, HD_STATUS.NAME AS STATUS_NAME, HD_TICKET.CUSTOM_FIELD_VALUE4 AS Percent_Complete, HD_TICKET.CUSTOM_FIELD_VALUE5 AS Project_Progress, HD_TICKET.CUSTOM_FIELD_VALUE6 AS Work_Effort_Estimate, HD_TICKET.CUSTOM_FIELD_VALUE14 AS Blank5,HD_TICKET.IS_PARENT AS Ticket_Type, Q.NAME AS QUEUE_NAME 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_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_STATUS.NAME not like '%closed%')) ORDER BY QUEUE_NAME, Assigned_To, ID - nadecats 8 years ago