In this post I will go through how to import Windows Autopilot devices using SCCM Report and sql query. Configuration Manager gives us the flexibility to generate the report which can be further used to to Import the CSV into MEM Admin Portal to make the devices ready for AutoPilot deployment.

Intro – Windows AutoPilot Devices

One common question may arise that why we need to import the hardware information into Intune console for AutoPilot Devices. If you are coming from SCCM background, you would understand that for OS deployment we need to deploy task sequence on Unknown Computers or specific collection to get the list of task sequence to initiate it.

The same way we need to target the devices using AutoPilot Deployment Profiles. We don’t have concept of Unknown computers here with Intune, hence we need to import the hardware information which consists of 3 columns :
Device Serial Number
Windows Product ID
Hardware Hash


Create csv file using SCCM Report

For a single device, we can use Get-WindowsAutoPilotInfo PowerShell script to generate csv file and import it. But for all existing devices in your environment this is not feasible. Thanks to Configuration manager team which has provided a readymade SSRS Report which can be used for this purpose, however after downloading it, you need some modification into csv file.

Launch SCCM’s SQL Server Reporting Services through the link, the information can of URL can be found through Report Manager link which can be accessed through \Monitoring\Overview\Reporting  and click on Report Manager link.

Report Manager

Once link is opened which will take you to SQL Server Reporting Services page search for Autopilot top right corner.

We can see 1 report available with the name Windows AutoPilot Device Information under Home > ConfigMgr_<SiteCode> > Hardware – General, click on it.

Windows AutoPilot Device Information

We can see list of all devices with 3 columns available ie. Device Serial Number, Windows Product ID, Hardware Hash.

This is all we need, click on save icon and select CSV (comma delimited) and save the file.

Import Windows AutoPilot Device Information

We have file available but its not ready for import yet. We need to format it or else we will get the error “Incorrect header, cannot proceed further

Incorrect headers, cannot proceed further

Edit csv file to make it usable

My downloaded csv is AutoPilot Device Information.csv, edit it with Notepad. This is how downloaded csv file will look when imported from SCCM SSRS report. We need to get rid of

DescriptionLabelTextbox,DescriptionTextbox
Description,Displays client device information that is needed for Windows AutoPilot registration.
Header_Table0_DeviceSerialNumber,Header_Table0_WindowsProductID,Header_Table0_DeviceHardwareData0,Details_Table0_DeviceSerialNumber,Details_Table0_WindowsProductID,Details_Table0_DeviceHardwareData0

Delete these rows.

The file should start with Device Serial Number,Windows Product ID,Hardware Hash. Remove the comma sign right after Hardware Hash and press Enter so that Device Serial Number should start from new Row.

Windows AutoPilot Device Information.csv

The first correction is made. Now we need to get rid of multiple repetition of following row which is getting generated after each device

Device Serial Number,Windows Product ID,Hardware Hash,

The easiest way to delete it using NotePad > Edit > Replace

Under Find What provide : Device Serial Number,Windows Product ID,Hardware Hash,
Under Replace with:
the value is blank to be replace. Click on Replace All, all multiple rows will be deleted and our csv file is ready to be imported.

Windows AutoPilot Device Information.csv

Login to Microsoft Endpoint Manager admin Center and navigate to Home > Devices > Enroll Devices.

On page Windows Autopilot devices, click on Import to Add Autopilot devices and specify the path to the list you want to import. Once file is specified we can see Rows formatted correctly an indication of correct csv file, click on Import

Add Autopilot devices

Allow some time for Importing devices.

Importing devices

Devices are imported now and ready to be assigned.

Windows Autopilot devices

The only problem with readymade SCCM report is that it extracts all devices and if you have thousands of records, it could be overwhelming to import all the devices all together even if we don’t want to import all. Editing notepad without knowing the device is also not good.

Hence, lets look at SQL query for SCCM which can pull out the information we need.

SQL Query for Windows Autopilot devices

Lets navigate to Configuration Manager, launch SQL Server Management Studio. Here, I am just pulling the information for one single device. Run the following query:

select distinct(bios.SerialNumber0+',') as "Device Serial Number,",
(osinfo.SerialNumber0+',') as "Windows Product ID,",
mdminfo.DeviceHardwareData0 as "Hardware Hash"
from v_R_System System
Inner Join v_GS_PC_BIOS bios on System.ResourceID=bios.ResourceID
Inner Join v_GS_OPERATING_SYSTEM osinfo on System.ResourceID=osinfo.ResourceID
Inner Join v_GS_MDM_DEVDETAIL_EXT01 mdminfo on System.ResourceID=mdminfo.ResourceID
where System.Name0 like 'VM00155D007F1F'
SQL Query for AutoPilot Devices

I have formatted the above mentioned report in a way which is already in a supported format and ready to be used for importing the csv file. You will see I have inserted several commas at multiple places which is a requirement of specific format for csv file import.

Under results, click any row, select all the rows (Ctrl + A) and right click to select Copy with Headers.

SQL Query Windows AutoPilot

Open Notepad and paste the content and save the file with csv format.

We have csv file available but with the benefit of customizing the files what we need.

AutoPilotSCCMQuery01.csv

SQL Query for specific systems

Lets use the query as base but with specific systems only. Following is the query which will fetch the systems for the devices we need to generate Device Serial Number, Windows Product ID, Hardware Hash:

select distinct(bios.SerialNumber0+',') as "Device Serial Number,",
(osinfo.SerialNumber0+',') as "Windows Product ID,",
mdminfo.DeviceHardwareData0 as "Hardware Hash"
from v_R_System System
Inner Join v_GS_PC_BIOS bios on System.ResourceID=bios.ResourceID
Inner Join v_GS_OPERATING_SYSTEM osinfo on System.ResourceID=osinfo.ResourceID
Inner Join v_GS_MDM_DEVDETAIL_EXT01 mdminfo on System.ResourceID=mdminfo.ResourceID
where System.Name0 in
('VM00155D007F27',
'VM00155D007F28',
'VM00155D007F1F'
)

SQL Query based upon specific collection

This query will pull all required fields for hardware related information based upon a specific collection. Run the query:

select distinct(bios.SerialNumber0+',') as "Device Serial Number,",
(osinfo.SerialNumber0+',') as "Windows Product ID,",
mdminfo.DeviceHardwareData0 as "Hardware Hash"
from v_R_System System
Inner Join v_GS_PC_BIOS bios on System.ResourceID=bios.ResourceID
Inner Join v_GS_OPERATING_SYSTEM osinfo on System.ResourceID=osinfo.ResourceID
Inner Join v_GS_MDM_DEVDETAIL_EXT01 mdminfo on System.ResourceID=mdminfo.ResourceID
Inner Join v_FullCollectionMembership Collection on System.ResourceID=Collection.ResourceID
where Collection.CollectionID='MAN00017'

Conclusion

We can see that Microsoft have made easy for importing the device information for Microsoft Intune AutoPilot Devices. However, SCCM report cannot be directly imported as it requires some modifications in csv file to support the correct format. The default report get all the devices, hence either we need to create a custom SSRS report or we can use SCCM SQL query to fetch the information based upon specific devices or specific collection.