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 accounts arranged in alphabetical order:
SELECT ad.ORG_NAME AS "Account",
sduser.FIRSTNAME AS "First Name",
sduser.LASTNAME AS "Last Name",
aaauser.FIRST_NAME AS "Display Name",
aaauser.USER_ID AS "Login User ID",
al.NAME as "Login Name",
aaacontact.EMAILID AS "E-Mail", aaacontact.LANDLINE AS "Phone" FROM People peopleci
LEFT JOIN BaseElement baseci ON peopleci.CIID=baseci.CIID
LEFT JOIN PortalUsers portalusers ON peopleci.CIID=portalusers.CIID
LEFT JOIN CI ci ON baseci.CIID=ci.CIID
LEFT JOIN AccountSiteMapping asm ON ci.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN SDUser sduser ON portalusers.USERID=sduser.USERID
LEFT JOIN AaaUser aaauser ON sduser.USERID=aaauser.USER_ID
LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID
LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID
LEFT join aaalogin al on aaauser.USER_ID = al.USER_ID order by 1
The above query returns the report in the below mentioned format,
2) Query to include the Employee ID in place of the Display name:
SELECT ad.ORG_NAME AS "Account",
sduser.FIRSTNAME AS "First Name",
sduser.LASTNAME AS "Last Name",
sduser.EMPLOYEEID AS "Employee ID",
aaauser.USER_ID AS "Login User ID",
al.NAME as "Login Name",
aaacontact.EMAILID AS "E-Mail", aaacontact.LANDLINE AS "Phone" FROM People peopleci
LEFT JOIN BaseElement baseci ON peopleci.CIID=baseci.CIID
LEFT JOIN PortalUsers portalusers ON peopleci.CIID=portalusers.CIID
LEFT JOIN CI ci ON baseci.CIID=ci.CIID
LEFT JOIN AccountSiteMapping asm ON ci.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN SDUser sduser ON portalusers.USERID=sduser.USERID
LEFT JOIN AaaUser aaauser ON sduser.USERID=aaauser.USER_ID
LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID
LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID
LEFT join aaalogin al on aaauser.USER_ID = al.USER_ID order by 1
New to ADSelfService Plus?
Related Articles
Query to get Requesters details for each account (Not Working)
Doesnt work in builds from PGSQL (14300) or MSSQL (14306) ERROR: missing FROM-clause entry for table "sdu" PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", ...Query to list account wise technicians and their respective support group details
Tested in: 14610 Compatible with: Both Postgres and MSSQL DB. Query 1: To return technicians and respective support group details from all accounts: SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support ...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 ...Query to export All Requesters from All Accounts (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Execute the below query under Reports > New Query Reports: SELECT AaaUser.USER_ID,AaaUser.FIRST_NAME "FullName",AaaLogin.NAME "LoginName",AaaLogin.DOMAINNAME "Domain",AaaContactInfo.EMAILID ...Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...