- Check 'DynamicGroupDB' option
Now that the required module is enabled follow this series of clicks to create a dynamic group.
- Users
- Users Groups
- Create new group
- Dynamic
- Enter a unique name
- Add a unique description
- Cached # no
- Validation type # "at least one"
- 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.
- Publication Wizard
- Use usergroups
- Select dynamic group you just created
- Next
- Create group with applications
- Select any/all applications you wish to provide to this dynamic group
- Next
- Enter unique name
- Enter unique description
- Next
- 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.
- /etc/ulteo/webclient - The primary configuration for the webclient can be found here.
- /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
- Apache - The apache webserver using TCP port 1113 (This port only needs to be accessible to & from the session manager)
- 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)
- 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)
- Xvnc - The Xvnc service listening on TCP port 5910 (This also needs to be accessable for remote authenticated users)
- 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)
- 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)
- Samba - The SMB service is bound to TCP port 445 (This port only needs to be accessible from the configured application servers)
- 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.
- /etc/ulteo - The configuration file location for the Ulteo-OVD subsystem
- /var/log/ulteo - The log files for the Ulteo-OVD subsystem application server
- /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
- epmap - TCP port 135 (This should only need to be accessible from the configured application servers)
- microsoft-ds - TCP port 445 (This should also only need to be accessible from the configured application servers)
- Python - TCP port 1112 (Also only needs to be accessible from the configured application servers)
- RDP - TCP port 3389 (This needs to be accessible from any authenticated user)
UDP
- microsoft-ds - UDP port 445 (Accessible from configured application servers)
- isakmp - UDP port 500 (Also only accessible from the configured application servers
- ipsec-msft - UDP port 4500 (Also only accessible from the configured application servers
- netbios-ns - UDP port 137 (Accessible from configured application servers
- 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.patchMake 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.patchTroubleshooting
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.
- 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
- 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.
- Use FQDN - Use of a FQDN (Fully Qualified Domain Name) during the installation is highly recommended.
- 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 hostnameExceptional 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: %3The above error is caused by the following query from the session manager.
[608] content type: text/htmlAnd 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.
- Offline - If the application & file server is not available using the Ulteo-OVD administrative interface the ulteo-ovd-subsystem must be restarted.
- 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.
- 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.
- 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.
- 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:
- 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.
- 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.
- 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
- 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/adminOptions 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 1111RewriteEngine 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
And here is how to create the certificate request from a certificate authority:SSLEngine on SSLCertificateFile /path/to/valid/signed/certificate.cer SSLCertificateKeyFile /path/to/valid/private/key/used/for/certificate/generation.key%> openssl genrsa -des3 -out server.key 1024 %> openssl req -new -key server.key -out server.csrRewriteEngine on ReWriteCond % !^443$ RewriteRule ^/(.*) https://%/$1 [NC,R,L]Database
- 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-suhosinOnce 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 # onI 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 # onsuhosin.apc_bug_workaround # onsuhosin.session.encrypt # on suhosin.cookie.encrypt # onLinux 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, mailsecurity # userinterfaces eth0 bind interfaces only yes socket options TCP_NODELAYApache
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 1113DAVMinTimeout 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
And here is how to create the certificate request from a certificate authority:SSLEngine on SSLCertificateFile /path/to/valid/signed/certificate.cer SSLCertificateKeyFile /path/to/valid/private/key/used/for/certificate/generation.key%> openssl genrsa -des3 -out server.key 1024 %> openssl req -new -key server.key -out server.csrcups Service
- 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).
- Terminal services - Terminal services should the following options enabled. You can modify these settings using the Administrative Tools -> Terminal Services Configuration MMC snap-in.
- Delete temporary folders on exit # Yes
- Use temporary folders per session # Yes
- Active Desktop # Disable
- Permission Compatibility # Full Security
- Restrict each user to one session # Yes
- 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.
