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
Mar 13, 2023 06:07 PM
@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!
Apr 14, 2023 12:01 PM
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 + "'.")
}
}
Mar 14, 2023 01:45 AM
@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.
Mar 17, 2023 05:54 PM
@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!
Apr 14, 2023 12:01 PM
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?
Apr 14, 2023 04:44 PM
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?
Apr 14, 2023 05:12 PM
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.
Apr 17, 2023 06:48 PM