jas- / readme.md

Save jas-/3ee76618b4f056d1a052 to your computer and use it in GitHub Desktop.

Ulteo-OVD implementation guide

Ulteo Remote Application Server

Installation, configuration, patching & troubleshooting guide to the Ulteo-OVD services. Additional details of this software can be found on their website. Here are some useful resources.

  1. Ulteo home - http://www.ulteo.com/home/
  2. Ulteo Downloads - http://ulteo.com/home/en/ovdi/openvirtualdesktop/3.0
  3. Ulteo OVD source code - http://www.ulteo.com/home/en/download/sourcecode
  4. Additional OVD source code access - http://archive.ulteo.com/mirror/ovd/releases/sources/
  5. Community forums - https://groups.google.com/forum/?fromgroups#!forum/ulteo-ovd-community-support

Ulteo OVD 4.0 RC1 Community Edition

Original Guides

All guides can be found @ http://doc.ulteo.com/latest and are recommended prior to applying the patch associated with this documentation.

Current (2012-08-28)

The current operating environments for the Ulteo-OVD application service details

Overview, Setup & Configuration

This section will provide a general overview of the various components that make up the Ulteo OVD software. The diagram below illustrates how the web client interfaces with the configured application server(s).

Session manager

The Session Manager component handles the Administrative panel which is used to configure the Ulteo software.

It uses the following locations.

  1. /etc/ulteo/sessionmanager - Here you can find the Apache virtual host configuration directives, the default administrative login for the Ulteo admin interface etc.
  2. /usr/share/ulteo/sessionmanager - The web interface for the session manager. This folder contains the administrative interface as well as components the webclient uses for authentication & session management.
  3. /var/log/ulteo/sessionmanager - The logs here are used within the administrative interface and can serve as a good source of troubleshooting

Network ports & services list

Being a complex application there are several TCP/UDP port requirements for remote application usage. The session manager port requirements are as follows:

  1. Apache - TCP ports 80 & 443 (I would recommend disabling port 80 and requiring access through 443)
  2. MySQL - TCP port 3306 (disabling of outside access is fine due to session manager & web client using localhost for access)
  3. LMSocialServer - TCP port 1111 (This port is used for application server status updates and can be limited via local port filters to application servers only)

Configuration settings

The current configuration settings within the Ulteo session manager are as follows:

System settings

  1. System on maintenence mode - no
  2. Administration console language - autodetect
  3. Debug option list - info, warning, error & critical
  4. Cache logs update every - 30 seconds
  5. Cache logs expiry time - a day
  6. Default user group - .
  7. Domain integration - internal
  8. Maximum items per page - 100
  9. Maximum number of running sessions - 0
  10. Modules activation - ApplicationDB, ApplicationsGroupDB, AuthMethod, ProfileDB, SessionManagement, SharedFolderDB, UserDB, UserGroupDB, UserGroupDBDynamic

Server settings

  1. Disable reverse FQDN checking - yes
  2. Action when a server status is not ready anymore - switch to maintenence
  3. Auto-recover server - yes
  4. Remove orphan applications when the application server is deleted - yes
  5. Auto register new servers - yes
  6. Auto switch new servers to production mode - yes
  7. When an Application Server have reached its "max sessions" limit, disable session launch on it ? - yes

Domain integration settings

  1. Internal database profiles - internal

Authentication settings

  1. AuthMethod - CAS
  2. CAS Server URL - https://go.utah.edu:443/cas

Session settings

  1. Default mode for session - applications
  2. Default language for session - english
  3. Default timeout for session - 1 day
  4. User can launch a session even if some of his published applications are not available - yes
  5. User can use a console in the session - no
  6. Multimedia - yes
  7. Redirect client drives - full
  8. Redirect client printers - yes
  9. RDP bpp - 16
  10. Enhance user experience - yes
  11. Enable user profiles - yes
  12. Auto-create user profiles when non-existant - yes
  13. Launch a session without a valid profile - yes
  14. Enable shared folders - yes
  15. Launch a session even when a shared folder's fileserver is missing - yes
  16. Forceable paramaters by users - none
  17. Enable Remote Desktop - yes
  18. Sessions are persistent - yes
  19. Show icons on user desktop - yes
  20. Allow external applications in Desktop - yes
  21. Desktop type - any
  22. Servers which are allowed to start desktop - empty
  23. Enable Remote Applications - yes

