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:

• SharePoint 2007: Script PowerShell pour nettoyer les listes vides avant migration vers Office 365 – Updated
• Check-SharePoint-Orphaned-Users et Delete Users in All SharePoint Site Collections
• …

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]

----

Une autre solution proposée par le Support de ShareGate (très actif d'ailleurs) est de passer par la fonction de chargement des CSV depuis ShareGate directement:

----

XML User Mapping

I saw your last e-mail with the workaround. Just so you know we have our own Powershell cmdlet that could help you create an SGUM file based on a csv. Here's an example:

#Here is how you can fill your CSV file. In the current example, my two columns are named SourceValue and DestinationValue, and we will set the variable $csvFile:

$csvFile = "C:\CSVfile.csv"

csv content:

SourceValue,DestinationValue

John Doe,Johnny Doe

Mary Doe,Maria Doe

etc...

# Create a table based on the csv file:

$table = Import-Csv $csvFile -Delimiter ","

# Declaration of the mapping settings:

$mappingSettings = New-MappingSettings

# Cycle through each row of the csv file:

foreach ($row in $table)

{

#add the current row's source user and destination user to the list of mappings

Set-UserAndGroupMapping -MappingSettings $mappingSettings -Source $row.SourceValue -Destination $row.DestinationValue

}

# The user and group mappings are exported to:

C:\FolderName\FileName.sgum

Export-UserAndGroupMapping -MappingSettings $mappingSettings -Path C:\FolderName\FileName

---

Fabrice