Query to show Site and its related settings(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Go to Reports-New Query Report and execute the query.
select adef.org_name "Account Name",sdef.siteid "Site ID", sdo.name "Site Name", siteconf.settingdetail "Related Settings", CASE WHEN (siteconf.configid) = 1 THEN 'Operational hours' WHEN (siteconf.configid) = 2 THEN 'Holidays' WHEN (siteconf.configid) = 3 THEN 'Departments' WHEN (siteconf.configid) = 4 THEN 'Support Groups' WHEN (siteconf.configid) = 7 THEN 'Technicians / Site Association' WHEN (siteconf.configid) = 5 THEN ' Business Rules' WHEN (siteconf.configid) = 6 THEN ' Service Level Agreements' ELSE 'Common' END "Related Settings Identifier" from sitedefinition sdef left join sdorganization sdo on sdo.org_id=sdef.siteid left join siteconfigsettings siteconf on siteconf.siteid=sdef.siteid left join accountsitemapping asm on asm.siteid=sdef.siteid left join accountdefinition adef on adef.org_id=asm.accountid where sdef.isdeleted='false' order by 1
New to ADSelfService Plus?
Related Articles
Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to pull Account ID and Site ID (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports Tab >> New Query Report and run the below query. For Postgres & MS SQL select adef.org_id "Account ID", adef.org_name "account",sdef.siteid "Siteid", sdo.name "Site" from sitedefinition ...Query to show resolved by value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", ...Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...