Exclude Group Reports from main Report

[SEC_ALERTS]

Hi Team,

I have one report for All Adobe Software's installed in my company.
I have also the two Asset groups X and Y with their Adobe reports respectively.
I want to exclude X and Y Asset Groups Adobe reports from All Adobe Software's main report.
Have would achieve this?

Thanks in advance!!!

[SEC_ALERTS]

It Works !!!

Thanks a lot for your valuable reply!!!

[Hemoco · Accepted solution]

Use the report below. You added your tblSoftwareUni.SoftwarePublisher and tblAssets.Lastseen criteria to two criteria columns, which is unnecessary, and the tblAssetGroups.AssetGroup criterion to only one. You were listing BOTH:
- Assets that met the tblSoftwareUni.SoftwarePublisher and tblAssets.Lastseen criteria.
- Assets that met the tblSoftwareUni.SoftwarePublisher, tblAssets.Lastseen and tblAssetGroups.AssetGroup criteria.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'DocTeam') Or
(tblAssetGroups.AssetGroup = 'UI Team')) And
tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen >
GetDate() - 30
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion

[SEC_ALERTS]

Hi Team,

I have tried all the mentioned instructions.
After this report is getting run but it is not exclude the mentioned(X and Y Custom asset group) from this report.
It Shows All Adobe product from company.
please refer the below report for further investigation.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen >
GetDate() - 30) Or
(tblAssets.AssetID Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'DocTeam') Or
(tblAssetGroups.AssetGroup = 'UI Team')) And
tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen >
GetDate() - 30)
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion

Regards,
SEC_ALERTS.

[RCorbeil]

If it helps:

SELECT TOP 1000000
  ...

WHERE
  tblSoftwareUni.SoftwarePublisher LIKE '%Adobe%'
  AND tblAssets.Lastseen > GetDate() - 30

-- [ BEGIN ]
  AND tblAssets.AssetID NOT IN ( SELECT Top 1000000
                                   tblAssetGroupLink.AssetID
                                 FROM
                                   tblAssetGroups
                                   INNER JOIN tblAssetGroupLink ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
                                 WHERE
                                   (tblAssetGroups.AssetGroup = 'X')
                                   OR (tblAssetGroups.AssetGroup = 'Y')
                               )
-- [ END ]

ORDER BY
  ...

where, of course, you replace 'X' and 'Y' with your asset groups.

[Hemoco]

Please add the below criterion to the Criteria column for the tblAssets.AssetID expression, exactly as it is shown. You added an "=" to the criterion.

Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'X') Or
(tblAssetGroups.AssetGroup = 'Y'))

[SEC_ALERTS]

Hi Team,

I have followed the mentioned Steps but I have received the attached error.

Regards,
SEC_ALERTS

Below is the report :


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID As AssetID1,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
(tblAssets.AssetID =
'Not In (Select Top 1000000 tblAssetGroupLink.AssetID From tblAssetGroups Inner Join tblAssetGroupLink On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupIDWhere (tblAssetGroups.AssetGroup = ''X'') Or (tblAssetGroups.AssetGroup = ''Y''))' And tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen > GetDate() - 30) Or
(tblSoftwareUni.SoftwarePublisher Like '%Adobe%' And tblAssets.Lastseen >
GetDate() - 30)
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion

Here X and Y are the Custom Group names.

[Hemoco]

Do the following:
- Add the AssetID field of tblAssets to your report, if you haven't already. (In the visual representation of the report in the upper section of the report builder, tick the AssetID field in tblAssets.)
- In the expression list, add the below criterion to the Criteria column for the tblAssets.AssetID expression.

Not In (Select Top 1000000 tblAssetGroupLink.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup = 'X') Or
(tblAssetGroups.AssetGroup = 'Y'))