Example of a Confluence Page that captures all changes in a Jira project over date range?

[Andy Pelzmann]

I want to improve our workflow by setting up a confluence page that will act as an automatic notebook for the week's activity. Are there examples/templates/tutorials about how to set this up? What I want on a page is:

For a given date or date range, create a table (or list, or other format) that displays:

• All tasks created
• All tasks that changed state (and what state the changed to, e.g. In Progress to Done)
• All comments added
• All descriptions changed

I have ScriptRunner available and can invest in other plug-ins if more appropriate. 

Thank you for any help or suggestions.

[Craig Nodwell]

I have done this exact thing.
I found it easier to push from Jira to Confluence.
With Scriptrunner setup a job to run once a week.
In the job execute queries to gather your data, populate to values, publish to Confluence.
I have an example script if you're interested.

[Andy Pelzmann]

That is a great suggestion. Yes I would really appreciate an example script. Thank you.

[Craig Nodwell]

Here's the script example.
There's different method examples for running queries in a script in here: re: plain text vs. variable assignment.
Also you should explore the Confluence Structured Macro Language and convert the publish to more align with that :) it does work though... Also note these jql's are not designed to satisfy your ask but were there from previous work.  You can get the specific details you are looking for either through queries or from other functions within scriptrunner re: the ability to find out how long an issue was in a particular status.

"I'm known for making it work not for making it pretty" 

I hope this gets you going.

//Jobs to Confluence Report
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
import groovy.xml.MarkupBuilder

import com.atlassian.jira.component.ComponentAccessor
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.search.SearchQuery
import java.text.SimpleDateFormat
import java.sql.Timestamp
import java.text.DateFormat
import java.util.Date

// 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);

// 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 projectName1 = "Project1"
def projectName2 = '"Project2"'
def groupName = "Developers"
def membersofGroupSearch = "AND assignee in membersOf(" + groupName + ") "
def statusInOpen = 'status in (Open,"To Do") '
def statusInClosed = ' status in (Closed,Done,"Closed Complete",Cancelled,"Release Done") '

//jqlSearch* - this is the jql query that we want to execute
//query* - sends our jql query to the jqlQueryParser for formatting
//results* - is the result of our query returned from the searchService if you wanted to see this you could print the variable
//totals* - this is a sum of our results

// query Project1

//All Project1 Issues
def jqlSearchProject1All = "project = " + projectName1
def queryProject1All = jqlQueryParser.parseQuery(jqlSearchProject1All)
def resultsProject1All = searchService.search(user,queryProject1All, PagerFilter.getUnlimitedFilter())
def totalsProject1All = ("Total issues: ${resultsProject1All.total}")

//All New Developer Only
def jqlSearchCreatedDeveloperOnly = 'assignee in membersOf("Developer") AND project = ' + projectName1 + " AND created >= " + previousMonthStartFormatted
def queryDeveloperOnlyCreated = jqlQueryParser.parseQuery(jqlSearchCreatedDeveloperOnly)
def resultsDeveloperOnlyCreated = searchService.search(user,queryDeveloperOnlyCreated, PagerFilter.getUnlimitedFilter())
def totalsDeveloperOnlyCreated = ("Total issues: ${resultsDeveloperOnlyCreated.total}")

//All Closed Developer Only
def jqlSearchClosedDeveloperOnly = "project = " + projectName1 + " AND" + statusInClosed + " AND created > " + previousMonthStartFormatted
def queryDeveloperOnlyClosed = jqlQueryParser.parseQuery(jqlSearchClosedDeveloperOnly)
def resultsDeveloperOnlyClosed = searchService.search(user,queryDeveloperOnlyClosed, PagerFilter.getUnlimitedFilter())
def totalsDeveloperOnlyClosed = ("Total issues: ${resultsDeveloperOnlyClosed.total}")

//All In Progress Developer Only
def jqlSearchWIPDeveloperOnly = '(assignee in membersOf("Developer") AND status not in (Open,"To Do",New,Done,Cancelled,"Release Done","Closed Completed",Closed) AND resolution = Unresolved AND project = "Project1" )'
def queryDeveloperOnlyWIP = jqlQueryParser.parseQuery(jqlSearchWIPDeveloperOnly)
def resultsDeveloperOnlyWIP = searchService.search(user,queryDeveloperOnlyWIP, PagerFilter.getUnlimitedFilter())
def totalsDeveloperOnlyWIP = ("Total issues: ${resultsDeveloperOnlyWIP.total}")

// query Project2

//All Project2 Issues
def jqlSearchProject2All = "project = " + projectName2
def queryProject2All = jqlQueryParser.parseQuery(jqlSearchProject2All)
def resultsProject2All = searchService.search(user,queryProject2All, PagerFilter.getUnlimitedFilter())
def totalsProject2All = ("Total issues: ${resultsProject2All.total}")

