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%'
New to ADSelfService Plus?
Related Articles
Duplicate users while importing from AD
Use the below queries; To find the number of duplicate users: SELECT objectguid, COUNT(*) TotalCount FROM aduser GROUP BY objectguid HAVING COUNT(*) > 1; SELECT userid, samaccname, objectguid, COUNT(*) TotalCount FROM aduser GROUP BY userid, ...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 ...Script to send approvals to the users using distribution list
Request additional fieldCreate a Single Line Request additional field common for incident and service with name 'Last Approval Comments'. Replace the api name in the below function. Global functions 1) Function Name - Get Configurations Return type - ...Query to find users under blocked state
select * from sduser sd left join CI ci on sd.ciid=ci.ciid left join AaaUserContactInfo auc on sd.userid=auc.user_id left join AaaContactInfo aci on auc.contactinfo_id=aci.contactinfo_id where sd.status='blocked'Script to send notification to users on upcoming contracts
Please follow the below link to setup python on the server https://pitstop.manageengine.com/portal/en/kb/articles/how-to-setup-python-on-the-server-11-11-2019 Steps to configure 1. Extract the attached zip file in <Home>/integration/custom_scripts ...