Query to show hours and days since last update (MSSQL)
Tested in Build MSSQL (14306)
Go to Reports-New Query Report and execute this query.
select wos.WORKORDERID "Request ID", max(accountdefinition.org_name) "Account",MAX(aau.FIRST_NAME) "Requester",MAX(wo.TITLE) "Subject", MAX(sd.statusname) "Ticket Status", max(au.FIRST_NAME) "Technician ", LONGTODATE(MAX(wo.createdtime)) "Ticket Created Time", LONGTODATE(MAX(woh.operationtime)) 'Last Updated Time', MAX(sdo.name) 'Site Name', max(au.FIRST_NAME) "Technician ", datediff(hh,dateadd(s,max(woh.operationtime/1000),'1970-01-01 00:00'),getdate()) "Hour Since Last Update", datediff(dd,dateadd(s,max(operationtime/1000),'1970-01-01 00:00'),getdate()) "Days Since Last Update", max(woh.operation) "Operation" from workorderhistory woh
inner join workorderstates wos ON woh.workorderid=wos.workorderid
inner join workorder wo ON wos.workorderid=wo.workorderid
left join sdorganization sdo ON wo.siteid=sdo.org_id
left join aaauser au ON au.USER_ID=wos.OWNERID
left join StatusDefinition sd on wos.statusid=sd.statusid
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON wo.requesterid=aau.USER_ID
LEFT JOIN sitedefinition sdef ON wo.siteid=sdef.siteid
LEFT JOIN accountsitemapping asm ON sdef.siteid=asm.siteid
LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid
where sd.statusname !='Closed' and accountdefinition.org_id in ($Account) and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 14 group by wos.WORKORDERID order by 9
New to ADSelfService Plus?
Related Articles
Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...Query to show last updated field (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...Query to show change planning details with total worklog hours
Working on Build: 14500 MSSQL: SELECT cdt.changeid "Change ID", cr.rolloutplan "Roll Out Plan", cr.backoutplan "Back Out Plan", cr.checklist "Check List", ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...