“Computers with Software Installed” returns no results when viewing Software Products

Products

• IT Management Suite

Issue/Introduction

When viewing a software product in the main Silverlight view, “Computers with Software Installed” returns no results.

Cause

The large number of groups and users imported from Active Directory caused the console to reach the trustee limit when trying to scope the computers to be shown in the view.

Resolution

Removing excess users and groups will resolve the issue. The following resolution method allows you to remove all users and groups except those in a specific role, specified by Guid. If you right-click on a role in the console and choose properties, you can obtain the Guid.

1. Choose a role to assign all non-default users and groups you wish to keep.
2. Assign those users and/or groups to the role.
3. Find the Guid of the role.
4. Modify the following query by changing the Guid value to the role you want and run it against the database.
5. This might take time to properly delete the users and groups depending on how many need to be deleted.

declare @t as table (AccountGuid uniqueidentifier, CredentialGuid uniqueidentifier)
insert into @t
select ra.ParentResourceGuid, ra.ChildResourceGuid
from ResourceAssociation ra
join vRM_Account_Item a on a.Guid = ra.ParentResourceGuid
join vRM_Credential_Item c on c.Guid = ra.ChildResourceGuid
join Item i on i.Guid = ra.ResourceAssociationTypeGuid
left join ResourceAssociation ra2 on ra2.ChildResourceGuid = ra.ParentResourceGuid
and ra2.ResourceAssociationTypeGuid = '63468F04-6751-448D-891C-B59906360A27'--Role Trustee
left join vRM_Role_Item r on r.Guid = ra2.ParentResourceGuid
where ra2.ParentResourceGuid = '867FD5A6-1109-4EC0-B20A-A3CAE5868794'--Enter the role Guid to keep here
 
insert into ItemToDelete
select Guid, GETDATE ()
from vRM_Account_Item
where Guid not in
(select AccountGuid from @t)
 
insert into ItemToDelete
select Guid, GETDATE()
from vRM_Credential_Item
where Guid not in
(select CredentialGuid from @t)