cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Alulux
Engaged Sweeper III
Hello, I´m desperate with a report I´m trying to generate:

My goal is to list all portable windows-devices which does NOT have a specific software installed (In this case Sophos Safeguard client).

I have created the below report, but it returns about 4500 results, and in these results each client is listed about 25-30 times, and even the ones which already have the software installed, are listed.




Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADComputers.Description,
TsysChassisTypes.ChassisName,
tblOperatingsystem.Caption As OS,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
(Case
When tsysIPLocations.IPLocation Is Not Null Then tsysIPLocations.IPLocation
Else 'Undefined' End) As IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
Inner Join tblSystemEnclosure On tblSystemEnclosure.AssetID =
tblAssets.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (TsysChassisTypes.ChassisName = 'Laptop' Or TsysChassisTypes.ChassisName =
'Notebook' Or TsysChassisTypes.ChassisName = 'Portable' Or
TsysChassisTypes.ChassisName = 'Convertible') And
Not tblSoftwareUni.softwareName = 'Sophos SafeGuard Client%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
1 REPLY 1
RCorbeil
Honored Sweeper II
First problem: you're looking for an exact match for 'Sophos SafeGuard Client%'. The % is a wildcard in SQL string matching, so more likely you're looking for "starts with", so you want to use LIKE 'Sophos SafeGuard Client%'.

Second, you've set it up to list all software that's not 'Sophos SafeGuard Client%' on the selected computers, not all computers that lack 'Sophos SafeGuard Client%'.

Try something like this:
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblADComputers.Description,
TsysChassisTypes.ChassisName,
tblOperatingsystem.Caption AS OS,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.IPAddress,
Case
When tsysIPLocations.IPLocation Is Not Null
Then tsysIPLocations.IPLocation
Else 'Undefined'
End As IPLocation
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where
TsysChassisTypes.ChassisName In ('Laptop', 'Notebook', 'Portable', 'Convertible')
And tblAssetCustom.State = 1
And Not Exists (Select
tblSoftware.AssetID
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftware.AssetID = tblAssets.AssetID
And tblSoftwareUni.softwareName Like 'Sophos SafeGuard Client%')
Order By
tblAssets.AssetName