云桌面-查询用户登录桌面历史记录-SQL
桌面云管理员在运维过程中可以面临统计用户登录桌面云的使用情况，默认情况下使用Citrix Director查询导出，但是面临导出数据不全或者有限，如果需要更加全面或者更准确的数据，那么就要查询数据库获取用户登录桌面云会话信息
-- 定义桌面会话开始和结束时间
DECLARE @StartDate DATETIME = '2024-01-02 00:00:00';
DECLARE @EndDate DATETIME = '2024-01-12 23:59:59';
-- 定义查询信息
SELECT
Users.UserName AS 用户账号,
Users.FullName AS 用户名称,
Connection.Clientname AS 客户端名称,
Connection.ClientAddress AS 客户端IP,
SUBSTRING(Machine.Name, CHARINDEX('\', Machine.Name) + 1, LEN(Machine.Name)) AS 云桌面名称,
Machine.IPAddress AS 云桌面IP,
DesktopGroup.Name AS 交付组,
Catalog.Name AS 计算机目录,
Connection.Protocol AS 协议,
CONVERT(
VARCHAR,
DATEADD(HOUR, 8, Connection.LogOnStartDate),
120
) AS 用户登录时间,
CONVERT(
VARCHAR,
DATEADD(HOUR, 8, Connection.DisconnectDate),
120
) AS 用户断开时间
FROM
[CitrixEDEN-CVADMonitoring].[MonitorData].[Connection] AS [Connection]
LEFT JOIN [CitrixEDEN-CVADMonitoring].[MonitorData].[Session] AS [Session] ON Session.SessionKey = Connection.SessionKey
LEFT JOIN [CitrixEDEN-CVADMonitoring].[MonitorData].[Machine] AS [Machine] ON Session.MachineId = Machine.Id
LEFT JOIN [CitrixEDEN-CVADMonitoring].[MonitorData].[DesktopGroup] AS [DesktopGroup] ON Machine.DesktopGroupId = DesktopGroup.Id
LEFT JOIN [CitrixEDEN-CVADMonitoring].[MonitorData].[Catalog] AS [Catalog] ON Machine.CatalogId = Catalog.Id
LEFT JOIN [CitrixEDEN-CVADMonitoring].[MonitorData].[User] AS [Users] ON Session.UserId = Users.Id
WHERE
Connection.Protocol = 'HDX'
AND Connection.Clientname NOT LIKE 'WR_%'
AND SUBSTRING(Machine.Name, CHARINDEX('\', Machine.Name) + 1, LEN(Machine.Name)) = ‘VDA-01’ # 查询指定计算机名登录信息，如果去掉查询所有
AND Connection.LogOnStartDate >= @StartDate
AND Connection.LogOnStartDate <= @EndDate
ORDER BY
-- 按照用户登录时间排序
Connection.LogOnStartDate DESC;
