kevin_christens
Active participant

Can you send Keywords to ServiceNow via 'meta' column in ITSM Create ServiceNow Import Set Record?

Jump to solution

Trying to send keywords from AppPortal to ServiceNow in 'meta' field of the ITSM "Create ServiceNow Import Set Record".  Is there  a way to do this without using custom fields since Keywords are part of the catalog item?
 

I was hoping I could just map ##keywords## to the meta field in ITSM for create/update of catalog items in ServiceNow.

0 Kudos
1 Solution

Jim,
The process I use is working well for updating the meta column in ServiceNow from keywords in AppPortal.    ServiceNow's meta column takes a coma delimited string so it was very easy to implement.  I then create a JSON object of values I want to update in ServiceNow and pass to api/now/table/x_fls_appportal_import_service.  Your sync process provided a great starting point for understanding how this worked.

I first created a web service that can be called from PowerShell.  The web service script is based on the web service created to synchronize catalog images but was modified to also get the keywords as well as custom variables we need to keep synchronized.   This query gets all items that were updated in ServiceNow within the past 70 minutes.  You could probably imbed the SQL into the PowerShell but I have other functions in the web service that I needed for other work.

 

 

SELECT DISTINCT
    pkgs.PackageTitle AS CatalogTitle,
    stuff((SELECT ',' + WD_WebPackageKeyword.KeyWord FROM WD_WebPackageKeyword WHERE WD_WebPackageKeyword.PackageID_FK = pkgs.PackageID FOR XML PATH('')), 1, 1, '') AS [Keywords],
    psnc.ServiceNowCatalogId AS ServiceNowCatalogId,
    psnc.UpdatedOn, pkgs.PackageID AS AppPortalPackageID,
    pkgs.[ImagePath]
FROM vSearchCatalog pkgs
    JOIN [dbo].[WD_Package_SNCatalog] psnc ON psnc.[PackageID_fk] = pkgs.PackageID
WHERE
    psnc.Info NOT IN ('Deleted')
    AND DATEDIFF(MINUTE, psnc.[UpdatedOn], SYSDATETIME()) < 70
    AND pkgs.PackageTitle NOT IN ('Template Freeware', 'Template Licensed');

 

 

The next step was to create a PowerShell script that calls web service and iterates through items that were updated in ServiceNow in past 60 minutes and create a JSON object that gets passed to ServiceNow REST API.

 

 

# Iterate through the recently synced catalog items and upload their "Keywords" to the "meta" field.
ForEach ($CatalogItem in $UpdatedCatalogItems) {
  Try {
    #Build JSON object of vields to update in ServiceNow. 
    $json = new-object -TypeName PSObject
    $json | Add-Member -Type NoteProperty -Name 'meta' -Value $($CatalogItem.keywords)
    $json | Add-Member -Type NoteProperty -Name 'ap_packageid' -Value $($CatalogItem.AppPortalPackageID)
    $bodyJson = $json | ConvertTo-Json

    # Build result string
    $result = "Update ServiceNow '$($CatalogItem.CatalogTitle)' (Updated in AppPortal on: $($CatalogItem.UpdatedOn)) (PackageID: $($CatalogItem.AppPortalPackageID)) meta='$($CatalogItem.keywords)'"
    
    # Call function to update ServiceNow.
    if ((UpdateSnCatalogField $($CatalogItem.ServiceNowCatalogId) $bodyJson)) {
      $result += ": SUCCESS"
      $updateCount += 1
    } else {
      $result += ": FAILED"
      $failCount += 1
    }
    Log($result)
  }
  Catch [System.Exception] {
    $errMsg = "EXCEPTION when calling UpdateSnCatalogField function... $($_.Exception) ServiceNowCatalogId: $($CatalogItem.ServiceNowCatalogId), Meta: $($CatalogItem.Keywords) " 
    LogError($errMsg)
  }
}

 

 

The function below would was called during the iteration of updated catalog items to call ServiceNow's REST API,  api/now/table/x_fls_appportal_import_service

 

 

function UpdateSnCatalogField([string]$snCatId, $bodyJson) {
  # Change the Content-Type header to JSON
  # Set headers for ServiceNow Attachment API call
  $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
  $headers.Add('Authorization', "Basic $SCRIPT:snAuth")
  $headers.Add('Accept', 'application/json')
  $headers.Add('Content-Type', 'application/json')
  $method = "POST"

  if ($GLOBAL:debugLogging) { Log ("BodyJson=($bodyJson)") }
  # Send HTTP request
  $response = Invoke-WebRequest -UseBasicParsing -Headers $headers -Method $method -Uri $SCRIPT:serviceNowAppPortalImportServiceURL -Body $bodyJson

  # Check response status
  if ($response.StatusCode -eq 201) {
    #success
    if ($GLOBAL:debugLogging) { Log ("Set $snFieldName value to ($snFieldValue) for catalog item ($snCatId)") }
  }
  else {
    LogError("Failed to set $snFieldName value to ($snFieldValue) for catalog item ($snCatId): $($response.StatusCode) $($response.StatusDescription)")
    return $false
  }
  return $true
}

 

 

I scheduled this task to run every 60 minutes.

