Query to find users with multiple E-mail addresses of the same name (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
select aaau.user_id,aaau.first_name,aaaci.emailid,sdu.status,sdo.name
"Site",adef.org_name "Account" from aaauser aaau LEFT JOIN
aaausercontactinfo aaauci ON aaauci.user_id=aaau.user_id LEFT JOIN
aaacontactinfo aaaci ON aaaci.contactinfo_id=aaauci.contactinfo_id LEFT
JOIN sduser sdu ON sdu.userid=aaau.user_id LEFT JOIN UserDepartment
userDept ON sdu.USERID=userDept.USERID LEFT JOIN DepartmentDefinition
deptTable ON userDept.DEPTID=deptTable.DEPTID LEFT JOIN sitedefinition
sdef ON sdef.siteid=deptTable.siteid 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 where aaaci.emailid in(select emailid from
aaacontactinfo where emailid !='' group by emailid HAVING count(*) >
1) group by
aaau.first_name,aaau.user_id,aaaci.emailid,sdu.status,sdo.name,adef.org_name
order by aaaci.emailid,aaau.first_name;
New to ADSelfService Plus?
Related Articles
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 list technician name, site, email and login name (MSSQL & PGSQL)
The below PGSQL query doesnt work in builds below PGSQL (14000) ERROR: missing FROM-clause entry for table "helpdeskcrew" Pgsql: SELECT HelpDeskCrew.TECHNICIANID "Technician ID", AaaUser.FIRST_NAME "Technician Name",AaaContactInfo.EMAILID "Email", ...Query to find out who created Accounts (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Execute the below queries under Reports->New Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down ...MSSQL error 'Invalid Object name'
When you get a similar error as mentioned below java.sql.BatchUpdateException: Invalid object name 'TABLE NAME' please change the COLLATION in the MSSQL server for the database 'servicedesk' as CASE-INSENSITIVE. You can get this error at the time of ...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 ...