→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
heybobby1
Engaged Sweeper III
Hi guys, struggling with this one. I'm putting together a report for Windows 10 Feature Updates and I want to pull the last feature update error event log only. Can anyone help? This is my report:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
Case When tblOperatingsystem.Version Like '10.0.17134' Then '1803'
When tblOperatingsystem.Version Like '10.0.16299' Then '1709'
When tblOperatingsystem.Version Like '10.0.15063' Then '1703'
When tblOperatingsystem.Version Like '10.0.14393' Then '1607'
End As OSVersion,
Case When tblOperatingsystem.Version Not Like '10.0.17134' Then '#C4E3F3'
End As backgroundcolor,
Case When tblOperatingsystem.Version Like '10.0.17134' Then 'Up-to-date'
Else 'Not Up-to-date' End As UpdateStatus,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x8024200D%' Then '0x8024200D'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x8024001e%' Then '0x8024001e'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0xC1900200%' Then '0xC1900200'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x80240020%' Then '0x80240020'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0xc1900130%' Then '0xc1900130'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x80070005%' Then '0x80070005'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x800704c7%' Then '0x800704c7'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x80070643%' Then '0x80070643'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0xc1900107%' Then '0xc1900107'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x80240fff%' Then '0x80240fff'
When tblOperatingsystem.Version Not Like '10.0.17134' And
Event20.NTLogMsg Like '%0x80070032%' Then '0x80070032'
End As UpdateErrorCode,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' Then Event20.NTLogMsg
End As UpdateErrorMessage,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' Then
Event20.LatestTimeGenerated End As UpdateErrorDate,
Case When SubQuery.Value Like '%Ring 0%' Then 'Ring 0'
When SubQuery.Value Like '%Ring 1%' Then 'Ring 1'
When SubQuery.Value Like '%Ring 2%' Then 'Ring 2'
When SubQuery.Value Like '%Ring 3%' Then 'Ring 3'
When SubQuery.Value Like '%Ring 4%' Then 'Ring 4'
When SubQuery.Value Like '%Ring 5%' Then 'Ring 5'
When SubQuery.Value Like '%Ring 6%' Then 'Ring 6' End As RingGroup,
Case When SubQuery.Value Like '%Ring 0%' Then '03/05/2018'
When SubQuery.Value Like '%Ring 1%' Then '17/05/2018'
When SubQuery.Value Like '%Ring 2%' Then '29/05/2018'
When SubQuery.Value Like '%Ring 3%' Then '11/06/2018'
When SubQuery.Value Like '%Ring 4%' Then '18/06/2018'
When SubQuery.Value Like '%Ring 5%' Then '25/06/2018'
When SubQuery.Value Like '%Ring 6%' Then '05/07/2018'
End As UpdateReleaseDate,
Case
When tblOperatingsystem.Version Like '10.0.17134' Then
tblOperatingsystem.InstallDate End As OSInstallDate,
SubQuery.Value As WSUSTargetGroup,
SubQuery5.Value As WUServer,
Case When SubQuery1.Value Like '0' Then 'HTTP Only'
When SubQuery1.Value Like '1' Then 'LAN'
When SubQuery1.Value Like '2' Then 'Group'
When SubQuery1.Value Like '3' Then 'Internet'
When SubQuery1.Value Like '99' Then 'Simple'
When SubQuery1.Value Like '100' Then 'Bypass' End As DODownloadMode,
SubQuery3.Value As DODownloadMonthlyLanBytes,
SubQuery4.Value As DOUploadMonthlyLanBytes,
SubQuery2.Value As MaxCacheAge,
Case When SubQuery2.Value Like '0' Then 'Yes' Else 'No'
End As PreferredCacheDevice,
tblAssetCustom.Custom1 As Office,
tblAssetCustom.Custom2 As Country,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As [User],
tblAssets.Username As Lastuser,
tblState.Statename As State
From tblAssets
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'TargetGroup') SubQuery On tblAssets.AssetID =
SubQuery.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'DODownloadMode') SubQuery1
On tblAssets.AssetID = SubQuery1.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'DOMaxCacheAge') SubQuery2
On tblAssets.AssetID = SubQuery2.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'DownloadMonthlyLanBytes') SubQuery3
On tblAssets.AssetID = SubQuery3.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'UploadMonthlyLanBytes') SubQuery4
On tblAssets.AssetID = SubQuery4.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'WUServer') SubQuery5 On tblAssets.AssetID =
SubQuery5.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select tblNtlog.AssetID,
tblNtlogMessage.Message As NTLogMsg,
Max(tblNtlog.TimeGenerated) As LatestTimeGenerated
From tblNtlog
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Where tblNtlog.Eventcode = '20' And tblNtlogSource.Sourcename Like
'%Microsoft-Windows-WindowsUpdateClient%' And tblNtlogMessage.Message Like
'%feature update%'
Group By tblNtlog.AssetID,
tblNtlogMessage.Message) As Event20 On Event20.AssetID = tblAssets.AssetID
Where tsysOS.OSname = 'Win 10'
Order By WSUSTargetGroup,
Country,
Office,
OSVersion


