cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Bennettleet
Engaged Sweeper
Hello, I am trying to put together a report that will output a list of assets missing any 1 of our basic applications. I can make a report for any 1 application, but I'd like to merge reports and possibly report for each entry what that machine is missing.

What I have so far is:

=============================================================================
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Username
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'AlphaApp')

=============================================================================================
What I've tried for expanding this to multiple apps:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Username
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
(tblSoftwareUni.softwareName Like 'AlphaApp' Or
tblSoftwareUni.softwareName Like 'BetaApp' Or
tblSoftwareUni.softwareName Like 'CharlieApp')) And tsysOS.OSname =
'Win 10'
============================================================

This report unfortunately isn't checking correctly, and is returning no results.

When I try to break up the Where clause, I get a syntax error
============================================================================
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'AlphaApp') or
(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'BetaApp')
============================================================================

I'm missing something simple, please assist. Thank you.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Take a look at this thread and see if it fits your situation.

View solution in original post

2 REPLIES 2
RCorbeil
Honored Sweeper II
Take a look at this thread and see if it fits your situation.
RC62N wrote:
Take a look at this thread and see if it fits your situation.


Thank you that's incredibly helpful!