Backup and Restore Last Modified Date for SCSM Change Requests

This is a classic Post cause it is related to System Center Service Manager. I am still doing a lot of Service Manager Staff, and now I start again talking about.

When you import a Management Pack related to the Service Request Class, the Last Modified Date will be updated. This is very badly related to Reporting and the Grooming Job for the Datawarehouse. This also affects incidents and Changes.

There is an awesome tool to Backup and Restore the Last Modified Date for Service Request and Incidents GitHub – BrettMoff/SCSM-Last.Modified.Date.Manager: Remember to always make backups as there is no undo button here. I have provided source code in case anyone is curious as to how this program works. I recommend using 7-Zip to unzip this file. As always, use at your own risk, I am not responsible for your actions, etc.

But, I miss the Change Requests. As a PowerShell Guy, I decided to write a Script to back up and Restore the Last Modified Date for Service Manager Change Requests.

The Script

The Script will query all Changes from the SQL Database and Export them to a CSV.

The Second Part will import the CSV and write the Last Modified via SQL Query back to the Database.

$SCSM_SQL_Server="PSQL700SCSM\SCSM"
$SCSM_SQL_DB="ServiceManager"

$ExportPath="C:\_SCOWorkingDir\DevOps\SeiMi\SMA - Projekt\Set Last Modified\CRExportafterImport.csv"



$AllCR = Invoke-Sqlcmd -ServerInstance $SCSM_SQL_Server -Database $SCSM_SQL_DB -Query "
select CR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as ID, 
CR.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as CreatedDate,
b.LastModified, Status.LTValue as Status,
DATEDIFF(day, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688,b.LastModified) AS DateDiff
from MT_System`$WorkItem`$ChangeRequest as CR
inner join BaseManagedEntity as B on CR.BaseManagedEntityId=b.BaseManagedEntityId
left outer join
                (
                        select *
                        from
                                LocalizedText
                        where
                                (
                                        LanguageCode = 'ENU'
                                )
                                AND
                                (
                                        LTStringType = '1'
                                )
                )
                as Status
                on   Status.LTStringId = CR.Status_72C1BC70_443C_C96F_A624_A94F1C857138

"


$AllCR.count
$AllCR | Export-csv -PAth $ExportPath -NoTypeInformation


$Import = Import-csv -Path $ExportPath
Foreach ($CR in $Import) {
        $DateSQL = Get-Date -UFormat (Get-Date -Date $CR.LastModified)

        $ID = $CR.Id
        $Query = "Update e
                set e.LastModified = '$DateSQL'
                from BaseManagedEntity as E
                inner join MTV_System`$WorkItem`$ChangeRequest as I
                on e.BaseManagedEntityId=i.BaseManagedEntityId where i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C = '$ID'"
    
                Invoke-Sqlcmd -ServerInstance $SCSM_SQL_Server -Database $SCSM_SQL_DB  -Query $Query

        $ID = ""
}

Please understand, there is no guarantee or support when a problem occurs. Make sure you created a DB Backup before.

GitHub Repo

Please see the Github Repo for the latest Version: Seidlm/System-Center-Service-Manager: Everything related to System Center Service Manager (github.com)

Michael Seidl aka Techguy
au2mate everything