In Configuration Manager Current Branch* we have a report which automatically gathers the Windows Autopilot hardware hashes and other information. This allows us to handily export the report into a CSV and hey presto it’s in the correct format to upload straight into Autopilot. I wrote it up at the time of the tech preview being released – https://morethanpatches.com/2018/03/06/sccm-report-on-windows-autopilot-device-information-tech-preview-1802/ 

The one problem I found with this is that it takes in all devices and sometimes I’d like to scope it to just one collection. At the time, I created a uservoice request as a result – Windows AutoPilot Device Information (TP 1802) – Welcome to Configuration Manager Feedback (uservoice.com)

That uservoice has gained a little traction but I’ve also been asked about it a few times and again today I was asked if the product group ever offered a solution. No it was never added, but there are no doubt reasons for this so I decided to make a couple of workarounds myself.

*I can’t actually find a reference for when this was introduced into current branch. It landed in Tech Preview 1802 though.

SQL Query

If you want to simply query the database directly (at your own risk not my fault if you screw it up so please be etc etc) then this query should give you the result you want. Replace PR100015 in the last line for your collection ID. You can find the collection ID in the properties of your collection, but hopefully if you’re drilling into SQL you knew that anyway.

select bios.SerialNumber0 as DeviceSerialNumber, (os.SerialNumber0) as WindowsProductID, mdm.DeviceHardwareData0 
from v_GS_PC_BIOS bios
inner join v_GS_OPERATING_SYSTEM os on bios.ResourceID=os.ResourceID
inner join v_GS_MDM_DEVDETAIL_EXT01 mdm on os.ResourceID=mdm.ResourceID
inner join v_R_System sys on mdm.ResourceID=sys.ResourceID
inner join v_FullCollectionMembership coll on sys.ResourceID=coll.ResourceID
where coll.CollectionID = 'PR100015'

SQL Report

If you want to keep the same report format than I’ve amended the existing one and added the filter for a collection. I will state here that by me adding a join into the v_FullCollectionMembership SQL I am bypassing the RBAC that is associated with the rest of the information in the report.

Side note – when the report runs it uses functions to take in the user SID and validate through RBAC that they can access the content they are trying to access. I couldn’t easily find one I could use with this so I bypassed it, the existing RBAC controls still remain though.

To import the report, you will first need to DOWNLOAD IT FROM GITHUB HERE 

Now visit your SQL Reporting services and upload the report file using the button provided. You will want to browse to the appropriate folder in the reporting service instance before importing. The example shown is from SQL 2016.

That should then appear in your Configuration Manager console too as it simply reflects what is in the SQL Reporting Services instance. Be mindful though, if you need to re-install your reports or re-install your ConfigMgr Report Services Point then you will need to add this again.

You’re going to need to edit that report and change the data source to your own SQL. One easy way to do this is to select the report in ConfigMgr and edit it. You’ll need Microsoft Report Builder installed to do this.

Now you can add a Data Source (on the left side) and browse through your Reporting Services folder structure for your data source

Now you should be able to run that report connected to your own data source.

There may also be other methods of obtaining this information of course, but this is an option if you prefer it.

Others options available to you if you wish to achieve the same outcome (obtaining Autopilot info and/or registering them) may include:

• CMPivot – maybe, I need to work on that
• Use a collection synchronisation into an Azure AD group. Then assign a deployment profile to that group and select the option to Convert all targeted devices to Autopilot
• Simply use an AAD dynamic group if your device are hybrid joined

I’ll add additional info as I work through any other options for this.

/Peter