EdgeSight: Filtering a query by IP Subnet

es_logoPrologue
Many of EdgeSight’s tables and views have a field for the client’s IP address, and this is stored as variable-length character string (varchar or nvarchar). In order to sort or filter on this field you must use a complex regular expression or find a way to split the field into different octets. In this blog post, we will do just that by presenting a problem that requires finding users based on their subnet…

Intro
Thanks to the vibrant competition present in the virtualization space, many Engineers find themselves always transitioning to the next version of their virtualization solution. During such a transition, management (and hopefully the engineers) want to know who’s using the new system and if users are still accessing the old one. In many cases this can be a trivial exercise, but for this scenario we’ll make it more complex.

Scenario
The networking team has intelligently organized its user’s locations by subnet. In fact, due to number of users and available IP’s, each floor at the main location has it’s own subnet. Recently, Citrix users at the main location were transitioned to the new environment, except for a subset who had legacy applications that would not work in the new Citrix farm. Management wants to know many of the transitioned users are using the new system.

Problem
Since we are using published desktops in both the old and new Citrix environments, EdgeSight (version 5.3) does not provide an easy way to query desktop launches (see this post on EdgeSight Under the Hood for how to get a query of published desktop launches). In this case, we have a different naming schema for the servers in the new farm, but since there are many different locations connecting back to our Citrix farms, we need to just select the users at the main location. This will require us to filter the users based on their IP subnet.

PARSENAME
While researching this issue I found that dealing with IP addresses in Transact-SQL is a common problem. Luckily there is a built-in function called PARSENAME that parses object names like ‘servername.databasename.schemaname.objectname’. Since IPv4 addresses follow the same convention, you can reference each part of the octet in an IP address.

For example:

DECLARE @IP nvarchar(15)
SET @IP = '192.168.1.1'
SELECT PARSENAME(@IP,4) AS 'Octet 1',
PARSENAME(@IP,3)AS 'Octet 2',
PARSENAME(@IP,2)AS 'Octet 3',
PARSENAME(@IP,1)AS 'Octet 4'

Gives us:

Octet 1    Octet 2    Octet 3    Octet 4
---------- ---------- ---------- ----------
192        168        1          1

(1 row(s) affected)


The query

For this query we will use vw_ctrx_archive_server_start_perf which has become my goto view for client related information and just sort by one subnet: 192.168.1.0 – 192.168.1.101 and look at the last 3 days of data

DECLARE @today datetime
SET @today = CONVERT(varchar(10),getdate(),111)
--we are using DATEADD and offsetting by minus four hours due to Eastern Daylight Time
SELECT CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111) as 'Date', ([user]) as 'User'
FROM vw_ctrx_archive_server_start_perf
WHERE CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111) >= @today-3 --past 3 days
and [user] <> 'UNKNOWN'
--Gets NEWSERVER01, NEWSERVER02, etc
and machine_name like 'NEWSERVER%
and PARSENAME(client_address,4) = '192'
and PARSENAME(client_address,3) = '168'  and (PARSENAME(client_address,2) = 1 and PARSENAME(client_address,1) between 0 and 101
GROUP BY CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111), [user]
ORDER BY CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111)

For our second example, we’ll sort with 11 sub-nets:
192.168.1.0 – 192.168.1.101
192.168.2.0 – 192.168.2.102
192.168.3.0 – 192.168.3.103
192.168.4.0 – 192.168.4.104
192.168.5.0 – 192.168.5.105
192.168.6.0 – 192.168.6.106
192.168.7.0 – 192.168.7.107
192.168.8.0 – 192.168.8.108
192.168.9.0 – 192.168.9.109
192.168.10.0 – 192.168.10.110
192.168.11.0 – 192.168.11.121

