Client logs for GUI start and GUI end

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

Written by Ines

Last published at: December 10th, 2021

Query Description

Display Log Time, Log Type (GUI Start, GUI End), Device Name, MAC Address, IP Address and Public IP Address


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'

  from LogsEvent 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