Tag Archives: MS SQL

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

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

Headless Edgesight: Using Stored procedures to have key metrics emailed to you.

If you have been coming to the site now for the last few years you have probably noticed that we kind of specialize in a “Headless” Edgesight deployment. Dave, Alain and I all tend to steer away from the Edgesight console and focus more on running ad hoc queries or specialized reports. In this entry, I want to focus on leveraging stored procedures to provide you with the data you need via an email every morning so that you can validate a few things in your environment and perhaps put yourself in a position to “right the ship” before your onrush of users pile on the to the system at 8am.

What we will be covering today is coupling a stored procedure with an HTML formatted SQL Mail. To do this we require a few pre-requisites.

1.) You have SQL Mail configured (if you do not, there are better blogs than mine to walk you through that).

2.) That you have enough of a basic understanding of SQL Server to schedule a stored procedure to run. (or the wherewithal to follow the directions I provide).

While there are a number of canned reports in Edgesight, navigating through them or getting the exact metric you are looking for can be painstaking. Using the stored procedure method, you can schedule them to run and provide you with key metrics made up of those custom queries you choose in a format that will work both on your desktop outlook as well as be readable on your smart phone.

First Stored Procedure:

Title: SP_CTX_REBOOTS

Purpose: Most of us have a reboot regimen that we put our XenAPP Servers through. This query/stored proc will email you the status of your nightly reboots. It will be up to you and your operations staff to know which servers need to get rebooted but you can at least find out that 10 of your 25 xenapp servers IMA service did not start before you get your morning rush of users.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_REBOOTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
 --Author:        <John Smith>
-- Create date: <04/02/2012>
-- Description:   <Email Reboot Records>
-- =============================================
CREATE PROCEDURE [dbo].[SP_CTX_REBOOTS]
-- Add the parameters for the stored procedure here
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
declare @Result NVARCHAR(MAX) ;
declare @today datetime ;
set @today = convert(varchar, getdate(),111) ;
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +
 N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +
N'<th>Time</th>'+

N'<th>Machine</th>'+

N'<th>Status</th>'+

