
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-22-2014 04:22 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-23-2014 07:25 PM
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
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-26-2014 09:48 PM
Works Perfect, thank you.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-23-2014 07:25 PM
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
