Using SCCM database and powershell to register devices to Intune – part 2

Have you ever tried to manage computer imports to Intune one by one. This happened for me and thus I had to make a new approach as I wasn’t sure how to do it so the client would feel that the solution would be easy. Hence powershell and sql script.

Part 1 was about a single computer and part 2 is about sccm collection

# Modify these values
$dataSource = "SCCM"
$database = "CM_DEV"
$csvFolderPath = "$env:USERPROFILE\Desktop\"
# Autopilot Enrollment Profiles Group Tags
$gt1 = "GroupTag 1"
$gt2 = "Autopilot test"
$gt3 = "AAD"

Add-Type -AssemblyName PresentationFramework

[xml]$xaml = @"
<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Autopilot Configuration" Height="200" Width="450" Background="#f0f0f0">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="Auto"/>
            <ColumnDefinition Width="*"/>
        </Grid.ColumnDefinitions>

        <!-- Collection ID Input -->
        <Label Content="Collection ID:" Grid.Row="0" Grid.Column="0" Margin="10" VerticalAlignment="Center"/>
        <TextBox Name="CollectionID" Grid.Row="0" Grid.Column="1" Margin="10" Width="250"/>

        <!-- Group Tag Selection -->
        <Label Content="Group Tag:" Grid.Row="1" Grid.Column="0" Margin="10" VerticalAlignment="Center"/>
        <ComboBox Name="GroupTagDropdown" Grid.Row="1" Grid.Column="1" Margin="10" Width="250">
            <ComboBoxItem Content="$gt1"/>
            <ComboBoxItem Content="$gt2"/>
            <ComboBoxItem Content="$gt3"/>
        </ComboBox>

        <!-- Submit Button -->
        <Button Name="Submit" Content="Query &amp; Export" Grid.Row="2" Grid.ColumnSpan="2" Margin="10" Width="150" 
                HorizontalAlignment="Center" Background="#0078d7" Foreground="White"/>
    </Grid>
</Window>
"@

$reader = (New-Object System.Xml.XmlNodeReader $xaml)
$window = [Windows.Markup.XamlReader]::Load($reader)
$submitButton = $window.FindName("Submit")
$collectionIDBox = $window.FindName("CollectionID")
$groupTagDropdown = $window.FindName("GroupTagDropdown")
$exportPathText = $window.FindName("ExportPathText")

$submitButton.Add_Click({
    $collectionID = $collectionIDBox.Text.Trim()
    $selectedTag = $groupTagDropdown.SelectedItem.Content

    if ([string]::IsNullOrWhiteSpace($collectionID)) {
        [System.Windows.MessageBox]::Show("Please enter a valid Collection ID.")
        return
    }

    $connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;"

    try {
        Write-Host "Connecting to '$database' on '$dataSource'"
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connectionString
        $connection.Open()
        $queryCollectionName = @"
        SELECT Name FROM v_Collection WHERE CollectionID = '$collectionID'
"@
        $cmdCollectionName = $connection.CreateCommand()
        $cmdCollectionName.CommandText = $queryCollectionName
        $collectionName = $cmdCollectionName.ExecuteScalar()

        if (-not $collectionName) {
            [System.Windows.MessageBox]::Show("Invalid Collection ID. No collection found.")
            return
        }

        Write-Host "Collection Name: $collectionName"

        # Query devices in the collection
        $query = @"
        SELECT 
            bios.SerialNumber0 AS 'Device Serial Number',
            os.SerialNumber0 AS 'Windows Product ID',
            mdm.DeviceHardwareData0 AS 'Hardware Hash'
        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_GS_COMPUTER_SYSTEM cpu ON mdm.ResourceID = cpu.ResourceID
        INNER JOIN v_FullCollectionMembership col ON cpu.ResourceID = col.ResourceID
        WHERE col.CollectionID = '$collectionID'
"@

        $command = $connection.CreateCommand()
        $command.CommandText = $query
        $result = $command.ExecuteReader()
        $table = New-Object "System.Data.DataTable"
        $table.Load($result)

        $count = $table.Rows.Count

        if ($count -gt 0) {
            $csvFilePath = "$csvFolderPath$collectionName.csv"
            $table.Columns.Add("Group Tag", [string])
            foreach ($row in $table.Rows) {
                $row["Group Tag"] = $selectedTag
            }
            $table | Export-Csv -Path $csvFilePath -NoTypeInformation -Encoding UTF8
            [System.Windows.MessageBox]::Show("Query executed successfully.`nFound $count records.`nResults saved to: $csvFilePath")
            $exportPathText.Text = "Export: $csvFilePath"
        } else {
            [System.Windows.MessageBox]::Show("No records found.")
        }
        $connection.Close()
        $window.Dispatcher.Invoke([System.Windows.Threading.DispatcherPriority]::Normal, [action]{
            $window.Close()
        })
    } catch {
        [System.Windows.MessageBox]::Show("An error occurred: $($_.Exception.Message)")
    }
})

$window.ShowDialog()

So how does this look?

You need to be able to find your collection from SCCM that you wish to export:

This will do then the collection named .csv file and include the hash values.

After being run you should be able to import your collection mass to Intune with group tag intact.