Query report on Maintenance module (MSSQL & PGSQL)
SELECT ad.ORG_NAME "Customer",
swo.WORKORDERID "Scheduled WOID",
ti.SCHEDULE_TIME "Next Schedule Time",
aau.FIRST_NAME "Requester",
sdo.NAME "Site",
dpt.DEPTNAME "Department",
cd.CATEGORYNAME "Category",
scd.NAME "Subcategory",
icd.NAME "Item",
qd.QUEUENAME "Group",
ati.FIRST_NAME "Technician",
swo.TITLE "Subject",
std.STATUSNAME "Request Status" FROM sworkorder swo
LEFT JOIN SDUser sdu ON swo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SiteDefinition siteDef ON swo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN SWorkOrderStates swos ON swo.WORKORDERID=swos.WORKORDERID
LEFT JOIN QueueDefinition qd ON swos.QUEUEID=qd.QUEUEID
LEFT JOIN CategoryDefinition cd ON swos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON swos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN ItemDefinition icd ON swos.ITEMID=icd.ITEMID
LEFT JOIN SDUser td ON swos.OWNERID=td.USERID
LEFT JOIN AaaUser ati ON td.USERID=ati.USER_ID
LEFT JOIN StatusDefinition std ON swos.STATUSID=std.STATUSID
LEFT JOIN SREQTASK_INPUT si ON si.WORKORDERID=swo.WORKORDERID
LEFT JOIN task_input ti ON ti.INSTANCE_ID = si.INSTANCE_ID
LEFT JOIN schedule s ON ti.SCHEDULE_ID = s.SCHEDULE_ID
LEFT JOIN UserDepartment ud ON aau.USER_ID=ud.USERID
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID
left join accountsitemapping asm on asm.siteid = swo.siteid
Left join accountdefinition ad on asm.accountid = ad.org_id
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 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 Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...Query to fetch the fields in the templates (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...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 ...