Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Domain,
Case
When tblOperatingsystem.Version Like '10.0.17763' Then
Case
When tRegUBR.Value Like '107' Then '2018-11'
When tRegUBR.Value Like '379' Then '2019-03'
When tRegUBR.Value Like '557' Then '2019-06'
When tRegUBR.Value Like '720' Then '2019-08'
When tRegUBR.Value Like '914' Then '2019-12'
When tRegUBR.Value Like '1131' Then '2020-03'
When tRegUBR.Value Like '1397' Then '2020-08'
When tRegUBR.Value Like '1457' Then '2020-09'
When tRegUBR.Value Like '1490' Then '2020-09'
When tRegUBR.Value Like '1554' Then '2020-10'
When tRegUBR.Value Like '1577' Then '2020-11'
When tRegUBR.Value Like '1637' Then '2020-12'
When tRegUBR.Value Like '1697' Then '2021-01'
When tRegUBR.Value Like '1757' Then '2021-02'
When tRegUBR.Value Like '1817' Then '2021-03'
When tRegUBR.Value Like '1823' Then '2021-03'
End
When tblOperatingsystem.Version Like '10.0.14393' Then
Case
When tRegUBR.Value Like '1884' Then '2017-11'
When tRegUBR.Value Like '2068' Then '2018-02'
When tRegUBR.Value Like '2214' Then '2018-04'
When tRegUBR.Value Like '2248' Then '2018-05'
When tRegUBR.Value Like '2363' Then '2018-07'
When tRegUBR.Value Like '2395' Then '2018-07'
When tRegUBR.Value Like '2485' Then '2018-09'
When tRegUBR.Value Like '2551' Then '2018-10'
When tRegUBR.Value Like '2724' Then '2019-01'
When tRegUBR.Value Like '2759' Then '2019-01'
When tRegUBR.Value Like '3025' Then '2019-06'
When tRegUBR.Value Like '3181' Then '2019-08'
When tRegUBR.Value Like '3204' Then '2019-09'
When tRegUBR.Value Like '3206' Then '2019-09'
When tRegUBR.Value Like '3242' Then '2019-09'
When tRegUBR.Value Like '3243' Then '2019-10'
When tRegUBR.Value Like '3274' Then '2019-10'
When tRegUBR.Value Like '3326' Then '2019-11'
When tRegUBR.Value Like '3384' Then '2019-12'
When tRegUBR.Value Like '3443' Then '2020-01'
When tRegUBR.Value Like '3474' Then '2020-01'
When tRegUBR.Value Like '3504' Then '2020-02'
When tRegUBR.Value Like '3542' Then '2020-02'
When tRegUBR.Value Like '3564' Then '2020-03'
When tRegUBR.Value Like '3595' Then '2020-03'
When tRegUBR.Value Like '3630' Then '2020-04'
When tRegUBR.Value Like '3686' Then '2020-05'
When tRegUBR.Value Like '3750' Then '2020-06'
When tRegUBR.Value Like '3755' Then '2020-06'
When tRegUBR.Value Like '3808' Then '2020-07'
When tRegUBR.Value Like '3866' Then '2020-08'
When tRegUBR.Value Like '3930' Then '2020-09'
When tRegUBR.Value Like '3986' Then '2020-10'
When tRegUBR.Value Like '4046' Then '2020-11'
When tRegUBR.Value Like '4048' Then '2021-03'
When tRegUBR.Value Like '4104' Then '2020-12'
When tRegUBR.Value Like '4169' Then '2021-01'
When tRegUBR.Value Like '4225' Then '2021-02'
When tRegUBR.Value Like '4283' Then '2021-03'
When tRegUBR.Value Like '4288' Then '2021-03'
End
When tblOperatingsystem.Version Like '6.3.9600' Then
Case
When cast(tRegUBR.Value As Int) < 19756 Then '2020-06 or prior'
When tRegUBR.Value Like '19756' Then '2020-07'
When tRegUBR.Value Like '19785' Then '2020-08'
When tRegUBR.Value Like '19817' Then '2020-09'
When tRegUBR.Value Like '19847' Then '2020-09'
When tRegUBR.Value Like '19756' Then '2020-10'
When tRegUBR.Value Like '19873' Then '2020-11'
When tRegUBR.Value Like '19893' Then '2020-12'
When tRegUBR.Value Like '19920' Then '2021-01'
When tRegUBR.Value Like '19941' Then '2021-02'
When tRegUBR.Value Like '19968' Then '2021-03'
End
When tblOperatingsystem.Version Like '6.2.9200' Then
Case
When tRegUBR.Value Like '23149' Then '2020-10'
When tRegUBR.Value Like '' Then '2020-11'
When tRegUBR.Value Like '19893' Then '2020-12'
When tRegUBR.Value Like '23255 ' Then '2021-01'
When tRegUBR.Value Like '23274' Then '2021-02'
When tRegUBR.Value Like '23298' Then '2021-03'
End
When Cast(Replace(tblOperatingsystem.Version, '.', '') As BIGINT) < 620000 Then 'EOL'
End As 'Latest Security Update',
Replace(Replace(tblassets.OScode, 'S', ''), 'R', '') + '.' + tRegUBR.Value As Build,
tblassets.Version,
tblassets.SP,
tblOperatingsystem.Caption As OSName,
tblOperatingsystem.Version As OSVersion,
Cast(tRegUBR.Value As INT) As BuildNumber,
Cast(Replace(tblOperatingsystem.Version, '.', '') As BIGINT) As VersionNumeric,
tblassets.Description,
tblassets.Lastseen,
tblassets.Lasttried
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join (Select tblRegistry.AssetID,
tblRegistry.Value,
tblRegistry.Valuename
From tblRegistry
Where
tblRegistry.Regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion' And
tblRegistry.Valuename='UBR') As tRegUBR
On tblAssets.AssetID = tRegUBR.AssetID
Where tblassetcustom.State = 1 and tblComputersystem.Domainrole > 1
order by tblassets.AssetName
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now