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

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.

For me I could only think of SQL query as the base on how to do this task. So I made an SQL query that would allow me to do this. Then started working on the powershell script that would allow me to use the SQL query that I wanted to use. So the results are like so:

# Modify these values to connect your SCCM server
$dataSource = "SCCM"
$database = "CM_DEV"
$csvPath = "$env:USERPROFILE\Desktop\Autopilot_Results.csv"

# Autopilot Enrollment  Group Tag HARD CODED VALUES!
$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="280" Width="400" 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>

        <Label Content="Computer Name:" Grid.Row="0" Grid.Column="0" Margin="10" VerticalAlignment="Center"/>
        <TextBox Name="ComputerName" Grid.Row="0" Grid.Column="1" Margin="10" Width="250"/>

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

        <Button Name="Submit" Content="Submit" Grid.Row="2" Grid.ColumnSpan="2" Margin="10" Width="150" HorizontalAlignment="Center" Background="#0078d7" Foreground="White"/>

        <!-- New TextBlock to Display CSV Export Path -->
        <TextBlock Name="ExportPathText" Grid.Row="3" Grid.ColumnSpan="2" Margin="10" Foreground="Gray" HorizontalAlignment="Center"/>
    </Grid>
</Window>
"@
$reader = (New-Object System.Xml.XmlNodeReader $xaml)
$window = [Windows.Markup.XamlReader]::Load($reader)
$submitButton = $window.FindName("Submit")
$computerNameBox = $window.FindName("ComputerName")
$groupTagBox = $window.FindName("GroupTag")
$exportPathText = $window.FindName("ExportPathText")
$exportPathText.Text = "Export: $csvPath"

$submitButton.Add_Click({
    $computerName = $computerNameBox.Text
    $groupTag = $groupTagBox.SelectedItem.Content

    # Validate inputs
    if ([string]::IsNullOrWhiteSpace($computerName) -or [string]::IsNullOrWhiteSpace($groupTag)) {
        [System.Windows.MessageBox]::Show("Please enter a Computer Name and select a Group Tag.")
        return
    }

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

    try {
        Write-Host "Opening a connection to '$database' on '$dataSource'"
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connectionString
        $connection.Open()

        # Running query
        Write-Host "Running query for ComputerName: $computerName and GroupTag: $groupTag"

        $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
        WHERE cpu.Name0 LIKE @ComputerName
"@
        $command = $connection.CreateCommand()
        $command.CommandText = $query
        $command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ComputerName", "%$computerName%")))
        $result = $command.ExecuteReader()
        $table = New-Object "System.Data.DataTable"
        $table.Load($result)

        $count = $table.Rows.Count

        if ($count -gt 0) {
            $table.Columns.Add("Group Tag") | Out-Null
            foreach ($row in $table.Rows) {
                $row["Group Tag"] = $groupTag
            }

            # Export to CSV
            $table | Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8

            [System.Windows.MessageBox]::Show("Query executed successfully.`nFound $count records.`nResults saved to: $csvPath")
        } 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 computer from the database:

After being run. It will then make a single computer file that can be imported to Intune with group tag intact.

This is not currently being developed into a multi computer import but it should be quite a simple change to do like sccm collection id query and get the results from there then. Well I found the time to do it, here is part 2