cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Kreg
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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.

View solution in original post

2 REPLIES 2
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper II
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!