Missing Laptop Encryption

[cvhyatt]

Hey All

I know this has been answered before and I have tried to make this work, but I am getting duplicate instances in my report. Here is what I am trying to do:

1) I want all laptops that do not have Dell Data Protection encryption installed.
2) Half of the laptops have software listed in add/remove programs - Dell Data Protection
3) The other half laptops (mostly Windows 7) have a file installed - wsscan.exe
4) I thought I could build a query that can identify all laptops that are either missing the Dell Data Protection software or the wsscan.exe file.
5) I have configured file scanning for the wsscan.exe file per instruction.

Here is the query I currently have that pulls back thousands of duplicates.

Select Top 100000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename As Type,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSystemEnclosure On tblSystemEnclosure.AssetID =
tblAssets.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where TsysChassisTypes.ChassisName In ('Laptop', 'Portable', 'Notebook') And
tblFileVersions.FilePathfull Like '%wsscan.exe%' And tblFileVersions.Found =
'False' And tblAssetCustom.State = 1 And tblSoftwareUni.softwareName
Not Like 'Dell Data Protection%'
Order By tblAssets.AssetName

[RCorbeil]

Try this:

Select Top 100000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  CASE   -- included so you can tell whether the file actually wasn't found or if it just hasn't been checked for yet
    WHEN tblFileVersions.Found IS NULL THEN 'Not checked'
    WHEN tblFileVersions.Found = 0     THEN 'Not found'
    ELSE                                    'Found'  -- filtered out below, but let's be thorough
  END AS FilePresent
From
  tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblSystemEnclosure On tblSystemEnclosure.AssetID = tblAssets.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
  LEFT  Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID And tblFileVersions.FilePathfull Like '%wsscan.exe%'
  LEFT  Join ( SELECT
                 tblSoftware.AssetID,
                 tblSoftwareUni.softwareName
               FROM
                 tblSoftware
                 INNER JOIN tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID And tblSoftwareUni.softwareName Like 'Dell Data Protection%'
             ) AS s ON s.AssetID = tblAssets.AssetID
Where
  TsysChassisTypes.ChassisName In ('Laptop', 'Portable', 'Notebook')
  And (tblFileVersions.Found IS NULL OR tblFileVersions.Found = 0)  -- file not found or not yet checked
  And (s.AssetID IS NULL)                                           -- AND software not found
  And tblAssetCustom.State = 1
Order By
  tblAssets.AssetName

[cvhyatt]

I was able to fix my duplicates problem by doing a Select Distinct at the top of the query, but now I find that the tblSoftwareUni.softwareName Not Like 'Dell Data Protection%' is not working. Thoughts on what I might be missing?