Auditing System Access
Accolade maintains a record of every user attempt to log on to Accolade. Using the information in the user access logs, Administrators can determine which users are actively accessing Accolade and conduct general system security audits. The user access logs contain the following information and are saved according to the Number of Days to Maintain User Access Logs parameter setting:
- The date and time of the login attempt.
- The Windows ID of the user logging in.
- Whether each login attempt was successful.
- The Accolade user name and system ID of the user who successfully logged in.
- The IP address, the browser version, and operating system of the client machine.
- The client machine's language setting as accepted by the Accolade server.
To access the data about user log in history, design reports using the RV_UserAccessLog and RV_UserAccessLastLogin reporting views.
SQL Query Examples
The following are example SQL queries that you can use to create reports regarding user access to Accolade.
SQL query to gather all user access log information:
SELECT * FROM RV_UserAccessLog
This query generates an HTML report similar to the following:
SQL query to gather the date of last attempted log in for each user:
SELECT UserLogin as Login, LastLoginDate as "Login Date" FROM RV_UserAccessLastLogin order by LastLoginDate desc
This query generates an HTML report similar to the following:
SQL query to gather the users that have not attempted access in 6 months or more:
SELECT Name, UserLogin as Login, LastLoginDate as "Login Date"
FROM RV_UserAccessLastLogin
WHERE (LastLoginDate IS NULL OR CONVERT(DATETIME, REPLACE(SUBSTRING
(LastLoginDate, 1, 10),' ','')) < dateadd(month,-6,GetDate())
)
order by LastLoginDate desc
This query generates an HTML report similar to the following:
