cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SEC_ALERTS
Engaged Sweeper
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!!!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
SEC_ALERTS
Engaged Sweeper
It Works !!!

Thanks a lot for your valuable reply!!!
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Honored Sweeper II
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
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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'))