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