How to report latest eventlog message only

[heybobby1]

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.

[heybobby1]

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]

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]

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]

BTW, if I change this to a MAX I get the latest date but the oldest message.

[heybobby1]

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]

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]

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]

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