Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)

Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)

Tested in builds from PGSQL (14300) or MSSQL (14306)

The query is compatiable with version 10.6 and above:

Query:

Select   au.FIRST_NAME  "Technicianname" , ar.NAME "Role",al.name "Login Name",aci.emailid "EmailID",SDUSER.STATUS "Status(Active/Inactive)",longtodate(MAX(AaaAccSession.OPENTIME)) "Last Logged In Date/Time" from PortalTechnicians pu
left join AaaUser au on pu.userid=au.USER_ID
left JOIN SDUser ON au.USER_ID=SDUser.USERID
left join aaausercontactinfo auci on auci.user_id = SDUser.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
left join AaaLogin al on au.USER_ID=al.USER_ID
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') group by au.first_name,ar.name,al.name,aci.emailid,sduser.status


        New to ADManager Plus?

          New to ADSelfService Plus?