DECLARE @today datetime
SET @today = convert(varchar(10),getdate(),111)
SELECT CONVERT(varchar(10),dateadd(hh,-4,time_stamp), 111) as 'Date', ([user]) as 'User'
FROM vw_ctrx_archive_server_start_perf
WHERE CONVERT(varchar(10),dateadd(hh,-4,time_stamp), 111) >= @today-3
and [user] <> 'UNKNOWN'
and machine_name like 'NEWSERVER%'
and PARSENAME(client_address,4) = '192'
and PARSENAME(client_address,3) = '168'
and (PARSENAME(client_address,2) =1 and PARSENAME(client_address,1) between 0 and 101
or PARSENAME(client_address,2) =2 and PARSENAME(client_address,1) between 0 and 102
or PARSENAME(client_address,2) =3 and PARSENAME(client_address,1) between 0 and 103
or PARSENAME(client_address,2) =4 and PARSENAME(client_address,1) between 0 and 104
or PARSENAME(client_address,2) =5 and PARSENAME(client_address,1) between 0 and 105
or PARSENAME(client_address,2) =6 and PARSENAME(client_address,1) between 0 and 106
or PARSENAME(client_address,2) =7 and PARSENAME(client_address,1) between 0 and 107
or PARSENAME(client_address,2) =8 and PARSENAME(client_address,1) between 0 and 108
or PARSENAME(client_address,2) =9 and PARSENAME(client_address,1) between 0 and 109
or PARSENAME(client_address,2) =10 and PARSENAME(client_address,1) between 0 and 110
or PARSENAME(client_address,2) =11 and PARSENAME(client_address,1) between 0 and 121)
GROUP BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111), [user]
ORDER BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111)

Hopefully this will provide you with some more options when you need to present data from your EdgeSight database. As always I welcome any and all questions and comments.

Thanks,
Alain

Advertisements

EdgeSight: Servers Not Appearing in Console

Recently we started migrating users to our new production environment which is provisioned.  I found that only half of the new servers were reporting to EdgeSight.  Initially I thought it was due to an issue solved by this EdgeSight agent hotfix, but this turned out to not be the case.

Digging deeper, I found that the missing servers had the same server name in the \Citrix\SystemMonitoring\Data\EdgeSight.ini file.  The quick fix was to stop the EdgeSight service (Citrix System Monitoring), delete the INI file, and restart the service.  I then forced a Configuration Check and Performance Upload on the worker agents and the servers appeared in the console.

Since these were provisioned servers and the EdgeSight data writes to a “cache” drive, the long-term fix is to mount the “cache” drive associated with our template and delete the INI file, then new servers will get a fresh INI file.

Here’s a list of EdgeSight Troubleshooting articles from Citrix

CTX111043: Newly Installed EdgeSight Agent Devices Do Not Report Up

CTX114939: Troubleshooting EdgeSight

CTX123446: Real Time Remote Report Error: Access denied: You do not have permission to access this resource

CTX123293: EdgeSight Remote, Troubleshoot, and Real-time Reports Error Messages

CTX118565: EdgeSight 5.0 Frequently Asked Questions

CTX115712: No User Data for EdgeSight EndPoint Reports

CTX115855: Citrix Presentation Servers Imaged with EdgeSight Agent are Not Reporting to EdgeSight

Thanks,
Alain

Article: End User Experience Monitoring Statistics Explained

I dive into many tables and views that are part of the EdgeSight database to produce custom reports for my co-workers and superiors.  Many of the metrics are easy to understand and many more are a little obtuse.  Here is a great CTX article that fully explains the metrics related to End User Experience Monitoring.

Source: CTX114495 – End User Experience Monitoring Statistics Explained – Citrix Knowledge Center

Updated: End User Experience Monitoring Data

Summary

This article describes the metrics for the Session Experience Monitoring Service (SEMS).

Background

EdgeSight for Presentation Server provides highly granular session experience monitoring data collected through Presentation Server and Presentation Server Client instrumentation. Data is collected through the SEMS. The following metrics are stored in the EdgeSight Agent database and can be displayed through the Presentation Server User Summary remote report.

Note: Data that is uploaded to the server and displayed in historical reports is listed in the Historical Report Counters and data fields.

The collection of SEMS data is dependent on the version of the EdgeSight Agent, Presentation Server, and Presentation Server Client installed, as described in the Session Experience Monitoring Metrics help topic under Working with Reports within the EdgeSight Server Console.

Note: Presentation Server 4.5 Enterprise or Platinum Edition or later is required to capture SEMS data.

This process is described below:

1. The Presentation Server Client and server running Presentation Server negotiate capabilities during session creation.

2. The launch mechanism and metrics are captured by Web Interface/Program Neighborhood Agent and recorded in the ICA file downloaded to the client.

3. The client and server metrics are sent to the SEMS (SemsService.exe) and are stored in memory.

4. The End User Experience Monitoring (EUEM) metrics are published to EdgeSight Agent and stored locally in the Firebird database along with other metrics.

Refer to the following graphics for more information:

Note: All time measurements are in milliseconds unless otherwise noted.

Session performance metrics:

AVG_NETWORK_LATENCY
Network latency is the detected network latency between the Presentation Server Client device and the server running Presentation Server. Unlike the ICA round trip metric, the network round trip is largely independent of processing time on the client or server.

