SQL to retrieve all collections

Below is the SQL code needed to retrieve all of the collections from ConfigMgr. This is useful when creating custom reports that need to be scoped to a specific collection. The query below selects the CollectionName and CollectionID columns and sorts the CollectionName column alphabetically.

Results from the query on my test system.

Copy GPO with PowerShell

If you need to make a copy of a group policy, using the Group Policy Management Console can be a slow process. Using PowerShell is a much faster process.

All you need is the name of the source GPO copied to the clipboard.

You can also copy by using the GUID of the source and/or destination GPO. In addition, you can copy GPOs between domains.

I don’t have an image of copying between domains because my lab only contains a single domain.

SQL to get last hardware scan

The last hardware scan is stored in the view v_GS_WORKSTATION_STATUS. When joined with either v_R_System_Valid or v_R_System, you can retrieve the last hardware scan for a computer.

The SQL code below will return the last hardware scan date for all computers in the ConfigMgr system.

Results from the above query.

If you need to return the last hardware scan for a single computer you can use the query below to filter by the computer name. In this example, we filtered on computer ‘pc01’.

Results from the above query.