In this blog we will discuss about how we can generate custom report from Xendesktop monitor Service database. There is already information provided on the Schema of Monitoring Services Database, in a blog written by Dana Gutride

I will try explain, on how to script and create your own custom reports using already publically available information and tools. Before we start you will need to understand:

  1.  Monitoring services basics explained at /blogs/2013/08/26/the-xendesktop-7-monitor-service-introduction-to-odata ,
  2. Data available in Monitoring services /blogs/2013/08/27/xendesktop-7-monitor-service-what-data-is-available,
  3. Understand Monitoring services OData: http://support.citrix.com/proddocs/topic/xendesktop-7/cds-ms-odata-methods.html

Creating reports include 2 steps explained below 

  • How to setup LINQPAD in order to make Monitoring service OData calls
  • How to retrieve data from different tables and create your own reports and export into Excel file.

How to setup LINQPAD to make Monitoring service OData calls

  • One window opens up, select wcf data service mentioned in “Build data context automatically” and click Next

  • Wizard moves to next window, where URI and credentials should be filled.
  • The format of URI should be : http://{MachineFQDN_OR_MachineIP}/ Citrix/Monitor/OData/v1/Data
  • Username should be in the format : username@domain
    • Few points to remember here
      • Username cannot be in the format “domain\username”.
      • Username provided here should be xendesktop admin and have permission to make OData quires.
  • Provide the password and click ok.
  • If the connection is successful we can see the table and Linqpad looks as follows

Now the LinqPad is connected to Xendesktop (Monitoring Service database). Setup is ready to run any OData query.

How to retrieve data from different tables and create your own reports and export into Excel file

In this section we will discuss one specific scenario and see how we can get the reports. Let’s take an example where we want to retrieve following report “Find the all the users who has made a login to machine ‘TSDAMachine’ in the time range 9:00 AM to 9:05 AM on 22-dec-2013”.

In order to get this report, we need to access Sessions table, Users table and Machines table from the database.  There are many ways to get these result. This is just one way of implementing it. . Before we look into code let us see what are steps we will follow :

(1)  Get the Time Range.

(2) Get the Machine name.

(3)  Get list of Sessions that was launched between the given time range for the given machine.

(4) Get username by referring the Users table

There you go !!! we have a list of users who launched the session with the given time range. Now lets have a look into the Linqpad statements.

1)     Input the time range from user :

//Please Provide the time in the format “MM/dd/yyyy hh:mm:ss AM”

DateTime StartDate = Convert.ToDateTime(“12/22/2013 09:00:00 AM”);

DateTime EndDate = Convert.ToDateTime(“12/22/2013 09:05:00 AM”);

2)    Input machine name :

//Provide the machine name here

String MachineName = @” TSDAMachine”;

3)    Now Make a Odata Query :

var SessionsWithinRange = from c in Sessions

where (c.StartDate > StartDate.ToUniversalTime()) && (c.StartDate < EndDate.ToUniversalTime()) && c.Machine != null && c.User != null && c.Machine.Name == MachineName

select new {UserName = c.User.UserName, MachineName = c.Machine.Name};

Let us break the query for better understanding,

We are getting the values from session table:

from c in Sessions



 


Session start time is in between the required time range :


(c.StartDate > StartDate.ToUniversalTime()) && (c.StartDate < EndDate.ToUniversalTime())




 


Machine and User name cannot be Null :


c.Machine != null && c.User != null




 


Machine name is equal to the user submitted machine name :


c.Machine.Name == MachineName




Here “Machine” is a different table which is referred from session table.



    

  •   Result will look like this
    • 














    

  • You can further download this report into excel files as follows :
    • 















Here is the complete Linqpad Statements :


//Please Provide the time in the format “MM/dd/yyyy hh:mm:ss AM” 


DateTime StartDate = Convert.ToDateTime(“12/22/2013 10:51:39 PM”); 


DateTime EndDate = Convert.ToDateTime(“01/24/2014 10:51:39 PM”); 



//Provide the machine name here 


String MachineName = @”DOMAIN\TSVDAMACHINE”; 


Console.WriteLine(“StartDate After Converting to UTC :” + StartDate ); 


Console.WriteLine(“EndDate After Converting to UTC :” + EndDate ); 


Console.WriteLine(); Console.WriteLine();



// Make a query 


var SessionsWithinRange = from c in Sessions 


where (c.StartDate > StartDate.ToUniversalTime()) && (c.StartDate < EndDate.ToUniversalTime()) && c.Machine != null && c.User != null && c.Machine.Name == MachineName 


select new {UserName = c.User.UserName, MachineName = c.Machine.Name}; 



Console.WriteLine(SessionsWithinRange.ToList());



Some more examples :


Application launch count for given delivery group


Application launched by different user


number of launches for each application


Total user count for a day


users connected to Delivery group for the day