How to get data beyond last month or even more than a year from Citrix Director if you don’t have Platinum Edition

If you are using Citrix Director not on a Platinum Edition and you want to report on your trends beyond last month then most probably you are out of luck as the Citrix Director web interface only supports the following time periods: Last 2 hours, Last 24 hours, Last 7 days and Last Month.  It became our issue just recently as we wanted to drill down trends beyond those parameters so I devised a solution to deal with the problem.

This solution is quite simple but you will need some knowledge on SQL Server and SSRS.  So what I did first was to investigate where the data is stored, it was not quite hard to find as it uses a SQL Server database and by default is in a database named CitrixCentralMonitoring.

From here you will notice there are tables for Session, Users, Machine and Desktop Group where the data of whats shown in the trend report lies.  You can be witty and find out how they are linked together but you can be smarter by using SQL Profiler to get the right query the trend reporting is using and thats what I did.

First I fired up the profiler then ran the report and I got this

From here you can copy and paste that in your SSMS and run the query you will immediately get some results.

exec sp_executesql N'SELECT 
 1 AS [C1], 
 [Limit1].[UserName] AS [UserName], 
 [Extent3].[Name] AS [Name], 
 [Extent4].[Name] AS [Name1], 
 [Limit1].[StartDate] AS [StartDate], 
 [Limit1].[EndDate] AS [EndDate]
 FROM (SELECT [Project1].[UserName] AS [UserName], [Project1].[StartDate] AS [StartDate], [Project1].[EndDate] AS [EndDate], [Project1].[MachineId] AS [MachineId]
 FROM ( SELECT 
 [Extent1].[UserName] AS [UserName], 
 [Extent2].[StartDate] AS [StartDate], 
 [Extent2].[EndDate] AS [EndDate], 
 [Extent2].[MachineId] AS [MachineId]
 FROM [MonitorData].[User] AS [Extent1]
 INNER JOIN [MonitorData].[Session] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
 WHERE ([Extent2].[StartDate] <= @p__linq__0) AND (([Extent2].[EndDate] IS NULL) OR ([Extent2].[EndDate] >= @p__linq__1)) AND (3 <> [Extent2].[LifecycleState])
 ) AS [Project1]
 ORDER BY [Project1].[UserName] ASC
 OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY ) AS [Limit1]
 INNER JOIN [MonitorData].[Machine] AS [Extent3] ON [Limit1].[MachineId] = [Extent3].[Id]
 INNER JOIN [MonitorData].[DesktopGroup] AS [Extent4] ON [Extent3].[DesktopGroupId] = [Extent4].[Id]',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2017-03-01 11:00:00',@p__linq__1='2017-02-01 11:00:00'

Now that you have the query you can clean them so its more readable.

SELECT
[User].[UserName] AS [UserName],
[Machine].[Name] AS [MachineName],
[DesktopGroup].[Name] AS [DeliveryGroup],
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[StartDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [SessionStartDate],
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[EndDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [SessionEndDate],
DATEDIFF(MILLISECOND , [Session].[StartDate] , [Session].[EndDate]) as [SessionDuration],
[Connection].[ClientName] as [ClientName],
[Connection].[ClientAddress] as [ClientAddress]
FROM [CitrixCentralMonitoring].[MonitorData].[User] AS [User]
INNER JOIN [CitrixCentralMonitoring].[MonitorData].[Session] AS [Session] ON [User].[Id] = [Session].[UserId]
INNER JOIN [CitrixCentralMonitoring].[MonitorData].[Connection] AS [Connection] ON [Session].[CurrentConnectionId] = [Connection].[Id] AND [Session].[SessionKey] = [Connection].[SessionKey]
INNER JOIN [CitrixCentralMonitoring].[MonitorData].[Machine] AS [Machine] ON [Session].[MachineId] = [Machine].[Id]
INNER JOIN [CitrixCentralMonitoring].[MonitorData].[DesktopGroup] AS [DesktopGroup] ON [Machine].[DesktopGroupId] = [DesktopGroup].[Id]
WHERE 3 <> [Session].[LifecycleState]
AND CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[StartDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) <= @EndDate
AND ((CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[EndDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) >= @StartDate))

For me here is my final query, which I parametarized to be ready to be consumed as as Stored Procedure I also localized the dates since it has a different format to what I need.

Now you have the key query all you need is to run this and keep the data somewhere and run an INSERT SELECT stored procedure.  For me the solution I made is to create a new database called CitrixCentralMonitoringArchive so that I can keep the records that I query and basically store it forever.

USE [CitrixCentralMonitoringArchive]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
-- =============================================
-- Author:        Raymund Macaalay
-- Create date: 02 March 2017
-- Description:   Archives Citrix Director Data 
-- =============================================
CREATE PROCEDURE [dbo].[ExtractDataForArchiving]
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
 
    SET NOCOUNT ON;
 
    INSERT INTO CitrixSessions 
    SELECT 
        [User].[UserName] AS [UserName], 
        [Machine].[Name] AS [MachineName], 
        [DesktopGroup].[Name] AS [DeliveryGroup], 
        CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[StartDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [SessionStartDate], 
        CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[EndDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [SessionEndDate], 
        DATEDIFF(MILLISECOND , [Session].[StartDate] , [Session].[EndDate])  as [SessionDuration],
        [Connection].[ClientName] as [ClientName],
        [Connection].[ClientAddress] as [ClientAddress]
        FROM  [CitrixCentralMonitoring].[MonitorData].[User] AS [User]
        INNER JOIN [CitrixCentralMonitoring].[MonitorData].[Session] AS [Session] ON [User].[Id] = [Session].[UserId]
        INNER JOIN [CitrixCentralMonitoring].[MonitorData].[Connection] AS [Connection] ON [Session].[CurrentConnectionId] = [Connection].[Id] AND [Session].[SessionKey] = [Connection].[SessionKey]
        INNER JOIN [CitrixCentralMonitoring].[MonitorData].[Machine] AS [Machine] ON [Session].[MachineId] = [Machine].[Id]
        INNER JOIN [CitrixCentralMonitoring].[MonitorData].[DesktopGroup] AS [DesktopGroup] ON [Machine].[DesktopGroupId] = [DesktopGroup].[Id]
        WHERE  3 <> [Session].[LifecycleState] 
        AND CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[StartDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) <= @EndDate 
        AND ((CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [Session].[EndDate]), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) >= @StartDate)) 
    
END
 
GO

From there I created SQL Server jobs which will run the stored procedure ExtractDataForArchiving and get the record on a daily basis with a dynamic date parameter.

DECLARE @StartDateParam DateTime
DECLARE @EndDateParam DateTime
SET @StartDateParam = CAST(CAST(DateAdd(day, -2,  GetDate()) as varchar(12)) as DATETIME)
SET @EndDateParam = CAST(CAST(DateAdd(day, -1,  GetDate()) as varchar(12)) as DATETIME)
 
EXEC   [dbo].[ExtractDataForArchiving]
        @StartDate = @StartDateParam,
        @EndDate = @EndDateParam

Now that I have my data all I need to worry is to show it, hence presented it in my Amazing Report Server like such

Now even the CitrixCentralMonitoring database information is purged you will always have it in your archive.