Login Frequency Query Report

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);

      • Related Articles

      • Steps for deleting/modifying the reports created by other techs:

        Steps for deleting/modifying the reports created by other techs: Connect to your database  To connect to your database please refer http://www.manageengine.com/products/support-center/faq-general.html Execute the below query which will give you the ...
      • Changing the ownership of Report

        In order to change the ownership of the existing Reports, please follow the below steps, 1. connect to your database.  Incase, if your connecting database is postgresql, please refer the below link to connect to it. viz. ...
      • Issue running reports

        Cause : Report API running the background causes the issue. If the user mentions issue with running reports with the below error and trace as mentioned below. Error trace: ...
      • Support rep last login time, start time, end time along with personal information

        select aas.session_id "SessionID", aal.name "User Name",longtodate(aaauser.createdtime) as "User Created Time", aaacontactinfo.emailid "Email", aaacontactinfo.landline "Phone",aaacontactinfo.mobile "Mobile", aas.user_host "User Host", ...
      • Followers report

        You can use the below query to get the Followers and the request details. Go to Reports --> New Query Report select wo.workorderid "Request Id",wo.title "Subject",aaau.first_name "Followers",aaauc.emailid "Followers email" from workorder_cclist woc ...