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

Advertisements

Director Under the Hood: Total Sessions and Unique Users Per Day

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 a query to show you the total sessions and unique users per day.

This is it…really?

The tables that make up the Director Database

The views that make up the Director Database

image image

After years of pouring through and querying EdgeSight’s tables and views, I first thought that something must be wrong. This can’t be all there is to the Director database, but that’s all there is.  Before we dive into SQL, let’s see what we can find using the Director GUI. I like to collect lots of metrics when I report on my environment. The 3 main session metrics I track are concurrent user per day, unique users per day and total sessions per day. Can we find this info in the Director Trends dashboard?

I set the Time period to Last Month and then set to custom ending to 10/1/2015. This should give me data for September 2015. Here’s what we get:

image

NOTE: For these examples, I’m looking at all delivery groups. You can limit your view by delivery group if you wanted to track metrics for different groups of users.

As you can see, we get a pretty graph, but we have to export the data to Excel to get precise detail:

image

What this doesn’t show us it how many sessions and unique users there are per day. The only way to get this using the Director interface is to click on a point on the graph to see the session details. This will only work for more recent time period.

image

SQL To the Rescue

For this query I’m using the following tables/views:

MonitorData.SessionV1 (View) MonitorData.Connection (Table) MonitorData.User (Table)
image image image

I’m linking the SessionV1 and Connection SessionKey columns together and the User.id and SessionV1.userid columns together. This ensures that I’m grouping the same sessions and users together (users can have more than one session). Then I group by the LogOnStartDate and count the distinct sessionkeys and distinct userids. This gives me the total sessions and unique users per day.
This query will pull all available data and total the sessions and unique users per day.

select convert(varchar(10),LogOnStartDate,111) as 'Date', count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions', count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
group by convert(varchar(10),LogOnStartDate,111)
order by convert(varchar(10),LogOnStartDate,111)

image
The following query is similar, but it just pulls data for the current month.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
select convert(varchar(10),LogOnStartDate,111) as 'Date', count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions', count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
and convert(varchar(10),LogOnStartDate,111) between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),111)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),111)
group by convert(varchar(10),LogOnStartDate,111)
order by convert(varchar(10),LogOnStartDate,111)

image

This query groups by the current month, so you can get the total unique sessions and users for the current month:

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
select convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate)) as 'Month',
count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions',
count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL
and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
and convert(varchar(25),LogOnStartDate,107) between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),107)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),107)
group by convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate))

image

This query is similar to above, but takes all the available data and groups it by month:

select convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate)) as 'Month',
count (distinct MonitorData.SessionV1.sessionKey) as 'Total Sessions',
count (distinct MonitorData.SessionV1.Userid) as 'Unique Users'
from MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
where FailureDate is NULL
and MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey
and MonitorData.[User].Id = MonitorData.SessionV1.userid
group by convert(char(9),datename(month,LogOnStartDate)) + ' ' + convert(char(4),datepart(year,LogonStartDate))

image

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

Article: How to Truncate EdgeSight’s Alert and Alert_Param Table

[NOTE: I had to recently follow similar steps to reduce the size of my EdgeSight database to save disk space]

Document ID: CTX137750   /   Created On: Jul 8, 2013   /   Updated On: Jul 8, 2013

Summary

This document describes how to truncate EdgeSight’s alert and alert_param table. This operation deletes all the “stability” (such as agent’s windows logs like application, system, and security) data.

Requirements

There are many other conditions that will also justify the need to clean out the “stability” data. Following are some guidelines.
If you have an appropriate reason or requirement, skip the “Pre-requisite” section.

Pre-requisite

Required conditions (so you do not arbitrarily empty out the alert and alert_param table)-

  • Core_zbatch_run.log shows [alert] table grooming resulted in timeout (in excess of 240 minutes) error. The ES web console timeout, “Background Services Query” has default setting of 14400 (seconds, 60s/m * 60m/hr * 4hr).
    The setting can be found in “Configure (top tab) > Server Configuration (left tab) > Settings; Timeouts (main window tab).”
  • Many days of failure in core_zbatch_run.log.
  • The filegroup5 is very large, with alert and alert_param tables being excessively large.
  • The customer consent to having the data deleted.
  • And one of the conditions:
  • The database server had run out of disk space or is about to.
  • The “Background Services Query” timeout was extended to a larger value- and timeout failure continues.

Background

Preceding screenshot displays EdgeSight 5.4 Web Server database diagram for “alert” table and its related tables. The highlighted key is “alert_param” table references “alertid,” therefore this relationship must be broken when truncating the “alert” table.

Procedure

As a pre-requisite for the procedure, do this as late in the working day as possible, it will avoid the grooming job and most of the current day’s performance payloads will have been processed.

You must have access to the EdgeSight database. The step described utilizes the SQL Server Management Studio.

Complete the following steps to truncate the EdgeSight’s alert and alert_param table:

  1. Stop the EdgeSight Server’s “Citrix RSSH Admin Service” (it stops the dependent “Citrix RSSH Application Manager” service).

  1. Save a script to recreate the “FK_alert_param_alert” index in “alert_param” table. This script is used to restore the FK.
    Right click on FK_alert_param_alert and select Script Key as > CREATE To > New Query Editor Window.


  1. Drop the FK_alert_param_alert key.
    Right click on FK_alert_param_alert and select Delete.

  1. Click OK on the Delete Object form. (OK button is not displayed in the following screenshot).

  1. Obtain a New Query window. Run both SQL commands here truncate table alert_param and truncate alert.
    In this example, all four commands have been issued at the same time and verified that the tables are empty (0 rows).

  1. Switch back to the Query Window (for example “SQLQuery6.sql…”) to recreate foreign key. Click Execute button to execute the ALTER TABLE commands.

  1. Right click on FK_alert_param_alert and select Refresh.

  1. Expand Keys and verify the FK_alert_param_alert key is present.

  1. Start the EdgeSight Server’s Citrix RSSH Application Manager service (it starts the dependent Citrix RSSH Admin Service service).

More Information

CTX138381 – FAQ – Truncate EdgeSight’s Alert and Alert_Param Table

This document applies to:

Thanks,
Alain