This is the bit of code for the eventlog:

  Left Join (Select tblNtlog.AssetID,
tblNtlogMessage.Message As NTLogMsg,
Max(tblNtlog.TimeGenerated) As LatestTimeGenerated
From tblNtlog
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Where tblNtlog.Eventcode = '20' And tblNtlogSource.Sourcename Like
'%Microsoft-Windows-WindowsUpdateClient%' And tblNtlogMessage.Message Like
'%feature update%'
Group By tblNtlog.AssetID,
tblNtlogMessage.Message) As Event20 On Event20.AssetID = tblAssets.AssetID


Most assets are reported once but if an asset has 2 different errors for example, it's reported twice. I only want the latest error.

Hope you can help. I'm not a SQL expert, which probably shows. I pick up stuff on the forums here and I try stuff. I might have it a bit confused as a result though.
8 REPLIES 8
heybobby1
Engaged Sweeper III
Cheers Charles. Added it but it's only reported for about 1/10th of the W10 machines in my report. Not sure why that is.
heybobby1
Engaged Sweeper III
heybobby1 wrote:
Cheers Charles. Added it but it's only reported for about 1/10th of the W10 machines in my report. Not sure why that is.


Lspush not scanning this? On my computer which has been scanned by Lspush a dozen times since I upgraded Lansweeper, I couldn't see "Build" or "Version", however they appeared after I did a rescan asset on the asset page.
heybobby1
Engaged Sweeper III
I probably have this a bit mixed up but I managed to get one eventlog per asset by changing the select for the NTLogMessage, error code and date to use Min Over Partition by tblAssets.AssetID, e.g.
Min(Event20.NTLogMsg) Over (Partition By tblAssets.AssetID)


This seemed to rewrite the multiple entries so they were the same. I then changed the Select to a Distinct which got rid of the duplicates.

