Help with report: List portable windows devices without a specific software

[Alulux]

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

[RCorbeil]

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