All posts by Alain

I’m a technology professional who’s worked in IT for 20+ years. In the last few years, I’ve concentrated in virtual technologies from Citrix, VMware, and Microsoft. I’ve had experience working in diverse environments such as architecture, small business, manufacturing/distribution, medical (hospital), government (federal), industrial (chemical), healthcare technology and most recently financial.

Director Under the Hood: New Users

Intro

Director is Citrix’s new metrics and monitoring dashboard. The interface is modern and the emphasis is on real-time information about your users. It consolidates information about your environment and makes it easy to differentiate between applications and desktops. If your only experience has been with EdgeSight in the past then you’ll see Director as a breath of fresh air.

There’s a lot of good views and data in the new Citrix Director and the “one pane of glass” view of your environment is pursued by all 3rd party monitoring, reporting, and alerting vendors. Unfortunately, it’s not easy to get all the same data I’ve gathered in past from the Director database. In this post we’ll look at tracking new users connecting to your Citrix environment.

For information on the database schema…read my previous article on Director.

New Users

I collect lots of metrics to report on my environment. One of the ones I track is the number of new users that connect to my Citrix environment. I view this metric as speaking to the overall adoption rate of my Citrix platform as well as a leading indicator for growth. Can we find this info in the Director Trends dashboard?

The short answer is no. The long answer is noooooooooooooooooooooooooooooo. In fact, it is not possbile to track this in EdgeSight. In a previous job, we worked around this by adding a USER table to the Edgesight database and then ran a query to compare the unique users who logged in that past month against the USER table. Who ever did not show up in the USER table was considered new.

SELECT distinct [user]
FROM vw_ctrx_archive_server_start_perf AS ESdata
WHERE [user] <> 'UNKNOWN'
and convert(varchar(10),time_stamp,111) between '2016/05/01'
and '2016/05/31'
and (NOT EXISTS
(SELECT distinct userid
FROM userarchive
WHERE (userarchive.userid = ESData.[user]))) order by [user]

The above query gets all the unqiue users who logged in between May 1st and May 31st (using the Edgesight view: vw_ctrx_archive_server_start_perf). It then compares this list against the userarchive table that we created to store the username and some other data about our users. Thus we got  a count of new users to our Citrix environment. Once we completed our monthly reporting, we added these new users to the userarchive table.

You say, “That’s great Alain. Wow! How the heck do I do this in Director?”

I say…

“SQL To the Rescue!”

For this query I’m using only one table:

MonitorData.User (Table)
image

I select the month and year and then count the usernames for that month and year. The great thing about this table is that it only creates a new row the first time a user connects to the system automatically. So, the following query will give you a easy way to see the new users who connected to your Citrix envrionment.

SELECT convert(char(9),datename(month,CreatedDate)) + ' '
+ convert(char(4),datepart(year,CreatedDate)) as 'Month',
count (Username) as 'New Users'
FROM MonitorData.[User]
GROUP BY convert(char(9),datename(month,CreatedDate)) + ' '
+ convert(char(4),datepart(year,CreatedDate))

MonitorData.User_query

In conclusion

I hope this encourages you to take a look under the hood of Director to see what you can get out of it. The database infrastructure is much, much simpler than EdgeSight and should provide a lot of good detail.

Thanks,
Alain

Director Under the Hood: Total Sessions and Unique Users Per Day

Intro

Director is Citrix’s new metrics and monitoring dashboard. The interface is modern and the emphasis is on real-time information about your users. It consolidates information about your environment and makes it easy to differentiate between applications and desktops. If your only experience has been with EdgeSight in the past then you’ll see Director as a breath of fresh air.

There’s a lot of good views and data in the new Citrix Director and the “one pane of glass” view of your environment is pursued by all 3rd party monitoring, reporting, and alerting vendors. Unfortunately, it’s not easy to get all the same data I’ve gathered in past from the Director database. In this post we’ll look at a query to show you the total sessions and unique users per day.

This is it…really?

The tables that make up the Director Database

The views that make up the Director Database

image image

After years of pouring through and querying EdgeSight’s tables and views, I first thought that something must be wrong. This can’t be all there is to the Director database, but that’s all there is.  Before we dive into SQL, let’s see what we can find using the Director GUI. I like to collect lots of metrics when I report on my environment. The 3 main session metrics I track are concurrent user per day, unique users per day and total sessions per day. Can we find this info in the Director Trends dashboard?

