Query to get Requester details with login info.

Version : 10609

DB : MSSQL

OUTPUT:

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,
longtodate(MAX(AaaAccSession.OPENTIME)) "Logged In Time" 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 AaaAccSession ON aa.ACCOUNT_ID = AaaAccSession.ACCOUNT_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' and adef.org_name='My Org Inc' group by AaaUser.FIRST_NAME,AaaUser.last_name,adef.org_name,sdo.name,AaaContactInfo.EMAILID,sduser.jobtitle,ar.name,AaaContactInfo.landline,AaaContactInfo.mobile ORDER BY 1

Note: kindly change the account name as per the requirement or remove account filter for full list irrespective of account.