Set custom field from external SQL database

[dstephens]

Brief: I would like to query an external SQL database using variables from the current issue so i can return relevant information to our support agents.

Detail: Currently we used elements connect to solve this issue. This allows us to run SQL against the database with jira variables. I have tried looking at the documentation and it looks like I need to make a listener for this as I want these values to always be correct.

I have successfully made the SQL resource but i am struggling to find an example or tutorial that I can follow / learn from. I believe i need to import some classes, define my variables, use those to run the SQL and return the SQL output to a custom field.

In elements connect (formerly nFeed) you can make a field that runs script to populate its value. The database picker option in script runner is almost what i need, I want to return some values from our CRM system so our support agents can see that information on the JIRA ticket without having to dig into CRM

I also fully understand I have no provided any code and it wont be possible to provide code based on the information i have provided - I wanted to confirm my approach was correct

[Lasse Langhorn]

Hi @djohnsoncainc

Please test this code first on your test instance. I did some minor adjustments to the code.

• Added guard for CalcPriorityObject
• Used constantsManager.priorities instead of constantsManager.priorityObjects (deprecated) 

// Set a priority using Post-function script on defect create
// Changes the priority of an issue depending on 2 other custom fields (severity and business impact ("bizimpact") with Script Runner
// Map this to issue creation transition - needs to be LAST post-function
// useful reference: https://community.atlassian.com/t5/Answers-Developer-Questions/How-to-change-issue-priority-with-script-runner-groovy/qaq-p/478700
// last updated: 1/25/2019 based on pilot feedback

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.comments.CommentManager;
import com.atlassian.jira.issue.Issue;
import com.atlassian.jira.issue.priority.Priority;
import com.atlassian.jira.issue.IssueInputParametersImpl;
import com.atlassian.jira.issue.customfields.option.Option;


// Get the current Issue
Issue issue = issue

// Get the Manager classes required
def customFieldManager = ComponentAccessor.getCustomFieldManager()
def commentManager = ComponentAccessor.getCommentManager()
def constantsManager = ComponentAccessor.getConstantsManager()
def issueService = ComponentAccessor.getIssueService()


// Get a pointer to the current logged in user
def user = ComponentAccessor.getJiraAuthenticationContext().getUser()


// Get objects and values for relevant fields associated with the specific issue
// Found you have to explicitly cast the field values as strings for script to work

def bizimpactObject = customFieldManager.getCustomFieldObject("customfield_13402")
def severityObject = customFieldManager.getCustomFieldObject("customfield_13402")

def bizimpact = issue.getCustomFieldValue(customFieldManager.getCustomFieldObject("customfield_13402"));
bizimpact = bizimpact.toString()
def severity = issue.getCustomFieldValue(customFieldManager.getCustomFieldObject("customfield_10201"));
severity = severity.toString()

// for debugging get current issue priority values to compare
// def OrigPriority = issue.getPriorityObject().getName()
// OrigPriority = OrigPriority.toString()
// def OrigPriorityID = issue.getPriorityObject().getId()


// use priorityMatrix below as map. Has linked pair value combinations of severity, and business impact values to priority keeping code in for doc purposes only in this version
// Key at far left is severity and second dimension key is the bizimpact. These values as keys and function returns priority name (in quotes)

def priorityMatrix =
        [
                Blocker: [Highest: "P0 - Urgent", Significant: "P1 - High" , Average: "P1 - High", Minimal: "P2 - Medium-High"],
                Major: [Highest: "P1 - High", Significant: "P2 - Medium-High", Average: "P3 - Medium-Low", Minimal: "P4 - Low"],
                Minor: [Highest:"P2 - Medium-High" , Significant: "P3 - Medium-Low" , Average: "P4 - Low", Minimal: "P4 - Low"],
                Trivial: [Highest:"P3 - Medium-Low" , Significant: "P3 - Medium-Low" , Average: "P4 - Low", Minimal: "P4 - Low"],
        ]

// use map lookup method to calculate priority and leave default value of Not Set if map is broken
def CalcPriority = "Not Set"
if (priorityMatrix.containsKey(severity) && priorityMatrix.find{bizimpact} != null) {
    CalcPriority = priorityMatrix[severity][bizimpact]
} else {
    log.warn "Business or Severity field selected not in matrix."
}
CalcPriority = CalcPriority.toString()
// once you have looked up the calculated priority name in the matrix, get the associated priority object based on the name

def CalcPriorityObject = constantsManager.priorities.findByName(CalcPriority)

// initialize some messages with default values
def msg1 = "No Special Message."
def errmsg = "No Errors."

// check lookup of priority by name
if (CalcPriorityObject) {
    def issueInputParameters = new IssueInputParametersImpl()
    issueInputParameters.setPriorityId(CalcPriorityObject.id)
    issueInputParameters.setSkipScreenCheck(true)
    def validationResult = issueService.validateUpdate(user, issue.id, issueInputParameters)
    if (validationResult?.isValid()) {
        issueService.update(user, validationResult)
    } else {
        log.warn("Validation errors: $validationResult?.errorCollection?.errors")
    }
    log.debug("Able to find priority based on lookup.")
} else {
    log.warn("Not able to get priority for new defect. Leaving priority not set (default).")
}

// for debugging create a string you can write to issue comments
// def stringbody = "Current Values of Business Impact, Severity, Original Priority, Original Priority ID, New Derived Priority, New Priority ID: \n" + bizimpact + ", " + severity + ", " + OrigPriority + ", " + CalcPriority.toString() + ", " + OrigPriorityID.toString() + ", " + msg1.toString()

// this is start of code to actually set field value based on id of the Calculated priority value on the create transition only.
// first create object to hold all parameters to pass into the issue service for new issue creation

Regards

Lasse Langhorn

[djohnsoncainc]

Thank you so very much, Lasse. Will check it out and let you know!
Donna

[djohnsoncainc]

HI @Lasse Langhorn 
Everything works as expected on our test server after I made suggested changes. I appreciate your improvements. That said, I have not been able recently to recreate the defect condition with current code on our test environment, so will have to wait to see if these changes resolve the issue. I will reply again in another few weeks if problem is no longer observable after we promote change to production server.

[djohnsoncainc]

Hi @Lasse_Langhorn We have now been running the changed code for several weeks and still have the same issue noted here. It is intermittent and not reproducible under normal usage conditions. Appears to be some kind of race condition. Thank you for the updates, nonetheless, as it was good for me to replace deprecated methods.

Sincerely,

Donna