you may have to add SH_HPD_Help_Desk to your universe if it is not there OOTB.
Required join is : HPD_HELP_DESK.Entry_ID=SH_HPD_Help_Desk.Entry_ID
This table gives the last date that a Status was updated
The audit log table is HPD_Help_Desk_Audit_LogSystem and this give you all updates to the incident.
This is trickier to pull data from as it is all held in a long string. I've done it, but it's a bit more complex to incorporate into the universe
We have Change Status History Class in ITSM universe, where you can track Status value and updated time.
Before that, you need to Updating the CHG_STATUS_HISTORY table ( Refer page no. 76). Doubt updating table was implemented by RoD guys.
Verify once, hope it can be helpful to you.
If you want to build the report to track all status change dates (current and previous changes) Status history table will not be any help, since it lists only the latest date the particular status has acquired by overwriting the previous date value for that column. You will have to use HPD_HELPDESK_AUDITLOGSYSTEM table for that purpose. I am listing the query below which will track all the dates for the incident when it moved to Assigned Status. You can add derived table in the universe and modify the query as per your requirement.
HPD_HELPDESK_AUDITLOGSYSTEM.MODIFIED_DATE AS INCIDENT_ASSIGNED_DATE
WHERE HPD_HELPDESK_AUDITLOGSYSTEM.ORIGINAL_REQUEST_ID = HPD_HELP_DESK.ENTRY_ID
AND HPD_HELPDESK_AUDITLOGSYSTEM.FIELDS_CHANGED LIKE '%;Status;%'
AND HPD_HELPDESK_AUDITLOGSYSTEM.LOG LIKE '%Status: Assigned%'
Hope that helps!
Thanks, everyone! Shwetambari pointed me in the right direction. Unfortunately, as it is RoD, we can't modify the universe without a customization.
For those interested, here's a query I wrote for this to pull the status by ticket, and the date it changed:
when (Fields_Changed LIKE '%;Status;%')
then left(substring(Log, charindex('Status: ',Log) + 8, 30),
charindex(' (',substring(Log, charindex('Status: ',Log) + 8, 30)))
end as Status
HPD_HELP_DESK left outer join HPD_HelpDesk_AuditLogSystem
(HPD_HELP_DESK.Entry_ID = HPD_HelpDesk_AuditLogSystem.Original_Request_ID)
Fields_Changed LIKE '%;Status;%'
Note that this will report the Status in text format (Assigned, Pending, Resolved, etc). The numerical status is also presented in the Log field for the Status: line, placed between parenthesis. Some tweaking with the statement should be able to pull that if required.
*modified to show how it links back to incidents