Making Citrix Stats Work for You – part 6

This post is part of a 6 part series. Jump to [part 1] [part 2] [part 3] [part 4] [part 5].

To sum up how we got here:

  1. We used PowerShell to gather some specific session stats from Citrix MFCom and output them to a text file. [part 1] [part 2]
  2. We then created a database and table to hold this data. [part 3]
  3. We created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]
  4. We showed how to save the Visual Studio job directly to an MS SQL server and have that server run a job to insert new data on a periodic basis. [part 4]
  5. We constructed a web page using MS SQL Reporting Services to present the data in real-time. [part 5]

For this final post, I want to add another piece of information to my report (which I’ll refer to as a dashboard from here on out).  I also will upload the report to the reporting services server so others can view the information.

We currently already show the currently disconnected users in the farm (last 5 minutes or so).  I also want to show the highest number of disconnected users for the day.  One quick way is to create a view based on our original data.  If we use the following query…


SELECT TOP (100) PERCENT CONVERT(varchar(5), msgdatetime, 108) AS Hour, COUNT(DISTINCT CONVERT(varchar, UserName))  AS Count
FROM dbo.UserSessions
WHERE (msgdatetime >= (datediff(d,0,getdate()))) AND (CONVERT(varchar, SessionState) LIKE '%5%')
GROUP BY CONVERT(varchar(5), msgdatetime, 108)
ORDER BY Hour

…we get a listing of total, unique disconnected users ordered by every 5 minutes for the current day.  To create a view in MS SQL,  I’ll open MS SQL Server Management Studio, login, open up our Syslog database and right-click on Views  and select “New View…”
stats6_1

A view is just a persistent query.  I’ll select the table we’re going to use and plug in the above query.
stats6_2

Saving it will name the view and make it available to future queries.  Now we’ll add a new field to our report that will show the largest number of disconnected users for the current day and base the information on our new view.  First, I’ll add a new text box…
stats6_3

and a new table object by clicking on Toolbox and dragging a table into the report.
stats6_3b

Remove the footer and a couple of columns and do some editing…
stats6_3c

Now we’ll add a new dataset by clicking on Data and selecting new Dataset on the drop down list.
stats6_4

I’ll name the dataset MaxDisconnected and use the following query which just selects the maximum value (of disconnected sessions) from the view.

select max(count) as MaxDisconnected
from VW_UserSessState_5

stats6_51

Now we have the new dataset. I’ll click on Datasets and Layout and simply drag the MaxDisconnected dataset into the table I created earlier.
stats6_6

So, the report is complete.  I want this report to automatically update every 5 minutes.  Do to this, I’ll click on the Report Menu and select Report Properties.  I’ll simply click on Autorefresh and set it to 300 seconds…
stats6_7

Now to import it to the reporting services server, we simply open our browser to the reporting services site:
stats6_8

Click on Upload file and browse to the .RDL file.
stats6_9

Name the report and click OK.  Now the report will show up on the web page.  Clicking on it will result in a failure, because we still have to associate a data source.  Click on Properties and Data Sources.  Set the data source, click OK, hit apply, and then view.  The report will now render and will autoupdate every 5 minutes so will show the most recent data.
stats6_10

Thanks for viewing.  I hope this gives you a basic introduction to using several tools to present data in a simple way (even though the method may be complicated).  As a system administrator/engineer  you (hopefully) typically have a reporting tool of some sort, but giving superiors or other teams access to it may prove to be more difficult or confusing than intended.  Using SQL Reporting Services allows you to show data in a simple form to a wider audience.

Please feel free to ask questions/comment. The methods described in this series have worked well in our environment, but I’d love to hear what others are doing to place metrics, stats, and such front and center.

Thanks,
Alain

Making Citrix Stats Work for You – part 5

This post is part of a 6 part series. Jump to [part 1] [part 2] [part 3] [part 4] [part 6].

