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.
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.
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.
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”
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.
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.
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
Allow some time for Importing devices.
Devices are imported now and ready to be assigned.
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'
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.
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.
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.
Trackbacks/Pingbacks