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 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 ...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 find deleted survey (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Go to Reports >> New Query Report >> Run the below query to get the data SELECT err.message "System log message", err.errormodule "Module", err.suberrormodule "Sub Module", err.action "Action", ...