AVG_ROUND_TRIP_TIME
The ICA round trip time. The average time interval measured at the client between the first step (user action) and the last step (graphical response is displayed).

INPUT_BANDWIDTH_USED
The actual bandwidth consumed on the network (Presentation Server Client to Presentation Server) in bits per second. This measurement is taken at the server end of the connection by counting the actual bytes that flow to and from the server running Presentation Server for each user’s session. It measures traffic on the wire (after optimization and compression has taken place). The network bandwidth used is averaged across the ICA round trip check period (by default, the Presentation Server Client initiates an ICA round trip check every 15 seconds).

OUTPUT_BANDWIDTH_USED
The actual bandwidth consumed on the network (Presentation Server to Presentation Server Client) in bits per second. This measurement is taken at the server end of the connection, counting the actual bytes that flow both to and from the server running Presentation Server for each user’s session. It measures traffic on the wire (after optimization and compression has taken place). The network bandwidth used is averaged across the ICA round trip check period.

Session performance data (ICA round trip):

Note: Metrics on the duration of a portion of the handling of a graphical frame during the measurement of ICA roundtrip time (EUEM trigger, frame cut, frame send, first draw, and Winstation Driver trigger) are included in this report. A frame is the unit of graphical transfer from the server to the client. Note that depending on the trigger for the graphical frame, not all of these metrics will be present for each ICA round trip.

SESSION_START
The time that the session was started.

SESSION_ID
Session identifier.

CLIENT_NAME
Name of the client associated with the session.

CLIENT_ADDRESS
Address of the client associated with the session.

CLIENT_VERSION
Version of the client associated with the session.

DOMAIN_NAME
Name of the domain to which the user belongs.

ACCOUNT_NAME
The user account name.

PROTOCOL_TYPE
The type of protocol used for the session.

WD_TRIGGER_ROUND_TRIP
The time from the start of the frame until frame handling is complete. This metric is collected when the trigger for the frame is a timeout in the Winstation Driver.

EUEM_TRIGGER_ROUND_TRIP
The time from the start of the frame until frame handling is complete. This metric is collected when an ICA round trip measurement occurs.

ICA_NETWORK_LATENCY
The detected network latency between the Presentation Server Client device and the server running Presentation Server.

FIRST_DRAW_ROUND_TRIP
The time from the start of the frame until frame handling is complete. This metric is collected when the frame trigger is the receipt of a graphical operation from an application.

ICA_INPUT_BANDWIDTH_USED
The actual bandwidth consumed on the network (Presentation Server Client to Presentation Server) in bits per second.

INPUT_BANDWIDTH_AVAILABLE
The bandwidth available on the network (Presentation Server Client to Presentation Server) in bits per second.

ICA_ROUND_TRIP
The time interval measured at the client between the first step (user action) and the last step (graphical response displayed). This metric can be thought of as a measurement of the screen lag that a user experiences while interacting with an application hosted in a session on a server running Presentation Server.

FRAME_CUT_ROUND_TRIP
The time from the start of the frame until the point at which the frame is complete.

FRAME_SEND_ROUND_TRIP
The time from the start of the frame until the completion of sending frame data to the client.

ICA_OUTPUT_BANDWIDTH_USED
The actual bandwidth consumed on the network (Presentation Server to Presentation Server Client) in bits per second.

OUTPUT_BANDWIDTH_AVAILABLE
The bandwidth available on the network (Presentation Server to Presentation Server Client) in bits per second. A value of 0 indicates that no data was available.

SYSTEMTIME
The time when the data was collected, as recorded on the system running the EdgeSight Agent.

Session client startup data:

LOGON_TIME
The time that the user logged on to start the session.

APP_NAME
The name of the published application.

LAUNCH_TYPE
The type of session launch. The values are listed below:

0—Unknown
1—Obsolete (This could be a captured ICA file being used through file-type association.)
2—Web Interface
3—Program Neighborhood
4—Program Neighborhood Agent
5—Web portal (Advanced Access Control or MetaFrame Secure Access Manager)

LAUNCH_TYPE_ID
Launch type identifier (0-5), indicating a type of session launch as shown above under LAUNCH_TYPE.

SESSION_ID
The session identifier.

SESSION_SHARED_FLAG
A flag indicating whether a pre-existing session is being shared for this application launch.

SCCD – STARTUP_CLIENT
This is the high-level client connection startup metric. It starts as close as possible to the time of the request (mouse click) and ends when the ICA connection between the client device and server running Presentation Server has been established. In the case of a shared session, this duration will normally be much smaller, as many of the setup costs associated with the creation of a new connection to the server are not incurred.

