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.

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.

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”

My Briforum Session is online for anyone who wants to go check it out. After viewing the session it appears I said “UH..” about 637 times? Anyway it was a great experience and an honor to speak in front of so many talented individuals. If you are curious of what I do when I go in and run queries you can watch this session and basically see how it’s done. The session also has some custom queries show integrating malware and geospatial data with your Edgesight data to check for infected systems that your antivirus may have missed.

My presentation was somewhat hampered by being told I could not use any of my customer data in the presentation. One of the hard tasks in teaching Edgesight is that most companies do not want their data made public so doing the presentation in a public forum was a bit tough. This would be much easier if it were done in a corporate classroom using the customer’s data.

Anyway, please have a look, if you have never run a SQL query you will see some of that here.

Take care and thanks for watching. I cannot recommend enough, attending Briforum, it is a true geek-to-geek, vender nuetral conference and was, hands down, the most informative virtualization conference I have ever gone to.

In my previous article: Average Session Count by Day and Hour: The Query, I dove into a query that shows the number of sessions on a farm at any given hour. Next, we go onto the creation of the report. For this I use SQL Server Business Intelligence Development Studio (BIDS). To begin, I created a new report to contain a matrix which runs the query that I created above. For this report to run in BIDS and EdgeSight, the entirety of the following should be placed in the query window:

In the “Design the Matrix Wizard” Add Time2 to the Columns, Date to the Rows, and Total Sessions to the Details value. Click finish.

The first necessary items that need to be added to the report in order for this report to run successfully are parameters. On the data or layout tab of your new report, open the Report Parameters window. Two parameters need to be added to the report “Start” and “End”. They both need to be string values. EdgeSight reads these parameters on the reports and allows you to choose what dates you want to run the report for. Go ahead and preview the new report. If it doesn’t work, take a break, watch an episode of Dexter, and be glad you work on computers, where everyone gets a second shot. Let’s continue down the steps to making this a report with a bit more useful data.

In the rows where the Date is, I needed to add the day of the week to display days like the following: 8/1/2011 – Monday. To do this I changed the formula for the cell to: =Fields!Date.Value & ” – ” & WeekdayName(Weekday(Fields!Date.Value))

In the columns where time2 resides I needed to show the hours and not show the integer. I’m not a big VB guy and am still working on growing my coding. For that reason I have change the cell to the impeccableformula of: =Fields!Time2.Value & “:00:00”. This shows the hours of the day in military format.

Good enough for me.

In BIDS, to show the average of rows and columns, a bit of code needs to be added to the report. In the Report Layout window, you can add the subtotals to the rows and columns by simply right clicking on any row and column and selecting “Subtotal”. BIDS will add a new row or column to your report that shows the subtotal of the row or column. The problem is that BIDS 2005, I don’t know about any newer version, only subtotals are shown and not the average. To show the average of the columns instead of the subtotals, open the “report properties” and go to the code tab. Based on a how-to from All About Business Intelligence add the following code:

Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
If InScope Then
Return sumValue
Else
If Not avgValue Is Nothing Then
Return String.Format(avgFormat, CDbl(avgValue))
Else
Return Nothing
End If
End If
End Function

After a few aesthetic changes, you can find an example data output of my finished report here:Download – Session Count by Date and Hour – Please note that this file is set to a .doc and needs to be renamed to a .rdl in order to be uploaded into EdgeSight.

***Update 8/19/11 – I did some playing around with this report and query and have noticed that I only applied the timezone offset to the time and not the date structure. If a user runs the report on GMT-5:00, then instead of Friday 7:00PM showing up as the time, it will show Saturday 7:00PM. I am correcting the query as soon as I can, and will update this post and the report once it is corrected.

****Update 8/20/11 – I fixed the query and the report. The new version of the report converts time correctly. You can download it here

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):

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!

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.

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.

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 QueryThis 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.

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.

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.

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.