Automated generation of month-wise report from Confluence, fetching Jira issues

Question

There is a requirement to export a monthly report from a Confluence page, which fetches Jira issues via JQL for each month. The current process requires copying of the Confluence Page, change in report title, and dates in JQL.

Is there a way to automate generation of the report? For example: have a "Generate Monthly report" button, choose the month and on "Apply" this would automatically generate the page with the data extracted from Jira

Craig Nodwell_7eve9b · Answer

Change the necessary variables.
Put code in the scriptrunner console and execute.
Once you get it to where you are satisfied then set it up to run as a job in scriptrunner on your desired intervals based off a CRON expression (examples exist in the job config).

Craig Nodwell_7eve9b · Answer

The imports are based on my needs so you can probably trim them down considerably, remove and test etc. You need to set your own parameters for an issue(to work in script console) your projects, your confluence URL, your confluence space key, your confluence page, page ancestor ID, fixVersion... I tried to put comments where I could. import com.atlassian.jira.component.ComponentAccessor import com.atlassian.jira.user.ApplicationUser import com.atlassian.jira.issue.index.DocumentConstants import com.atlassian.jira.issue.search.SearchProviderFactory import com.onresolve.scriptrunner.canned.util.OutputFormatter import com.onresolve.scriptrunner.parameters.annotation.ShortTextInput import org.apache.lucene.index.Term import org.apache.lucene.search.BooleanClause import org.apache.lucene.search.BooleanQuery import org.apache.lucene.search.TermQuery import com.atlassian.jira.issue.search.SearchQuery import com.onresolve.scriptrunner.parameters.annotation.* import com.atlassian.jira.issue.fields.Field import java.text.SimpleDateFormat import com.atlassian.jira.issue.link.RemoteIssueLinkManager import com.atlassian.jira.issue.IssueManager import com.atlassian.applinks.api.application.jira.JiraApplicationType import com.atlassian.jira.issue.Issue import com.opensymphony.workflow.loader.ActionDescriptor import groovy.xml.MarkupBuilder import com.atlassian.jira.config.properties.APKeys import com.atlassian.jira.issue.link.LinkCollectionImpl import com.atlassian.jira.issue.link.IssueLink import com.atlassian.jira.datetime.DateTimeFormatter import com.atlassian.jira.issue.customfields.impl.DateTimeCFType import com.atlassian.jira.issue.fields.CustomField import com.onresolve.jira.groovy.user.FieldBehaviours import org.apache.log4j.Logger import org.apache.log4j.Level import groovy.transform.BaseScript import com.atlassian.jira.issue.MutableIssue import java.sql.Timestamp import java.text.DateFormat import java.util.Date import com.atlassian.jira.issue.ModifiedValue import com.atlassian.jira.issue.util.DefaultIssueChangeHolder import com.atlassian.jira.issue.comments.CommentManager import com.atlassian.jira.util.ImportUtils import com.atlassian.jira.user.util.DefaultUserManager import com.atlassian.crowd.embedded.api.User import com.atlassian.jira.project.Project import com.onresolve.scriptrunner.runner.util.UserMessageUtil import com.atlassian.jira.event.type.EventDispatchOption import com.atlassian.jira.bc.issue.search.SearchService import com.atlassian.jira.jql.parser.JqlQueryParser import com.atlassian.jira.web.bean.PagerFilter import com.atlassian.jira.issue.CustomFieldManager import com.atlassian.jira.event.issue.DelegatingJiraIssueEvent import com.atlassian.jira.event.issue.IssueEvent import com.atlassian.jira.event.issue.IssueEventBundle import com.atlassian.jira.event.type.EventType import com.atlassian.applinks.api.ApplicationLink import com.atlassian.applinks.api.ApplicationLinkService import com.atlassian.applinks.api.application.confluence.ConfluenceApplicationType import com.atlassian.sal.api.component.ComponentLocator import com.atlassian.sal.api.net.Request import com.atlassian.sal.api.net.Response import com.atlassian.sal.api.net.ResponseException import com.atlassian.sal.api.net.ResponseHandler import com.atlassian.sal.api.net.ReturningResponseHandler import groovy.json.JsonBuilder import groovy.json.JsonSlurper // retrieve details of the current date so that we can run at midnight first day of month against prior month def cal = Calendar.instance; def currentYear = cal.get(Calendar.YEAR); def currentMonth = cal.get(Calendar.MONTH); def customFieldManager = ComponentAccessor.customFieldManager def issueManager = ComponentAccessor.issueManager def issueKey = "JIRA-123" // Set an issue to run in the script console you can get the issue in a listener using event.issue def issue = issueManager.getIssueObject(issueKey) // get the issue object // set the month instance to the start of the previous month if ( currentMonth == 0 ) { cal.set(currentYear-1, 11, 1); } else { cal.set(currentYear, (currentMonth-1), 1); // comment this line to run the report mid-month for current month //cal.set(currentYear, (currentMonth), 1); // uncomment this line run the report mid-month re:remove the -1 from (currentMonth-1) } // extract the date, and format it to a string Date previousMonthStart = cal.time; String previousMonthStartFormatted = previousMonthStart.format('yyyy-MM-dd'); String previousMonthStartFormattedwithName = previousMonthStart.format('MMM-yyyy'); def today = new Date().format('MMM-dd-yyyy'); //for mid-month report run //setup user query parser and search service def user = ComponentAccessor.jiraAuthenticationContext.loggedInUser def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser) def searchService = ComponentAccessor.getComponent(SearchService) //setup some variables we'll use in the script def projectNames = "A,B,C" //Your Projects def queryfixVersion= "2023.1" //The FixVersion def groupName = "DevGroup" //Your Group def membersofGrouDPDearch = "AND assignee in membersOf(" + groupName + ") " def statusInOpen = 'status in (Open,"To Do") ' def statusInClosed = ' status in (Closed,Done,"Closed Complete",Cancelled,"Release Done") ' //def version = issue.getFixVersions() // if you need a fixVersion in your query get it off the triggering issue //def priorVersions = version.toList() // pass the fixVersion into a list //String afterVersionId = priorVersions.last().name // string out the version //queryfixVersion = afterVersionId //set variable to last found fixVersion // query base this on your requirements def jqlSearchRelease = 'project in ( ' + projectNames + ") AND issueType in(Story,Bug) ORDER BY project,component ASC" // Your query def queryRelease = jqlQueryParser.parseQuery(jqlSearchRelease) def resultsRelease = searchService.search(user,queryRelease, PagerFilter.getUnlimitedFilter()) def format = ''' monthly_jira_report