I set the Time period to Last Month and then set to custom ending to 10/1/2015. This should give me data for September 2015. Here’s what we get:

image

NOTE: For these examples, I’m looking at all delivery groups. You can limit your view by delivery group if you wanted to track metrics for different groups of users.

As you can see, we get a pretty graph, but we have to export the data to Excel to get precise detail:

image

What this doesn’t show us it how many sessions and unique users there are per day. The only way to get this using the Director interface is to click on a point on the graph to see the session details. This will only work for more recent time period.

image

SQL To the Rescue

For this query I’m using the following tables/views:

MonitorData.SessionV1 (View) MonitorData.Connection (Table) MonitorData.User (Table)
image image image

I’m linking the SessionV1 and Connection SessionKey columns together and the User.id and SessionV1.userid columns together. This ensures that I’m grouping the same sessions and users together (users can have more than one session). Then I group by the LogOnStartDate and count the distinct sessionkeys and distinct userids. This gives me the total sessions and unique users per day.
This query will pull all available data and total the sessions and unique users per day.

select convert(varchar(10),LogOnStartDate,111) as 'Date', count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions', count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
group by convert(varchar(10),LogOnStartDate,111)
order by convert(varchar(10),LogOnStartDate,111)

image
The following query is similar, but it just pulls data for the current month.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
select convert(varchar(10),LogOnStartDate,111) as 'Date', count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions', count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
and convert(varchar(10),LogOnStartDate,111) between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),111)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),111)
group by convert(varchar(10),LogOnStartDate,111)
order by convert(varchar(10),LogOnStartDate,111)

image

This query groups by the current month, so you can get the total unique sessions and users for the current month:

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
select convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate)) as 'Month',
count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions',
count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL
and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
and convert(varchar(25),LogOnStartDate,107) between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),107)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),107)
group by convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate))

image

This query is similar to above, but takes all the available data and groups it by month:

select convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate)) as 'Month',
count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions',
count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL
and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
group by convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate))

image

In conclusion

I hope this encourages you to take a look under the hood of Director to see what you can get out of it. The database infrastructure is much, much simpler than EdgeSight and should provide a lot of good detail.

Thanks,
Alain

EdgeSight: Timezone offsets

Intro

If you have implemented any of the ad hoc SQL queries available on this site, you may have noticed that most time queries are offset by –4 or –5 hours. This is due to the fact that the EdgeSight database uses GMT to record time and John and I are located in the U.S. Eastern Time Zone.

In this post we will take a look at some tables in the EdgeSight database that you can utilize to make your queries more local and portable.

seamonsterThere Be Monsters Here!

Most of my experience with EdgeSight has been with the database views that summarize and organize the vast amounts of data that EdgeSight collects. On occasion I’ve gone where few dare to tread to look directly at the tables for the data I need.

EdgeSight’s views are dizzying enough, but the table structure of the EdgeSight database is intimidating to the SQL neophyte. Despite this, I was inspired to look deeper after David did his post on session counts. His query uses the ‘timezone’ table to determine the time offset for his query and this got me curious. How can I utilize this to make my queries easier to maintain and more portable?

Timezone table

Lets take a look at the timezone table

SELECT *
FROM timezone

image

The above picture is only a portion of the table. It consists of 74 rows. Yeah makes  total sense right? Naturally, I had to do some more checking. If you check the company table, we get a clue.

SELECT *
FROM company

image

As you can see in the above picture, each company in the EdgeSight database has an associated Time Zone and Language. In this case, we have a timezone id (tzid) of 13 and a culture_name of en-US. If we cross reference the tzid with the timezone table we get:

image

Looking at the result above, we can see that this is for the U.S. Eastern time zone and includes daylight savings time as well. You can configure this in the EdgeSight console by clicking on the Configure tab. Look under the Server Configuration section and click on Companies to see where to add/edit company information.

image

So for the example above, I have the language set to English and the time zone set to U.S. Eastern Time which has a GMT offset of –5 hours.

How does this help me?

Let’s take a look at a query I’ve posted on this site before:

DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111), 'username'

As you can see above, all the timedate fields are offset by –4 hours. To keep from having to change the offset to –5 or –4 depending on what time of year it was (standard vs. daylight savings time), I developed a simple select query that determines the current offset by checking the timezone table.

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13

In layman’s terms, look at the timezone table where the timezone id (tzid) is equal to 13. If the field ‘use_daylight’ is equal to zero, use the ‘standard_bias’ otherwise use the ‘daylight_bias’.

