cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Bulk Update Catalog Items Actions

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

(1) Solution

@jdempsey, we ended up the SQL code in the PowerShell script and used a SQL query to update the packages.  The PowerShell script helped me see the relationships between the SQL tables. 

We tested in our Dev environment by changing one manually and found the values in the WD_ITSM_Actions table and used this script to change another:

Update [WD_ITSM_Actions]
Set SystemID_FK = 11, ActionID = 38
Where PackageID = 631

If you don't see an issue with that, I am going to change all packages with a certain SystemID_FK and ActionID to the values above.

We have a couple more scenarios that will have a different ActionID, so will use similar query for those as well.

Thanks!

View solution in original post

(6) Replies

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 + "'.")
    }
}

 

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

@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.

 

@jdempsey, we ended up the SQL code in the PowerShell script and used a SQL query to update the packages.  The PowerShell script helped me see the relationships between the SQL tables. 

We tested in our Dev environment by changing one manually and found the values in the WD_ITSM_Actions table and used this script to change another:

Update [WD_ITSM_Actions]
Set SystemID_FK = 11, ActionID = 38
Where PackageID = 631

If you don't see an issue with that, I am going to change all packages with a certain SystemID_FK and ActionID to the values above.

We have a couple more scenarios that will have a different ActionID, so will use similar query for those as well.

Thanks!

That looks like the right approach to me, but without looking at the environment in more detail, I couldn't say with 100% confidence that there isn't anything else that needs to be changed.  There might possibly be some cleanup needed in the WD_ITSM_Actions_Results table as well.  I assume SystemID_FK = 11 corresponds with your ServiceNow ITSM System in WD_ITSM_Systems and that ActionID = 38 corresponds with whatever ITSM operation it is that you're trying to call (e.g. Create Incident) within WD_ITSM_Operations?

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

Yes, that is correct, SystemID_FK = 11 corresponds with our ServiceNow ITSM System in WD_ITSM_Systems and that ActionID = 38 corresponds with Create Incident operation in WD_ITSM_Operations.  It took me a while to figure out that ActionID (WD_ITSM_Systems) was the same as OperationID (WD_ITSM_Operations). 🙂

I am using this query to find all the values I need:

Select WA.ID, WA.PackageID, WA.SystemID_FK, WA.ActionID, WA.EventID, WA.ActionClass, WA.OrderIndex, WA.StatusID, WP.PackageTitle, WO.OperationName, WO.OperationDescription, WS.SystemName, WS.SystemDescription
from [WD_ITSM_Actions] WA
JOIN WD_WebPackages WP on WA.PackageID = WP.PackageID
JOIN [WD_ITSM_Operations] WO on WO.OperationID = WA.ActionID
JOIN [WD_ITSM_Systems] WS on WS.SystemID = WA.SystemID_FK
Where WP.PackageVisible = 1 and WA.statusID <> 1002 and WA.statusID <> 1001

The WD_ITSM_Actions_Results table looks like a record of all the package requests that had an action associated with it.  I am not seeing why that would need to be modified.  Wouldn't it be the same thing if I change the actions manually?  The requests after the change will just reflect the new values?

Some ITSM actions will make an initial connection and then check back periodically on a scheduled interval until the response matches a designated value.  You'll notice a [StatusMatched] column in that WD_ITSM_Actions_Results table.  That's the column that tracks if the expected value has been matched yet, and if not, it will continue to repeat that action.  If you've configured any of your actions in this way and there are requests where those actions haven't "completed" (i.e. you haven't gotten a matching response yet), then you would want to modify or remove those entries in that table so they don't keep calling the old Remedy actions.  If you're only creating incidents and not checking their status or anything, then you're probably fine and shouldn't need to modify anything in that table.

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".