→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jperry
Engaged Sweeper III
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
7 REPLIES 7
jacob_bks
Champion Sweeper
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
Engaged Sweeper III
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
Engaged Sweeper
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
Engaged Sweeper III
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
Engaged Sweeper II
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
Engaged Sweeper III
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
Champion Sweeper
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