//All New Project2 Developer Only
def jqlSearchCreatedProject2Only = 'assignee in membersOf("Developer") AND project = ' + projectName2 + " AND created > " + previousMonthStartFormatted
def queryProject2OnlyCreated = jqlQueryParser.parseQuery(jqlSearchCreatedProject2Only)
def resultsProject2OnlyCreated = searchService.search(user,queryProject2OnlyCreated, PagerFilter.getUnlimitedFilter())
def totalsProject2OnlyCreated = ("Total issues: ${resultsProject2OnlyCreated.total}")

//All Closed Project2 Only
def jqlSearchClosedProject2Only = "project = " + projectName2 + " AND" + statusInClosed + " AND created > " + previousMonthStartFormatted
def queryProject2OnlyClosed = jqlQueryParser.parseQuery(jqlSearchClosedProject2Only)
def resultsProject2OnlyClosed = searchService.search(user,queryProject2OnlyClosed, PagerFilter.getUnlimitedFilter())
def totalsProject2OnlyClosed = ("Total issues: ${resultsProject2OnlyClosed.total}")

//All In Progress Project2 Only example of text query
def jqlSearchWIPProject2Only = '(assignee in membersOf("Developers") AND status not in (Open,"To Do",New,Done,Cancelled,"Release Done","Closed Completed",Closed) AND resolution = Unresolved AND project = "Project 2" )'
def queryProject2OnlyWIP = jqlQueryParser.parseQuery(jqlSearchWIPProject2Only)
def resultsProject2OnlyWIP = searchService.search(user,queryProject2OnlyWIP, PagerFilter.getUnlimitedFilter())
def totalsProject2OnlyWIP = ("Total issues: ${resultsProject2OnlyWIP.total}")

// query other projects that members of the group Developers have been assigned

// All Issues Developers Other Projects
def jqlSearchOtherAll = '(assignee in membersOf("Developers") AND project not in ("Project 1","Project 2"))'
def queryOtherall = jqlQueryParser.parseQuery(jqlSearchOtherAll)
def resultOtherall = searchService.search(user,queryOtherall, PagerFilter.getUnlimitedFilter())
def totalsOtherall = ("Total issues: ${resultOtherall.total}")

// All New Developers Other Projects
def jqlSearchCreatedOtherOnly = statusInOpen + membersofGroupSearch + " AND created > " + previousMonthStartFormatted
def queryOthersOnlyCreated = jqlQueryParser.parseQuery(jqlSearchCreatedOtherOnly)
def resultsOtherOnlyCreated = searchService.search(user,queryOthersOnlyCreated, PagerFilter.getUnlimitedFilter())
def totalsOthersOnlyCreated = ("Total issues: ${resultsOtherOnlyCreated.total}")

//All Closed Developers Other Projects
def jqlSearchClosedOtherOnly = statusInOpen + membersofGroupSearch + " AND " + statusInClosed + " AND created > " + previousMonthStartFormatted
def queryOthersOnlyClosed = jqlQueryParser.parseQuery(jqlSearchClosedOtherOnly)
def resultsOtherOnlyClosed = searchService.search(user,queryOthersOnlyClosed, PagerFilter.getUnlimitedFilter())
def totalsOthersOnlyClosed = ("Total issues: ${resultsOtherOnlyClosed.total}")

// query the Backlog for Developers
def jqlSearchBacklog = '(project in ("Project 1","Project 2") AND statusCategory not in("In Progress", Done) AND issuetype not in (Epic,Sub-task))'
def queryBacklog = jqlQueryParser.parseQuery(jqlSearchBacklog)
def resultsBacklog = searchService.search(user,queryBacklog, PagerFilter.getUnlimitedFilter())
def totalsBacklog = ("Total issues: ${resultsBacklog.total}")

// query all issues assigned or associated to Developers
def jqlSearchAll = 'assignee in membersOf("Developers")'
def queryAll = jqlQueryParser.parseQuery(jqlSearchAll)
def resultAll = searchService.search(user,queryAll, PagerFilter.getUnlimitedFilter())
def totalsAll = ("Total issues: ${resultAll.total}")

def jqlSearchAllCreated = '(assignee in membersOf("Developers") AND created > ' + previousMonthStartFormatted + ')'
def queryallCreated = jqlQueryParser.parseQuery(jqlSearchAllCreated)
def resultsallCreated = searchService.search(user,queryallCreated, PagerFilter.getUnlimitedFilter())
def totalsallCreated = ("Total issues: ${resultsallCreated.total}")

