Report of assets that don't have certain sofware installed

[Ruben1]

I would like to create a report of assets that don't have certain sofware installed.
I have already created following report with the parameter Not Like 'Pulse Secure Installer Service' , but this will create a list of every software packet on each computer.
Any solution for this? I also tried a Dynamic group which gives me the right results, but there I don't have the "Lastseen" option.

This is what I currently have:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblADComputers.OU,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
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 tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tsysAssetTypes.AssetTypename = 'Windows' And
  (tblADComputers.OU Like '%OU=Laptop%' And
  tblSoftwareUni.softwareName Not Like 'Pulse Secure Installer Service' And
  tblAssetCustom.State = 1

[RCorbeil]

The filter should work. I've done the same thing for lists of machines in my inventory that don't have specific software installed and it's always worked. Is it possible that, for the machines that are leaking through, the software name isn't exactly "Pulse Secure Installer Service"? I copied the LIKE part of your code rather than change it to = on the assumption that you used LIKE intentionally and were planning on dropping a wildcard in there.

You could add a LEFT JOIN in the main query against the software tables, filtering that for something like "softwarename LIKE 'Pulse%'" to see what turns up.

[Ruben1]

I solved this now by creating a report that looks for a custom scanned file.
A file that only computers with that application installed have.
Not the best way to do this, but it works.

Still interested in your solution though, but don't lose too much sleep on it! 😄

[Ruben1]

Thanks, but it doesn't seem to give me the right results.
I see computers in that list that have that application (Pulse Secure Installer Service) installed.
"Pulse Secure Installer Service" is also listed in the softwarelist of that asset.

Is there a way I can also let it show me the Softwarename and the version number in the results?
Because when I try to do that it will again show me all the software that is installed.

[RCorbeil]

Try this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblADComputers.OU
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
Where
  tsysAssetTypes.AssetTypename = 'Windows'
  And tblADComputers.OU Like '%OU=Laptop%'
  And tblAssetCustom.State = 1
  AND tblAssets.AssetID Not In (Select
                                  Top 1 tblSoftware.AssetID
                                From
                                  tblSoftware
                                  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
                                Where
                                  tblSoftware.AssetID = tblAssets.AssetID
                                  And dbo.tblsoftwareuni.softwareName Like 'Pulse Secure Installer Service')

You want to check if any of the software on a particular machine matches what you're looking for, not if every piece of software matches.