We don't do any transformation on the ServiceNow 'meta' column as it appears to work fine with comma delimited list of keywords.

View solution in original post

8 Replies
jdempsey
Moderator Moderator
Moderator

Unfortunately, I don't believe this is possible, as the keywords are stored in a separate table (WD_WebPackageKeyword) and linked back to the catalog items in the WD_WebPackages table.  Generally, you can use any column name in the WD_WebPackages table as a variable name in the catalog sync.  While there are some "special" variables that don't necessarily map to columns in that table, I don't believe ##keywords## is one of them.  Having said that, it is possible to get keywords for catalog items via an API.  There is an example in the product docs here.  You could write an external process (either in ServiceNow or as a Windows Scheduled Task) to get the keywords and update ServiceNow.

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

Jim,

Thank you for your suggestions.  I made a new process similar/stolen on how the icon resize/sync process from AppPortal to ServiceNow was created.  A new web service, GY_SNKeywordsSync.asmx, and a new PowerShell script, GY_SyncCatalogKeywords.ps1.  The web service is basically a clone of  GCS_SNCatalogSync.asmx but has a query to build a string of existing comma delimited keywords and the ServiceNow PackageID that were updated within the past 60 minutes (you should see the similarities to the icon sync process there).  I then use the packageID and keywords to update ServiceNow by using, /api/now/table/x_fls_appportal_import_service.

I would like to see if ##keywords## could be added to the ITSM catalog update process to eliminate the need for this custom code

0 Kudos

@kevin_christens 

The Ideas portal is where you can suggest product enhancements like the ##keywords## idea.  Submitted ideas go directly into the queue for triage by our Product Management team, and any updates they make to it will be visible in the portal.  Other community users will be able to see the submitted idea and can add their own comments or upvote the idea.  The more votes an idea gets from users, the more likely it will be included in a future release.

One thing to note about this specific idea is that all data being sent to ServiceNow during the catalog sync is sent as strings.  This means that ##keywords## would send a comma-delimited string into a single field in the import service table in ServiceNow.  You would then need some kind of transform script to parse the comma-delimited string into individual keywords before adding them as keywords in ServiceNow.  This is exactly the same behavior you see today with categories if you add a catalog item to more than one category in App Broker.  Out of the box, instead of placing the catalog items into multiple categories in ServiceNow, it would create a new category named with the exact comma-delimited string and would place the item in just that one new category (e.g. "Adobe,File Viewers" would create a category called "Adobe,File Viewers" and would put the catalog item for Adobe Reader into that new category instead of placing it into a category called "Adobe" and another category called "File Viewers").

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".
0 Kudos

One issue I am having with using  x_fls_appportal_import_service web service is that, after calling to update a field in Import_Service, there are custom fields we have in Import_Service that are being cleared.  We have stopped using our custom script to sync keywords to ServiceNow meta column until we can resolve this issue.  I noticed x_fls_appportal_import_service is not being used in the icon sync custom script.  If it was, I would imagine we would have the same problem with the icon sync script.

the AppPortal import Service URL is <serviceNowServer>api/now/table/x_fls_appportal_import_service

 
function UpdateSnCatalogField([string]$snCatId, [string]$snFieldName, [string]$snFieldValue) {
  # Change the Content-Type header to JSON
  # Set headers for ServiceNow Attachment API call
  $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
  $headers.Add('Authorization'"Basic $SCRIPT:snAuth")
  $headers.Add('Accept''application/json')
  $headers.Add('Content-Type''application/json')

  # Set the JSON request body
  $body = @{
    "ap_packageid" = "$($svc.GetPackageIdBySysId($SCRIPT:logFilePrefix,$snCatId))"
    "$snFieldName" = "$snFieldValue"
  }
  $method = "POST"
  $bodyJson = $body | ConvertTo-Json
  if ($GLOBAL:debugLogging) { Log ("BodyJson=($bodyJson)") }
  # Send HTTP request
  $response = Invoke-WebRequest -UseBasicParsing -Headers $headers -Method $method -Uri $SCRIPT:serviceNowAppPortalImportServiceURL -Body $bodyJson
}
0 Kudos

I think you'll find that the handling of image-related fields in ServiceNow is very unique compared to other fields, so I'm not sure that the technique would be exactly the same as what I used in the icon sync script.  Unfortunately, since I'm not a ServiceNow consultant, it would take me a fair amount of time to research this and develop a solution, so that would require a paid Services engagement.  However, given that this is really a ServiceNow problem (how to use their API's) and not an App Broker problem, you might consider reaching out to ServiceNow to see if they can offer any assistance in this area.

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

Hello Jim,

The script I wrote is working well for updating the meta column in ServiceNow from keywords in FNMS.    ServiceNow's meta column takes a coma delimited string so it was very easy to implement.  I then create a JSON object of values I want to update in ServiceNow and pass to api/now/table/x_fls_appportal_import_service.  Your icon sync process provided a great starting point for understanding how this worked.

Example:

