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

[Vinay Sharma]

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]

Hi @Vinay Sharma quick question do you have any scripting tools available to you?  This can be accomplished via one of those re: scriptrunner.

[Vinay Sharma]

Thanks! @Craig Nodwell

Yes, ScriptRunner is there. Any suggested steps?

[Craig Nodwell]

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

[Craig Nodwell]

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 = '''<ac:structured-macro ac:macro-id="9250c4a0-3df7-435e-9a22-e00817f8da67" ac:name="details" ac:schema-version="1">
  <ac:parameter ac:name="label">monthly_jira_report</ac:parameter>
  <ac:rich-text-body>
    <table class="wrapped" style="text-align: left;">
  <thead>
    <tr>
      <th style="text-align: left;">Project</th>
      <th style="text-align: left;">Key</th>
      <th style="text-align: left;">Component/s</th>
      <th style="text-align: left;">Issue Type</th>

     </tr>
''' 

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 += """

        <tr>

          <td>${cfProject}</td>
          <td>${cfKey}</td>
          <td>${cfComponent}</td>
          <td>${cfIssueType}</td>        

         </tr>

 """

}
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 = '''</thead></table>'''
String format3 = format + format2
format += '''</thead></table></ac:rich-text-body>
      </ac:structured-macro>'''

    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 += '''</thead></table></ac:rich-text-body>
      </ac:structured-macro>'''
// 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<Response, Map>() {
        @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<Response>() {
        @Override
        void handle(Response response) throws ResponseException {
            if (response.statusCode != HttpURLConnection.HTTP_OK) {
                throw new Exception(response.getResponseBodyAsString())
            } 
        }
    })

[Craig Nodwell]

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