IT Management Suite

hello everyone,

I am working on a filter to list primary computer user by department. I copied bsakata's sql script (thank you bsakata)

select a1._ResourceGuid, i.name from
(select name, domain from dbo.vuser where department like '%IT%') aduser
JOIN dbo.Inv_AeX_AC_Primary_User a1 on aduser.name = a1.[user]
JOIN dbo.vitem i on a1._ResourceGuid = i.guid
WHERE a1.[Month] = datename(month,dateadd(mm,0,getdate()))

the link:

https://www-secure.symantec.com/connect/forums/how-can-i-target-computers-where-primary-user-specific-department.

I would like to also add the user manager in the where clause (to filter by department and manager). But in the vUser table I noticed that there is no "manager" column. In which table or view is this information kept? Also would someone point me towards documentation/videos on how to create a filter/a report using the report/filter generator?

thank you all in advance

regards

I am working on a filter to list primary computer user by department.

1. If your filter groups user resources, the reference to vItem in your query is unnecessary and very expensive.
2. In the latest version of SMP, the Inv_AeX_AC_Primary_User table is no longer multi-row so the WHERE filter is unnecessary.
3. When defining a filter, you should only specify the Guid of the resource in the SELECT statement.

This query will give you the resource guids for all the users who are primary users of computers and are managed by johndoe in the IT department:

select wu._ResourceGuid [Guid]
from [Inv_Global_Windows_Users] wu
    inner join Inv_AeX_AC_Primary_User pu
        on pu.[User] = wu.UserId
    inner join [ResourceAssociation] dra on dra.ParentResourceGuid = wu.[_ResourceGuid]
        and dra.ResourceAssociationTypeGuid = 'D9656701-B8EE-48c1-A4E3-5741CA18471D' -- User's Department
    inner join vRM_Department_Item dep on dep.Guid = dra.ChildResourceGuid
    inner join [ResourceAssociation] mra on dra.ParentResourceGuid = wu.[_ResourceGuid]
        and mra.ResourceAssociationTypeGuid = '049C633F-8413-42AE-93EA-F4EB7EDAFC65' -- User's Manager
    inner join [Inv_Global_Windows_Users] man on man._ResourceGuid = mra.ChildResourceGuid
    where dep.Name = 'IT' and man.UserId = 'johndoe'

You can make this filter execute a bit faster by specifying the department and manager by their resource guids in the WHERE clause. That would allow you to eliminate the join to the vRM_Department_Item view and the second join to the Inv_Global_Windows_Users table.

Hello The Gaffer,

thank you very much for your reply and the given information. I wll try that out

Regards