/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


how to backdate an exisitng Kace report

05/18/2015 867 views
I have a report based on service desk that I need to run as if it was 1/2/2015. Is this even possible?
Answer Summary:
4 Comments   [ + ] Show comments

Comments

  • Can you provide more details on what the report is supposed to capture?
  • SELECT HD_TICKET.ID, HD_TICKET.TITLE AS Issue_Summary, HD_TICKET.CUSTOM_FIELD_VALUE4 AS Dept, HD_TICKET.CUSTOM_FIELD_VALUE14 AS Dept_Priority,HD_TICKET.CUSTOM_FIELD_VALUE13 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_VALUE11 AS Percent_Complete, HD_TICKET.CUSTOM_FIELD_VALUE12 AS Project_Progress, HD_TICKET.CUSTOM_FIELD_VALUE1 AS Work_Effort_Estimate, HD_TICKET.CUSTOM_FIELD_VALUE2 AS Severity, HD_TICKET.CUSTOM_FIELD_VALUE10 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 = 1) AND ((HD_STATUS.NAME not like '%closed%'))
    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
    • So are you looking to find tickets that were open on 1/2/2015?
  • Yes
  • Yes, I want ticket that were status = opened on that day. I don't necessarily want ticket that were created on that day.

Answer Chosen by the Author

0
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"

Answered 05/18/2015 by: chucksteel
Red Belt

All Answers

0
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'
Answered 05/18/2015 by: aragorn.2003
Red Belt

  • or WHERE HD_TICKET.CREATED = '2015-02-01 00:00:00' if you want tickets that are opened on this day.

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