Category Archives: Edgesight Custom Queries

I recorded a walk-thru of the Citrix Alpha Bundle now integrated with our latest 4.0 release. Below is an example of the dashboard features. Keep in mind, all of this can be done with NO AGENTs installed on your system and NO WMI walking or interrogating your systems. We are completely passive and can similar detailed information for all of your environments (Database, SOAP/REST, Web and pretty much anything with an IP Addresses).

In the video below I will discuss some of the application containers and how they can be leveraged for troubleshooting (some overlap in information)

If you are interested in checking this out, we offer a free discovery edition or reach out to me at johnsmith@wiredata.net and I will put you in contact with your area team.

In an hosting environment, you may have multiple customers logging into your Citrix environment. If you have domain trusts set up, the users may be authenticating and launching applications based on their domain credentials. In edgesight you can run reports based on user groups configured based on each individual domain, but first it has to be configured correctly.

The first thing that needs to happen is you have to get the list of domains from the Edgesight database.

SELECT domain_name
FROM vw_es_usergroup_ica_users
GROUP BY domain_name
ORDER BY domain_name

You are then presented with results like the following:

The information you need from this query is the text that is presented in the results. Next you will need to go into your edgesight website. GO to Configure –> Company Configuration –> User Groups. On this window you will need to click New User Group. Enter a name for the domain group you are about to create and make sure that “Take me to the Add Members Page” is checked.

On the next page make sure you select “Queries” and click “Next” A new page will open and ask you to select a query. Here you will want to select “New Query”. This is where you need to remember your results from the first query. Once you click “New Query”, you will have to type in a name for the query, and in the query text window enter the following:

Save the query and test it out. You should see all the users in the domain you listed. In the bottom window make sure you click “Add Query” so that the group uses the query you just created. Congratulations! You can now use this user group to run reports.

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.

There 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

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

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:

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.

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.

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.

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:

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

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.

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

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.

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:

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.

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

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.

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.

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