当前位置：网站首页>How to perform multi table associated query on Citrix SQL data

How to perform multi table associated query on Citrix SQL data

2022-04-22 13:32:00 【51CTO Deng Peng】

In addition to DDC Check the relevant information of the login user on the console , We can also go through Citrix Connected SQL Database to view . Check through the database , You can see more information .

Here is an example of a multi table query case .

 
       -- Query user history session information 
       
       SELECT  top
       
       (
       
       20
       
       )   
       
    c
       
       .Clientname  Client name 
       
       ,
       
    c
       
       .ClientAddress  client IP Address 
       
       ,
       
    m
       
       .Name 
       
       AS VDI desktop 
       
       ,
       
    u
       
       .UserName 
       
       AS  user name 
       
       ,
       
    c
       
       .Protocol  agreement 
       
       ,
       
    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
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

Make another comment below

 
       -- Query user history session information 
       
       SELECT  top
       
       (
       
       20
       
       )                                         ## The following indicates the parameters to be queried and displayed  
       
    c
       
       .Clientname  Client name 
       
       ,                 ## Display client name , Alias is client name 
       
    c
       
       .ClientAddress  client IP Address 
       
       ,
       
    m
       
       .Name 
       
       AS VDI desktop 
       
       ,
       
    u
       
       .UserName 
       
       AS  user name 
       
       ,
       
    c
       
       .Protocol  agreement 
       
       ,
       
    c
       
       .IsReconnect
       
       ,
       
    s
       
       .startDate 
       
       AS SessionStartdate
       
       ,
       
    c
       
       .logonenddate
       
       ,
       
    c
       
       .disconnectdate
       
       ,
       
    s
       
       .enddate 
       
       AS SessionEndDate
       
       ,
       
    c
       
       .sessionkey         ### Add whatever you need to query , Note that the last line displayed does not need punctuation , Press the database alias , The search box will pop up automatically 
       
       FROM         ## Perform an associated query on multiple tables ,LEFT Represents the left connection , The display is subject to the left 
       
       [CitrixRofficeMonitoring
       
       ].
       
       [MonitorData
       
       ].
       
       [Connection
       
       ] 
       
       AS 
       
       [c
       
       ]  ## The database alias is c, Here we need to pay attention to , Please replace with the actual database name and query .
       
    LEFT 
       
       JOIN 
       
       [CitrixRofficeMonitoring
       
       ].
       
       [MonitorData
       
       ].
       
       [Session
       
       ] 
       
       AS 
       
       [s
       
       ] 
       
       ON s
       
       .SessionKey 
       
       = c
       
       .SessionKey  ## The database alias is s  c Connect to s surface 
       
    LEFT 
       
       JOIN 
       
       [CitrixRofficeMonitoring
       
       ].
       
       [MonitorData
       
       ].
       
       [Machine
       
       ] 
       
       AS 
       
       [m
       
       ] 
       
       ON s
       
       .MachineId 
       
       = m
       
       .Id   ## The database alias is m   
       
    LEFT 
       
       JOIN 
       
       [CitrixRofficeMonitoring
       
       ].
       
       [MonitorData
       
       ].
       
       [User
       
       ] 
       
       AS 
       
       [u
       
       ] 
       
       ON s
       
       .UserId 
       
       = u
       
       .Id              #### The database alias is u
       
       --WHERE c.Protocol='HDX'## Filter connection protocols  AND u.UserName='dengpeng'## Filter user name  and m.Name is not null 
       
       ORDER 
       
       BY S
       
       .StartDate 
       
       DESC
       
 Notice that the code is preceded by 
       
       --  For comments 
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

Here's the picture , use SQL After the client connects to the database , New query , Press Ctrl+F Key to query and replace with the actual database name

Citrix SQL How to perform multi table associated query on data _ user name