Complete worklog report -MSSQL
Query:
SELECT aau.FIRST_NAME AS "Requester",
max(wo.WORKORDERID) AS "Request ID",
rtdef.NAME AS "Request request type",
wo.TITLE AS "Subject",
wo.description ,
std.STATUSNAME AS "Request Status",
ti.FIRST_NAME AS "Assigned Technician",
Longtodate(wos.ASSIGNEDTIME) AS "Request assigned time",
Longtodate(wo.RESOLVEDTIME) AS "Resolved Time",
ct.SHORT_DESCRIPTION AS "Worklog Description",
Longtodate(ct.CREATEDTIME) As "Worklog added time",
CONVERT(VARCHAR(8), DATEADD(MILLISECOND, SUM(ct.TIMESPENT), 0), 108) "Work Log Time Spent",
ad.org_name AS "Account Name",
sdo.NAME AS "Site" FROM WorkOrder wo
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID
LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_lastweek> AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY aau.FIRST_NAME,rtdef.NAME,wo.TITLE,std.STATUSNAME,ti.FIRST_NAME,wos.ASSIGNEDTIME,wo.RESOLVEDTIME,wo.description,ad.org_name,sdo.NAME,ct.TIMESPENT,ct.SHORT_DESCRIPTION,ct.CREATEDTIME
New to ADSelfService Plus?
Related Articles
Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...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 retrieve worklog details
Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' ...Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...Query to show both task comments and worklog comments ( MSSQL )
Tested in MSSQL build (14306) SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category", "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS ...