Computer time zone setting

[Susan_A]

The report below lists your Windows computers' time zones. The report will only list assets that meet all of the following criteria:

• The asset is a Windows computer.
  
• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  'UTC' + ' ' + (Case When tblComputersystem.CurrentTimeZone > 0 Then '+'
    Else '' End) + (Case When tblComputersystem.CurrentTimeZone = 0 Then ''
    Else (Cast(Floor((tblComputersystem.CurrentTimeZone / 60)) As nvarchar))
  End) As Timezone,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[JSchlackman]

The above report (and the one that currently comes built into Lansweeper that is based on this) does not correctly deal with fractional time zones such as India Standard Time (UTC+5:30) due to the use of the FLOOR function. The following definition for the Timezone column will handle these time zones correctly, and format the output in ISO 8601 format:

'UTC' + (Case
  When tblComputersystem.CurrentTimeZone < 0 Then '-'
  Else '+'
End) + Format(DateAdd(Minute, Abs(tblComputersystem.CurrentTimeZone), 0), 'HH:mm') As Timezone