Notification

Icon
Error

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

Posted: Wednesday, March 31, 2021 3:56:55 PM(UTC)
Tythesly

Tyler M.

Member Original PosterPosts: 31
1
Like
This issue has been solved! Click here to view the solution
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.
Andy.S
#1Andy.S Member Posts: 93  
posted: 4/1/2021 9:05:59 AM(UTC)
This should give you a good starting point , this lists all machines with Adobe Reader but Missing Google Chrome :

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 '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


Tythesly
#2Tyler M. Member Original PosterPosts: 31  
posted: 4/1/2021 1:00:53 PM(UTC)
Originally Posted by: Andy.S Go to Quoted Post
This should give you a good starting point , this lists all machines with Adobe Reader but Missing Google Chrome :

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 '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.
Tythesly
#3Tyler M. Member Original PosterPosts: 31  
posted: 5/7/2021 4:25:13 PM(UTC)
Originally Posted by: Andy.S Go to Quoted Post
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:

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?
Tythesly
#4Tyler M. Member Original PosterPosts: 31  
posted: 5/7/2021 5:00:02 PM(UTC)
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.

Code:
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
Andy.S
#5Andy.S Member Posts: 93  
posted: 5/10/2021 2:10:24 PM(UTC)
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
#6Tyler M. Member Original PosterPosts: 31  
posted: 5/10/2021 2:19:58 PM(UTC)
Originally Posted by: Andy.S Go to Quoted Post
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.
Andy.S
#7Andy.S Member Posts: 93  
posted: 5/10/2021 2:25:32 PM(UTC)
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 ?
Tythesly
#8Tyler M. Member Original PosterPosts: 31  
posted: 5/10/2021 2:55:19 PM(UTC)
Originally Posted by: Andy.S Go to Quoted Post
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 :)
Andy.S
#9Andy.S Member Posts: 93  
posted: 5/10/2021 3:24:38 PM(UTC)
Hi
I don't have access to Lansweeper at the moment but try this untested code :

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
Tythesly
#10Tyler M. Member Original PosterPosts: 31  
posted: 5/10/2021 3:37:06 PM(UTC)
It says the report has no results.
Andy.S
#11Andy.S Member Posts: 93  
posted: 5/10/2021 4:01:17 PM(UTC)
OK, once I can get back on Lansweeper , I'll have a look and see where I went wrong :-)
Tythesly
#12Tyler M. Member Original PosterPosts: 31  
posted: 5/10/2021 4:33:21 PM(UTC)
I appreciate the help!
Andy.S
#13Andy.S Member Posts: 93  
posted: 5/11/2021 12:27:08 PM(UTC)
Tyler this should do what your asking for Malware and then you can adapt for the Office Report :

Code:
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
#14Tyler M. Member Original PosterPosts: 31  
posted: 5/11/2021 1:09:45 PM(UTC)
This is perfect thank you once again!

Active Discussions

Lansweeper Enterprise Options in Menu Bar/Configuration
by  mk@allan   Go to last post Go to first unread
Last post: 6/18/2021 7:38:43 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  Kenneth Lindsay  
Go to last post Go to first unread
Last post: 6/18/2021 3:31:06 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno  
Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)
Lansweeper Exclude Search
by  pryan67  
Go to last post Go to first unread
Last post: 6/16/2021 4:01:43 PM(UTC)
Lansweeper Report: All Apple Mac devices with Memory RAM asset
by  gabrielo   Go to last post Go to first unread
Last post: 6/16/2021 3:17:24 PM(UTC)
Lansweeper Does technical support for LS really respond?
by  tosch  
Go to last post Go to first unread
Last post: 6/16/2021 12:48:50 PM(UTC)