Software compliance < to version x

[Kreg]

Hi there,

I'm trying to create some basic queries for the software compliance.
The goal is to show on the dashboard my core applications non compliant to the version defined.

Example, Acrobat Reader < 11.0.4

So I've made I query like that, but the result was crazy! What is bad in my query?

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.Username,
tsysIPLocations.IPLocation,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.email,
tblOperatingsystem.Caption,
tblAssets.Lastseen,
tblAssets.Firstseen,
tblBIOS.Manufacturer,
tblAssetCustom.Model,
tblBIOS.SerialNumber,
tblstate.Statename,
tblADComputers.OU,
tblOperatingsystem.InstallDate,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblstate On tblstate.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblOperatingsystem.Caption Like '%Windows 7%') Or
(tblOperatingsystem.Caption Like '%Windows 8%' And
tblSoftwareUni.softwareName Like '%Adobe Reader%' And
tblSoftware.softwareVersion < '11.0.4')
Order By tsysIPLocations.IPLocation

Many thanks!
Kreg

[Daniel_B · Accepted solution]

This won't be easy because software versions are stored as string in the registry of Windows computers, not as number. Therefore SQL will consider "11.0.4" to be bigger than "11.0.12". What you always can do is listing computers which don't have one specific version installed, using a filter like

tblSoftware.softwareVersion <> '11.0.4'

More advanced filtering would require a lot of SQL code to convert the string into a number and won't be precise in all cases.

[Kreg]

Ok i've made some modification on my query, but the result stay not correct
The conditions are filtered to a name of an application (Acrobat Reader) and a version inferior to 11.0.4

But the query show version 11.0.8, 11.0.11....

Here is the query :

 select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName,
  tblAssets.AssetID,
  tblAssets.Username,
  tsysIPLocations.IPLocation,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblADusers.email,
  tblOperatingsystem.Caption,
  tblAssets.Lastseen,
  tblAssets.Firstseen,
  tblBIOS.Manufacturer,
  tblAssetCustom.Model,
  tblBIOS.SerialNumber,
  tblstate.Statename,
  tblADComputers.OU,
  tblOperatingsystem.InstallDate,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
    tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblstate On tblstate.State = tblAssetCustom.State
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblstate.Statename = 'active' And tblSoftwareUni.softwareName Like
  '%Adobe Reader%' And tblSoftware.softwareVersion < '11.0.4' And
  tblComputersystem.Domainrole < 2
Order By tsysIPLocations.IPLocation

An idea ?
Thanks!