cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tythesly
Engaged Sweeper III
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.
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
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
Andy_Sismey
Champion Sweeper III
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
Engaged Sweeper III
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 🙂
Tythesly
Engaged Sweeper III
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
Andy_Sismey
Champion Sweeper III
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


Tythesly
Engaged Sweeper III
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?
Tythesly
Engaged Sweeper III
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.