Login Frequency Query Report

The below report would help us find the last logged in time of the users in SCP 11.0

SELECT AaaUser.FIRST_NAME "Technician",
       MAX(AaaLogin.NAME) "LoginName",
       MAX(AaaContactInfo.EMAILID) "Email",
       MAX(AaaAccSession.USER_HOST) "IP Address",
       longtodate(MAX(AaaAccSession.OPENTIME)) "Last Logged In Time" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
INNER JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
INNER JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
WHERE SDUser.STATUS = 'ACTIVE'
GROUP BY AaaUser.FIRST_NAME

Below helps in identifying not logged in users

select aal.name "User" from aaalogin aal inner join helpdeskcrew hc on aal.user_id=hc.technicianid left join aaaaccount aac on aal.login_id=aac.login_id where aac.account_id not in(select account_id from aaaaccsession);