Windows Computers Falling Behind in Patching

[jperry]

This report relies on adding three custom registry scans.
Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
Valuename = 'UBR'
Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
Valuename = 'CurrentBuildNumber'
Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
Valuename = 'ReleaseID'
The UBR matches the subcode on the build: https://support.microsoft.com/en-us/help/4018124



I wanted to create a report that identified windows 10 computers that were not applying cumulative updates.
This report shows computers that do no have one of the two newest(in your environment) cumulative updates applied; for the OS Version (1607, 1703, etc.)

Select Top 1000000 tblAssets.AssetID,
    tsysAssetTypes.AssetTypeIcon10 As icon,
    tblAssets.AssetName,
    tblOperatingsystem.Version As [OS Version],
    tblRegistryReleaseID.Value As [ReleaseID],
    Convert([INT], tblRegistryUBR.Value )  AS [UBR],
    Convert([INT], TargetUBR.UBR) AS [UBR Target],
    /* Convert([INT], tblRegistryCurrentBuildNumber.Value ) AS [CurrentBuildNumber], */
    Convert(Date, lastPatchDate.lastPatchDate) AS 'LastPatchDate',
    Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As [Days up],
    tblAssets.Lastseen - Convert(Decimal,tblAssets.Uptime / 60 / 60 /  24) As [Boot Time],
    Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) [Free Space],
    tblAssets.Lastseen,
    Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) As OU,
    '<img src="thumbnail.aspx?user=' + tblADusers.Username + '&domain=' +
    tblADusers.Userdomain + '&size=12" class="rimage"/>' As Picture,
    tblADusers.Displayname,
    tblADusers.Username,
    tblADusers.Userdomain,
    tblAssetCustom.Model,
    tblOperatingsystem.Caption As [OS Name],
    case
        WHEN tblComputersystem.Domainrole = 0 Then 'Standalone Workstation'
        WHEN tblComputersystem.Domainrole = 1 Then 'Member Workstation'
        WHEN tblComputersystem.Domainrole = 2 Then 'Standalone Server'
        WHEN tblComputersystem.Domainrole = 3 Then 'Member Server'
        WHEN tblComputersystem.Domainrole = 4 Then 'Backup Domain Controller'
        WHEN tblComputersystem.Domainrole = 5 Then 'Primary Domain Controller'
        ELSE Convert(varchar, tblComputersystem.Domainrole) END
    AS [Domain Role],
    tsysIPLocations.IPLocation,
    tblAssets.Description As [LS Description],
