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
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
New to ADSelfService Plus?
Related Articles
Login frequency
This report is used to find the Technician and Requester Login frequency. SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP Address", ...Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...Query to Pull out Login Hours
Purpose To generate the Total Login Hours per Technician. Query : select ai as "Account ID", tn as "Technician Name ( First Name )", lh as "Login hours", LONGTODATE(llt) as "Last Login Time", ln as "Login Name", domain as "Domain", email as ...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 ...Incident and Service Catalog Template Frequency
1 . This report is used to find the most used template in the application. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT ...