Assignment group changes in ServiceNow

General Questions
, ,
1

Originally asked by Jillani Fazal on 20 January 2023 (original question)

I have configured a connection for ServiceNow - Azure DevOps. The trigger for this connection is on the ServiceNow side based on the assignment group. We use assignment group as the identifier to steer an entity to the correct project in Azure DevOps.

Background:

Entities, such as incidents, tend to change assignment groups often. What I have scripted now is that when the assigment group is changed that the work item is removed on the destination side and the synchronisation is broken.

Since the assignment group is the trigger, if there is another connection available mapped to the new assignment group it will regenerate the sync using the secondary connection and thus regenerates the work item in the new destination project.

It works alright, but stakeholders don’t like the approach as they aren’t able to track their progress properly over multiple projects since the work item is not moved but regenerated.

Query:

Stakeholders require work items to be moved from one project in Azure DevOps to the next if the assignment group changes in ServiceNow.

Do you guys have any customers who have had this query previously and how did you guys solve it?

My own thought would be to create a list in the code mapping all the assignment groups to their respective projects and using REST-API of Azure DevOps to move items if current assignment group =! previous assignment group. Does that sound logical to you guys? Is there something in Exalate already programmed for moving work items from one Azure DevOps project to the next so I skip coding the API myself alltogether?

Thanks for the help

(posting it on the behalf of a client)

[Failed to download attachment: attachment_58498776_smile.png. Error: 401 Client Error: for url: https://support.idalko.com/images/icons/emoticons/smile.png]

2

Answer by Sonal Otwani on 16 February 2023

Hi Robbert Ijsselsteijn ,

Here is the the solution

import groovy.sql.Sql

def id=""
def projectName=""

if(replica.assignment_group.display_value=="ABC"){
   workItem.projectKey  =  "Demo_Exalate"
   workItem.typeName =  "Bug"
   workItem.iterationPath = "Demo_Exalate"
   workItem.areaPath = "Demo_Exalate"
   workItem."System.TeamProject"="Demo_Exalate"
   projectName="Demo_Exalate"
}
else
{
    workItem.projectKey  =  "Demo Project"
    workItem.typeName =  "Bug"
    workItem.iterationPath = "Demo Project"
    workItem.areaPath = "Demo Project"
    workItem."System.TeamProject"="Demo Project"
    projectName="Demo Project"
}
if(!firstSync)
{
    id=workItem.key
    def someMap = [
  "project": projectName
  ]
def json  = groovy.json.JsonOutput.toJson(someMap)
    def sqlStmts = [
        """
    SELECT     id, issue_urn, field_values 
    FROM       replica
    WHERE      issue_id_str = '"""+id+"""';
""",
    """UPDATE replica SET field_values = '"""+json+"""' WHERE issue_id_str = '"""+id+"""';""", 
    """
    SELECT     id, issue_urn, field_values 
    FROM       replica
    WHERE      issue_id_str = '"""+id+"""';
"""
    ]
    
    
    def sb = new StringBuilder()
    def sqlConnectionFactory = syncHelper.syncRequestReplication.db.source
    def sqlConnection = null
    try {
        sqlConnection = sqlConnectionFactory.createConnection()
        def sql = new Sql(sqlConnection)
        sqlStmts.each { sqlStmt ->
            sb << "-----------------------------------\nSQL: $sqlStmt\nResults: \n"
            if (sqlStmt.toUpperCase().matches("\\s*UPDATE\\s+.+")) {
                def result = sql.execute(sqlStmt)
                sb << (!result ? sql.updateCount : " no updated row number ")
            } else {
                def counter = 0
                sql.eachRow(sqlStmt) { rs ->
                    def nCols = rs.getMetaData().columnCount
                    if (counter == 0 && nCols > 0) {
                            for (i in 1..nCols) {
                                    sb << rs.getMetaData().getColumnName(i)+ ((i<(nCols)) ? "|" : "")
                            }
                            sb<<"\n"
                    }
                    for (i in 1..nCols) {
                            sb << ""+rs.getObject(i)+ ((i<(nCols)) ? "|" : "")
                    }
                    sb<<"\n"
                    counter = counter + 1
                }
            }
            sb << "-----------------------------------\n"
        }
    } finally {
        sqlConnection?.close()
    }
log.error("#sql sb=${sb.toString()}")
}

workItem.summary      = replica.summary
workItem.description  = replica.description
....

Kindly let us know in case of any concerns.

Thanks,

Sonal

Comments:

Robbert Ijsselsteijn commented on 20 February 2023

Hey Sonal, thanks a lot! It works perfectly. Can you describe what the code does so I can troubleshoot and for future reference? Does it remap the exalate connection to another project or something? Thanks again for the help (old community)

3

Answer by Sonal Otwani on 30 January 2023

Hi,

Here is the solution for following use case:

Move the workitem between Azure Devops projects based on ServiceNow’s Assignment Group value:

if(replica.assignment_group.display_value=="ABC"){
workItem.projectKey = "Demo_Exalate"
workItem.typeName = "Bug"
workItem.iterationPath = "Demo_Exalate"
workItem.areaPath = "Demo_Exalate"
workItem."System.TeamProject"="Demo_Exalate"
}
else
{
workItem.projectKey = "Demo Project"
workItem.typeName = "Bug"
workItem.iterationPath = "Demo Project"
workItem.areaPath = "Demo Project"
workItem."System.TeamProject"="Demo Project"
}


workItem.summary = replica.summary
workItem.description = replica.description.........<remaining script for setting other field values>

Let us know in case you need further help for your use case.

Thanks,

Sonal

Comments:

Robbert Ijsselsteijn commented on 01 February 2023

Hey Sonal, Jillani,

At first I thought this would work, but I seem to got stuck again. I can indeed move work items this way and it works well.

However, what now happens is the following:

I create an incident in ServiceNow > a work item is created in Azure DevOps.

I change assignment group in ServiceNow, based on mapping, the project of the work item now changes in Azure DevOps (so far so good).

If I now try to update the work item in Azure DevOps it will not send this update back to ServiceNow, I do not see any sync happening. For instance if I send a comment to ServiceNow it is not visible.

If I change the item back to the original servicenow group and try to send an update from Azure DevOps it will synchronize again (including the old comment I placed earlier. Interestingly the comment will be placed in ServiceNow and in a subsequent sync the exact same comment will be posted back to Azure DevOps too).

Can you guys help out further? Thanks in advance.

4

Answer by Jillani Fazal on 31 January 2023

Hi Robbert Ijsselsteijn

I am glad that Sonal shared the solution. May I know if it did served the purpose? Please let us know. Looking forward to it.

BR,

Jillani

Comments:

Robbert Ijsselsteijn commented on 02 February 2023

Hey Jillani, the solution worked semi-well. There’s a blocking issue now, that if the work item is moved from one Azure DevOps project to the next, the work item is unable to send back updates to servicenow, somehow its not being recognized, it doesn’t give an error, just nothing happens.

Sonal Otwani commented on 06 February 2023

HI Robbert Ijsselsteijn ,

Let me check this issue and update you asap.

Thanks,

Sonal

Robbert Ijsselsteijn commented on 08 February 2023

Thanks looking forward to it (old community)

5

Answer by Robbert Ijsselsteijn on 30 January 2023

Hi Sonal,

Thanks for the answer. I had not expected this to work as I tried this once before with altering the project key, iteration and area paths, but I did not alter the system.teamproject that time. That’s the variable I seemed to have missed! Thanks again!