SQL Query To Find The Collection Membership of a Specific Computer in ConfigMgr

Every now and then, you will encounter a situation when you need to find which ConfigMgr Collection(s) a specific computer is a member of for troubleshooting purposes. I came across this TechNet post which describes a SQL query to find the collection information.

Run the following query in SQL against the SMS Database:

select v_FullCollectionMembership.CollectionID As ‘Collection ID’, v_Collection.Name As ‘Collection Name’, v_R_System.Name0 As ‘Machine Name’ from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
Where v_R_System.Name0=’ClientMachineName’

Note: Replace ClientMachineName with the name of the Client Machine in question. Additionally, you can also make a Custom Report to get this information if you intend to use this frequently:

The SQL Statement For this Report would be as follows:

select v_FullCollectionMembership.CollectionID As ‘Collection ID’, v_Collection.Name As ‘Collection Name’, v_R_System.Name0 As ‘Machine Name’ from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
Where
v_R_System.Name0=@Comp

Click on Prompts while providing the SQL Statement, and Create a new prompt named ‘Comp’ without the quotes. Provide a SQL Statement for the prompt as follows:

select Name0 from v_R_System

Source: http://blogs.technet.com/b/configurationmgr/archive/2009/08/24/how-to-find-the-collection-membership-information-of-a-specific-client-machine.aspx

Please like & share:

Repair Windows Update Agent

Windows-Update

Every now and then, you may discover a computer system that is unable to detect or install available software updates from Microsoft or from your internal Windows Server Update Services (WSUS) repository. You’ll find an error such as 8007000E generated and often times running the Windows Update Troubleshooter FixIt tool is sufficient to fix Windows Update errors, but sometimes a manual approach that requires the deletion of the SoftwareDistribution content and re-registering the Windows Update components is needed, and this can be a tedious task. By using a script, this repair task can be automated.

Here are some batch scripts to help  fix a broken Windows Update Agent on a computer system. The scripts can also be run on a remote system using the ‘psexec‘ tool, which is part of the Sysinternals Suite from Microsoft. Copy and paste the content of your desired batch file below into Notepad and save the file with the “.bat” file extension, for example: repair_wua.bat .

Version 1:

Version 2:

Here’s another Windows Update Agent Reset script available on Technet.
The Windows Update Troubleshooter is available here.

Follow me on Twitter (@Hoorge) and join Tech Konnect on Facebook to stay current on technology related matters.

Please like & share: