SQL Script - Client logs for GUI start and GUI end
Log time, Log Type, Device name, MACaddress, IP address
Query Description:
Display Log Time, Log Type (GUI Start, GUI End), Device Name, MAC Address, IP Address and Public IP Address
Make sure that the Event logs are enabled within the Console > Default Device Properties or on a Folder Level using the "Edit Folder" option:
Query Example:
Query
Select le.LogDate, le.LogTime,
'LogType'=CASE
WHEN LogType = 6 THEN 'GUIStart'
WHEN LogType = 7 THEN 'GUIEnd'
End,
df.DeviceName,
'ProductType'=CASE
WHEN ds.ProductType = 0 THEN 'ThinKiosk'
WHEN ds.ProductType = 1 THEN 'ThinIO'
WHEN ds.ProductType = 2 THEN 'Intelliperform'
WHEN ds.ProductType = 3 THEN 'Connector'
WHEN ds.ProductType = 4 THEN 'SecureRemoteWorker'
WHEN ds.ProductType = 5 THEN 'ThinScaleVDA'
WHEN ds.ProductType = 6 THEN 'ManagementServer'
WHEN ds.ProductType = 7 THEN 'ManagementConsole'
WHEN ds.ProductType = 8 THEN 'ValidationTool'
WHEN ds.ProductType = 9 THEN 'ManagementPSModule'
WHEN ds.ProductType = 65534 THEN 'Invalid'
End,
JSON_VALUE(Inventory, '$.MACAddress') AS 'MAC Address',
convert(varchar(3), DeviceIPAddress & 255) + '.'
+ convert(varchar(3), (DeviceIPAddress/256)& 255) + '.'
+ convert(varchar(3), (DeviceIPAddress/65536)& 255) + '.'
+ convert(varchar(3), (DeviceIPAddress/16777216)& 255)
as 'LocalIPAddress',
convert(varchar(3), PublicIPAddressCurrent & 255) + '.'
+ convert(varchar(3), (PublicIPAddressCurrent/256)& 255) + '.'
+ convert(varchar(3), (PublicIPAddressCurrent/65536)& 255) + '.'
+ convert(varchar(3), (PublicIPAddressCurrent/16777216)& 255)
as 'PublicIPAddress'
-- Change LogsEvent to LogsEvent2 if the server is on version 7.4
from LogsEvent/LogeEvent2 le
inner join DeviceFulls df on le.DevicePk = df.DevicePk
inner join DeviceSlims ds on df.DeviceId = ds.DeviceId
where le.LogType = 6 or
le.LogType = 7
order by DeviceName, LogDate, LogTime DESC