Wednesday, December 30, 2015

Primary Devices of a User Collection

One of the limitations (to my knowledge) within ConfigMgr's console, is the ability to cross views easily in reference to the SQL DB.

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. :)