Citrix SQL数据如何进行多表联查
除了通过DDC控制台查看登陆用户相关信息,我们还可以通过Citrix 连接的SQL数据库去查看。通过数据库去查看,可以看到更多的信息。
这里示例一个多表查询案例。
--查询用户历史会话信息 SELECT top(20) c.Clientname 客户端名称, c.ClientAddress 客户端IP地址, m.Name AS VDI桌面, u.UserName AS 用户名, c.Protocol 协议, c.IsReconnect, s.startDate AS SessionStartdate, c.logonenddate, c.disconnectdate, s.enddate AS SessionEndDate, c.sessionkey FROM [CitrixRofficeMonitoring].[MonitorData].[Connection] AS [c] LEFT JOIN [CitrixRofficeMonitoring].[MonitorData].[Session] AS [s] ON s.SessionKey = c.SessionKey LEFT JOIN [CitrixRofficeMonitoring].[MonitorData].[Machine] AS [m] ON s.MachineId = m.Id LEFT JOIN [CitrixRofficeMonitoring].[MonitorData].[User] AS [u] ON s.UserId = u.Id WHERE c.Protocol='HDX' AND u.UserName='dengpeng' and m.Name is not null ORDER BY S.StartDate DESC
如下再做一个注释
--查询用户历史会话信息 SELECT top(20) ##如下表示要查询显示的参数 c.Clientname 客户端名称, ##显示客户端名字,别名为客户端名称 c.ClientAddress 客户端IP地址, m.Name AS VDI桌面, u.UserName AS 用户名, c.Protocol 协议, c.IsReconnect, s.startDate AS SessionStartdate, c.logonenddate, c.disconnectdate, s.enddate AS SessionEndDate, c.sessionkey ###需要查询什么就添加,注意最后显示一行的不需要加标点符号,按下数据库别名后,会自动弹出搜索框 FROM ##多表进行联查,LEFT代表左连接,显示以左边为准 [CitrixRofficeMonitoring].[MonitorData].[Connection] AS [c] ##数据库别名为c,这里需要注意,请替换为实际数据库名称后进行查询。 LEFT JOIN [CitrixRofficeMonitoring].[MonitorData].[Session] AS [s] ON s.SessionKey = c.SessionKey ##数据库别名为s c连接到s表 LEFT JOIN [CitrixRofficeMonitoring].[MonitorData].[Machine] AS [m] ON s.MachineId = m.Id ##数据库别名为m LEFT JOIN [CitrixRofficeMonitoring].[MonitorData].[User] AS [u] ON s.UserId = u.Id ####数据库别名为u --WHERE c.Protocol='HDX'##过滤连接协议 AND u.UserName='dengpeng'##过滤用户名 and m.Name is not null ORDER BY S.StartDate DESC 注意代码前面加上-- 代表注释
如下图,用SQL客户端连接数据库后,新建查询,按Ctrl+F键进行查询替换为实际数据库名称