Rob3r70 SharePoint Blog

SharePoint Enthusiast

recent posts

Social links

  • PowerShell scripts

    I have created a github repository where I uploaded a few scripts I use to manage SharePoint On-prem environments.

    Github repository can be found at https://github.com/Rob3r70/SharePoint

  • Reverse Engineering SharePoint Cmdlets for URL Updates

    Not too long ago, I had a project where we were upgrading a client environment from SharePoint 2016 to Subscription Edition and one of the tasks was to move to new URL as well. All good when we arrived at the part where we needed to show social comments on pages. SharePoint used to have really nice PowerShell cmdlet called “Move-SPSocialComment” but apparently in SPSE it does not work any more.

    So without being to lazy I had to reverse engineer what this cmdlet does and wrote my own function based on the source code.

    Exporting current links from database

    First task was to get all URLs of pages where social comments were posted at. That was easily achieved with a select statement on a UPS Social database.

    Next, a mapping needed to be done and a new column in Excel was added to replace old URL with new URL.

    After that, a script was executed to replace links in database. This code was reverse engineered from SharePoint cmdlet and uses the same code as the original.

    
$dbServer="dbserver"
$socialDb="SP2022_SA_UPS_Social"
$path="c:\temp\socialLinks.csv"

$csv=import-csv $path


# Define connection string (modify as needed)
$connectionString = "Server=$dbServer;Database=$socialDb;Integrated Security=True"

$upsProxy=Get-SPServiceApplicationProxy | ?{$_.typename -like "*user*"}
# Define parameters
$partitionID = [Guid]"0C37852B-34D0-418e-91C6-2AC25AF4BE5B"  # Replace with actual partitionID
$correlationId = [Guid]::NewGuid() # Replace with the actual correlation ID logic


foreach($c in $csv){
		
	$oldUrl = $c.old # Replace with actual old URL
	$newUrl = $c.newUrl # Replace with actual new URL

	# Create SQL connection
	$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
	$connection.Open()

	# Create SQL command
	$command = $connection.CreateCommand()
	$command.CommandText = "dbo.proc_SocialData_MergeSocialNotes"
	$command.CommandType = [System.Data.CommandType]::StoredProcedure

	# Add parameters
	$command.Parameters.Add((New-Object System.Data.SqlClient.SqlParameter("@partitionID", [System.Data.SqlDbType]::UniqueIdentifier))).Value = $partitionID
	$command.Parameters.Add((New-Object System.Data.SqlClient.SqlParameter("@correlationId", [System.Data.SqlDbType]::UniqueIdentifier))).Value = $correlationId
	$command.Parameters.Add((New-Object System.Data.SqlClient.SqlParameter("@oldUrl", [System.Data.SqlDbType]::VarChar, 255))).Value = $oldUrl
	$command.Parameters.Add((New-Object System.Data.SqlClient.SqlParameter("@newUrl", [System.Data.SqlDbType]::VarChar, 255))).Value = $newUrl

	# Output parameter
	$successParam = New-Object System.Data.SqlClient.SqlParameter("@success", [System.Data.SqlDbType]::Bit)
	$successParam.Direction = [System.Data.ParameterDirection]::Output
	$command.Parameters.Add($successParam)

	# Execute command
	$ex=$command.ExecuteNonQuery()

	# Check output parameter value
	$success = [bool]$command.Parameters["@success"].Value

	# Close connection
	$close=$connection.Close()

	# Return success value
	if( $success){
		write-host "Migrate $oldUrl to $newUrl success"
	}
	else{
		write-host "Migrate $oldUrl to $newUrl error" -foregroundcolor red
	}
}

    Hope it helps someone…

  • SPMT PowerShell: Create JSON for Migration Tasks

    I have always struggled with SPMT PowerShell because the use is not really intuitive and comparing to UI tool it does not offer so many options. One of the things that really bothered me was hot to migrate a SharePoint web or sub web to a new site collection and I found out that only option is to use JSON tasks to be able to achieve this.

    # Path to the CSV file with source-target pairs
$csvPath = "C:\temp\Allwebs.csv"
# Import CSV and specify column headers
$pairs = Import-Csv -Path $csvPath -Delimiter ";"

