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

I have the following query. It works fine when i change the query to show only Windows 10 (shows they are missing the software), but when i add Windows 7 machines, it shows machines that have the software installed on it! Any ideas? Thanks

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Netskope%') And
tsysOS.OSname = 'win 10') Or
(tsysOS.OSname = 'win 7')
Order By tblAssets.AssetName
1 REPLY 1
RCorbeil
Honored Sweeper II
Check what you're saying in your WHERE clause.
Where
( tblAssets.AssetID Not In (Select Top 1000000
tblSoftware.AssetID
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Netskope%')
And tsysOS.OSname = 'win 10'
)
Or (tsysOS.OSname = 'win 7')

(The computer is running Win10 and is missing the software)
OR (The computer is running Win7, don't care about whether or not the software is present)

Change it to
Where
tblAssets.AssetID Not In (Select Top 1000000
tblSoftware.AssetID
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Netskope%')
And (tsysOS.OSname = 'win 10' Or tsysOS.OSname = 'win 7')

(The computer is running either Win10 or Win7) and is missing the software.

Or, if you prefer
  And tsysOS.OSname IN ('win 10', 'win 7')