To sum up how we got here:

  1. We used PowerShell to gather some specific session stats from Citrix MFCom and output them to a text file. [part 1] [part 2]
  2. We then created a database and table to hold this data. [part 3]
  3. Following this, we created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]
  4. Then, I  demonstrated how to save the Visual Studio job directly to an MS SQL server and have that server run a job to insert new data on a periodic basis. [part 4]

Now that we have new data automatically entering our database table every 5 minutes or so, we can construct a web page using MS SQL Reporting Services to present the data in real-time.  For this example, we’ll create a real-time report that shows the currently disconnected users in my Citrix farm.

Open Visual Studio and open up a Report Server Project.
stats5_1

Now we’ll create a Shared Data Source to connect to our database server by right-clicking on Shared Data Sources and Add New Data Source.
stats5_2

We’re connecting to an MS SQL server, click Edit to enter the connection information, and choose the database we’ll query for our data.
stats5_3

Now we’ll add a report by right-clicking on Reports and (amazingly enough) select Add New Report
stats5_4

This will bring up the Report Wizard which we’ll follow for this example.  First, I’ll use the data source we’ve already created.  Since it’s already selected, we’ll click Next and move on to the query builder.  Here, we’ll create an SQL query that will gather the data we want to display in our report.  Click on Query Builder, then click on the Generic Query Designer button to bring up some QBE (query by example) tools.
stats5_5

Click on the Add Table button and select the table we want to query from.
stats5_6

So, for this example, I want query this table to get the number of disconnected users currently in my Citrix farm.  So, I’m looking for a session state of 5, I just want to count each user once, and I only need the last 5 minutes since this is the update interval of the table.  Here’s the query I’m using to get this data.

SELECT COUNT(DISTINCT CONVERT(varchar, UserName)) AS Disconnected
FROM UserSessions
WHERE (SessionState LIKE '%5%') AND (msgdatetime >= GETDATE() - .0031) 

stats5_7

This gives us the information we need, so I’ll click OK and go back to the Query builder and click Next.  We’ll choose Tabular for the report type, click Finish, and name the report.  This takes us to the design view, where we can format the report.  Hitting the Preview tab let us see how the finished report will look (I made some minor layout changes and added some text).
stats5_8

For the final post in this series, we’ll create a database view so we can show the currently disconnected users and the maximum disconnected users for the current day and upload the finished report to our Reporting Services server for public viewing.

Thanks,
Alain

Making Citrix Stats Work for You – part 4

This post is part of a 6 part series. Jump to [part 1] [part 2] [part 3] [part 5] [part 6].

To sum up how we got here:

  1. We used PowerShell to gather some specific session stats from Citrix MFCom and output them to a text file. [part 1] [part 2]
  2. We then created a database and table to hold this data. [part 3]
  3. Following this, we created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]

Now, I will show how to save the Visual Studio job directly to an MS SQL server and have that server run a job to insert new data on a periodic basis.

With your Visual Studio package open, go to File and select Save Copy of Package As…

Select SQL Server for package location, enter the server name, set the authentication type and credentials for the DB owner.

vs-1

Choose and name a location for the Package Path and hit OK.

vs-2

Change the protection level to “Rely on server storage and roles for acces control” and click OK to save.

Now login the SQL server with Management Studio and open SQL Server Agent and Jobs.

vs-31
Right-click on jobs and select New Job.  Select the Steps page and click new.

vs-4

Hit the Type drop down and select SQL Server Integration Services Package.  Enter the server name and then look for the Package.  It will have the same name you gave it when you exported it to the SQL server.  Give the step a name and click OK.

vs-51

Now click on schedules to run this job every 5 minutes.

vs-6

Click OK, name the job and open the Job Activity Monitor to confirm the job kicks off.  If you get a Succeeded result, you can query the table to ensure it has got new data.  So now the SQL server is kicking off the Visual Studio data flow job to read a flat text file and enter the data into a table every 5 minutes.  Next post, we’ll pull the data out of the table in some meaningful ways and place it in MS SQL Reporting Services.

Thanks,
Alain

%d bloggers like this: