â€Ž08-23-2018 01:51 PM
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
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
â€Ž09-03-2018 03:33 PM
â€Ž09-06-2018 11:20 AM
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.
â€Ž08-30-2018 11:03 AM
Min(Event20.NTLogMsg) Over (Partition By tblAssets.AssetID)
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
â€Ž08-30-2018 11:13 AM
â€Ž09-03-2018 03:04 PM
heybobby1 wrote:
BTW, if I change this to a MAX I get the latest date but the oldest message.
â€Ž08-24-2018 01:53 PM
â€Ž08-29-2018 04:24 PM
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
â€Ž09-03-2018 03:24 PM
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?
Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.