This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

Go to solution
Tythesly
Engaged Sweeper III

‎03-31-2021 04:56 PM

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.
Labels:
1 ACCEPTED SOLUTION
Go to solution
Andy_Sismey
Champion Sweeper III
In response to Tythesly

‎05-11-2021 01:27 PM

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




View solution in original post

15 REPLIES 15
Go to solution
Andy_Sismey
Champion Sweeper III
In response to Tythesly

‎05-10-2021 03:25 PM

So you are not interested in actually knowing which of the 4 bits of software are installed , so just any and missing the other application ?
0 Kudos
Go to solution
Tythesly
Engaged Sweeper III
In response to Andy_Sismey

‎05-10-2021 03:55 PM

Andy.S wrote:
So you are not interested in actually knowing which of the 4 bits of software are installed , so just any and missing the other application ?


Yes that is correct. I can just open the assets page and figure that part out for myself 🙂
0 Kudos
Go to solution
Tythesly
Engaged Sweeper III

‎05-07-2021 06:00 PM

I am also having the same issue for this other report. I want it to find all the computers with any version of office that is missing a phishing button.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype,
  tsysOS.OSname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select tblSoftwareUni.softwareName,
      tblSoftware.AssetID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      (tblSoftwareUni.softwareName Like
        'Microsoft 365 Apps for enterprise - en-us' Or
        tblSoftwareUni.softwareName Like 'Microsoft Office Stan%' Or
        tblSoftwareUni.softwareName Like 'Microsoft Office Pro%' Or
        tblSoftwareUni.softwareName Like 'Microsoft Office Home%')) Query1 On
      Query1.AssetID = tblAssets.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'Phish Alert') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
0 Kudos
Go to solution
Andy_Sismey
Champion Sweeper III

‎04-01-2021 10:05 AM

This should give you a good starting point , this lists all machines with Adobe Reader but Missing Google Chrome :

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype,
  tsysOS.OSname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID,
        tblSoftware.softwareVersion
      From tblSoftware
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like 'Adobe Acrobat Reader DC%') Query1
    On Query1.AssetID = tblAssets.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
      From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
      Where tblSoftwareUni.softwareName Like 'Google Chrome%') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName


Go to solution
Tythesly
Engaged Sweeper III
In response to Andy_Sismey

‎05-07-2021 05:25 PM

Andy.S wrote:
This should give you a good starting point , this lists all machines with Adobe Reader but Missing Google Chrome


I have another puzzle for you. We have computers with Malwarebytes Anti-Exploit, Malwarebytes Anti-Ransomware and Malwarebytes Managed Client. We switched to using Malwarebytes Endpoint Agent. I tried using this with the following code:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype,
  tsysOS.OSname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select tblSoftwareUni.softwareName,
      tblSoftware.AssetID,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where (tblSoftwareUni.softwareName Like 'Malwarebytes'' Managed Client' Or
        tblSoftwareUni.softwareName Like '''Malwarebytes version' Or
        tblSoftwareUni.softwareName Like 'Malwarebytes Anti%')) Query1
    On Query1.AssetID = tblAssets.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'Malwarebytes Endpoint Agent') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName


And it does work... however its double/triple listing the same assets because if one computer has all three of that software then its listed three times haha.

How do I get it to only list the asset once if any of those three applications are found on it?
0 Kudos
Go to solution
Tythesly
Engaged Sweeper III
In response to Andy_Sismey

‎04-01-2021 02:00 PM

Andy.S wrote:
This should give you a good starting point , this lists all machines with Adobe Reader but Missing Google Chrome :

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype,
  tsysOS.OSname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID,
        tblSoftware.softwareVersion
      From tblSoftware
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where tblSoftwareUni.softwareName Like 'Adobe Acrobat Reader DC%') Query1
    On Query1.AssetID = tblAssets.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
      From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
      Where tblSoftwareUni.softwareName Like 'Google Chrome%') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName




Hey thanks for the help! I appreciate it.
0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now