# Define common settings for each task
$settings = @{
    MigrateFileVersionHistory = $true
    KeepAllVersions = $true
    MigrateHiddenItems = $true
    PreservePermission = $true
    EnableIncremental = $true
    MigrateOneNoteNotebook = $true    
}


# Initialize task list
$tasks = @()

# Build tasks from CSV input
foreach ($pair in $pairs) {
    $task = @{
        SourcePath = $pair.SourcePath
        TargetPath = $pair.TargetPath
        Settings = $settings
		Items=@{ SubSites = @() }
    }
    $tasks += $task
}

# Wrap in the main JSON structure
$jsonObject = @{ Tasks = $tasks }

# Convert to JSON string with depth for nested objects
$jsonOutput = $jsonObject | ConvertTo-Json -Depth 5

# Output JSON to file
$outputPath = "C:\temp\migration_tasks-1.json"
$jsonOutput | Out-File -Encoding UTF8 -FilePath $outputPath

Write-Host "Migration JSON file created: $outputPath"

    This code goes through a list of SharePoint webs that you intent to migrate and creates a JSON task to initiate the migration.

    After you have the JSON file created, you can actually use SPMT PowerShell module and start you migration. The script is as follows:

    Import-Module Microsoft.SharePoint.MigrationTool.PowerShell


$onpremCred=Get-Credential -Message "Enter On-prem creds"
$spoCred=Get-Credential -Message "Enter SPO Creds"


#Register the SPMT session with SPO credentials#
Register-SPMTMigration -SPOCredential $spoCred -Force -MigrateAllSiteFieldsAndContentTypes $true -MigrateFileVersionHistory $true -KeepAllVersions $true `
        -AutomaticUserMapping $true -PreserveUserPermissionsForSharePointSource $true -MigrateSiteSettings ONLY_TITLE_LOGO -WorkingFolder C:\SPMT -LookupReferencePolicy FIND_ALL_REFERENCE `
        -DisableNoScriptDuringMigration $true -MigrateNavigation $true


$jsonItems = Get-Content -Raw -Path  "C:\temp\migration_tasks-1.json" | ConvertFrom-Json

ForEach ($taskItem in $jsonItems.Tasks)
{
    $jsonString = ConvertTo-Json $taskItem -Depth 100
    Add-SPMTTask -JsonDefinition $jsonString -SharePointSourceCredential $onpremCred
}


$timer=[System.Diagnostics.Stopwatch]::StartNew()
$timer.Start()


Start-SPMTMigration -NoShow

$session = Get-SPMTMigration



# Query migration status every 5 seconds until migration is finished
while ($session.Status -ne "Finished")
{
    Write-Host "Session status: $($session.Status) | Task count: $i" -BackgroundColor Yellow -ForegroundColor Black
    $i=1
    # Query migration progress of each tasks
    Foreach ($taskStatus in $session.StatusOfTasks)
    {  
        if($($taskStatus.MigratingProgressPercentage) -ne 100){
          
            Write-Host "$($taskStatus.SourceURI ) => $($taskStatus.TargetURI) Complete: $($taskStatus.MigratingProgressPercentage)% | Scanned: $($taskStatus.NumScannedTotalFiles) | ToMig: $($taskStatus.NumFileWillBeMigrated) | ActualMig: $($taskStatus.NumActuallyMigratedFiles)"
            Add-Content -Value "$($taskStatus.SourceURI ) => $($taskStatus.TargetURI) Complete: $($taskStatus.MigratingProgressPercentage)% | Scanned: $($taskStatus.NumScannedTotalFiles) | ToMig: $($taskStatus.NumFileWillBeMigrated) | ActualMig: $($taskStatus.NumActuallyMigratedFiles)" `
                        -Path "C:\SPMTLog\MigLog-$(Get-Date -uformat "%Y-%m-%d").txt"
            $i++
       }

    }
    Write-Host "$("-"*50)" -BackgroundColor Yellow -ForegroundColor Black
    Start-Sleep -Seconds 60
}
$timer.Stop()

$timer.Elapsed

    Hope it helps someone with SPMT migrations.

  • My first blog on this site

    I intend to use this blog site to write about my findings and learnings with SharePoint OnPrem and SharePoint Online