SELECT
pkgs.PackageID,
pkgs.PackageTitle,
stuff((SELECT ',' + wpk.KeyWord
FROM WD_WebPackageKeyword wpk
WHERE wpk.PackageID_FK = pkgs.PackageID FOR XML PATH('')), 1, 1, '') AS [Keywords]
FROM
vSearchCatalog pkgs
WHERE
pkgs.PackageID BETWEEN 100 AND 110
0 Kudos

Jim,
The process I use is working well for updating the meta column in ServiceNow from keywords in AppPortal.    ServiceNow's meta column takes a coma delimited string so it was very easy to implement.  I then create a JSON object of values I want to update in ServiceNow and pass to api/now/table/x_fls_appportal_import_service.  Your sync process provided a great starting point for understanding how this worked.

I first created a web service that can be called from PowerShell.  The web service script is based on the web service created to synchronize catalog images but was modified to also get the keywords as well as custom variables we need to keep synchronized.   This query gets all items that were updated in ServiceNow within the past 70 minutes.  You could probably imbed the SQL into the PowerShell but I have other functions in the web service that I needed for other work.

 

 

SELECT DISTINCT
    pkgs.PackageTitle AS CatalogTitle,
    stuff((SELECT ',' + WD_WebPackageKeyword.KeyWord FROM WD_WebPackageKeyword WHERE WD_WebPackageKeyword.PackageID_FK = pkgs.PackageID FOR XML PATH('')), 1, 1, '') AS [Keywords],
    psnc.ServiceNowCatalogId AS ServiceNowCatalogId,
    psnc.UpdatedOn, pkgs.PackageID AS AppPortalPackageID,
    pkgs.[ImagePath]
FROM vSearchCatalog pkgs
    JOIN [dbo].[WD_Package_SNCatalog] psnc ON psnc.[PackageID_fk] = pkgs.PackageID
WHERE
    psnc.Info NOT IN ('Deleted')
    AND DATEDIFF(MINUTE, psnc.[UpdatedOn], SYSDATETIME()) < 70
    AND pkgs.PackageTitle NOT IN ('Template Freeware', 'Template Licensed');

 

 

The next step was to create a PowerShell script that calls web service and iterates through items that were updated in ServiceNow in past 60 minutes and create a JSON object that gets passed to ServiceNow REST API.

 

 

# Iterate through the recently synced catalog items and upload their "Keywords" to the "meta" field.
ForEach ($CatalogItem in $UpdatedCatalogItems) {
  Try {
    #Build JSON object of vields to update in ServiceNow. 
    $json = new-object -TypeName PSObject
    $json | Add-Member -Type NoteProperty -Name 'meta' -Value $($CatalogItem.keywords)
    $json | Add-Member -Type NoteProperty -Name 'ap_packageid' -Value $($CatalogItem.AppPortalPackageID)
    $bodyJson = $json | ConvertTo-Json

    # Build result string
    $result = "Update ServiceNow '$($CatalogItem.CatalogTitle)' (Updated in AppPortal on: $($CatalogItem.UpdatedOn)) (PackageID: $($CatalogItem.AppPortalPackageID)) meta='$($CatalogItem.keywords)'"
    
    # Call function to update ServiceNow.
    if ((UpdateSnCatalogField $($CatalogItem.ServiceNowCatalogId) $bodyJson)) {
      $result += ": SUCCESS"
      $updateCount += 1
    } else {
      $result += ": FAILED"
      $failCount += 1
    }
    Log($result)
  }
  Catch [System.Exception] {
    $errMsg = "EXCEPTION when calling UpdateSnCatalogField function... $($_.Exception) ServiceNowCatalogId: $($CatalogItem.ServiceNowCatalogId), Meta: $($CatalogItem.Keywords) " 
    LogError($errMsg)
  }
}

 

 

The function below would was called during the iteration of updated catalog items to call ServiceNow's REST API,  api/now/table/x_fls_appportal_import_service

 

 

function UpdateSnCatalogField([string]$snCatId, $bodyJson) {
  # Change the Content-Type header to JSON
  # Set headers for ServiceNow Attachment API call
  $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
  $headers.Add('Authorization', "Basic $SCRIPT:snAuth")
  $headers.Add('Accept', 'application/json')
  $headers.Add('Content-Type', 'application/json')
  $method = "POST"

  if ($GLOBAL:debugLogging) { Log ("BodyJson=($bodyJson)") }
  # Send HTTP request
  $response = Invoke-WebRequest -UseBasicParsing -Headers $headers -Method $method -Uri $SCRIPT:serviceNowAppPortalImportServiceURL -Body $bodyJson

  # Check response status
  if ($response.StatusCode -eq 201) {
    #success
    if ($GLOBAL:debugLogging) { Log ("Set $snFieldName value to ($snFieldValue) for catalog item ($snCatId)") }
  }
  else {
    LogError("Failed to set $snFieldName value to ($snFieldValue) for catalog item ($snCatId): $($response.StatusCode) $($response.StatusDescription)")
    return $false
  }
  return $true
}

 

 

I scheduled this task to run every 60 minutes.

We don't do any transformation on the ServiceNow 'meta' column as it appears to work fine with comma delimited list of keywords.

View solution in original post

You rock, Kevin!  Thanks for sharing your solution with the community.

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".
0 Kudos