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
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 ...
Unable to start the application due to Pgsql privilege issue
ISSUE: Application does not start via both services and command prompt. ERROR TRACE: Serverout: [16:26:36:443]|[05-10-2023]|[com.adventnet.db.adapter.postgres.PostgresDBAdapter]|[SEVERE]|[27]: Exception occurred while obtaining mode of the database| ...
Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...
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 ...
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 ...