Report Request - Find computers with one piece of software but missing another

[Tythesly]

Can somebody help me create a report for Active computers that have one piece of software installed but it missing another?

Like we have one piece of software that requires another to run properly but it seems the deployment tool has not been including it. It would be nice to have a report that finds computers missing this required piece of software (which I have a report for already) but it excludes any computers that do not have the main program.

[matthewovermyer]

Has anyone got an example of this but using file scanning (tblFileVersions table)? I want to find assets that have a file present, but missing a piece of software. I've tried to modify the existing code, but my join of the two tables is messed up somehow. Not sure what I'm missing.

[Tythesly]

This is perfect thank you once again!

[Tythesly]

I appreciate the help!

[Andy_Sismey · Accepted solution]

Tyler this should do what your asking for Malware and then you can adapt for the Office Report :

Select Distinct Top 1000000 tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select tblSoftware.AssetID
      From tblSoftware
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like '%Malwarebytes Managed Client%') As
  soft01 On soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
      From tblSoftware
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like '%Malwarebytes version%') As soft02
    On soft02.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
      From tblSoftware
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like '%Malwarebytes Anti%') As soft03 On
    soft03.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
      From tblSoftware
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where
        tblSoftwareUni.softwareName Not Like '%Malwarebytes Endpoint Agent%') As
  soft04 On soft04.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1

[Tythesly]

It says the report has no results.

[Andy_Sismey]

OK, once I can get back on Lansweeper , I'll have a look and see where I went wrong 🙂

[Andy_Sismey]

Hi
I don't have access to Lansweeper at the moment but try this untested code :

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID Inner Join tsysAssetTypes
      On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblSoftware
      On tblAssets.AssetID = tblSoftware.AssetID Inner Join tblSoftwareUni
      On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Malwarebytes Managed Client  %') And
  tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID Inner Join tsysAssetTypes
      On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblSoftware
      On tblAssets.AssetID = tblSoftware.AssetID Inner Join tblSoftwareUni
      On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Malwarebytes version%') 
And
  tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID Inner Join tsysAssetTypes
      On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblSoftware
      On tblAssets.AssetID = tblSoftware.AssetID Inner Join tblSoftwareUni
      On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Malwarebytes Anti%') 
And
tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
      tblAssetCustom.AssetID Inner Join tsysAssetTypes
      On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblSoftware
      On tblAssets.AssetID = tblSoftware.AssetID Inner Join tblSoftwareUni
      On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName not Like '%Malwarebytes Endpoint Agent%') 
And
  tblAssetCustom.State = 1

[Andy_Sismey]

So for the report are you interested in knowing which of the software is installed or are you not bothered ?

So something like :


Microsoft Home | Microsoft Std | Microsoft Pro | 365

Yes | NO | No | No


Or

Office Installed

No

[Tythesly]

Andy.S wrote:
So for the report are you interested in knowing which of the software is installed or are you not bothered ?

So something like :


Microsoft Home | Microsoft Std | Microsoft Pro | 365

Yes | NO | No | No


Or

Office Installed

No

So for the first one involving Malwarebytes the goal is to list an asset if it has one of any of the malwarebytes applications installed but not the endpoint security one. But only list the asset once. Currently if it has all three of those applications installed it will list it 3 times on the report.


The second one involving microsoft office the goal is to list an asset once if it has any of those versions of office but is missing phish alert. Right now it will list an asset multiple times if more than one version of office is installed.

OH to answer your question I don't need to see the softwares name in the report itself. Just the asset listed once.