cast ((Select distinct td=  dateadd(hh,-5,time_stamp),           '               ',

td= Machine_name,             '               ',

td= Service_State_name,             '               '

from vw_ctrx_archive_service_state

where dateadd(hh,-5,time_stamp) > getdate()-1

order by machine_name

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Server Reboots',

@importance = 'High' ;

end ;

Second Stored Procedure:

Title: SP_CTX_Alerts

Purpose: This is actually a very valuable alert, this will provide you with the top errors/popups users receive when they are on your system. This is made up of both Windows and Application level alerts. I have noted a number of application (custom written errors) that show up here because they popped up during the users session. This can serve as a very valuable QA tool for both Systems Admins and Developers to run down the errors that they see.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_ALERTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:        <John Smith>

-- Create date: <04/02/2012>

-- Description:   <Email top Errors and popups>

-- =============================================

CREATE PROCEDURE [dbo].[SP_CTX_ALERTS]

-- Add the parameters for the stored procedure here

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Begin

DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @Result NVARCHAR(MAX) ;

declare @today datetime ;

set @today = convert(varchar, getdate(),111) ;

SET @tableHTML =

N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +

N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +

N'<th>EXE-Name(If Any)</th>'+

N'<th>Alert Text</th>'+

N'<th>Context</th>'+

N'<th>Total</th>'+

cast ((Select td= CASE

WHEN exe_name IS NULL Then 'None'

Else exe_name

END,          '               ',

td= replace(Alert_text,'°',''),             '               ',

td= Context_text,             '               ',

td= count(alert_text),             '               '

from vw_es_archive_alert

where dateadd(hh,-5,time_stamp) > getdate()-1

group by exe_name, alert_text, context_text

having count(alert_text) > 20

order by count(alert_text) desc

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Alerts and Popups',

@importance = 'High' ;

end ;

Scheduling the Stored Procedure:

Step 1: Add a new SQL Server Agent Job.

 

Step 2: Enter in the name of the Stored Procedure


Step 3: Got to “Steps” Configure the Steps by telling it to execute the stored procedure. (Set the Database Name to the Edgesight Database)


Step 4: Schedule the job to run so that the report is waiting for you when you get in that morning.

What does it look like: (You may have saw this at Briforum ’11)

Basically it is a very simple table that can auto size for your smart phone.

Time

Machine

Status

2012-04-05T02:03:15 SERVER19 Machine Boot
2012-04-05T02:05:06 SERVER19 Service Started
2012-04-05T02:03:48 SERVER20 Machine Boot
2012-04-05T02:05:40 SERVER20 Service Started
2012-04-05T02:15:33 SERVER21 Machine Boot
2012-04-05T02:17:27 SERVER21 Service Started
2012-04-05T02:04:28 SERVER22 Machine Boot
2012-04-05T02:06:19 SERVER22 Service Started
2012-04-05T02:05:55 SERVER23 Machine Boot
2012-04-05T02:07:47 SERVER23 Service Started
2012-04-05T02:06:44 SERVER24 Machine Boot
2012-04-05T02:08:36 SERVER24 Service Started
2012-04-05T02:07:38 SERVER25 Machine Boot
2012-04-05T02:09:33 SERVER25 Service Started
2012-04-05T02:10:08 SERVER27 Machine Boot
2012-04-05T02:12:07 SERVER27 Service Started
2012-04-05T02:10:38 SERVER28 Machine Boot
2012-04-05T02:12:31 SERVER28 Service Started
2012-04-05T02:11:46 SERVER29 Machine Boot
2012-04-05T02:13:35 SERVER29 Service Started
2012-04-05T02:12:55 SERVER30 Machine Boot
2012-04-05T02:14:45 SERVER30 Service Started
2012-04-05T02:02:28 SERVER35 Machine Boot
2012-04-05T02:04:18 SERVER35 Service Started
2012-04-05T02:04:59 SERVER35 Service Started
2012-04-05T02:10:50 SERVER35 Service Started

Conclusion:
So, the two queries included in the stored procedures are very useful but the point of the post this time is not to provide a useful query so much as it is to provide a useful method. If you have some rudimentary SQL Skills you should be able to reverse engineer this to include any custom Edgesight Query that you see on this post or that you have made up yourself. Sometimes it is nice to be able to have a simple, formatted report automatically sent to you, a customer or your boss so that they can get the key metrics they need on a consistent basis. I have found Edgesight to actually be more useful in a headless configuration (ad hoc and these stored procs) than to use the console.

Thanks for reading! If you are at Synergy and you see me (big ugly guy w/thick glasses) say “heya”

John

Average Session Count by Day and Hour: The Query


A common support question that my clients have asked is, “On what days of the week do we have the greatest number of people in the system?”  Or “How many users do we have on the farm?” The User Logon Counts report in EdgeSight helps you see the total number of session for each day or device, but this report doesn’t really have the answer I was looking for.  What I needed was a report that showed the number of sessions on my farm for each hour of the day. In order to do this, I needed not only a query but a report that could get me the requested information.

The first part of this solution was to create the query that will be used in the report.  This query I decided on was a combination of a standard view provided by Citrix in EdgeSight: vw_ctrx_archive_system_perf, and two tables; Company and Timezone.  You will find the query below (please note that if you run this query in SQL, you must input a start and end time for data to be returned):

SELECT     SUM(vw_ctrx_archive_system_perf.active_sessions_peak + vw_ctrx_archive_system_perf.inactive_sessions_peak) AS TotalSessions, 
                      CONVERT(varchar(10), DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 111) AS Date, CONVERT(varchar(10), 
                      DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 108) AS Time
FROM         vw_ctrx_archive_system_perf INNER JOIN
                      company ON vw_ctrx_archive_system_perf.compid = company.compid INNER JOIN
                      timezone ON company.tzid = timezone.tzid
GROUP BY vw_ctrx_archive_system_perf.time_stamp, timezone.standard_bias
HAVING      (DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp) >= @LocalStartDate) AND (DATEADD(mi, timezone.standard_bias, 
                      vw_ctrx_archive_system_perf.time_stamp) < DATEADD(d, 1, @LocalEndDate))
ORDER BY Date, Time

Lets talk about the output real fast:

The first column of data is labeled “TotalSessions”.   This data combines the total number of inactive and active sessions for any given hour of the day.  Since we want the total number of Citrix sessions on the farm and not just the active sessions,  I needed to combine both the inactive and active sessions.

The second column of data is the Date:  The dataset’s date is split out of the vw_ctrx_archive_system_perf.time_stamp data field to supply you with only the YYYY/MM/DD value.  This is much easier to use in the report that we are going to be creating.

The third column of data Time:  This is the hour of the day in GMT that the data is recorded for.  If you are on east coast time (-5), and the Time column says 00:00:00, your local time is 7:00 PM.  Because we don’t want out times to show up in GMT, we have to connect the company and timezone tables to calculate the correct time of the day, which show up in the fourth column

The fourth column of data is Time2: This column shows the time of the day with integers.  This data is converted to your timezone (but does not adjust for DST.  I will try to spend some time to work on DST at a later date).

Before I go any further with this data and creating a report, we have to dive into the grooming policies in EdgeSight.  Because this query runs off vw_ctrx_archive_system_perf, the data is subject to the EdgeSight grooming policy.  By default, this view is groomed to only hold 30 days of data.  This is an out of the box nightmare.   As most of the general public knows, are at least one or two months in the year with more than 30 days in them.  For this reason we are going to update the grooming policy to groom every 45 days.  Why 45? So that you can run this report on last month’s data, and it can be ran up to 14 days into the current month and still pull last month’s data..

To update this grooming policy: In EdgeSight go to Configure: Server Configuration: Data Maintenance: Grooming.  The policy that you will need to adjust is the one on the very bottom called: XenApp System Performance: ctrx_system_perf:  vw_ctrx_archive_system_perf.

As stated earlier, I put this figure on 45.  You can do more, but note that the view will hold more data in your EdgeSight Database.  Tune this as you see fit.

Please follow up with my next article to the creation of the Average Session Count by Day and Time report.

Or… if you would rather just play with the report and don’t feel like reading any more, Download the Report here.  Simply rename the file to a .rdl and upload it to your custom reports on your EdgeSight web server!

EdgeSight: BRIFORUM Content

Intro
To everyone who attended my session at Briforum, thank you.  It was an honor to present and to meet so many Virtualization rock stars.  As promised, I am posting most of the content from my presentation at Briforum.  There were a few new queries that were not yet part of the site in addition to a few that I did not have time to get to.  Also, I meant what I said about converting ESUTH to a forum so that we can have multiple authors.  If you have some good Edgesight Queries to share, let me know and I will make you an author on the site!

Holistic Query
This query is designed to give you your basic system performance including Memory and Disk Queue.  You can use this to query specific metrics of a system during a reporting period (by hour).  The only value you have to manually enter is the date in the format you see above.  You will take the “Machine Name” and “Time Stamp” column values and enter them into the query below to get more detail.

The Query:

select machine_name, dateadd(hh,-5,time_stamp), disk_time_sum/disk_time_cnt as "Disk Time", total_processor_time_sum/total_processor_time_cnt as "Processor Time",
(committed_Kbytes_sum/committed_kbytes_cnt)/1000.0 as "Committed memory", Context_switches_sec_sum/Context_switches_sec_cnt as "Context Switches",
current_disk_queue_length_sum/current_disk_queue_length_cnt as "Disk Queue Length"
from c3edgesight.citgop_edgesightxa.dbo.vw_es_archive_system_performance
where  machine_name like '%ComputerName%'
and CONVERT(varchar(10),dateadd(hh,-5,time_stamp),101) = '07/14/2011'
order by dateadd(hh,-5,time_stamp)

Gives us:

machine_name Time Disk Time Processor Time Committed memory Context Switches Disk Queue Length
SVR-DS81 7/14/11 13:00 0.2470124 6.543246415 4140.62 9686 0
SVR-DS66 7/14/11 13:00 0.1102058 26.54753931 2491.53 3814 0
SVR-DS68 7/14/11 13:00 0.2426049 5.727161964 3956.425 9090 0
SVR-DS69 7/14/11 13:00 0.140912 3.041551342 4347.409 8636 0
SVR-DS83 7/14/11 13:00 0.2029572 4.014888507 4259.335 7362 0
SVR-DS67 7/14/11 13:00 0.1474974 29.02722529 3434.519 6479 0
SVR-DS80 7/14/11 13:00 0.1451845 3.177582946 4403.747 8482 0
SVR-DS74 7/14/11 13:00 0.1923944 3.841824931 4391.01 7557 0
SVR-DS78 7/14/11 13:00 0.1418489 2.496465329 4249.419 6625 0


Drilling Down:
So, if looking at the results of the query above uncovers an issue of high CPU, RAM or Disk Que you can drill down with the following query.  In this example we will copy the Machine_name column and Time_Stamp column and paste them in between the single quotes to populate the variable.  So we will copy SVR-DS81 and 2011-07-14 13:00:00.000 which will give us a list of every processor owned by every user on the system during that reporting period.

declare @MachineName nvarchar (255)
declare @timestamp datetime
set @MachineName= 'SVR-DS81'
set @timestamp = '2011-07-14 13:00:00.000'
select account_name, exe_name, App_description, total_processor_time_sum/total_processor_time_cnt as "Processor Time",(Private_kbytes_sum/private_kbytes_cnt)/1000 as "Private Memory(megs)", pages_sec_sum/Pages_sec_cnt as "Pages/Sec"
from vw_es_archive_application_performance
where DATEADD(hh,-5,time_stamp) = @timestamp
and machine_name = @MachineName
order by "pages/sec" desc
account_name exe_name App_description Processor Time Private Memory(megs) Pages/Sec
JSmith Excel.exe Microsoft Office Excel 0.00021222 194 2
FJones Outlook.exe Microsoft Office Outlook 1.12339166 91 60
SStills Cutefiller.exe CutePDF Form Filler 1.66994562 87 127
NYoung Wfica32.exe Citrix 0.00010988 83 0
SPickens Outlook.exe Microsoft Office Outlook 0.05320751 79 14
CWebster Wfica32.exe Citrix 0.01940179 78 0
SBASS Outlook.exe Microsoft Office Outlook 2.86540794 65 99
SYSTEM Pmagent.exe Performance Manager Agent 0.13332299 47 44
Jsmith Outlook.exe Microsoft Office Outlook 0.00087658 45 0
SYSTEM Icalm server.exe iCALM Server 0.01223421 44 3
DFeller Explorer.exe Windows Explorer 0.12927156 43 27
JOlsen Outlook.exe Microsoft Office Outlook 0.51810852 43 142

How long did a user’s session last:
So this is one of my cross-tab queries that you can copy and paste and just change the username for.  This will give you the date, sessoin ID, logon and logoff time as well as the length of the session for a specific user.  In this instance I am NOT pulling data from the views, rather I am pulling the data from two tables, the session table and the principal table.  This query can be handy when you are trying to audit the time a user is on the system.

The Query:

declare @today datetime
set @today = convert(varchar(10),getdate(),111)
select convert(varchar(10),dateadd(hh,-5,logon_time),111), inst_sessid, dateadd(hh,-5,logon_time),
dateadd(hh,-5,logoff_time),
convert(varchar,floor(datediff(mi,dateadd(hh,-5,logon_time) , dateadd(hh,-5,Logoff_time))/60))+ ':' +Right('0' + convert(varchar,datediff(mi,dateadd(hh,-5,logon_time) , dateadd(hh,-5,Logoff_time)) % 60), 2) as HrMinfrom session s, principal p
where convert(varchar(10),dateadd(hh,-5,logon_time),111) > @today-30
and account_name = '%AccountName%'
and p.prid = s.prid

Average Start/End Time:
The idea for this Query came about after listening to Chris Nickerson (one of the top Social Engineers in the world) discuss how, as part of penetration testing, they can call a helpdesk and try to get them to change a passoword after finding a  user ID for a particular company.  I tend to think of this query as a social engineering counter measure.  What it gives me is the average logon and logoff time for a particular user.  How this can be valuable would be if you could set it up as part of the helpdesk solution so that when someone calls in, the helpdesk knows the average logon and logoff time for the person they are talking to.  If a person who normally works M-F 8AM to 5PM calls in at 2:15AM wanting to change their password, the helpdesk technician can maybe ask a few more questions or be more alerted to a possible social engineering attempt.  This query is still not entirely vetted but as I have changed employers, I may not get a chance to vet it further.  I have noticed it is accurate for standard users but for Admins who may have that “special” ICA Timeout, it my show some skewed results because lets face it, we are on the system pretty much 24×7.  This query gives the average logon and logoff time for the last 7 days.

The Query:

select account_name, right(convert(varchar(20),(cast(AVG(cast(dateadd(hh,-5,logon_time)as float) - floor (cast(dateadd(hh,-5,logon_time) as float)))as datetime)),100),7),
right(convert(varchar(20),(cast(AVG(cast(dateadd(hh,-5,logoff_time)as float) - floor(cast(dateadd(hh,-5,logoff_time) as float)))as datetime)),100),7)
from session] s, principal p
where s.prid = p.prid
and account_name = '%UserName%'
and  dateadd(hh,-5,logon_time) >= GETDATE()-7
group by account_name

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

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 users’ 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