Query to get Users/Requesters list who has login.
Version : 14200
DB : MSSQL / PGSQL
OUTPUT :

SELECT AaaUser.USER_ID,
AaaUser.FIRST_NAME "FullName",
AaaLogin.NAME "LoginName",
AaaLogin.DOMAINNAME "Domain",
AaaContactInfo.EMAILID "Email" FROM AaaUser
LEFT JOIN UserDepartment ON AaaUser.USER_ID = UserDepartment.USERID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID = AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID = AaaContactInfo.CONTACTINFO_ID
LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID = DepartmentDefinition.DEPTID
LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID = SiteDefinition.SITEID
LEFT JOIN SDOrganization ON SiteDefinition.SITEID = SDOrganization.ORG_ID
INNER JOIN SDUser ON AaaUser.USER_ID = SDUser.USERID
LEFT JOIN Portalusers PU ON SDUser.USERID = PU.userid
LEFT JOIN portaltechnicians PT ON PU.id = pt.id
LEFT JOIN AaaLogin ON AaaUser.USER_ID = AaaLogin.USER_ID
left join AaaAccount aa on AaaLogin.LOGIN_ID=aa.LOGIN_ID
left join sdAuthorizedRole aar on aa.ACCOUNT_ID=aar.ACCOUNT_ID
left join AaaRole ar on aar.ROLE_ID=ar.ROLE_ID
LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID
LEFT JOIN accountsitemapping asm ON asm.SITEID = SiteDefinition.SITEID
LEFT JOIN accountdefinition ad ON ad.ORG_ID = asm.ACCOUNTID
WHERE (( pt.id IS NULL ) AND (AaaLogin.NAME != 'Not Assigned') )
New to ADSelfService Plus?