Bulk Update Catalog Items Actions

[joan_mckinley]

We are migrating from Remedy to Service Now.  Our current App Portal catalog items have Actions that point to ITSM operations.  Are we able to update those in bulk to point to the Service Now Operations in Actions instead?  

I have all the tables and was trying to do it in SQL but it looks like there are many columns that need to be updated not just:

[WD_ITSM_Systems].ServiceUrl 
[WD_ITSM_Systems].SystemName
[WD_ITSM_Operations].OperationDescription
[WD_ITSM_Actions] - many columns

Do you have a way of doing it, so we don't have to touch each catalog item?

Thanks,

Joan

[jdempsey]

Are you able to configure your ITSM actions at the global level (i.e. applying to all catalog items)?  Or do you need to apply them selectively to specific catalog items?

To get rid of the existing Remedy actions, I believe you could use something like this...

$SCRIPT:logfile = "C:\Path\To\Logfile.log"
$SCRIPT:ConnectionStringAppPortal = "EnterYourAppPortalConnectionStringHere"

##########################################################################
# Log
##########################################################################
function Log([object]$logobj) {
    ForEach ($obj in $logobj) {
        (Get-Date -Format G) + ' ' + $obj.ToString() | Out-File -FilePath $SCRIPT:logfile -Append
        # Write-Host (Get-Date -Format G) $obj.ToString()
    }
}

##########################################################################
# ExecuteSimpleSQLQuery1
##########################################################################
function ExecuteSimpleSQLQuery1($szConnect, $szQuery, $szParam1) {
    $szResult = ""
    $result = 0
    $con = New-Object System.Data.SqlClient.SqlConnection
    Try {
        $con.ConnectionString = $szConnect
        $con.Open()
        $command = $con.CreateCommand()
        $command.CommandText = $szQuery
        $res = $command.Parameters.AddWithValue('@Param1', $szParam1)
        $result = $command.ExecuteScalar()
    }
    Catch [System.Exception] {
        Log ('EXCEPTION: ' + $_.Exception)
        $Error.Clear()
    }
    Finally {
        $con.Close()
    }
    if ($result -ne $null) {
        $szResult = $result.ToString()
    }
    return $szResult
}

##########################################################################
# ExecuteSimpleNonSQLQuery1
##########################################################################
function ExecuteSimpleNonSQLQuery1($szConnect, $szQuery, $szParam1) {
    $result = 0
    $con = New-Object System.Data.SqlClient.SqlConnection
    Try {
        $con.ConnectionString = $szConnect
        $con.Open()
        $command = $con.CreateCommand()
        $command.CommandText = $szQuery
        $res = $command.Parameters.AddWithValue('@Param1', $szParam1)
        $result = $command.ExecuteNonQuery()
    }
    Catch [System.Exception] {
        Log ('EXCEPTION: ' + $_.Exception)
        $Error.Clear()
    }
    Finally {
        $con.Close()
    }
    return $result
}

##########################################################################
# RemoveAllItsmActions
##########################################################################
function RemoveAllItsmActions($APPackageID) {

    # Check to see if there are actions configured for the specified catalog item
    $Query = 'SELECT [ID] from [WD_ITSM_Actions] WHERE [PackageID] = @Param1'
    [int] $ExistingAction = ExecuteSimpleSQLQuery1 $SCRIPT:ConnectionStringAppPortal $Query $APPackageID
    if ($ExistingAction -eq 0) {
        return
    }

    Log ("Removing all ITSM actions")

    # Update the database to remove all actions from the specified catalog item
    $Query = 'DELETE FROM [WD_ITSM_Actions] WHERE [PackageID] = @Param1'
    $Rows = ExecuteSimpleNonSQLQuery1 $SCRIPT:ConnectionStringAppPortal $Query $APPackageID
    Log ('    Deleted ' + $Rows.ToString() + ' row(s) from WD_ITSM_Actions')
    if ($Rows -gt 0) {
        Log ("    Successfully removed all ITSM actions.")
    }
    else {
        Log ("    Failed to remove all ITSM actions")
    }
}

Then to add your ServiceNow actions, add them in the global actions configuration (if possible).  If that isn't possible and you need to add them to individual catalog items, then you could probably modify this code to suit your needs (this is written for command line actions like PowerShell scripts rather than ITSM actions, so it won't work as-is for your purposes).

##########################################################################
# ExecuteSimpleSQLQuery3
##########################################################################
function ExecuteSimpleSQLQuery3($szConnect, $szQuery, $szParam1, $szParam2, $szParam3) {
    $szResult = ""
    $result = 0
    $con = New-Object System.Data.SqlClient.SqlConnection
    Try {
        $con.ConnectionString = $szConnect
        $con.Open()
        $command = $con.CreateCommand()
        $command.CommandText = $szQuery
        $res = $command.Parameters.AddWithValue('@Param1', $szParam1)
        $res = $command.Parameters.AddWithValue('@Param2', $szParam2)
        $res = $command.Parameters.AddWithValue('@Param3', $szParam3)
        $result = $command.ExecuteScalar()
    }
    Catch [System.Exception] {
        Log ('EXCEPTION: ' + $_.Exception)
        $Error.Clear()
    }
    Finally {
        $con.Close()
    }
    if ($result -ne $null) {
        $szResult = $result.ToString()
    }
    return $szResult
}

