Query to get Users/Requesters list who has login.

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 ADManager Plus?

                New to ADSelfService Plus?