Query to show Technicians' login name, email, role and last login (PGSQL & MSSQL )

Query to show Technicians' login name, email, role and last login (PGSQL & MSSQL )

Tested in Build PGSQL (14300) or MSSQL (14306)

SELECT AaaUser.FIRST_NAME "First Name",
AaaUser.last_name "Last Name",
 adef.org_name "Account Name",
 sdo.name "Site Name",
 AaaContactInfo.EMAILID "E-mail",
 sduser.jobtitle,
 ar.name "Role",
AaaContactInfo.landline,
 AaaContactInfo.mobile FROM AaaUser

 INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID

 INNER JOIN portaltechnicians ON SDUser.USERID=portaltechnicians.userid

 LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID

 LEFT JOIN AaaContactInfo ON  AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID

 LEFT JOIN AaaLogin al ON AaaUser.USER_ID=al.USER_ID

 LEFT JOIN UserDepartment ON AaaUser.USER_ID=UserDepartment.USERID

 LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID=DepartmentDefinition.DEPTID

 LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID=SiteDefinition.SITEID

 LEFT JOIN SDOrganization sdo ON SiteDefinition.SITEID=sdo.ORG_ID

 LEFT JOIN accountsitemapping asm on asm.siteid=sdo.ORG_ID

 LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
 left join AaaAccount aa on al.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

 WHERE (AaaRoleToCategory.ROLE_ID IS NULL) and SDUser.STATUS='ACTIVE' ORDER BY 1


        New to ADManager Plus?

          New to ADSelfService Plus?