I’m setting whichever bias this query returns equal to the variable @tzbias. I then use the @tzbias variable in my timedate fields in my queries. If we rewrite the above query with the tzbias variable, we get the following:

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(mi,@tzbias,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111), 'username'

Since the timezone bias is in minutes, I had to change the DATEADD functions to use mi for minutes. Now I can use my queries year around without worrying about daylight savings time changes.

I hope this provides you some options when doing ad hoc queries against the EdgeSight database. As always, I welcome all comments and questions.

Thanks,
Alain

Are You There EdgeSight? It’s me Worker

Intro

If you rely on EdgeSight to provide accurate and timely information about your farm you have to assume that all your EdgeSight Worker Agents are functioning as expected.  Or do you?  In this post, we will review the information that the EdgeSight console provides you as well as creating a dashboard that can give you detailed information on your EdgeSight Worker agents.

EdgeSight Console: Configuration Tab

The first place you can check the health of your EdgeSight server and its agents in under the Configuration Tab

image

Along the left-hand side of this screen you will see way to configure your workers, alerts, and other server settings. We’re going to spotlight some items under Server Configuration and Server Status.

image

Server Configuration: Status
Your first overview of server health comes when you click on Status under Server Configuration.

image

The first line lists the workers that were and were not updated in the current 24 hour period as well as newly added workers.  Right away you see (in this case) that 48 workers updated and 32 did not.  That’s a large number of EdgeSight agents that have not uploaded their data into your database and therefore any reports you are running will not include these systems.  The question becomes which systems did not update and why?

ES_ZQUEUE…Gesundheit!

The service on the EdgeSight server that processes payloads from the worker agents is the es_zqueue (seen under Server Script Host Status).  This process is not reporting any issues and there are no pending payloads to process (we’ll look at this more later).

Server Status: Messages
Message Status lists all the system messages generated by EdgeSight.  This includes Agent errors, payload errors, and new agents alerts to name a few.

image

Here you will see which servers had a payload issue (Data Upload), but not a reason why systems have not updated the database.

Server Status: Server Script Host
Clicking on this in your EdgeSight Console will show you the following screen:

image

The es_zqueue manages the modules that keep the EdgeSight database updated, cleaned, and running smoothly. The core_zpd_loader 1 and 2 manage the data payloads from devices with the EdgeSight agent including errors. Clicking on the triangle will show the following menu.

image

Clicking on View Log will allow us to investigate why a payload might have failed or created an error.

4/13/2012 5:18:03 AM: PayloadLoader: Starting payload load for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd
4/13/2012 5:18:11 AM: PayloadLoader: Payload load completed with errors for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd. Error: -2146233088: Citrix.EdgeSight.Loader. System.Exception

As we can see if this example, the payload completed with an error and we can try searching Citrix to see if there is a resolution related to this error, but we do not see which server failed to upload any data.

I’ve walked through the diagnostic information that is available in the EdgeSight console to show that we still do not have a clear sign of which servers have updated the EdgeSight database recently. To address this issue, I did some digging around in the EdgeSight database and created a query that links the instance, machine, and OS_version tables.

The Query

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
SELECT    i.instid, m.name as 'System', ip_address AS 'IP', product_version AS 'ES Version',
CASE dept_set_type    WHEN 1 THEN 'XenApp' WHEN 2 THEN 'Endpoint' END AS 'ES Agent',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_sync),100) AS 'Last Sync',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_config_start),100) AS 'Last Config Check',
CONVERT(DECIMAL(19, 2),(last_db_size/1048576.0)) AS 'Last FBDB Size (MB)',
CASE o.short_name    WHEN 'Windows Server 2008' THEN 'W2K8'
WHEN 'Windows Server 2008 R2' THEN 'W2K8R2'
WHEN 'Windows Server 2003' THEN 'W2K3'
WHEN 'Windows XP'           THEN 'XP'
ELSE 'Other' END AS 'OS',
CASE o.ptype        WHEN 'Standard x64 Edition' THEN 'Std x64'
WHEN 'Professional'         THEN 'Pro'
WHEN 'Enterprise Edition'   THEN 'Ent'
WHEN 'Standard Edition'     THEN 'Std'
WHEN 'Enterprise x64 Edition' THEN 'Ent x64'
ELSE 'Other' END AS 'Edition',
sp_level,
CONVERT(VARCHAR,DATEADD(mi,@tzbias,tstamp),111) AS 'Date Added',
cps_farm_name,i.cps_product_name, i.cps_product_version, i.cps_product_service_pack
FROM instance i,machine m, os_version o
WHERE m.machid=i.machid and i.osid = o.osid
ORDER BY dateadd(mi,@tzbias,last_sync) DESC