##########################################################################
# ExecuteSimpleNonSQLQuery3
##########################################################################
function ExecuteSimpleNonSQLQuery3($szConnect, $szQuery, $szParam1, $szParam2, $szParam3) {
    $result = 0
    $con = New-Object System.Data.SqlClient.SqlConnection
    Try {
        $con.ConnectionString = $szConnect
        $con.Open()
        $command = $con.CreateCommand()
        $command.CommandText = $szQuery
        $res = $command.Parameters.AddWithValue('@Param1', $szParam1)
        $res = $command.Parameters.AddWithValue('@Param2', $szParam2)
        $res = $command.Parameters.AddWithValue('@Param3', $szParam3)
        $result = $command.ExecuteNonQuery()
    }
    Catch [System.Exception] {
        Log ('EXCEPTION: ' + $_.Exception)
        $Error.Clear()
    }
    Finally {
        $con.Close()
    }
    return $result
}

##########################################################################
# AddScriptCommandToEvent - This function is not currently being used
##########################################################################
function AddScriptCommandToEvent($APPackageID, $ActionName, $EventName) {

    Log ("Adding Script Command '" + $ActionName + "' to event '" + $EventName + "'.")

    # Map EventName to EventID
    switch ($EventName) {
        "On Submit"               {$EventID = 0}
        "On Submit No Approval"   {$EventID = 12}
        "On Submit Approval"      {$EventID = 13}
        "On Approver Approval"    {$EventID = 1}
        "On Request Approval"     {$EventID = 2}
        "On Request Rejected"     {$EventID = 8}
        "On Submit For Install"   {$EventID = 3}
        "On Submit For Uninstall" {$EventID = 4}
        "On Success Install"      {$EventID = 5}
        "On Fail Install"         {$EventID = 6}
        "On Success Uninstall"    {$EventID = 10}
        "On Fail Uninstall"       {$EventID = 11}
        "By URL"                  {$EventID = 7}
        "On Cancel"               {$EventID = 9}
        "On Check Status"         {$EventID = 99}
        Default                   {Log ("ERROR: Invalid event name '" + $EventName + "' specified."); return}
    }
    Log ("    EventID = " + $EventID.ToString())
    
    # Look up the ActionID associated with the specified ActionName
    $Query = 'SELECT [ExtensionID] from [WD_ClientCommands] WHERE [ExtensionName] = @Param1'
    [int] $ActionID = ExecuteSimpleSQLQuery1 $ConnectionStringAppPortal $Query $ActionName
    if ($ActionID -eq 0) {
        Log ("    Failed to add Script Command '" + $ActionName + "' to event '" + $EventName + "'. No such Script Command found.")
        return
    }
    Log ("    ActionID = " + $ActionID.ToString())

    # Check to see if there is already an action configured for the specified ActionID, EventID, and PackageID
    $Query3 = 'SELECT [ID] from [WD_Actions] WHERE [EventID] = @Param1 AND [ActionID] = @Param2 AND [PackageID] = @Param3'
    [int] $ExistingAction = ExecuteSimpleSQLQuery3 $ConnectionStringAppPortal $Query3 $EventID $ActionID $APPackageID
    if ($ExistingAction -gt 0) {
        Log ("    Script Command '" + $ActionName + "' is already connected to event '" + $EventName + "' for Package ID " + $APPackageID + ".")
        return
    }

    # Update the database to add the specified action to the specified event for the specified catalog item
    $Query3 = 'INSERT INTO [WD_Actions] ([ActionClass], [EventID], [ActionID], [PackageID], [OrderIndex], [StatusID], [WorkflowName])
                                 VALUES (1, @Param1, @Param2, @Param3, 0, 0, NULL)'
    $Rows = ExecuteSimpleNonSQLQuery3 $ConnectionStringAppPortal $Query3 $EventID $ActionID $APPackageID
    Log ('    Inserted ' + $Rows.ToString() + ' row(s) into WD_Actions')
    if ($Rows -gt 0) {
        Log ("    Successfully added Script Command '" + $ActionName + "' to event '" + $EventName + "'.")
    }
    else {
        Log ("    Failed to add Script Command '" + $ActionName + "' to event '" + $EventName + "'.")
    }
}

[joan_mckinley]

@jdempsey , thank you for your script.  I will test it in our Dev environment.  Unfortunately, we need to apply them selectively to specific catalog items.  75% would use one action and 25% would need other actions.