Logging User Access in a XenDesktop Environment

Overview

Access Log is a small custom-built utility that is designed to track users logging on and log to XenDesktop virtual desktops and then write that information to a database. This database can then be queried to provide simple reporting about usage of a XenDesktop environment.

Components / Design

Access Log comprises a single component: an executable called AccessLog.exe that runs on the virtual desktop. It is designed to be run as part of logon and logoff scripts. When run, it contacts an SQL 2005 (or 2008) database and inserts information about the user, virtual desktop, session start time, session stop time and duration.
To extract session information, the database can be queried directly or there is a stored procedure that can be run which returns session information.

Download and Installation

The required files are available for download here..
Pre-requisites & assumptions:

• SQL 2005/2008 Server.
• Rights to create new database and edit access.
• Knowledge of creating login/logoff scripts to be run by GPO.
• Knowledge of SQL Server Management Studio.
• Knowledge of retrieving data from external sources into excel (optional).

The installation process entails two steps: setting up the database and configuring the virtual desktop.
Database Setup

1. Extract the zip file.
2. Copy the contents of the "DB Creation Scripts" folder to the SQL Server.
3. Edit the contents of Script1.sql:
   a. Change D:\ClientAccess\ClientAccess.mdf to the target location of the database file. Make sure that the target folder exists before executing the scripts.
   b. Change D:\ClientAccess\ClientAccess_log.ldf to the target location of the database log file.
4. Open SQL Server Management Studio and run Script1.sql.
5. Open SQL Server Management Studio and run Script2.sql.
6. Check that there is now a Database called "ClientAccess" listed. It should have a single table "SP_AccessLog" as well as 3 stored procedures: GetAccessLog, SP_InsertAccessLog and SP_UpdateAccessLog
7. Ensure that Domain Users (the users who will run the utility on the virtual desktop) are granted "Execute" permissions on the ClientAccess database.

Virtual Desktop Setup

1. Copy the AccessLog.exe and cnn.ini files to a location on the virtual desktop.
2. Edit the "data source=(local)" section of the cnn.ini file, entering the name of the database server used in the above setup without brackets.
3. Add a login script for the virtual desktop users with the contents:
   <path to AccessLog>\AccessLog.exe
4. Add a logoff script for the virtual desktop users with the contents:
   <path to AccessLog>\AccessLog.exe logout
   Reporting
   Retrieving information about XenDesktop sessions is a little complex. However, the following are ways this data can be returned:

• Running the SP_GetAccessLog stored procedure on the SQL server. This will output a table of XenDesktop sessions as well as calculating the session duration.
• Embedding an SQL connection in an Excel spreadsheet to return a list of sessions.

Notes:

1. You can attach the database files included in this package at (Database\Database Files) directly to your database server instead of running the scripts.
2. The AccessLog.exe executable can be placed and run from a file share if necessary. However, you need to ensure that there no group pollicise defined in the domain that could prevent running.NET applications from file shares.
3. If they utility fails to reach database for any reason, I will create a file called error.log in the same directory that details the error cause.

Disclaimer

These software applications are provided to you as is with no representations, warranties or conditions of any kind. You may use and distribute it at your own risk. CITRIX DISCLAIMS ALL WARRANTIES WHATSOEVER, EXPRESS, IMPLIED, WRITTEN, ORAL OR STATUTORY, INCLUDING WITHOUT LIMITATION WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NONINFRINGEMENT. Without limiting the generality of the foregoing, you acknowledge and agree that (a) the software application may exhibit errors, design flaws or other problems, possibly resulting in loss of data or damage to property; (b) it may not be possible to make the software application fully functional; and (c) Citrix may, without notice or liability to you, cease to make available the current version and/or any future versions of the software application. In no event should the code be used to support of ultra-hazardous activities, including but not limited to life support or blasting activities. NEITHER CITRIX NOR ITS AFFILIATES OR AGENTS WILL BE LIABLE, UNDER BREACH OF CONTRACT OR ANY OTHER THEORY OF LIABILITY, FOR ANY DAMAGES WHATSOEVER ARISING FROM USE OF THE SOFTWARE APPLICATION, INCLUDING WITHOUT LIMITATION DIRECT, SPECIAL, INCIDENTAL, PUNITIVE, CONSEQUENTIAL OR OTHER DAMAGES, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You agree to indemnify and defend Citrix against any and all claims arising from your use, modification or distribution of the code.