It's not working quite right though. Looking at one asset that was previously reported twice with different error codes, I'm now seeing it once, with the latest error message, which is good. However, the date being reported is for the oldest message. Is there a way I can sort it and select the top row maybe?

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
Case
When tblOperatingsystem.Version Like '10.0.17134' Then '1803'
When tblOperatingsystem.Version Like '10.0.16299' Then '1709'
When tblOperatingsystem.Version Like '10.0.15063' Then '1703'
When tblOperatingsystem.Version Like '10.0.14393' Then '1607'
End As OSVersion,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' Then '#C4E3F3'
End As backgroundcolor,
Case
When tblOperatingsystem.Version Like '10.0.17134' Then 'Up-to-date'
Else 'Not Up-to-date'
End As UpdateStatus,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' Then
Min(SubString(Event20.NTLogMsg, CharIndex('0x', Event20.NTLogMsg), 10))
Over (Partition By tblAssets.AssetID)
End As UpdateErrorCode,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' Then
Min(Event20.NTLogMsg) Over (Partition By tblAssets.AssetID)
End As UpdateErrorMessage,
Case
When tblOperatingsystem.Version Not Like '10.0.17134' Then
Min(Event20.LatestTimeGenerated) Over (Partition By tblAssets.AssetID)
End As UpdateErrorDate,
Case
When SubQuery.Value Like '%Ring%' Then SubString(SubQuery.Value,
CharIndex('Ring', SubQuery.Value), 6)
End As RingGroup,
Case
When SubQuery.Value Like '%Ring 0%' Then '03/05/2018'
When SubQuery.Value Like '%Ring 1%' Then '17/05/2018'
When SubQuery.Value Like '%Ring 2%' Then '29/05/2018'
When SubQuery.Value Like '%Ring 3%' Then '11/06/2018'
When SubQuery.Value Like '%Ring 4%' Then '18/06/2018'
When SubQuery.Value Like '%Ring 5%' Then '25/06/2018'
When SubQuery.Value Like '%Ring 6%' Then '05/07/2018'
End As UpdateReleaseDate,
Case
When tblOperatingsystem.Version Like '10.0.17134' Then
tblOperatingsystem.InstallDate
End As OSInstallDate,
SubQuery.Value As WSUSTargetGroup,
SubQuery5.Value As WUServer,
Case
When SubQuery1.Value Like '0' Then 'HTTP Only'
When SubQuery1.Value Like '1' Then 'LAN'
When SubQuery1.Value Like '2' Then 'Group'
When SubQuery1.Value Like '3' Then 'Internet'
When SubQuery1.Value Like '99' Then 'Simple'
When SubQuery1.Value Like '100' Then 'Bypass'
End As DODownloadMode,
SubQuery3.Value As DODownloadMonthlyLanBytes,
SubQuery4.Value As DOUploadMonthlyLanBytes,
SubQuery2.Value As MaxCacheAge,
Case
When SubQuery2.Value Like '0' Then 'Yes'
Else 'No'
End As PreferredCacheDevice,
tblAssetCustom.Custom1 As Office,
tblAssetCustom.Custom2 As Country,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As [User],
tblAssets.Username As Lastuser,
tblState.Statename As State
From tblAssets
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'TargetGroup') SubQuery On
tblAssets.AssetID = SubQuery.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'DODownloadMode') SubQuery1 On
tblAssets.AssetID = SubQuery1.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'DOMaxCacheAge') SubQuery2 On
tblAssets.AssetID = SubQuery2.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'DownloadMonthlyLanBytes') SubQuery3 On
tblAssets.AssetID = SubQuery3.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'UploadMonthlyLanBytes') SubQuery4
On tblAssets.AssetID = SubQuery4.AssetID
Left Join (Select tblRegistry.Value,
tblRegistry.AssetID
From tblRegistry
Where tblRegistry.Valuename = 'WUServer') SubQuery5 On tblAssets.AssetID =
SubQuery5.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select tblNtlog.AssetID,
tblNtlogMessage.Message As NTLogMsg,
Max(tblNtlog.TimeGenerated) As LatestTimeGenerated
From tblNtlog
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID =
tblNtlog.MessageID
Where tblNtlogMessage.Message Like '%Feature update%' And
tblNtlog.Eventcode = '20' And tblNtlogSource.Sourcename Like
'%Microsoft-Windows-WindowsUpdateClient%'
Group By tblNtlog.AssetID,
tblNtlogMessage.Message) As Event20 On Event20.AssetID =
tblAssets.AssetID
Where tsysOS.OSname = 'Win 10'
Order By WSUSTargetGroup,
Country,
Office,
OSVersion
heybobby1
Engaged Sweeper III
BTW, if I change this to a MAX I get the latest date but the oldest message.
heybobby1
Engaged Sweeper III
heybobby1 wrote:
BTW, if I change this to a MAX I get the latest date but the oldest message.


Given up on this. I tried combinations of Min and Max but cannot be certain the uppermost or lowermost NtLogMsg is selected. Think perhaps because the NtLogMsg contains an error code that can be a mix of letters and numbers the sort is not always as you'd expect.
Esben_D
Lansweeper Employee
Lansweeper Employee
Ok wow, thats a lot to go through.

First, I see that you're using a case to get W10 build numbers. This is not needed if you're running LS 6.230 since this was added into Lansweeper. You can find it in tblAssets.BuildNumber

I usually do these type of things with a lot of trial and error, so it's hard saying exactly what the fix would be. Since you're using multiple sub-queries, I don't think there is a way to prevent the problem you're having.

If you use one query with the Max function, and make a large where clause with all your conditions, it should be possible.
heybobby1
Engaged Sweeper III
Charles.X wrote:
First, I see that you're using a case to get W10 build numbers. This is not needed if you're running LS 6.230 since this was added into Lansweeper. You can find it in tblAssets.BuildNumber


Thanks Charles. So I added tblAssets.BuildNumber but it's not what I was expecting. Mostly nothing is reported but for some PCs numbers such as the following are reported 81,64,611,551,492,228 - can't see what this correlates to?

Esben_D
Lansweeper Employee
Lansweeper Employee
heybobby1 wrote:

Thanks Charles. So I added tblAssets.BuildNumber but it's not what I was expecting. Mostly nothing is reported but for some PCs numbers such as the following are reported 81,64,611,551,492,228 - can't see what this correlates to?



My bad, I mentioned the wrong column, its tblAssets.Version