tblADComputers.Description As [AD Description]
From tblAssets
    Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
    Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username
    Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType =
    tblAssets.Assettype
    Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
    Left Outer Join tblOperatingsystem On tblAssets.AssetID =
    tblOperatingsystem.AssetID
    Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
    Left Outer Join (
        Select tblQuickFixEngineering.AssetID,
            Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate
            From tblQuickFixEngineering
            Where isdate(tblQuickFixEngineering.InstalledOn) = 1
            Group By tblQuickFixEngineering.AssetID) 
        As lastPatchDate On lastPatchDate.AssetID = tblAssets.AssetID
    Left Outer Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID And tblDiskdrives.Caption Like 'C%'
    Left Outer Join (
        Select tblRegistry.AssetID, convert([int], tblRegistry.Value) As Value
            from tblRegistry
            Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                And  tblRegistry.Valuename = 'UBR' )
        As tblRegistryUBR On tblAssets.AssetID = tblRegistryUBR.AssetID
    Left Outer Join (
        Select tblRegistry.AssetID, tblRegistry.Value
            from tblRegistry
            Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                And  tblRegistry.Valuename = 'CurrentBuildNumber' )
        As tblRegistryCurrentBuildNumber On tblAssets.AssetID = tblRegistryCurrentBuildNumber.AssetID
    Left Outer Join (
        Select tblRegistry.AssetID, Convert([INT], tblRegistry.Value) AS Value
            from tblRegistry
            Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                And  tblRegistry.Valuename = 'ReleaseID' )
        As tblRegistryReleaseID On tblAssets.AssetID = tblRegistryReleaseID.AssetID
    Left Outer JOIN (
            /* Find Second highest UBR for the version (or highest if there is only 1) */
            Select
                tblRegistryCurrentBuildNumber.Value As [CurrentBuildNumber],
                Max(convert([int], tblRegistryUBR.Value)) AS [UBR]
            From tblAssets
                Join (
                    Select tblRegistry.AssetID, tblRegistry.Value
                    from tblRegistry
                    Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                        And  tblRegistry.Valuename = 'UBR' )
                    As tblRegistryUBR On tblAssets.AssetID = tblRegistryUBR.AssetID
                Join (
                    Select tblRegistry.AssetID, tblRegistry.Value
                    from tblRegistry
                    Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                        And  tblRegistry.Valuename = 'CurrentBuildNumber' )
                    As tblRegistryCurrentBuildNumber On tblAssets.AssetID = tblRegistryCurrentBuildNumber.AssetID
            Where tblRegistryCurrentBuildNumber.Value + '+' + tblRegistryUBR.Value Not IN (
                /* Find highest version if there is more than one version */
                Select
                tblRegistryCurrentBuildNumber.Value + '+' + convert(Varchar, Max( convert([int], tblRegistryUBR.Value)))  AS [UBR]
                From tblAssets
                Join (
                    Select tblRegistry.AssetID, tblRegistry.Value
                    from tblRegistry
                    Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                        And  tblRegistry.Valuename = 'UBR' )
                    As tblRegistryUBR On tblAssets.AssetID = tblRegistryUBR.AssetID
                Join (
                    Select tblRegistry.AssetID, tblRegistry.Value
                    from tblRegistry
                    Where tblRegistry.Regkey =  'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                        And  tblRegistry.Valuename = 'CurrentBuildNumber' )
                    As tblRegistryCurrentBuildNumber On tblAssets.AssetID = tblRegistryCurrentBuildNumber.AssetID
               
                 Group By tblRegistryCurrentBuildNumber.Value
                 HAVING Max( tblRegistryUBR.Value) <> Min( tblRegistryUBR.Value) )
            Group By tblRegistryCurrentBuildNumber.Value
    ) AS TargetUBR ON TargetUBR.CurrentBuildNumber = tblRegistryCurrentBuildNumber.Value
Where tblAssetCustom.State = 1 
  /* This is where the version and desired version are compared 
     use one of the two below lines. The first will include machines where data is not collected, the second will not */
  And  ( TargetUBR.UBR > tblRegistryUBR.Value OR tblRegistryUBR.Value IS NULL )
  /* And TargetUBR.UBR > tblRegistryUBR.Value */
    
  /* filter by OU */
  /* And  tblADComputers.OU Like '%OU=Workstations,%' */
  
  /* filter by domain role */
  /* AND tblComputersystem.Domainrole IN (0, 1) */
  
  /* exclude XP */
  AND tblOperatingsystem.Version Not Like '5.%'
Order By
/* put not scanned yet at end */
Case WHEN tblRegistryUBR.Value is Null Then '1' Else '0' END,
/* put servers first */
Case When tblComputersystem.Domainrole < 2 Then '1' Else '0' END,
tblOperatingsystem.Version,
tblRegistryReleaseID.Value,
tblRegistryUBR.Value,
tblOperatingsystem.Caption,
lastPatchDate.lastPatchDate

Don't forget to "like" if this was useful for you.

Edited Oct 11th 2017 - Fixed issue with comparing Ints as strings.
Edited Mar 05 2018 - Fixed issue with validating bad dates

[jacob_bks]

Thank you for this report!! Specifically, your joins and columns for a nice presentation. I'm always thinking about things you can report on via registry and file values... (like SCCM, McAfee, etc)

Most helpful when dealing with > 25,000 workstations

[jperry]

Error while saving: "There was an error parsing the query. [ Token line number = 1,Token line offset = 530,Token in error = Left ]

After making the edits on October 19, Change line 14 from

   Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) As OU,

to

tblADComputers.OU As OU,

Let me know if that works for you.
Also, what database engine are you running? [M$ SQL 2012, Oracle?]

[milpo2717]

Spent some time on this report, although not a SQL guy by any stretch.

Putting the report in SQL (Edited on Oct 11), I get this error:

Error: The specified data type is not valid. [ Data type (if known) = Date ]

After making the edits on October 19, I get this error:

Error while saving: "There was an error parsing the query. [ Token line number = 1,Token line offset = 530,Token in error = Left ]

Help!

JMM

[jperry]

Getting the following error:
Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

You're getting that error because something in your database's dbo.tblQuickFixEngineering InstalledOn field isn't being recognized as a date format by SQL.


Remove this lines from the SELECT section

    Convert(Date, lastPatchDate.lastPatchDate) AS 'LastPatchDate',

Remove these lines from the FROM section:

    Left Outer Join (
        Select tblQuickFixEngineering.AssetID,
            Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate
            From tblQuickFixEngineering
            Where tblQuickFixEngineering.InstalledOn Like '%/%'
            Group By tblQuickFixEngineering.AssetID) 
        As lastPatchDate On lastPatchDate.AssetID = tblAssets.AssetID

Remove this line from the ORDER BY Section

,
lastPatchDate.lastPatchDate

(don't forget the commas)


That should get you working.

P.S. There was a field for last updated, but I can't find it now. It was always NULL for me so I used this method instead.

[comtcjm]

Getting the following error:
Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Am I missing something?

thanks

[jperry]

comtcjm wrote:
Getting the following error:
Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Am I missing something?

thanks

I was missing something:

Where tblQuickFixEngineering.InstalledOn Like '%/%' 

Should be:

Where isdate(tblQuickFixEngineering.InstalledOn) = 1 

I've updated the query above.

You can see the bad data with this query:

Select tblQuickFixEngineering.AssetID,
    tblQuickFixEngineering.InstalledOn
  From tblQuickFixEngineering
  Where tblQuickFixEngineering.InstalledOn <> '' and Isdate(tblQuickFixEngineering.InstalledOn) <> 1
  

I had a bunch of dates: 12/31/1600 16:00:53

[jacob_bks]

Thanks JPerry, wish there was a way to message inside forum - would love to share neat queries/uses for Lansweeper -

Looking forward to more posts