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 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 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 list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...