Query to find duplicates list of user with various criteria (PGSQL)
Tested in Build PGSQL (14300)
Listed queries for the below cases
- Query to list requesters and technicians with email address filter.
- Query to list requesters and technicians with login name filter
- Query to list support groups with email address filter
- Query to list accounts with support email address filter.
You can run the queries under Reports Tab >> New Query Report.
Query to list requsters and technicians with email address filter. Replace test@test.com with the user email address
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 like '%test@test.com%'
Query to list requesters and technicians with login name filter. Replace test with username
select aaau.user_id,aaau.first_name,aaaci.emailid,sdu.status,aaal.name "Login Name",aaal.domainname "Domain", 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 LEFT JOIN aaalogin aaal ON aaal.user_id=aaau.user_id where aaal.name like '%test%'
Query to list support groups with email address filter. Replace test@test.com with supportemail address
select qd.queuename "Group Name",qe.email "Email",sdo.name "Site Name",adef.org_name "Account Name" from queuedefinition qd LEFT JOIN queue_email qe ON qd.queueid=qe.queueid LEFT JOIN sitedefinition sdef ON sdef.siteid=qd.siteid LEFT JOIN sdorganization sdo ON sdo.org_id=sdef.siteid LEFT JOIN accountsitemapping asm ON asm.siteid=sdef.siteid LEFT JOIN accountdefinition adef ON adef.org_id=asm.accountid where qe.email like '%test@test.com%'
Query to list accounts with support email address filter. Replace support@sample.com with account support email dress.
select org_id, org_name "Account Name", login_uri, support_email "Support Email Address" from accountdefinition where support_email like '%support@sample.com%'