Office 365 : Script PowerShell pour créer le mapping des utilisateurs dans ShareGate à partir de SharePoint 2007
Lors du travail de préparation à la migration des sites SharePoint On Premise vers Office 365 SharePoint Online, il y a plusieurs étapes à mener telles que:
Une fois ce travail effectué, le client de migration ShareGate vous permet de faire un mapping pour chaque utilisateur entre le monde NTLM On Premise et le monde Office 365 et Azure AD.
Pour ceci, l’option la plus simple reste le client lui même avec un module de recherche simple:
En revanche dès que le nombre d’utilisateurs est important, il devient impossible de faire ce type de mapping manuel. Le module d’import XML est la solution en respectant le format suivant:
<?xml version="1.0"?>
<UserAndGroupMappings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Mappings>
<Mapping>
<Source AccountName="DOMAIN\xxxxx_yyyyyy1" DisplayName="Xxxxx Yyyyyy 1" PrincipalType="User" />
<Destination AccountName="i:0#.f|membership|xxxxx.yyyyyy1@mydomain.com" DisplayName="Xxxxx Yyyyyy 1" PrincipalType="User" />
</Mapping>
<Mapping>
<Source AccountName="DOMAIN\xxxxx_yyyyyy2" DisplayName="Xxxxx Yyyyyy 1" PrincipalType="User" />
<Destination AccountName="i:0#.f|membership|xxxxx.yyyyyy2@mydomain.com" DisplayName="Xxxxx Yyyyyy 2" PrincipalType="User" />
</Mapping>
</Mappings>
</UserAndGroupMappings>
Ainsi, dans mon cas, j’ai la chance d’avoir une base de données de référence avec tous les utilisateurs existants et valides dans le domaine.
Le script PowerShell permet donc de:
- Se connecter à la collection de sites
- Obtenir la liste de tous les utilisateurs de la collection (SPUsers List)
- Tester chaque utilisateur dans la base de données
- Créer le bloc XML de mapping
Une fois terminé, le script crée le fichier XML avec l’extension acceptée par ShareGate.
[string]$ConnectionString = "Data Source=MySQLServer;Integrated Security=True;Initial Catalog=MyDatabase;uid=MyAccount;pwd=MyPassword"
[string]$ProfileTable = "MyInternalUserTable"
[bool]$Verbose = $false
[int]$GLOBAL:TotalUsersUpdated = 0
[int]$NumberRow = 0
[string]$CleanedDestination_DisplayName = ""
[string]$CleanedSource_DisplayName = ""
[string]$MappingFileExported = "C:\TEMP\ShareGate-Create-UserMapping-SiteCollection\_SGS-UserAndGroupMappings-$(get-date -f yyyyMMdd-HHmm).sgum"
function GetSQLToDataSet([string]$sql)
{
if($Verbose){Write-Host "SQL: ", $sql}
$sqlCon = New-Object Data.SqlClient.SqlConnection
$sqlCon.ConnectionString = $ConnectionString
$ds = new-object "System.Data.DataSet" "MyDataSet"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($sql, $sqlCon)
$da.Fill($ds)
Write-Host " DataSet.Table Row Number:", $ds.Tables[0].Rows.Count
$sqlCon.close()
$sqlCon.dispose()
$table = new-object system.data.datatable
$table = $set.Tables[0]
return $table
}
function GetDataTablewithExecuteReader ([string]$SQLQuery)
{
if($Verbose){Write-Host "SQL: ", $SQLQuery}
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
Write-Host " DataTable Row Number:", $Datatable.Rows.Count
return $Datatable
}
function Get-All-SiteCollection-Users([string]$SiteCollectionURL)
{
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Command = New-Object System.Data.SQLClient.SQLCommand
$Connection.ConnectionString = $ConnectionString
[string]$SQLCommandScript = ''
[string]$AccountMappingXML = ''
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null
$site = new-object Microsoft.SharePoint.SPSite($SiteCollectionURL)
$web = $site.openweb()
#Debugging - show SiteCollectionURL
write-host "SiteCollectionURL: ", $SiteCollectionURL
Write-Output "SiteCollectionURL - $SiteCollectionURL"
$siteCollUsers = $web.SiteUsers
write-host "Users Items: ", $siteCollUsers.Count
[string]$XMLFile = "<?xml version=""1.0""?> `n"
$XMLFile += "<UserAndGroupMappings xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""> `n <Mappings> `n"
foreach($MyUser in $siteCollUsers)
{
$NumberRow += 1
if(($MyUser.LoginName.ToLower() -ne "sharepoint\system") -and ($MyUser.LoginName.ToLower() -ne "nt authority\authenticated users") -and ($MyUser.LoginName.ToLower() -ne "nt authority\local service"))
{
#Write-Host " USER: ", $MyUser.LoginName
$UserName = $MyUser.LoginName.ToLower()
Write-Host " >> User Login: ", $MyUser.LoginName
$SQLCommandScript = "SELECT USER_NTNAME AS Source_AccountName, USER_PREFERRED_NAME AS Source_DisplayName, CASE LEN(USER_EMAIL) WHEN 0 THEN 'i:0#.f|membership|'+ (RIGHT(USER_NTNAME,LEN(USER_NTNAME)-CHARINDEX('\',USER_NTNAME)) + '@yourtenant.onmicrosoft.com') ELSE 'i:0#.f|membership|'+ USER_EMAIL END AS Destination_AccountName, (USER_LAST_NAME +', '+ USER_FIRST_NAME +' ('+ USER_CITY +')') AS Destination_DisplayName FROM [metricsops].[dbo].[MOSSProfileDataToWSS] WHERE USER_NTNAME = '$($MyUser.LoginName.ToLower())' ORDER BY USER_NTNAME ASC"
#Write-Host "SQL: ", $SQLCommandScript
$Datatable.Clear()
$Connection.Open()
$Command.Connection = $Connection
$Command.CommandText = $SQLCommandScript
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
Write-Host " DataTable Row Number:", $Datatable.Rows.Count
if ($Datatable.Rows.Count -gt 0)
{
foreach($myUserDataMapping in $Datatable.Rows)
{
$CleanedDestination_DisplayName = $myUserDataMapping.Destination_DisplayName -replace "&", " "
$CleanedSource_DisplayName = $myUserDataMapping.Source_DisplayName -replace "&", " "
Write-Host " "
Write-Host " ========================================================="
Write-Host " Row Number:", $NumberRow, " of ", $siteCollUsers.count
Write-Host " SharePoint Onpremise Account Name:", $myUserDataMapping.Source_AccountName
Write-Host " SharePoint Onpremise Display Name:", $CleanedSource_DisplayName
Write-Host " O365 Destination Account Name:", $myUserDataMapping.Destination_AccountName
Write-Host " O365 Destination Display Name:", $CleanedDestination_DisplayName
Write-Host " ---------------------------------------------------------"
$AccountMappingXML = " <Mapping> `n"
$AccountMappingXML += " <Source AccountName=""$($myUserDataMapping.Source_AccountName)"" DisplayName=""$($CleanedSource_DisplayName)"" PrincipalType=""User"" /> `n"
$AccountMappingXML += " <Destination AccountName=""$($myUserDataMapping.Destination_AccountName)"" DisplayName=""$($CleanedDestination_DisplayName)"" PrincipalType=""User"" /> `n"
$AccountMappingXML += " </Mapping> `n"
$XMLFile += $AccountMappingXML
}
}
}
}
$GLOBAL:TotalUsersUpdated += 1
$XMLFile += " </Mappings> `n </UserAndGroupMappings>"
$XMLFile >> $MappingFileExported
$web.Dispose()
$site.Dispose()
}
function StartProcess()
{
# Create the stopwatch
[System.Diagnostics.Stopwatch] $sw;
$sw = New-Object System.Diagnostics.StopWatch
$sw.Start()
#cls
Get-All-SiteCollection-Users “http://MySharePoint2007WebApp/sites/MySitecollection”
$sw.Stop()
# Write the compact output to the screen
write-host "Time: ", $sw.Elapsed.ToString()
}
StartProcess
Il vous faudra juste adapter le script pour le test des utilisateurs selon votre propre configuration.
Fabrice Romelard [MBA Risk Management]
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
