VMware Horizon - 如何查詢 VDI 登入和登出紀錄

在 VMware Horizon 架構中，系統運作狀態及 VDI 桌面相關的紀錄，都會記錄在事件資料庫 (Event DB) 內。設定完事件資料庫後，從 Horizon Console 管理介面，能夠查看 VDI 桌面以及系統運作相關事件，以幫助管理者進行故障排除。但如果管理者想知道每天有多少使用者？在哪些時段？連線進來使用 VDI 桌面，則可以透過 SQL 語法查詢 VDI 桌面的登出和登入記錄。

SQL 語法

查詢 VDI 桌面登出和登入紀錄的 SQL 語法如下，此範例可以列出最近 7 天的連線紀錄

select
    Loggin.SessionID,
    UserName.username as 'User',
    Loggin.StartSession as 'StartTime',
    Logout.EndSession as 'EndTime'
from
    (select H1.EventID, H1.Time as StartSession, D1.StrValue as SessionID
    from [Event_DB].[dbo].[event_historical] H1
    LEFT JOIN [Event_DB].[dbo].[event_data] D1
    ON  H1.EventID = D1.EventID AND D1.name = 'BrokerSessionId'
    where H1.EventType = 'BROKER_USERLOGGEDIN' and datediff(day,Time, Getdate())<7) Loggin,
    
    (select H2.EventID, H2.Time as EndSession, D2.StrValue as SessionID
    from [Event_DB].[dbo].[event_historical] H2
    LEFT JOIN [Event_DB].[dbo].[event_data] D2
    ON  H2.EventID = D2.EventID AND D2.name = 'BrokerSessionId'
    where H2.EventType = 'BROKER_USERLOGGEDOUT' and datediff(day,Time, Getdate())<7) Logout,
    
    (select H3.EventID, D3.StrValue as username
    from [Event_DB].[dbo].[event_historical] H3
    LEFT JOIN [Event_DB].[dbo].[event_data] D3
    ON  H3.EventID = D3.EventID AND D3.name = 'UserDisplayName'
    where H3.EventType = 'BROKER_USERLOGGEDIN' and datediff(day,Time, Getdate())<7) UserName

where 
    Loggin.SessionID = Logout.SessionID and UserName.EventID = Loggin.EventID
Order by Loggin.StartSession

如果想要查詢不同時間區間的紀錄，修改 where 條件就能查詢不同時間區間的紀錄。例如：想要查詢 6/1 ~ 6/30 的紀錄，可以將 SQL 語法修改為：

select
    Loggin.SessionID,
    UserName.username as 'User',
    Loggin.StartSession as 'StartTime',
    Logout.EndSession as 'EndTime'
from
    (select H1.EventID, H1.Time as StartSession, D1.StrValue as SessionID
    from [Event_DB].[dbo].[event_historical] H1
    LEFT JOIN [Event_DB].[dbo].[event_data] D1
    ON  H1.EventID = D1.EventID AND D1.name = 'BrokerSessionId'
    where H1.EventType = 'BROKER_USERLOGGEDIN' and (H1.Time between '2023-06-01' and '2023-06-30')) Loggin,
    
    (select H2.EventID, H2.Time as EndSession, D2.StrValue as SessionID
    from [Event_DB].[dbo].[event_historical] H2
    LEFT JOIN [Event_DB].[dbo].[event_data] D2
    ON  H2.EventID = D2.EventID AND D2.name = 'BrokerSessionId'
    where H2.EventType = 'BROKER_USERLOGGEDOUT' and (H2.Time between '2023-06-01' and '2023-06-30')) Logout,
    
    (select H3.EventID, D3.StrValue as username
    from [Event_DB].[dbo].[event_historical] H3
    LEFT JOIN [Event_DB].[dbo].[event_data] D3
    ON  H3.EventID = D3.EventID AND D3.name = 'UserDisplayName'
    where H3.EventType = 'BROKER_USERLOGGEDIN' and (H3.Time between '2023-06-01' and '2023-06-30')) UserName

where 
    Loggin.SessionID = Logout.SessionID and UserName.EventID = Loggin.EventID
Order by Loggin.StartSession