Extract HDX/ICA Connection info from Citrix Monitoring Database

Follow OData Connection to Citrix Delivery Controller to create a connection to Citrix monitoring data.
for XenDestkop version 7.0 – 7.5 use http://{ddc-host}/Citrix/Monitor/OData/v1/Data
for XenDestkop version 7.6 and 7.7 use http://{ddc-host}/Citrix/Monitor/OData/v2/Data
for XenDestkop 7.8 and above use http://{ddc-host}/Citrix/Monitor/OData/v3/Data

/*Report HDX/ICA connection information for User: mulpurus and DesktopGroup: Win10-Standard for the May 31 - Jun 17
 Extract UserFullName, DesktopGroupName, StartTime, EndTime, ClientName and Client IP Address */
//Start Date
String Date = "05/31/2017";
//Converting string to date
DateTime StartDate = Convert.ToDateTime(Date);
//EndDate - Adding 18 days to startdate
DateTime EndDate = StartDate.AddDays(18);
Console.WriteLine("Report Generated From {0} to {1}",StartDate,EndDate);
//LINQ query returns UTC, will be using this TimeZoneInfo estZone obj to convert to EST.
//Tweak this to your desired Timezone
TimeZoneInfo estZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
//Connection varible to hold the query result, for each session
var Connection = from C in Sessions
 //filters - Start Date and EndDate, UserName and DeliveryGroup
 where (C.StartDate >= StartDate && C.StartDate < EndDate && C.User.UserName == "mulpurus" && C.Machine.DesktopGroup.Name == "Win10-Standard" && C.EndDate != null)
 //Sort by StartDate
 orderby C.StartDate
 //Extract UserName, FullName, DesktopGroup, StartDateTime, EndDateTime, ClientName, ClientAddress
 select new {C.User.UserName,C.User.FullName, DesktopGroup = C.Machine.DesktopGroup.Name,StartDateTime = TimeZoneInfo.ConvertTimeFromUtc(C.StartDate.Value, estZone), EndDateTime = TimeZoneInfo.ConvertTimeFromUtc(C.EndDate.Value, estZone), C.CurrentConnection.ClientName,C.CurrentConnection.ClientAddress };
//Display the query result
Console.WriteLine(Connection.ToList().GroupBy(u=>u.DesktopGroup));

Output

Use-Cases

1. Usage trends
2. Capacity planning
3. Auditing