Error in August 2024 Patch Tuesday Report

[CarlV]

The SQL query has a command "Try_convert" in it.  This is an invalid command.  Revised code below.

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
Case
When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation'
End As [Workstation/Server],
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Version,
SubQuery2.Buildnumber,
PatchIDMax as [Highest KB Patch installed],
CASE
    WHEN tsysOS.OSname = 'Win 2008' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname IN ('Win 7', 'Win 7 RC', 'Win 2008 R2') THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname = 'Win 8' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname = 'Win 2012' and SubQuery3.AssetID = tblassets.AssetID then 'Up to date'
    WHEN tsysOS.OSname = 'Win 8.1' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OSname = 'Win 2012 R2' and SubQuery3.AssetID = tblassets.AssetID then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.10240%' And SubQuery2.Buildnumber >= 20751 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.10586%' THEN 'EOL, update to a higher Windows version'
    WHEN (tsysOS.OScode LIKE '10.0.14393%' OR tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7259 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.15063%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.16299%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.17134%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption NOT LIKE '%LTSC%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.17763' AND tblOperatingsystem.Caption LIKE '%LTSC%' And SubQuery2.Buildnumber >= 6189 Then 'Up to date'
WHEN tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6189 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.18362%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.18363%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19041%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19042%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19043%' THEN 'EOL, update to a higher Windows version'
    WHEN tsysOS.OScode LIKE '10.0.19044%' And SubQuery2.Buildnumber >= 4780 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.19045%' And SubQuery2.Buildnumber >= 4780 Then 'Up to date'
WHEN tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 2655 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.22000%' And SubQuery2.Buildnumber >= 3147 Then 'Up to date'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') And SubQuery2.Buildnumber >= 4037 Then 'Up to date'
    WHEN tsysOS.OScode LIKE '10.0.25398%' And SubQuery2.Buildnumber >= 1085 Then 'Up to date'
Else 'Out of date'
End As [Patch status],
tblAssets.Username,
tblAssets.Userdomain,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblState.Statename As State,
tblAssets.Lastseen,
tblAssets.Lasttried,
QuickFixLastScanned.QuickFixLastScanned,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
Case
When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
GetDate())) > 3 Then
'Windows update information may not be up to date. We recommend rescanning this machine.'
Else ''
End As Comment,
CASE
    WHEN tsysOS.OSname = 'Win 2008' THEN '#ffadad'
    WHEN tsysOS.OSname IN ('Win 7', 'Win 7 RC', 'Win 2008 R2') THEN '#ffadad'
    WHEN tsysOS.OSname = 'Win 8' THEN '#ffadad'
    WHEN tsysOS.OSname = 'Win 2012' and SubQuery3.AssetID = tblassets.AssetID then '#d4f4be'
    WHEN tsysOS.OSname = 'Win 8.1' THEN '#ffadad'
    WHEN tsysOS.OSname = 'Win 2012 R2' and SubQuery3.AssetID = tblassets.AssetID then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.10240%' And SubQuery2.Buildnumber >= 20751 Then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.10586%' THEN '#ffadad'
    WHEN (tsysOS.OScode LIKE '10.0.14393%' OR tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7259 Then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.15063%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.16299%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.17134%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.17763%' AND tblOperatingsystem.Caption NOT LIKE '%LTSC%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.17763%' AND tblOperatingsystem.Caption LIKE '%LTSC%' And SubQuery2.Buildnumber >= 6189 Then '#d4f4be'
    WHEN tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6189 Then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.18362%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.18363%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.19041%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.19042%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.19043%' THEN '#ffadad'
    WHEN tsysOS.OScode LIKE '10.0.19044%' And SubQuery2.Buildnumber >= 4780 Then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.19045%' And SubQuery2.Buildnumber >= 4780 Then '#d4f4be'
    WHEN tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 2655 Then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.22000%' And SubQuery2.Buildnumber >= 3147 Then '#d4f4be'
    WHEN (tsysOS.OScode LIKE '10.0.22621%' OR tsysOS.OScode LIKE '10.0.22631%') And SubQuery2.Buildnumber >= 4037 Then '#d4f4be'
    WHEN tsysOS.OScode LIKE '10.0.25398%' And SubQuery2.Buildnumber >= 1085 Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
left Join (select tblassets.AssetID, convert(bigint,tblassets.BuildNumber) as Buildnumber from tblAssets where Assettype = -1) As SubQuery2 On tblAssets.AssetID =
SubQuery2.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB5041851','KB5041828')) As SubQuery3 On
tblAssets.AssetID = SubQuery3.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID,
Max(Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint)) As
PatchIDMax
From tblQuickFixEngineering
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Where Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%'
Group By tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID =
SubQuery1.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
TsysLastscan.Lasttime As QuickFixLastScanned
From TsysWaittime
Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
tblAssets.AssetID = QuickFixLastScanned.ID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tsysOS.OSname <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
tsysOS.OSname Not Like '%2003%' And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
tblAssets.AssetName

[Esben_D]

I updated the report with an alternative to TRY_CONVERT

CASE
WHEN ISNUMERIC(tblAssets.BuildNumber) = 1 THEN CONVERT(bigint, tblAssets.BuildNumber)
ELSE NULL
END as Buildnumber

[Esben_D]

I found online that TRY_CONVERT function is supported by SQL Server 2012 and higher. If you have an older SQL Version, that will be the issue.

A way to resolve it is to replace the TRY_CONVERT to just the regular CONVERT. The Try was added as an extra safety mechanism to avoid errors, but I didn't know that it was limited to newer versions.

What Database version are you using?

[JanC]

Tried with the updated Patch tuesday script - still getting error saying "Conversion failed when converting date and/or time from character string."