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 sdef
left join accountsitemapping asm on asm.siteid=sdef.siteid
left join accountdefinition adef on adef.org_id=asm.accountid
left join sdorganization sdo on sdo.org_id=sdef.siteid order by 2
New to ADSelfService Plus?
Related Articles
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 get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...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 ...Query to show account,site and its departments (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. select deptdef.deptname "Department",sdo.name "Site", adef.org_name "Account" from departmentdefinition deptdef LEFT JOIN sitedefinition sdef ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...