cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
borgoncete88
Engaged Sweeper II

Hi all,

I have made a custom report to check if the AV and OS are updated. So my problem is when I'm getting the information from Windows versions, I only got it using two columns, one for Win 10 and the other for Win 11. If version of Win 10 is different from 22H2 is not updated and if the version of Win 11 is different from 23H2 is not updated too. What I want is to join the information from these columns in the same. I don't mind if the asset is Win 11 or 10, I want to check the OS version for every asset in the same column. If anyone can help me it would very  useful. Thank you in advance.

This is my code:

Select Distinct Top 1000000 unioned.assetid,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
tblSoftware.softwareVersion,
Case
When tblSoftware.softwareVersion != '8.0.22' Then 'NO'
Else 'SI'
End As [¿AV Actualizado?],
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssets.Version,
Case
When tsysOS.OSname = 'Win 10' And tblAssets.Version != '22H2' Then 'NO'
Else 'SI'
End As [¿OS Win 10 Actualizado?],
Case
When tsysOS.OSname = 'Win 11' And tblAssets.Version != '23H2' Then 'NO'
Else 'SI'
End As [¿OS Win 11 Actualizado?],
tblAssets.Lastseen
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Where (unioned.software = 'WatchGuard EPDR' And unioned.Enabled = 'Yes' And
tblSoftware.softwareVersion = '8.0.22') Or
(tblSoftware.softwareVersion = '8.0.21')
Order By tblAssets.AssetName Desc

1 ACCEPTED SOLUTION
Hendrik_VE
Champion Sweeper III

Replace the two "case" statements with this one:

Case
When tsysOS.OSname = 'Win 10' And tblAssets.Version = '22H2' Then 'SI'
When tsysOS.OSname = 'Win 11' And tblAssets.Version = '23H2' Then 'SI'
Else 'NO'
End As [¿OS Actualizado?]

View solution in original post

2 REPLIES 2
borgoncete88
Engaged Sweeper II

Hi @Hendrik_VE,

It works perfectly, thank you!

What a noob I have been... It was so easy to solve it, but I didn't know I can use two "When" in the same "Case".

Greetings.

Hendrik_VE
Champion Sweeper III

Replace the two "case" statements with this one:

Case
When tsysOS.OSname = 'Win 10' And tblAssets.Version = '22H2' Then 'SI'
When tsysOS.OSname = 'Win 11' And tblAssets.Version = '23H2' Then 'SI'
Else 'NO'
End As [¿OS Actualizado?]