View Windows Automatic Update Configuration Settings
Organizations use Windows update configuration settings to manage when devices receive updates and also how potential system reboots are scheduled. By using WSUS, organizations ensure that the right updates are sent to devices at the right time. Due to potential issues with windows updates, updating all your devices at the same time might not be the best setup. By utilizing the options that WSUS provides, you can ensure your updates run smoothly.
The report below uses the Windows automatic update configuration registry keys to provide you an overview of all your assets along with the settings they have for Windows automatic updates. If you're interested in more ways to efficiently Windows updates you can take a look at the related Windows Server Update Services blog post.
Windows Automatic Update Configuration Query
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Version,
tblAssets.SP,
SubQuery1.AUOptions,
SubQuery2.AutoInstallMinorUpdates,
SubQuery3.DetectionFrequency,
SubQuery4.DetectionFrequencyEnabled,
SubQuery5.NoAutoRebootWithLoggedOnUsers,
SubQuery6.NoAutoUpdate,
SubQuery7.RebootRelaunchTimeout,
SubQuery8.RebootRelaunchTimeoutEnabled,
SubQuery9.RebootWarningTimeout,
SubQuery10.RebootWarningTimeoutEnabled,
SubQuery11.RescheduleWaitTime,
SubQuery12.RescheduleWaitTimeEnabled,
SubQuery13.ScheduledInstallDay,
SubQuery14.ScheduledInstallTime,
SubQuery15.UseWUServer,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Information may not be up-to-date. Try rescanning this machine.'
End As Comment,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
TsysLastscan.Lasttime As LastRegistryScan,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
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
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
Case
When tblRegistry.Value = 2 Then 'Notify before download'
When tblRegistry.Value = 3 Then
'Automatically download and notify of installation'
When tblRegistry.Value = 4 Then
'Automatically download and schedule installation'
When tblRegistry.Value = 5 Then
'Automatic Updates is required and users can configure it'
End As AUOptions
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'AUOptions') SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As AutoInstallMinorUpdates
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'AutoInstallMinorUpdates') SubQuery2 On
SubQuery2.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As DetectionFrequency
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'DetectionFrequency') SubQuery3 On
SubQuery3.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As DetectionFrequencyEnabled
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'DetectionFrequencyEnabled') SubQuery4 On
SubQuery4.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As NoAutoRebootWithLoggedOnUsers
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'NoAutoRebootWithLoggedOnUsers') SubQuery5 On
SubQuery5.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As NoAutoUpdate
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'NoAutoUpdate') SubQuery6 On SubQuery6.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As RebootRelaunchTimeout
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'RebootRelaunchTimeout') SubQuery7 On
SubQuery7.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As RebootRelaunchTimeoutEnabled
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'RebootRelaunchTimeoutEnabled') SubQuery8 On
SubQuery8.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As RebootWarningTimeout
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'RebootWarningTimeout') SubQuery9 On
SubQuery9.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As RebootWarningTimeoutEnabled
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'RebootWarningTimeoutEnabled') SubQuery10 On
SubQuery10.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As RescheduleWaitTime
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'RescheduleWaitTime') SubQuery11 On
SubQuery11.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As RescheduleWaitTimeEnabled
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'RescheduleWaitTimeEnabled') SubQuery12 On
SubQuery12.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
Case
When tblRegistry.Value = 0 Then 'Every day'
When tblRegistry.Value = 1 Then 'Sunday'
When tblRegistry.Value = 2 Then 'Monday'
When tblRegistry.Value = 3 Then 'Tuesday'
When tblRegistry.Value = 4 Then 'Wednesday'
When tblRegistry.Value = 5 Then 'Thursday'
When tblRegistry.Value = 6 Then 'Friday'
When tblRegistry.Value = 7 Then 'Saturday'
End As ScheduledInstallDay
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'ScheduledInstallDay') SubQuery13 On
SubQuery13.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As ScheduledInstallTime
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'ScheduledInstallTime') SubQuery14 On
SubQuery14.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value As UseWUServer
From tblRegistry
Where
tblRegistry.Regkey Like
'%Software\Policies\Microsoft\Windows\WindowsUpdate\AU' And
tblRegistry.Valuename = 'UseWUServer') SubQuery15 On SubQuery15.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName
