I've wanted, for awhile, to be able to take a User Collection, and generate a Device Collection that are the Primary devices those users are set to.
This is what I've come up with so far as a new view in SQL.
001
002 003 004 005 006 |
SELECT dbo.vCollectionMembers.SiteID AS CollectionID, dbo.vCollectionMembers.Name AS Username, dbo.v_R_System_Valid.Netbios_Name0 AS DeviceName
FROM dbo.v_UsersPrimaryMachines INNER JOIN dbo.v_R_User ON dbo.v_UsersPrimaryMachines.UserResourceID = dbo.v_R_User.ResourceID INNER JOIN dbo.v_R_System_Valid ON dbo.v_UsersPrimaryMachines.MachineID = dbo.v_R_System_Valid.ResourceID RIGHT OUTER JOIN dbo.vCollectionMembers ON dbo.v_R_User.Name0 = dbo.vCollectionMembers.Name WHERE (dbo.vCollectionMembers.SiteID = N'XYZ123456') AND (dbo.vCollectionMembers.ArchitectureKey = '4') |
I also popped this together, a list of all users, and their assigned Primary Devices.
001
002 003 004 005 |
SELECT dbo.v_R_User.Unique_User_Name0 AS [User], dbo.v_R_System_Valid.AD_Site_Name0, dbo.v_R_System_Valid.Netbios_Name0 AS ComputerName
FROM dbo.v_UsersPrimaryMachines INNER JOIN dbo.v_R_User ON dbo.v_UsersPrimaryMachines.UserResourceID = dbo.v_R_User.ResourceID RIGHT OUTER JOIN dbo.v_R_System_Valid ON dbo.v_UsersPrimaryMachines.MachineID = dbo.v_R_System_Valid.ResourceID WHERE (dbo.v_R_User.Unique_User_Name0 <> N'NULL') |
Next, I'll put together a little PowerShell function to get this list of Devices, and then place them in a Collection.
Fancy!
Now I fully expect to be shown how this is something do-able in one or two simple clicks within the console. :)
