Bulk Update Catalog Items Actions

March 13, 2023 at 11:07 PM

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

App Broker

Top Rated Answers

joan_mckinley
3 years ago
@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!
Expand Post
Selected as Best

All Answers

jdempsey (Flexera Software)
4 years ago
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" Logfunction 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() }} ExecuteSimpleSQLQuery1function 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} ExecuteSimpleNonSQLQuery1function 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} RemoveAllItsmActionsfunction 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).
ExecuteSimpleSQLQuery3function 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} ExecuteSimpleNonSQLQuery3function 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 usedfunction 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 + "'.") }}

Expand Post
- joan_mckinley
  3 years ago
  @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.
  
  Expand Post
- joan_mckinley
  3 years ago
  @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!
  Expand Post
  Selected as Best
  - jdempsey (Flexera Software)
    3 years ago
    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?
    Expand Post
    joan_mckinley
    3 years ago
    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?
    Expand Post
    jdempsey (Flexera Software)
    3 years ago
    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.
    Expand Post

Related Product Forums

→ Flexera One

→ Snow Atlas

→ FlexNet Manager

→ Snow License Manager

→ App Broker

Need help finding an answer?

Ask a Question →