Events settings

  1. Email address to send alerts to - User definable
  2. Server status changed - checked
  3. Session startup - checked
  4. SQL failure - checked

Web interface settings

  1. Display users list - no
  2. Public Webservices access - yes

Groups

Because we are using the CAS (common authentication service) a dynamic group must be configured to handle users coming from this service.

In order for this dynamic group configuration you must first enable the 'DynamicGroupDB' module. You can do this by this series of clicks.

  1. Login to the administration area
  2. Select Configuration
  3. System Settings
  4. Modules Activation
    1. Check 'DynamicGroupDB' option

    Now that the required module is enabled follow this series of clicks to create a dynamic group.

    1. Users
    2. Users Groups
      1. Create new group
      2. Dynamic
      3. Enter a unique name
      4. Add a unique description
      5. Cached # no
      6. Validation type # "at least one"
      7. Login stats with # "u"

      Publications

      In order for any of our CAS authenticated users (members of our new dynamic group) to use any of the applications the Ulteo software provides you must first create a list of published applications. The following series of clicks will do this.

      1. Publication Wizard
      2. Use usergroups
      3. Select dynamic group you just created
      4. Next
      5. Create group with applications
      6. Select any/all applications you wish to provide to this dynamic group
      7. Next
      8. Enter unique name
      9. Enter unique description
      10. Next
      11. Confirm

      Web client component

      The web client component is the access point that clients wishing to launch virtualized/remote applications will use. This component relies upon java applets once authentication has occured to load the requested piece of software. It can be found in /usr/share/ulteo/webclient.

      It uses the following locations.

      1. /etc/ulteo/webclient - The primary configuration for the webclient can be found here.
      2. /usr/share/ulteo/webclient - The webclient application including the Java applets, ajaxplorer etc can be found in this location.

      Current configuration changes

      Default to portal or application mode

      Force portal mode for clients edit /etc/ulteo/webclient/config.inc.php

      define('OPTION_FORCE_SESSION_MODE', 'applications'); 

      Force the default session manager URI

      You may wish to force the default session manager URL edit /etc/ulteo/webclient/config.inc.php

      define('SESSIONMANAGER_HOST', '[FQDN of session manager]'); 

      Application server(s)

      Linux

      The linux application server is used to provide the file system interface and mapping to local shares for the remote authenticated user. Below are details of the installed environement.

      Network ports & services list

      The linux application server & filesystem uses several processes to make up the whole. Included in the ulteo-ovd-subsystem processes are the following:

      TCP

      1. Apache - The apache webserver using TCP port 1113 (This port only needs to be accessible to & from the session manager)
      2. Python - A customized python client socket is open on TCP port 1112 (This port also only needs to be accessible to & from the session manager)
      3. NetBIOS - The netbios service initialized from the Samba service using TCP port 139 (This port is required for the file service for remote authenticated users)
      4. Xvnc - The Xvnc service listening on TCP port 5910 (This also needs to be accessable for remote authenticated users)
      5. Xrdp - The Xrdp service listening on TCP 3350 (This is only bound to the local loop back adapter or localhost address and does NOT need to be publicly accessible for remote authenticated users)
      6. Cups - The cupsd service listening on TCP port 631 (This also is only bound to the local loop back adapter or localhost and does NOT need to be publicly accessible for remote authenticated users)
      7. Samba - The SMB service is bound to TCP port 445 (This port only needs to be accessible from the configured application servers)
      8. RDP - The RDP (Remote Desktop Protocol) is bound to TCP port 3389 (This needs to be accessible from remote authenticated users)

      Service details

      Here are the details of the various files installed with the Ulteo-OVD subsystem (filesystem & application server) on a linux host.

      1. /etc/ulteo - The configuration file location for the Ulteo-OVD subsystem
      2. /var/log/ulteo - The log files for the Ulteo-OVD subsystem application server
      3. /opt/ulteo - The chroot environment used for the file system services as well as the application services

      Windows

      The windows application server is used by remote authenticated users to launch applications.

      Network ports & services list

      Being a complex application there are several TCP/UDP port requirements for remote application usage. The applicaiton port requirements are as follows:

      TCP

      1. epmap - TCP port 135 (This should only need to be accessible from the configured application servers)
      2. microsoft-ds - TCP port 445 (This should also only need to be accessible from the configured application servers)
      3. Python - TCP port 1112 (Also only needs to be accessible from the configured application servers)
      4. RDP - TCP port 3389 (This needs to be accessible from any authenticated user)

      UDP

      1. microsoft-ds - UDP port 445 (Accessible from configured application servers)
      2. isakmp - UDP port 500 (Also only accessible from the configured application servers
      3. ipsec-msft - UDP port 4500 (Also only accessible from the configured application servers
      4. netbios-ns - UDP port 137 (Accessible from configured application servers
      5. netbios-dgm - UDP port 138 (Also accessible from configured application servers

      Patching (to provide CAS authentication)

      As of this writing (2012-08) CAS authentication for the Ulteo-OVD software is broken. The phpCAS::Client performs a redirect to the CAS authentication service when no ST or PG ticket exists on the client. However due to the authentication form posting credentials to the sessionmanager which then generates an XML formatted query prior to performing this redirection header information does not work properly.

      The steps following will upgrade the current phpCAS module and implement the proper redirection based on the Ulteo-OVD CAS enabled options within the Ulteo-OVD admin interface.

      Latest patch

      Here is the latest [https://raw.github.com/jas-/ulteo/master/ulteo-latest-CAS.patch patch] which will update the phpCAS client included with the latest version of the Ulteo Session Manager. Please note that you must have the 'DynamicGroupDB' module enabled and also have defined a group using the DynamicGroupDB module as listed above for the Session Manager configuration section.

      %> wget https://raw.github.com/jas-/ulteo/master/ulteo-latest-CAS.patch 

      Make backup

      You should first make a backup of the /usr/share/ulteo folder. This folder contains the session manager and the web client (if installed on the same web server).

      %> cd /usr/share && tar zcvf ~/ulteo-backup.tgz ulteo/ 

      Apply patch

      In order to apply the patch to the latest Ulteo installation (v3.x) you must first remove the outdated phpCAS installation. This is why the backup in the previous step is crucial should something go wrong. To do this issue the following command.

      %> rm -frv /usr/share/ulteo/sessionmanager/PEAR/CAS* 

      Next simply apply the patch using the following command.

      %> cd /usr/share && patch -p0 < ~/2012-08-24.patch 

      Troubleshooting

      Here are some general troubleshooting guidelines to the various components that make up the Ulteo-OVD service.

      Session manager

      Server status

      Occasionally an application server status will be in a 'broken' state. Generally this refers to the application server process is no longer sending status updates to the session manager.

      When this type of proplem occurs a restart of the Ulteo-OVD application service must be restarted.

      Broken windows application server

      Here are some common problems encountered when using the Ulteo-OVD application server (v3.0.2) in a Windows 2003 server environement.

      Not listed in session manager

      If the Windows application server is not registering within the Ulteo-OVD session manager there are a couple of DNS errors that could be the cause of the problem.

      1. FQDN of session manager - During the installation process you were prompted to enter a session manager hostname, if an IP address was entered you may experience problems with the application server not registering with the session manager
      2. DNS A record - If the DNS A record of the session manager OR the application server is incorrect you may experience problems with the application server registering with the session manager

      In order to resolve these problems the following solutions may be applied.

      1. Use FQDN - Use of a FQDN (Fully Qualified Domain Name) during the installation is highly recommended.
      2. Static host entry - Although not recommended a static route can be added to the "C:\Windows\System32\drivers\etc\hosts" file and would look like this example.

      127.0.0.1 localhost 10.0.0.2 hostname.of.session.manager hostname 

      Exceptional condition

      Windows 2003 server error logs when handling exceptional conditions may return errors similar to the following in the event viewer. The error listed below is due to a problem with the XML formatted response from the session manager when recieving a status request. This error could be an indication of a man in the middle attack scenario because the application server is expecting an XML formatted query from the session manager.

      The instance's SvcRun() method failed Traceback (most recent call last): File "win32serviceutil.pyc", line 806, in SvcRun File "OVDWin32Service.pyc", line 95, in SvcDoRun File "ovd\SlaveServer.pyc", line 167, in loop_procedure File "ovd\SMRequestManager.pyc", line 169, in send_server_monitoring File "ovd\SMRequestManager.pyc", line 69, in get_response_xml IOError: (9, 'Bad file descriptor') %2: %3 

      The above error is caused by the following query from the session manager.

      [608] content type: text/html 

      And usually results in errors similar to the following:

      Windows saved user ULTEO-WIN2K3\OVDAdmin registry while an application or service was still using the registry during log off. The memory used by the user's registry has not been freed. The registry will be unloaded when it is no longer in use. This is often caused by services running as a user account, try configuring the services to run in either the LocalService or NetworkService account. 

      Although this scenario is rare, mitigation of this problem in the future is to modify the Ulteo-OVD application server to use a LocalServer or NetworkService account as stated in the error. This is possible by using the 'services' administrative panel to modify the running user account. However, due to problems with the system account used to run the service errors in creating profiles and mapping SID to the user accounts will fail due to privilege errors because the specified account must be able to create users & their associated profiles.

      As of this writing (2012-08-27) the ulteo service must be run as the 'OVDAdmin' user account (default user created during installation of the OVD Application server).

      To resolve this communication error between the Ulteo Application server and session manager the service must be stopped and restarted. You can use taskmanager or the administrative services managmement console to do this.

      Broken linux application server

      A linux application server serves dual roles. It first provides linux applications and it also provides file system drive & printer mapping to authenticated clients.

      1. Offline - If the application & file server is not available using the Ulteo-OVD administrative interface the ulteo-ovd-subsystem must be restarted.
      2. No file browser - If an authenticated user connects to the service and does not see a file browser it is due to the Ulteo-OVD SMBD service being down or that no Linux application & file server has been registered.
      3. Cannot save to desktop - If an authenticated user cannot save to their desktop it is because their current OS username does not match the authenticated username provided to the Ulteo-OVD service or the necessary samba file service to WebDAV folder mapping did not take place

      In most situations these problems can be resovled by simply restarting the Ulteo-OVD-subsystem (from a command line)

      %> sudo -c '/etc/init.d/ulteo-ovd-subsystem restart' 

      Broken Windows application server

      A windows application server provides remote application to authenticated clients using terminal services connections.

      1. Offline - If the windows application service is shown as broken or offline using the Ulteo-OVD sessionmanager administrative interface, or clients are not able to access windows applications, the Ulteo-OVD-slaveservice may need to be restarted. Use the Administrative Tools -> Services MMC snap-in to stop and restart the service. If the service cannot be restarted use the Task Manager to stop any OVD services and restart.
      2. Non-responsive - I have also witnessed situations where many of the child processes which the UlteoOVDSlaveServer.exe initializes upon session start become orphaned thereby consuming memory and critical system resources eventually leading to a crash or non-responsive service. This can be verified on the application server by examining the contents of the taskmanager.exe. Stopping all orphaned processes and restarting the ulteo service resolves the problem in 90% of occurrences. Occasionally you must restart the application server to resolve the problem however.

      Client authentication errors

      There exists a couple of conditions in regards to authentication. Below are details of these:

      1. Session exists - This condition presents itself when an authenticated session exists within the Ulteo-OVD session manager. When a client selects 'logoff' within the SessionManager interface this 'session destroy' command must replicate to the configured application servers. Occasionally the redirection from the CAS authentication service occurs faster than this replication process. To remedy simply allow a few minutes to pass before refreshing the Ulteo-OVD session manager page.
      2. Unknown error - This error (9-10) times is also a general error which occurs when a redirection to or from the CAS authentication service transpires faster then the session destroy replication from the Ulteo-OVD session manager to the configured application servers. If waiting a few minutes does not resolve the problem then an administrative user must manually destroy the users session.
      3. Session ended unexpectedly - This error has been experienced upon restoration of the Ulteo-OVD application & session manager from backup. Upon further examination of the configured application servers (which were destroying the session locally, thus forcing replication to the session manager and other application server) were not able to properly obtain the SID information for the authenticated user. A synchronization problem exists when profiles & user accounts exist within the application servers if a virtual machine was restored from backup. To resolve this problem use the Ulteo-OVD administrative interface to manually destroy the session as well as the associated user profile information.

      Additional recommendations (hardening the service)

      Because of the many components this is broken into sections each component such as the session manager or application server is broken down into the core services each provide.

      Session manager

      Here are some additional configuration options you may apply to the default session manager installation.

      Web server

      1. Use of ACL's (Session manager administrative control panel) - The use of an allowed/deny list should be used within the /etc/ulteo/sessionmanager/apache2-admin.conf to limit administrative access. An example follows:
      Alias /ovd/admin /usr/share/ulteo/sessionmanager/admin Options FollowSymLinks AllowOverride None Order allow,deny allow from 192.168.1.0/24 allow from 10.0.1.0/24 deny from all DirectoryIndex index.php php_admin_flag magic_quotes_gpc Off 
      NameVirtualHost *:1111 Listen 1111 RewriteEngine on RewriteCond % ^/(.+)/(.+)$ RewriteRule . /%1_%2.php [L] DocumentRoot /usr/share/ulteo/sessionmanager/webservices Order deny,allow deny from all allow from 192.168.1.10 #Linux application/file server allow from 192.168.1.11 #Windows application server allow from 192.168.2.0/24 #Or if you use an entire subnet for your application servers  
      SSLEngine on SSLCertificateFile /path/to/valid/signed/certificate.cer SSLCertificateKeyFile /path/to/valid/private/key/used/for/certificate/generation.key 
      And here is how to create the certificate request from a certificate authority:
      %> openssl genrsa -des3 -out server.key 1024 %> openssl req -new -key server.key -out server.csr 
      RewriteEngine on ReWriteCond % !^443$ RewriteRule ^/(.*) https://%/$1 [NC,R,L] 

      Database

      1. MySQL user - The default installation does not create and associate a user which can be used to access the MySQL database. This is strongly recommended and can be done with the examples show below:
      %> mysql -u root -p -e 'CREATE USER "[dbUser]"@"localhost" IDENTIFIED BY "[dbPassword]"' %> mysql -u root -p -e 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, ALTER ON `OVD`.* TO "[dbUser]"@"localhost"' %> mysql -u root -p -e 'FLUSH PRIVILEGES' 

      PHP

      The PHP interpeter can also be hardened with the assistance of the suhosin patch. To install simple run the following as a root user:

      %> apt-get install php5-suhosin 

      Once it is installed it is wise to configure it. Below are some options to harden this feature providing the maximum protection for the PHP interpreter.

        Executor options - The suhosin patch can be used to prevent things such as directory traversals, stack execution depths and white/black listing of specific PHP functions. Below are the 'minimum' options to be configured for this section.

      suhosin.executor.max_depth # 50 suhosin.executor.include.max_traversal # 5 suhosin.executor.disable_eval # on 

      I also highly recommend disabling the /e modifier available within the PCRE (perl compatible regular expression library) as they contain remote execute of scripts. However, this option requires modification of the PHP source code within the Ulteo-OVD software to remove all instances of the /e modifier used in the preg_match() function.

      suhosin.executor.disable_emodifier # on 
      suhosin.apc_bug_workaround # on 
      suhosin.session.encrypt # on suhosin.cookie.encrypt # on 

      Linux Application Server

      The linux application server provides several services which you may additionally configure using the recommendations below.

      Samba

      Additional configuration settings for the Samba file server service (within the chroot environment) may be used. Below are some guides:

        File types - Disabling specific file types using the 'veto files' configuration directive in the '/opt/ulteo/etc/samba/smb.conf' can be used like so (this example disables most common script types & executables):

      veto files /$RECYCLE.BIN/*.cpp/*.exe/*.sh/*.php/*.pl/*.bat/ 
      invalid users # daemon, bin, sys, sync, games, man, lp, mail 
      security # user 
      interfaces eth0 bind interfaces only yes socket options TCP_NODELAY 

      Apache

      Additional configuration settings may also be applied to the Apache web server service (also located within the chroot environment). Below are some recommendations:

        Hosts - The apache webserver can be hardened by restricting access through the use of the 'hosts allow' directive limiting access only to the currently configured session manager when sending requests. Keep in mind if you decide to enable this only the clients added to this whitelist would be able to access the mapped WebDAV fileshare. Here is an example configuration for the '/opt/ulteo/usr/share/ulteo/ovd/slaveserver.conf':

      NameVirtualHost *:1113 Listen 1113 DAVMinTimeout 600 DAVDepthInfinity On Alias /ovd/fs /var/lib/ulteo/ovd/slaveserver/fs DAV on AuthNAme "WebDAV Storage" AuthType Basic AuthUserFile /var/spool/ulteo/ovd/fs.dav.passwd Require valid-user AllowOverride AuthConfig Limit Order allow,deny allow from 192.168.1.0/24 allow from 10.0.1.0/24 deny from all  
      SSLEngine on SSLCertificateFile /path/to/valid/signed/certificate.cer SSLCertificateKeyFile /path/to/valid/private/key/used/for/certificate/generation.key 
      And here is how to create the certificate request from a certificate authority:
      %> openssl genrsa -des3 -out server.key 1024 %> openssl req -new -key server.key -out server.csr 

      cups Service

      1. Use of ACL's -The cups printing service may also be hardened with the use of access control lists. Much like ACL's in the Apache webservice limiting access to the cups service by allowed remote clients will aid in preventing unauthorized use. Below is an example configuration:
      # Restrict access to the server. Order allow,deny allow from 191.168.1.10 #Individual machine allowed access to the cups printing service allow from 192.168.2.0/24 #Entire subnet of allowed machines to the cups printing service deny from all 

      Windows Application Server

      The Windows Ulteo-OVD application server can also be further restricted, below are some available options for hardning the application server service on Windows (This guide was developed using Windows Server 2003).

      1. Terminal services - Terminal services should the following options enabled. You can modify these settings using the Administrative Tools -> Terminal Services Configuration MMC snap-in.
        1. Delete temporary folders on exit # Yes
        2. Use temporary folders per session # Yes
        3. Active Desktop # Disable
        4. Permission Compatibility # Full Security
        5. Restrict each user to one session # Yes
        1. OVDWin[arch]Service.exe - Edit the scope for this service to either use a custom list of allowed machines or restrict to the current subnet of the server

        Usage statistics

        The Ulteo graphing system is lacking. Use the following for more information:

        How to

        %> mysql -u root -p -e 'use ovd; CALL UlteoStatistics()' 

        Sample

        A sample output of statistics:

        +----------------+-----------------+-----------------+ | total_sessions | unique_sessions | average_session | +----------------+-----------------+-----------------+ | 1228 | 192 | 01:30:31 | +----------------+-----------------+-----------------+ 1 row in set (0.27 sec) +----------+--------------------+ | user | total_session_time | +----------+--------------------+ | u0368839 | 04:00:43 | | u0443761 | 03:11:41 | | u0519980 | 00:10:49 | | u0002727 | 00:10:39 | | u0201598 | 03:28:08 | | u0531567 | 00:19:49 | | u0109301 | 00:10:48 | | u0076374 | 00:15:37 | | u0002063 | 00:04:31 | | u0738045 | 00:15:04 | | u0644364 | 00:44:52 | | u0783746 | 00:04:34 | | u0736485 | 00:10:53 | | u0083707 | 00:09:52 | | u0833911 | 00:47:32 | | u0792022 | 00:43:43 | | u0204646 | 01:00:19 | | u0708304 | 01:57:58 | | u0373118 | 00:16:20 | | u0606723 | 00:10:51 | | u0778036 | 02:33:01 | | u0822975 | 04:38:05 | | u0806602 | 01:17:30 | | u0818635 | 01:15:53 | | u0821012 | 00:56:31 | | u0343164 | 00:04:41 | | u0734645 | 01:57:00 | | u0441973 | 00:49:03 | | u0629997 | 00:27:15 | | u0512515 | 01:51:55 | | u0692967 | 06:39:33 | | u0475478 | 00:14:40 | | u0669108 | 00:03:28 | | u0313033 | 00:28:33 | | u0745796 | 01:24:08 | | u0746109 | 01:00:34 | | u0532799 | 03:06:37 | | u0624747 | 00:08:56 | | u0706728 | 00:07:42 | | u0731353 | 00:10:09 | | u0632744 | 01:29:45 | | u0173913 | 01:24:07 | | u0625540 | 01:33:44 | | u0773457 | 00:28:33 | | u0118794 | 00:21:26 | | u0702728 | 00:00:44 | | u0030918 | 00:24:30 | | u0064349 | 02:12:16 | | u0532805 | 00:00:45 | | u0789117 | 02:22:40 | | u0854879 | 00:01:50 | | u0733760 | 02:29:11 | | u0754931 | 00:12:46 | | u0741080 | 00:10:53 | | u0686002 | 00:38:43 | | u0546149 | 02:01:33 | | u0757393 | 01:36:27 | | u0498238 | 05:43:30 | | u0789120 | 00:29:28 | | u0545206 | 00:00:39 | | u0678546 | 05:43:22 | | u0270784 | 03:20:12 | | u0748365 | 01:47:13 | | u0826476 | 07:55:34 | | u0536523 | 02:07:26 | | u0567198 | 08:43:03 | | u0060773 | 16:12:58 | | u0454832 | 00:10:39 | | u0820018 | 00:17:08 | | u0155731 | 00:50:30 | | u0535068 | 02:09:00 | | u0248886 | 01:23:36 | | u0540656 | 01:45:41 | | u0544678 | 02:01:30 | | u0672216 | 02:54:27 | | u0545115 | 00:11:34 | | u0166092 | 01:11:20 | | u0549985 | 04:43:18 | | u0173800 | 00:54:15 | | u0640744 | 00:13:51 | | u0415209 | 01:42:24 | | u0614516 | 00:16:21 | | u0817168 | 05:18:14 | | u0549644 | 00:17:59 | | u0687118 | 04:36:32 | | u0597728 | 02:09:00 | | u0493884 | 02:13:58 | | u0595081 | 00:05:39 | | u0565447 | 00:39:32 | | u0225212 | 01:17:08 | | u0713708 | 01:00:15 | | u0820752 | 02:03:40 | | u0635246 | 01:26:38 | | u0008846 | 01:00:11 | | u0465391 | 01:38:42 | | u0531664 | 08:33:03 | | u0669900 | 01:28:45 | | u0799203 | 00:05:43 | | u0576021 | 00:47:49 | | u0345651 | 00:25:14 | | u0738543 | 00:27:45 | | u0766570 | 03:10:26 | | u0825063 | 00:15:23 | | u0528430 | 00:21:42 | | u0328312 | 00:10:23 | | u0074061 | 08:01:49 | | u0686906 | 00:10:21 | | u0234664 | 00:42:23 | | u0822118 | 00:19:37 | | u0314760 | 00:01:22 | | u0746749 | 00:10:31 | | u0208801 | 00:22:19 | | u0809134 | 00:00:31 | | u0542020 | 00:00:20 | | u0664455 | 00:36:05 | | u0524231 | 00:10:49 | | u0595019 | 07:54:59 | | u0842605 | 00:28:07 | | u0823153 | 00:01:07 | | u0617248 | 00:11:00 | | u0821038 | 01:43:21 | | u0833323 | 00:11:10 | | u0666104 | 00:42:55 | | u0707313 | 17:55:38 | | u0790485 | 00:25:30 | | u0848181 | 00:21:21 | | u0495609 | 00:23:43 | | u0615486 | 00:00:30 | | u0102005 | 00:02:18 | | u0574025 | 01:18:28 | | u0080920 | 02:13:01 | | u0661753 | 00:13:20 | | u0617850 | 00:04:48 | | u0351555 | 05:00:43 | | u0823041 | 00:09:54 | | u0745839 | 00:16:04 | | u0820613 | 00:20:33 | | u0390491 | 01:05:18 | | u0822042 | 01:00:27 | | u0694496 | 00:10:43 | | u0634916 | 00:10:46 | | u0741592 | 00:19:55 | | u0529609 | 00:11:37 | | u0825387 | 00:51:53 | | u0628074 | 00:02:56 | | u0561678 | 00:23:00 | | u0682491 | 00:02:00 | | u0480590 | 01:58:31 | | u0105540 | 00:00:41 | | u0245036 | 04:52:29 | | u0250882 | 00:11:52 | | u0855519 | 00:21:06 | | u0711473 | 00:37:12 | | u0775029 | 15:04:22 | | u0480765 | 00:10:45 | | u0698188 | 00:10:16 | | u0649439 | 00:42:21 | | u0850882 | 00:10:29 | | u0826477 | 00:21:05 | | u0755738 | 00:14:19 | | u0518593 | 00:01:09 | | u0871715 | 03:51:40 | | u0570163 | 00:10:38 | | u0749641 | 00:03:22 | | u0216274 | 00:10:39 | | u0355317 | 03:06:32 | | u0486496 | 00:06:48 | | u0619875 | 00:03:39 | | u0556861 | 00:01:46 | | u0824176 | 00:43:27 | | u0678428 | 00:10:37 | | u0235960 | 06:37:25 | | u0791861 | 01:10:41 | | u0827666 | 00:11:56 | | u0816158 | 00:10:47 | | u0697901 | 00:16:20 | | u0541843 | 03:36:52 | | u0251730 | 00:04:56 | | u0544763 | 00:21:51 | | u0696038 | 00:24:21 | | u0358106 | 00:10:26 | | u0818438 | 01:08:08 | | u0241888 | 00:10:54 | | u0064587 | 02:45:37 | | u0743801 | 00:11:29 | | u0780745 | 00:21:06 | | u0663102 | 00:39:49 | | u0606438 | 01:27:39 | | u0704018 | 03:15:46 | | u0615946 | 00:37:24 | | u0342942 | 00:01:28 | | u0734310 | 00:12:07 | +----------+--------------------+ 

        Stored procedure & view re-creation

        If you have to re-create it the following will work (here as a backup):

        %> mysql -u root -p mysql> CREATE OR REPLACE VIEW `statistics` AS SELECT `user`, UNIX_TIMESTAMP(`start_stamp`) AS start, UNIX_TIMESTAMP(stop_stamp) AS stop FROM `ulteo_sessions_history`; mysql> DELIMITER // DROP PROCEDURE IF EXISTS UlteoStatisticsTmp// CREATE DEFINER='root'@'localhost' PROCEDURE UlteoStatisticsTmp() DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Creates temporary tables for statistics operations' BEGIN DROP TABLE IF EXISTS `processing`; CREATE TEMPORARY TABLE IF NOT EXISTS `processing`( `user` CHAR(32) NOT NULL, `time` INT(20) NOT NULL, UNIQUE KEY `user` (`user`) ); END// DROP PROCEDURE IF EXISTS UlteoStatistics// CREATE DEFINER='root'@'localhost' PROCEDURE UlteoStatistics() DETERMINISTIC SQL SECURITY INVOKER COMMENT 'Retrieves and calculates usage statistics' BEGIN DECLARE c BOOLEAN DEFAULT FALSE; DECLARE usr CHAR(32) DEFAULT NULL; DECLARE st INT(20) DEFAULT NULL; DECLARE stp INT(20) DEFAULT NULL; DECLARE ops CURSOR FOR SELECT `user`, `start`, `stop` FROM `statistics`; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c # TRUE; SELECT COUNT(`user`) FROM `statistics` INTO @total_sessions; CALL UlteoStatisticsTmp; OPEN ops; read_loop: LOOP FETCH OPS INTO usr, st, stp; IF c THEN CLOSE ops; LEAVE read_loop; END IF; SELECT SUM(stp - st) INTO @time; SET @sql # CONCAT('INSERT INTO `processing` SELECT "',usr,'" AS user, "',@time,'" AS time ON DUPLICATE KEY UPDATE `time` # `time` + "',@time,'"'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; DELETE FROM `processing` WHERE `user` # "u0072039" OR `user` LIKE "test%" OR `user` # "jeff" OR `user` # "u0368839"; SELECT MIN(start_stamp) FROM `ulteo_sessions_history` INTO @since; SELECT COUNT(`user`) FROM `processing` INTO @total_unique_users; SELECT SEC_TO_TIME(AVG(`time`)) FROM `processing` INTO @average_session_time; SELECT @total_sessions AS total_sessions, @total_unique_users AS unique_sessions, @average_session_time AS average_session, @since AS Since; SELECT `user`, SEC_TO_TIME(`time`) AS total_session_time FROM `processing`; DROP TEMPORARY TABLE IF EXISTS `processing`; CLOSE ops; END// DELIMITER ; 

        Session XML example

        Once an authenticated session is initialized the following XML example is returned to the client so that the Java applet can initialize connections over RDP to the allowed list of applications and their corresponding server.