Le script ci-dessous requete la base SQL de SCCM pour lister et exporter en CSV, les points de distribution SCCM
########################################################################################################
### REQUETE LA BASE SQL DE SCCM POUR OBTENIR LA LISTE DES POINTS DE DISTRIBUTION SCCM.
### EXPORT DES RESULTAT EN FICHIER CSV #####
########################################################################################################
# AUTHOR: CJOURDAN
<#
.SYNOPSIS
REQUETE LA BASE SQL DE SCCM POUR OBTENIR LA LISTE DES POINTS DE DISTRIBUTION SCCM
EXPORT DU RESULTAT EN FICHIER CSV.
.PARAMETER
SQLInstance : Instance SQL
SQLDB : Instance SQL
SQLQuery : Requete SQL
ExportFolder : Dossier d'export du fichier CSV
LogFolder : Chemin du dossier où creer le log du script
.EXAMPLE
.\SCCM_SCCM_Distribution_Points.ps1 -SQLInstance SQLSCCM\SCCM -SQLDB CM_BIM -ExportFolder C:\MyExport -LogFolder C:\MyLogs
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,HelpMessage="Instance SQL")]
[string]$SQLInstance,
[Parameter(Mandatory=$true,HelpMessage="Base SQL")]
[string]$SQLDB,
[Parameter(Mandatory=$false,HelpMessage="Requete SQL")]
[string]$SQLQuery= $("/* --- ALL SCCM DISTRIBUTION POINTS --- */
Declare @UserSIDs As Varchar(25);
Set @UserSIDs = 'Disabled'
SELECT DISTINCT
dp.ServerName AS Distribution_Point
from fn_rbac_SystemResourceList(@UserSIDs) as sys
join fn_rbac_DistributionPointInfo(@UserSIDs) as dp
on sys.NALPath = dp.NALPath
where sys.RoleName = 'SMS Distribution Point'
"),
[Parameter(Mandatory=$true,HelpMessage="Dossier d'export du fichier CSV")]
[string]$ExportFolder,
[Parameter(Mandatory=$true,HelpMessage="Chemin du dossier où creer le log du script")]
[string]$LogFolder
)
# SCRIPT NAME
$ScriptName = "SCCM_SCCM_Distribution_Points.ps1"
# LogName = ScriptName without extension
$Log = $ScriptName.Split('.')[0]
### FUNCTIONS
# Function Write-Log
function Write-Log
{
<#
.SYNOPSIS
This function creates or appends a line to a log file.
.PARAMETER Message
The message parameter is the log message you'd like to record to the log file.
.EXAMPLE
PS C:\> Write-Log -Message 'Value1'
This example shows how to call the Write-Log function with named parameters.
#>
[CmdletBinding()]
param (
[Parameter(Mandatory)]
[string]$Message,
[Parameter(Mandatory)]
[string]$LogPath,
[Parameter(Mandatory)]
[string]$LogName
)
try
{
$DateTime = Get-Date -Format ‘MM-dd-yy HH:mm:ss’
Add-Content -Value "$DateTime # $Message" -Path "$LogPath\$LogName.log"
}
catch
{
Write-Error $_.Exception.Message
}
}
Function GetSQLData {
<#
.SYNOPSIS
This function query SQL Database and get Data
.PARAMETER
SQLInstance: Instance SQL.
SQLDB: Base SQL.
SQLQuery: Requete SQL.
.EXAMPLE
GetSQLData -SQLInstance "MyInstance" -SQLDB "MyDB" -SQLQuery "Select * from MyView"
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$false)]
[string[]]
$SQLInstance,
[Parameter(Mandatory=$false)]
[string[]]
$SQLDB,
[Parameter(Mandatory=$false)]
[string[]]
$SQLQuery
)
$connectionString = "Data Source=$SQLInstance;"+"Integrated Security=SSPI;"+"Initial Catalog=$SQLDB"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()
$dataSet.Tables
}
# EXECUTE Query ($SQLQuery)
Write-Log -Message "Execution of GetSQLData on $SQLDB" -LogPath $LogFolder -LogName $Log
$Result =
Try {
GetSQLData -SQLInstance $SQLInstance -SQLDB $SQLDB -SQLQuery $SQLQuery
}
Catch
{
$Message = "ERROR DURING EXECUTION OF QUERY"
Write-Host -F Red $Message
Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
Exit 1
}
########################################
# SOUS-REGROUPEMENTS
########################################
# All SCCM DP
$AllSCCMDP = $Result | ConvertTo-Csv -Delimiter ';' -NoTypeInformation | foreach {$_.replace('"','')}
$AllSCCMDP
# EXPORTS TO TXT FILES
$AllSCCMDP | Out-File -FilePath "$ExportFolder\All_SCCMDP.txt" -Force
# DISPLAY SUCCESS
$Message = "--- EXECUTION OK ---"
Write-Host -F Green $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Besoin :
Supprimer en masse des devices Autopilot d'un tenant source (Intune) pour pouvoir les migrer vers un tenant cible.
N.B : Un device Autopilot qu'on intègre dans Intune en utilisant son Hash ID ne peut pas co-exister dans deux tenants.
Solution :
1- Extraire les numéros de série des devices Autopilot à supprimer depuis Intune et les mettre dans un fichier texte nommé "Autopilot_Device_SN_list.txt" et le placer sous "c:\Windows\Temp".
2- Exécuter le script PowerShell ci-dessous afin de supprimer des devices Autopilot :
#STEP 1: Install WindowsAutopilotIntune Powershell module (if required, need ot run PowerShell as admin)
Install-module WindowsAutopilotIntune -Force -AllowClobber
#STEP 2: Install WindowsAutopilotIntune Powershell module
Import-module WindowsAutopilotIntune
#STEP 3: Connect to Microsoft Graph
Connect-MgGraph -scopes "Group.ReadWrite.All, Device.ReadWrite.All, DeviceManagementManagedDevices.ReadWrite.All, DeviceManagementServiceConfig.ReadWrite.All, GroupMember.ReadWrite.All"
#STEP 4: Get the Autopilot devices Serial Numbers in a variable $DeviceSNs
$DeviceSNs = Get-Content "C:\Windows\Temp\Autopilot_Device_SN_list.txt"
$Counter = 0
#STEP 5: Delete the Autopilot devices based on serial number list
foreach ($DeviceSN in $DeviceSNs)
{
$Counter++
#Get Autopilot device ID
$DeviceID = (Get-AutopilotDevice -serial $DeviceSN).id
#Get current device information
$CurrentDevice = Get-AutopilotDevice -id $DeviceID
Write-host "Working on device $DeviceSN" -ForegroundColor Cyan
#Delete device from Intune Windows Autopilot devices
try
{
Remove-AutopilotDevice -id $DeviceID
Write-host "- Device $DeviceSN is deleted" -ForegroundColor Green
}
catch{
$ErrorMessage = $_.Exception.message; Write-Host $ErrorMessage -ForegroundColor Red
}
#Counter for PowerShell execution progress
Write-Progress -Activity "Processing $($Counter) of $($DeviceSNs.count)" -CurrentOperation $DeviceSN -PercentComplete (($Counter / $DeviceSNs.count) * 100)
Start-Sleep -Milliseconds 200
}
N.B : le chemin vers le fichier texte qui contient les numéros de série des devices Autopilot "C:\Windows\Temp\Autopilot_Device_SN_list.txt" peut être modifié.