→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
novasam
Engaged Sweeper III
I tried a dynamic group, but putting two filters about software that are opposite in condition breaks the Dynamic Group.

Basically the object is to Take all systems that have 'Microsoft Office%' installed, and see which of these are missing 'Cisco IronPort Email Security Plug-In'. I'm guessing because this query is a little more complicated to pull it will need to be a report if possible.

I'n guessing I would need to somehow add it to my existing query:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
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 '%Cisco IronPort Email Security Plug-In%')
And tblAssets.Lastseen <> '' And tblAssets.Assettype = -1 And
tblComputersystem.Domainrole <= 1
Order By tblAssets.Domain,
tblAssets.AssetName
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
A second subquery which gets you a list of computers with "Microsoft Office%" installed should do it:

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Cisco IronPort Email Security Plug-In%')
And tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Microsoft Office%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1 And
tblComputersystem.Domainrole <= 1
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

2 REPLIES 2
novasam
Engaged Sweeper III
Works Perfect, thank you.
Daniel_B
Lansweeper Alumni
A second subquery which gets you a list of computers with "Microsoft Office%" installed should do it:

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Cisco IronPort Email Security Plug-In%')
And tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Microsoft Office%') And
tblAssets.Lastseen <> '' And tblAssets.Assettype = -1 And
tblComputersystem.Domainrole <= 1
Order By tblAssets.Domain,
tblAssets.AssetName

New to Lansweeper?

Try Lansweeper For Free

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

Try Now