The Report

image

Click on the image to see a larger version..

With this dashboard (I created it based on the query above in SQL Reporting Services) you can quickly see which servers have updated (Last Config Ck) and which have not. Armed with this information you can review your EdgeSight Agent worker schedules or check the agent on the system in question to make sure it is communicating with the EdgeSight server.

As always I welcome all questions and comments.

Thanks,
Alain

Finding Users On Your Network, Using PC’s, and Running a Certain Application

Intro

Recently I was asked to determine which users were using a certain application in our Citrix Farm.  We are using a published desktop and while EdgeSight has reports to show published applications, few built-in reports to show what users are running in their session.  In addition, I was only looking for users who were on our internal network and not using a thin client.  Unless your network team has created a very segregated network, and you have set up user groups based on various subnets and devices, this sort of information is impossible to pull out of EdgeSight.   In this post I will show you a query that gathers this information.

The Query

DECLARE @today datetime 
DECLARE @app varchar(20) 
SET @today = convert(varchar,getdate(),111) 
SET @app = 'notepad.exe' 
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version 
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl 
WHERE apptbl.exe_name like '%'+@app+'%' 
and apptbl.account_name <> 'UNKNOWN' 
and serv.client_address not like '192%' 
and icatbl.client_directory not like '\%' 
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30 
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid 
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111) 
ORDER BY CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111), 'username' 

The Query Explained

Let’s review the criteria we are looking for in this query:

  1. Users accessing a certain application
  2. Users who are not using thin clients
  3. Users who are on the internal LAN

To gather this information, I’m using 3 different views in the EdgeSight database:

  1. vw_es_archive_application_usage – aliased as “apptbl”
    • This will give me Application Description, the EXE name, the EXE version
  2. vw_ctrx_archive_server_start_perf – aliased as “serv”
    • This will give me the XenApp server, the Username, the Client Name, IP Address, and ICA Version
  3. vw_es_usergroup_ica_users – aliased as “icatbl”
    • This will give me the ICA Client Directory

These 3 views will be linked by the SESSID (session id) column with is present in all the views.

and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid

First we declare some variables and assign them values:

DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'

If you have the several requests with different criteria you can declare some variables to help you.  In this case, I’ve created a variable called @app that I can set to any executable that I’m reporting on. To refer to this variable in the query, I use it in the WHERE clause using a LIKE operator and a regular expression.

WHERE apptbl.exe_name like '%'+@app+'%'

The rest of the WHERE clause helps us find the users we are looking for.

 
and apptbl.account_name <> 'UNKNOWN' 
and serv.client_address not like '192%' 
and icatbl.client_directory not like '\%' 
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30 
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid 
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111) 

I have filtered out user IP addresses that start with “192” as this is typical of home-based routers.  Obviously, you can modify this to reflect your own network.  To filter out thin-clients, I’m not selecting any client directories that start with “\”.  I’ve found that thin clients (in my case Wyse) have file systems that begin with a “\” and you can refer to my post that covered finding non-PC devices in EdgeSight here. Finally, I’m only looking at entries for the past 30 days, where the sessid’s match, and where the time_stamps match.

I always welcome comments and questions.

Thanks,
Alain

Reporting On Non-PC Devices

Intro

Today’s workplace no longer follows a strict standard in terms of endpoint devices.  Despite the efforts of your infrastructure, network, and security teams users are connecting non-approved devices to your network and your Citrix farm.  A lot has been said about the “Consumerization of IT” and it is a reality for any Citrix administrator/engineer.  In this blog post we will explore how to find these types of devices using EdgeSight.

The Query

We will use the VW_ES_USERGROUP_ICA_USERS view for this query.  Here are the columns in this view:

image

Here is a sample of data in this view (customer specific information hidden):

image

Mobile Devices

The following query will select mobile devices that connected to your farm in the last 30 days.

SELECT CONVERT(VARCHAR,dtlast,111) AS 'Date', account_name, client_buildnum, client_productid, client_disp_horiz, client_disp_vert
FROM vw_es_usergroup_ica_users
WHERE client_name = 'mobile'
and account_name <> 'UNKNOWN'
and CONVERT(VARCHAR,dtlast,111) >= getdate() - 30
ORDER BY 'Date' DESC

