SQL Script - Client logs for GUI start and GUI end

Log time, Log Type, Device name, MACaddress, IP address

Written by Ines

Last published at: June 23rd, 2023

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