cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cvhyatt
Engaged Sweeper II
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

2 REPLIES 2
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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?