CFDCD – CONFIG_FILE_DOWNLOAD_CLIENT
The time it takes to get the configuration file from the XML server.

BUCC – BACKUP_URL_CLIENT_COUNT
The number of backup URL retries before success. Note that this is the only start-up metric that is a count of attempts, rather than a duration.

AECD – APPLICATION_ENUM_CLIENT
The time it takes to get the list of applications.

COCD – CREDENTIALS_OBTENTION_CLIENT
The time it takes to get the user credentials. Note that COCD is only measured when credentials are entered manually by the user.

IFDCD – ICA_FILE_DOWNLOAD_CLIENT
The time it takes the client to download the ICA file from the Web server for Program Neighborhood Agent or Web Interface.

NRWD – NAME_RESOLUTION_WEB_SERVER
The time it takes the XML Service to resolve the name of a published app to a presentation server address. This metric is collected when the application is launched through the Program Neighborhood Agent or Web Interface.

RECONNECT_ENUM_CLIENT
The time it takes a client to get a list of reconnections.

RECONNECT_ENUM_WEB_SERVER
The time it takes the Web Interface to get the list of reconnections from the XML Service.

TRWD – TICKET_RESPONSE_WEB_SERVER
The time it takes to get a ticket (if required) from the STA server or XML Service. This metric is collected when the application is launched via the Program Neighborhood Agent or Web Interface.

LPWD – LAUNCH_PAGE_WEB_SERVER
The time it takes to process the launch page (launch.aspx) on the Web Interface server.

SCD – SESSION_CREATION_CLIENT
New session creation time, from the moment wfica32.exe is launched to when the connection is established.

NRCD – NAME_RESOLUTION_CLIENT
The time it takes the XML Service to resolve the name of a published application to an IP address. This metric is only collected for new sessions, and only if the ICA file does not specify a connection to a Presentation Server with the IP address already provided.

SLCD – SESSION_LOOKUP_CLIENT
The time it takes to query every ICA session to host the requested published application. The check is performed in the client to determine whether the application launch request can be handled by an existing session. A different method is used depending on whether the sessions are new or shared.

Session Server Startup Data:

LOGON_TIME
The time that the user logged on to start the session.

SESSION_ID
The session identifier.

SESSION_CREATION_STARTED
The time that session creation started.

SESSION_CREATION_ENDED
The time that session creation was completed.

SSSD – SESSION_STARTUP_SERVER
This is the high-level server connection startup metric. This includes the time spent on the Presentation Server performing the entire startup operation. In the event of an application starting in a shared session, this metric is expected to be much smaller, as starting a completely new session involves potentially high-cost tasks, such as profile loading and logon script execution.

CASD – CREDENTIALS_AUTH_SERVER
The time spent on the server authenticating the user credentials.

CONSD – CREDENTIALS_OBT_NETWORK_SVR
The time spent by the server performing network operations to obtain credentials for the user. This only applies to a Security Support Provider Interface (SSPI) logon (a form of pass-through authentication where the client device is a member of the same domain as the server and Kerberos tickets are passed in place of manually entered credentials).

COSD – CREDENTIALS_OBT_SERVER
This is also called COSD. It is the time taken for the server to obtain the user credentials. This is only likely to be a significant amount of time if a manual logon is used and the server-side credentials dialog is displayed (or if a legal notice is displayed before the logon commences).

PNCOSD – PNC_CREDENTIALS_OBT_SERVER
The time taken for the server to cause the Program Neighborhood instance running on the client (Program Neighborhood Classic) to obtain the user credentials. This credentials dialog is displayed and managed by the client side, but the duration is measured on the server.

DMSD – DEVICE_MAPPING_SERVER
The time spent on the server mapping the user’s client drives, devices, and ports.

LSESD – LOGIN_SCRIPT_EXEC_SERVER
The time spent on the server running the user’s logon script(s).

PCSD – PRINTER_CREATION_SERVER
The time spent on the server synchronously mapping the user’s client printers. If the configuration is set such that this printer creation is performed asynchronously, no value is recorded; it does not affect the completion of the session startup.

PLSD – PROFILE_LOAD_SERVER
The time spent on the server loading the user’s profile.

SCSD – SESSION_CREATION_SERVER
The time spent on the server creating the session. The duration starts when the Presentation Server Client connection is opened and ends when authentication begins. This should not be confused with the overall Session Startup Server duration.

Thanks,
Alain

End User Experience Monitoring Data