''' resultsRelease.results.each { devissue -> def cfSummary = devissue.summary.toString() def cfProject = devissue.getProjectObject().name.toString() def cfKey = devissue.key def cfComponent = devissue.getComponents().getAt(0)?.getName().toString() def cfIssueType = devissue.issueType.name.toString() def buildList = issue.getComponents()*.getName().toSet() format += """ """ } static ApplicationLink getPrimaryConfluenceLink() { def applicationLinkService = ComponentLocator.getComponent(ApplicationLinkService.class) final ApplicationLink conflLink = applicationLinkService.getPrimaryApplicationLink(ConfluenceApplicationType.class) conflLink } // define confluence SpaceKey and URL def confluenceSpaceKey = "CKEY" //Your Confuence Space def baseConfluenceURL = "https://confluence.mycompany.com" //Your Confluence URL def writer = new StringWriter() def xml = new MarkupBuilder(writer) def buildList = issue.getComponents()*.getName().toSet() //get confluence link and request factory def confluenceLink = getPrimaryConfluenceLink() assert confluenceLink // must have a working app link set up def authenticatedRequestFactory = confluenceLink.createImpersonatingAuthenticatedRequestFactory() def cfFormatFieldName = "formatfield" def format2 = '''

Project	Key	Component/s	Issue Type
${cfProject}	${cfKey}	${cfComponent}	${cfIssueType}

''' String format3 = format + format2 format += '''

''' xml.style(type: "text/css", ''' #scriptField, #scriptField *{ border: 1px solid black; } #scriptField{ border-collapse: collapse; } #scriptField2, #scriptField2 *{ border: 1px solid black; } #scriptField2{ border-collapse: collapse; } ''') xml.table(id: "scriptField") { tr { th("Component") } buildList.each { issueComponent -> def compname = issueComponent.each { } tr { td(compname) } }} log.debug(writer.toString()) String testing = (writer.toString()) String format4 = format + testing format4 += ''' ''' // set the page title - this should be unique in the space or page creation will fail def pageTitle = "Test2" //Your Page Title def params = [ type : "page", title: pageTitle, space: [ key: confluenceSpaceKey // set the space key - or calculate it from the project or something ], ancestors: [ [ type: "page", id: "123456789", // set this to the page id of your parent page in Confluence - you can get that by going to the page clicking page information and then looking at the url ] ], body : [ storage: [ value: format.toString(), representation: "storage" ] ] ] def confluencResponse = authenticatedRequestFactory .createRequest(Request.MethodType.POST, "rest/api/content") .addHeader("Content-Type", "application/json") .setRequestBody(new JsonBuilder(params).toString()) .executeAndReturn(new ReturningResponseHandler() { @Override Map handle(Response response) throws ResponseException { if (response.statusCode != HttpURLConnection.HTTP_OK) { throw new Exception(response.getResponseBodyAsString()) } return response.getEntity(Map) } }) def contentId = confluencResponse.id def labelsBody = [ [ prefix: "global", name : "worksheet" ], ] authenticatedRequestFactory .createRequest(Request.MethodType.POST, "rest/api/content/$contentId/label") .addHeader("Content-Type", "application/json") .setRequestBody(new JsonBuilder(labelsBody).toString()) .execute(new ResponseHandler() { @Override void handle(Response response) throws ResponseException { if (response.statusCode != HttpURLConnection.HTTP_OK) { throw new Exception(response.getResponseBodyAsString()) } } })

Craig Nodwell_7eve9b · Answer

Give me a little time I have to head to the office, I'll send you some script examples once I'm there.