def jqlSearchAllClosed = statusInClosed + membersofGroupSearch + " AND created > " + previousMonthStartFormatted
def queryAllClosed = jqlQueryParser.parseQuery(jqlSearchAllClosed)
def resultsAllClosed = searchService.search(user,queryAllClosed, PagerFilter.getUnlimitedFilter())
def totalsAllClosed = ("Total issues: ${resultsAllClosed.total}")

//All In Progress
def jqlSearchWIP = 'assignee in membersOf("Developers") AND status not in (Open,"To Do",New,Done,Cancelled,"Release Done","Closed Completed",Closed) AND resolution = Unresolved'
def queryWIP = jqlQueryParser.parseQuery(jqlSearchWIP)
def resultsWIP = searchService.search(user,queryWIP, PagerFilter.getUnlimitedFilter())
def totalsWIP = ("Total issues: ${resultsWIP.total}")

// Setup Confluence.

static ApplicationLink getPrimaryConfluenceLink() {
def applicationLinkService = ComponentLocator.getComponent(ApplicationLinkService.class)
final ApplicationLink conflLink = applicationLinkService.getPrimaryApplicationLink(ConfluenceApplicationType.class)
conflLink
}

// define confluence SpaceKey and URL
def confluenceSpaceKey = "Your Space Key"
def baseConfluenceURL = "https://Your Confluence address"

//get confluence link and request factory
def confluenceLink = getPrimaryConfluenceLink()
assert confluenceLink // must have a working app link set up

def authenticatedRequestFactory = confluenceLink.createImpersonatingAuthenticatedRequestFactory()

//This is your Conflunce Page Layout in the Confluence Storage Format

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">
<colgroup>
<col style="width: 209.0px;"/>
<col style="width: 268.0px;"/>
</colgroup>
<tbody>
<tr>
<th>Updated Date</th>
<td>
<div class="content-wrapper">
<p>
<ac:structured-macro ac:macro-id="3ba3c5f9-aa38-4a1c-9bff-c14d69489c0c" ac:name="last_updated_date" ac:schema-version="1"/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Total New Issues Project 1</th>
<td colspan="1">'''+ resultsProject1OnlyCreated.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Total New Issues Project 2</th>
<td colspan="1">'''+ resultsProject2OnlyCreated.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Total New Issues Other</th>
<td colspan="1">'''+ resultsOtherOnlyCreated.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Total Issues Closed Project 1</th>
<td colspan="1">'''+ resultsProject1OnlyClosed.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Total Issues Closed Project 2</th>
<td colspan="1">'''+ resultsProject2OnlyClosed.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Total Issues Closed Other</th>
<td colspan="1">'''+ resultsOtherOnlyClosed.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th colspan="1">Our Current Backlog</th>
<td colspan="1">'''+ resultsBacklog.total +'''<div class="content-wrapper">
<p>
<br/>
</p>
</div>
</td>
</tr>
<tr>
<th>Status</th>
<td>
<div class="content-wrapper">
<p>
<ac:structured-macro ac:macro-id="c3dad684-83a1-46a2-9442-e11e07dd9c84" ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="colour">Green</ac:parameter>
<ac:parameter ac:name="title">Green</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
</tbody>
</table>
</ac:rich-text-body>
</ac:structured-macro>
'''

// write storage format using an XML builder
def writer = new StringWriter()
def xml = new MarkupBuilder(writer)

// print the storage that will be the content of the page
log.debug(writer.toString())

// set the page title - this should be unique in the space or page creation will fail
//comment this line to run report mid-month
def pageTitle = previousMonthStartFormattedwithName + " - Month End Jira Report"
//uncomment this line to run report mid-month
//def pageTitle = today + " - Jira Report"

def params = [
type : "page",
title: pageTitle,
space: [
key: confluenceSpaceKey // set the space key
],
ancestors: [
[
type: "page",
id: "#######", //ex: - id: "56988356", // 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())
} JsonSlurper().parseText(response.responseBodyAsString)["_links"]["webui"]
return response.getEntity(Map)
}
})

def contentId = confluencResponse.id
def labelsBody = [
[
prefix: "global",
name : "monthly_jira_report"
],

]
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())
} JsonSlurper().parseText(response.responseBodyAsString)["_links"]["webui"]
}
})

[Andy Pelzmann]

I really appreciate it, Craig. I'll go try this now and report back. At first glance it looks like a excellent framework to start from. And I'm at the stage now where "making it work" far out weighs "making it pretty" so we're aligned.

[Craig Nodwell]

LOL good luck and you are welcome.

[Craig Nodwell]

@Andy Pelzmann how's that working out for you?

[Andy Pelzmann]

• Due date change would be another valuable piece of data (and what it changed from-to)