SCCM Collection Relationships Using SQL Queries

 In System Center Configuration Manager (SCCM), collections are used to group systems or devices based on specific criteria for easier management. But how do these collections relate to each other? In this blog post, we’ll explore how to find relationships between collections, such as including, excluding, or limiting systems in source collections using SQL queries in SCCM.

Understanding Collection Relationships in SCCM

SCCM allows administrators to manage collections by creating dependencies between them. These dependencies determine how one collection impacts another. You may have scenarios where one collection is a subset of another (limited), one collection is included in another (include), or one collection excludes another collection (exclude).

SCCM maintains collection dependencies in the vSMS_CollectionDependencies table, which stores the relationship between a source collection and a dependent collection.

Key Columns Involved

• SourceCollectionID: The ID of the collection that is the source of the dependency.
• DependentCollectionID: The ID of the collection that is dependent on the source collection.
• RelationshipType: The type of relationship, where:
• 1 = Limited To
• 2 = Include
• 3 = Exclude

Using SQL to Query Collection Relationships

You can use SQL queries to extract details about collection relationships in SCCM. Below is a SQL query that will help you retrieve the relationships for a specific source collection

SELECT

    v_Collection.name,

    v_Collections.CollectionName AS [Source Collection Name],

    SourceCollectionID,

    CASE

        WHEN vSMS_CollectionDependencies.relationshiptype = 1 THEN 'Limited To ' + SourceCollectionID

        WHEN vSMS_CollectionDependencies.relationshiptype = 2 THEN 'Include ' + SourceCollectionID

        WHEN vSMS_CollectionDependencies.relationshiptype = 3 THEN 'Exclude ' + SourceCollectionID

    END AS "Type of Relationship"

FROM

    v_Collection

JOIN

    vSMS_CollectionDependencies ON vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID

JOIN

    v_Collections ON v_Collections.SiteID = vSMS_CollectionDependencies.SourceCollectionID

WHERE

    vSMS_CollectionDependencies.SourceCollectionID LIKE 'CollectionID';

Conclusion

Using SQL queries to analyze collection relationships in SCCM can save time and improve your ability to manage your environment efficiently. The query shared in this blog will help you gain insights into how collections are interdependent and ensure that your configuration management policies are applied correctly.

Windows LAPS with Intune

Windows Local Administrator Password Solution (LAPS) has been a crucial tool for securing local administrator accounts in managed Windows devices. With the recent Windows 24H2 update, Microsoft has introduced several enhancements to LAPS, empowering IT administrators with new options for automatic account management and increased flexibility for password security policies. One of the standout improvements is the ability to create managed accounts and configure automatic account management directly from Intune, making it easier than ever to enforce security standards across your devices.

 

Key Features of LAPS in Windows 24H2

With the release of Windows 24H2, Microsoft has made several improvements to LAPS, including the ability to define policies that streamline the management of local administrator accounts through Intune. The primary update is the inclusion of new policies for **Automatic Account Management**, which makes it easier to automate and enforce the creation, management, and maintenance of local administrator accounts. Additionally, administrators can now randomize the account name for an added layer of security.

 

New LAPS Policies in Intune

With these updates, administrators can now leverage the **Configuration Service Provider (CSP)** to define policies for LAPS in Microsoft Intune. Below are the essential CSPs that are now available to configure LAPS policies:

 

1. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementEnabled`** 

   This policy allows administrators to enable or disable automatic management of the local administrator account.

 

2. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementEnableAccount`** 

   This policy controls whether or not the local administrator account is automatically created and managed via LAPS.

 

3. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementNameOrPrefix`** 

   Here, administrators can define a name or prefix for the local administrator account. The flexibility to define custom account names is crucial for organizations with specific naming conventions.

 

4. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementRandomizeName`** 

   This policy allows the administrator to randomize the local administrator account name, which enhances security by reducing the predictability of the account name.

 

5. **`./Device/Vendor/MSFT/LAPS/Policies/AutomaticAccountManagementTarget`** 

   This policy defines the target device group or specific devices that will be subject to LAPS management.

 

6. **`./Device/Vendor/MSFT/LAPS/Policies/BackupDirectory`** 

   This policy specifies the backup directory for LAPS password storage, ensuring that backup copies of passwords are safely stored for recovery when needed.

 

Configuring LAPS Policies in Intune

To create a new LAPS policy via Intune, administrators can use the aforementioned CSPs in the **Device Configuration** section. These settings can be pushed to managed Windows devices, allowing for centralized control over the local administrator account security.

 

1. **Navigate to Intune > Devices > Configuration Profiles** in the Intune portal.

2. **Create a New Profile** and select **Windows 10 and later** as the platform.

3. **Choose the Profile Type** as **Custom**, and under **OMA-URI Settings**, you can input the appropriate CSPs based on your requirements.

 

Once configured, these settings will be applied to the managed devices, ensuring the local administrator account is automatically created and secured according to the defined policies.

 

Backup and Recovery Considerations

As part of the improved LAPS functionality, administrators are now encouraged to set up a backup directory for storing passwords securely. This ensures that in the event of an emergency or a recovery scenario, administrators can retrieve the local administrator password for troubleshooting and remediation.

 

For more detailed information on configuring backup directories and additional LAPS policy options, refer to the official [Microsoft documentation on LAPS

CSP](https://learn.microsoft.com/en-us/windows/client-management/mdm/laps-csp#policiesbackupdirectory).

 

Conclusion

The introduction of **Automatic Account Management** in Windows LAPS (available in the latest Windows 24H2 update) represents a significant step forward in securing local administrator accounts in a streamlined, automated manner. With the ability to manage account names, randomize credentials, and enforce automatic updates directly through Intune, organizations can enhance their security posture while reducing the administrative overhead of managing these critical accounts.