Here’s a sample of the output:
image

The new Citrix Receiver sets the client name to ‘mobile’. On a PC this is typically the environment variable %COMPUTERNAME%.  To find the devices that are connecting, you can use the horizontal (client_disp_horiz) and vertical (client_disp_vert) resolutions and compare them to current resolutions of mobile devices.  I found a nice reference list here.  This can get you half-way there.  The only other way that I’ve been able to distinguish the client that is connecting are the ‘client_buildnum’ and ‘client_productid’ fields.  Unfortunately, finding an updated list of ICA/Receiver build numbers is not easyStephane Thirion at Archy.net provides a recently updated list.

Thin Clients

We can also use VW_ES_USERGROUP_ICA_USERS to report on thin client devices.  It is unlikely that thin clients will be an unapproved device on your network, but we can get some useful data on them from this view.  The following query will select thin client devices that connected to your farm in the last 30 days.

SELECT  CONVERT(VARCHAR,dtlast,111) AS 'Date', account_name, client_directory, client_version, client_buildnum, client_productid, client_disp_horiz, client_disp_vert
FROM vw_es_usergroup_ica_users
WHERE client_directory like '\%'
and account_name <> 'UNKNOWN'
and CONVERT(VARCHAR,dtlast,111) >= getdate() - 30
ORDER BY 'Date' DESC

Here’s a sample of the output:
image

If the thin client is windows-based, chances are the client_version field will give you the currently installed ICA client on the device.  You can use this information to pester the person in charge of thin client’s to update them or replace them (just kidding – but really you need to get them updated).  For this example, we examined the ‘client_directory’ column and determined that if it started with a  ‘\’, it was a thin client.  You may have to experiment with this field depending on which thin clients you have in your environment.

I hope this post has shown you how to track down non-pc devices connecting to your Citrix farm.  Once you have determined the ICA/Java client versions connecting to your farm (see the ICA Client Version report in EdgeSight!) you can modify these queries to find Java client users and Macintosh users.

As always, I welcome all comments and questions.

Thanks,
Alain

Reporting on Alerts

Intro

EdgeSight allows to you to create alerts that trigger on many criteria.  In this post, we will configure an alert and show how to query the database directly to get this information.

Creating an alert

For the purposes of this post, I have created a Process Hung alert for outlook.exe.  This is a built-in Application Error alert that can trigger on the EXE file name, the application description, the process file version, and/or the process company name.  The actual alert will show up in the Farm Monitor and Alert List view under the Monitor Tab in the EdgeSight console.

Now you will get a near real-time alert in the console that looks like this:
image

I found that this alert triggered quite often and while you can use the “Process Not Responding Alert” report, this blog is all about pulling back the veil.

The Query

We will use the VW_ES_ARCHIVE_ALERT view for this query.  Here is an example of all the columns in this view (customer specific information hidden):

image

For our purposes, I want to get the date of the alert, the machine name, the username, the process name, the process description, and the actual text of the alert.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name,alert_text
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

This gives me:
image

If you look at the alert_text field, you will see some information that doesn’t look right.  You can see “Microsoft Office Outlook”, a weird character, and a series of numbers.  These numbers are in fact the actual process hang measured in milliseconds.  You can see this if you go back to the farm monitor and select the detail for an alert:

image

You have the information you need to determine who is having a real long delay, but how can we sort or organize this delay information.  There is no built-in MSSQL function to break this column up into two useful fields.  A Google search pointed me to a user-written function that will strip non-alphanumeric from a column.

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression =  '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END

Once you execute this in the MSSQL Management Studio, you can reference the function in your query:

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, dbo.fn_StripCharacters(alert_text, '^a-z0-9')
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

This now gives us:
image

Now the special character is gone, but how can you split the process delay out of the column?  You can use a built-in MSSQL function call SUBSTRING.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, SUBSTRING(dbo.fn_StripCharacters(alert_text, '^a-z0-9'),23,6) AS 'Delay'
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

Now we get:

image

To finish up, we’ll divide the Delay by 1000 to get the delay in seconds.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, CONVERT(INTEGER,SUBSTRING(dbo.fn_StripCharacters(alert_text, '^a-z0-9'),23,6),10)/1000.0 AS 'Delay'
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)
ORDER BY 'Delay' desc

Our end result:

image

With this information, you can do further manipulation including counting the number of alert instances for a user or tracking a single user over time.

As always I welcome all questions and comments.

Thanks,
Alain