→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ruben1
Engaged Sweeper III
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
4 REPLIES 4
RCorbeil
Honored Sweeper II
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
Engaged Sweeper III
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
Engaged Sweeper III
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
Honored Sweeper II
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.