Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,541
Community Members
 
Community Events
176
Community Groups

How get issue created, and resolved count weekly?

We are looking to send a weekly report via email to a group of people. It should have the project name, number of issues that got created, number of issues that got resolved, and how many are still open. It should have a number of issues from Monday to Friday. Here is an example:

Project: ABC
Issue created: 650
Issue Resolved: 590
Issue still unresolved: 60

It should include all projects, every project should have a separate row.

Is it possible to generate numbers like this via a script?

Thank you for your help.

1 answer

1 accepted

0 votes
Answer accepted

Hi @Shah Baloch 

I just whipped this together ... obviously untested (I have >1000 projects in my instance, I would not want to run this).

But here is a starting point on how you might achieve this with a script:

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.mail.Email
import com.atlassian.mail.MailFactory
import com.atlassian.mail.queue.SingleMailQueueItem
import com.atlassian.mail.server.MailServerManager
import com.atlassian.mail.server.SMTPMailServer
import groovy.xml.MarkupBuilder

def emailRecipients = ''
def adminUser = ComponentAccessor.userManager.getUserByName('admin')
def searchService = ComponentAccessor.getComponent(SearchService)
def writer = new StringWriter()
def builder = new MarkupBuilder(writer)
builder.table {
thead {
tr {
td "Project"
td "Created"
td "Resolved"
td "Unresolved"
}
}
tbody {
ComponentAccessor.projectManager.projectObjects.each { project ->
tr {
def createdJql = "project = $project.key and created > StartOfWeek('1d')"
def resolvedJql = "project = $project.key and resolved > StartOfWeek('1d')"
def unresolvedJql = "project = $project.key and resolved is empty"
def createdJqlParseResult = searchService.parseQuery(adminUser, createdJql)
def resolvedJqlParseResult = searchService.parseQuery(adminUser, resolvedJql)
def unresolvedJqlParseResult = searchService.parseQuery(adminUser, unresolvedJql)
def createdCount = searchService.searchCount(adminUser, createdJqlParseResult.query)
def resolvedCount = searchService.searchCount(adminUser, resolvedJqlParseResult.query)
def unresolvedCount = searchService.searchCount(adminUser, unresolvedJqlParseResult.query)
td project.key
td createdCount
td resolvedCount
td unresolvedCount
}
}

}
}
MailServerManager mailServerManager = ComponentAccessor.mailServerManager
SMTPMailServer mailServer = mailServerManager.defaultSMTPMailServer

if (mailServer && !MailFactory.settings.sendingDisabled) {
Email email = new Email(emailRecipients)
email.setMimeType("text/html")
email.setFrom(mailServer.getDefaultFrom())
email.setSubject('All Project Report')
email.setBody(writer.toString())
ComponentAccessor.getMailQueue().addItem(new SingleMailQueueItem(email))
} else {
log.warn "Unable to send, outgoing email is currently disabled"
}

Adjust the JQLs as necessary and provide adequate recipients and configure it as a custom scheduled job at the desired day and time of the week.

You may desire to add some styling to the html table.

In which case you can try something like this

builder.table (style:"border-style:solid;border-width:thin"){
...
}

Hi @Peter-Dave Sheehan 

I'm getting some errors when I try to run the script. I added the script in the custom schedule job. Initially I got the "The script could not be compiled" error, then I added "

import groovy.xml.MarkupBuilder" I thought that might be an issue but now I'm seeing the below error.

2022-07-20 11:11:48,155 ERROR [jobs.AbstractCustomScheduledJob]: ************************************************************************************* 2022-07-20 11:11:48,163 ERROR [jobs.AbstractCustomScheduledJob]: Script job: 'email' failed groovy.lang.GroovyRuntimeException: Could not find matching constructor for: groovy.xml.MarkupBuilder(Class) at Script12.run(Script12.groovy:14)

Screenshots are attached. sr_72022_3.JPGsr_72022_2.jpgsr_72022.JPG

Any idea how can we fix this?

Thank you for your help.

Ah yes, I see the error on my side too.

Try to replace 

def writer = StringWriter

with

def writer = new StringWriter()

And yes, you need 

import groovy.xml.MarkupBuilder

@Peter-Dave Sheehanit works, thank you, the only issue I'm having with the email that I receive. The table headings are not aligned. I tried to add styling but it didn't work. Here is the styling part I added.

builder.table ("style:border-style:solid;border-width:thin"){
   thead {
        tr {
            td "Project"
            td "Created"
            td "Resolved"
            tr "Unresolved"
        }
}

Here are the screenshots one is with styling that you see code on the top and another one is without styling.

Thank you for your help.

sr_72022_5.jpgsr_72022_4.jpg

Try

builder.table (style:"border-style:solid;border-width:thin"){

Notice the word "style" is not in quotes. Only the style value. 
There was obviously a typo on my part in the original response (I'll update it)

@Peter-Dave Sheehan"Unresolved is displaying in the second row, same "d" of "Resolved". Attaching screenshot.

Thank you for your help.

sr_72022_6.jpg

Another typo... line 21 or so

Instead of 

tr "Unresolved"

It should be

td "Unresolved"

@Peter-Dave Sheehanmy bad I didn't pay attention. It works.

Is it possible to not include projects if no issue is created in the project that week? I mean currently, it is sending all projects regardless if an issue is created or not. I mean all three columns have zero values for some projects.

Also, how can I make the heading bold?

Thank you for your help.

Try this version:

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.mail.Email
import com.atlassian.mail.MailFactory
import com.atlassian.mail.queue.SingleMailQueueItem
import com.atlassian.mail.server.MailServerManager
import com.atlassian.mail.server.SMTPMailServer
import groovy.xml.MarkupBuilder

def emailRecipients = ''
def adminUser = ComponentAccessor.userManager.getUserByName('admin')
def searchService = ComponentAccessor.getComponent(SearchService)
def writer = new StringWriter()
def builder = new MarkupBuilder(writer)
builder.table (style:"border-style:solid;border-width:thin") {
thead {
tr {
td { strong "Project" }
td { strong "Created" }
td { strong "Resolved" }
td { strong "Unresolved" }
}
}
tbody {
ComponentAccessor.projectManager.projectObjects.each { project ->
def createdJql = "project = $project.key and created > StartOfWeek('1d')"
def resolvedJql = "project = $project.key and resolved > StartOfWeek('1d')"
def unresolvedJql = "project = $project.key and resolved is empty"
def createdJqlParseResult = searchService.parseQuery(adminUser, createdJql)
def resolvedJqlParseResult = searchService.parseQuery(adminUser, resolvedJql)
def unresolvedJqlParseResult = searchService.parseQuery(adminUser, unresolvedJql)
def createdCount = searchService.searchCount(adminUser, createdJqlParseResult.query)
def resolvedCount = searchService.searchCount(adminUser, resolvedJqlParseResult.query)
def unresolvedCount = searchService.searchCount(adminUser, unresolvedJqlParseResult.query)
if (createdCount) {
tr {
td project.key
td createdCount
td resolvedCount
td unresolvedCount
}
}
}

}
}
MailServerManager mailServerManager = ComponentAccessor.mailServerManager
SMTPMailServer mailServer = mailServerManager.defaultSMTPMailServer

if (mailServer && !MailFactory.settings.sendingDisabled) {
Email email = new Email(emailRecipients)
email.setMimeType("text/html")
email.setFrom(mailServer.getDefaultFrom())
email.setSubject('All Project Report')
email.setBody(writer.toString())
ComponentAccessor.getMailQueue().addItem(new SingleMailQueueItem(email))
} else {
log.warn "Unable to send, outgoing email is currently disabled"
}

Hi, @Peter-Dave Sheehan with the new script I'm getting emails but there is no data. It is just sending the heading of the table. Though there are more than 200 issues got created last 7 days. Any idea why it's not including data?

sr_72122.jpg

Thank you for your help.

You know you don't have to wait for emails to test this.

Just paste the code in the console and replace everything from line 48 to the end (the part starting withMailServerManager) with "writer.toString()"

This will output the table right there on the page for you to review.

Then, play around with the code until you get the results you want.

Add some logging statements to see what's being processed.

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.mail.Email
import com.atlassian.mail.MailFactory
import com.atlassian.mail.queue.SingleMailQueueItem
import com.atlassian.mail.server.MailServerManager
import com.atlassian.mail.server.SMTPMailServer
import groovy.xml.MarkupBuilder

def emailRecipients = ''
def adminUser = ComponentAccessor.userManager.getUserByName('admin')
def searchService = ComponentAccessor.getComponent(SearchService)
def writer = new StringWriter()
def builder = new MarkupBuilder(writer)
builder.table (style:"border-style:solid;border-width:thin") {
thead {
tr {
td { strong "Project" }
td { strong "Created" }
td { strong "Resolved" }
td { strong "Unresolved" }
}
}
tbody {
ComponentAccessor.projectManager.projectObjects.each { project ->
def createdJql = "project = $project.key and created > StartOfWeek('1d')"
def resolvedJql = "project = $project.key and resolved > StartOfWeek('1d')"
def unresolvedJql = "project = $project.key and resolved is empty"
def createdJqlParseResult = searchService.parseQuery(adminUser, createdJql)
def resolvedJqlParseResult = searchService.parseQuery(adminUser, resolvedJql)
def unresolvedJqlParseResult = searchService.parseQuery(adminUser, unresolvedJql)
def createdCount = searchService.searchCount(adminUser, createdJqlParseResult.query)
def resolvedCount = searchService.searchCount(adminUser, resolvedJqlParseResult.query)
def unresolvedCount = searchService.searchCount(adminUser, unresolvedJqlParseResult.query)
log.info "project $project.key found $createdCount created issue, $resolvedCount resolved issues and $unresolvedCount unresolved issues"
if (createdCount || resolvedCount || unresolvedCount) {
tr {
td project.key
td createdCount
td resolvedCount
td unresolvedCount
}
}
}

}
}
return writer.toString()

I added one line of log for you to review the output and changed the logic so that only projects with triple 0 counts are excluded.

If you don't see any logs, add 

import org.apache.log4j.Level
log.setLevel(Level.INFO) near the top

@Peter-Dave SheehanI'm getting the same output as I got in the last email, I mean just heading. it didn't show anything in the log. I added the log line, the import part in the top, and log.set in the bottom, it didn't show anything in the log. But when I add log.set above the return or on the top then I get errors like below.

groovy.lang.MissingPropertyException: No such property: LevelINFO for class: Script576 at Script576.run(Script576.groovy:10)

--------------

2022-07-21 10:51:21,475 ERROR [jobs.AbstractCustomScheduledJob]: ************************************************************************************* 2022-07-21 10:51:21,476 ERROR [jobs.AbstractCustomScheduledJob]: Script job: 'email' failed groovy.lang.MissingPropertyException: No such property: LevelINFO for class: Script576 at Script576.run(Script576.groovy:10)

Sorry.. typo again.

log.setLevel(Level.INFO) 

@Peter-Dave Sheehanon the result table it is still showing the heading of the table with no project data and the log is showing "found 0 created issue, 0 resolved issues and 0 unresolved issues". Any idea why it's not showing numbers when there are issues created, resolved, and resolved? Here is the screenshot of the log.

sr_72122_02.jpg

Thank you for your help.

Try to add

log.info "createdJql=$createdJql"
log.info "resolvedJql=$resolvedJql"
log.info "unresolvedJql=$unresolvedJql"

Then grab some examples JQL generated and try them in your issue navigator to see how it might differ.

Also, make sure that the user specified in "adminUser" has browse permission for those projects. Or if you want to use your own permissions change that line to:

def adminUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser

@Peter-Dave Sheehanuser I added has administrator privileges that can browse, all projects. I also tested the above line as well. The project in the screenshot has issues that were created this week, also some got closed and some are still unresolved. but it's still showing only tables heading, no data. Any idea why it's not showing data?

sr_72122_03.jpg

So, when you copy the JQL

project = AHS and created > StartOfWeek('1d')

To your issue navigator, you get some issues?

Here is what it looks like in my environment (i only added a filter to only get a few sample projects):

2022-07-21 16_10_42-Script Console.png

2022-07-21 16_11_01-Script Console.pngIf that's not working for you, then there is something different about your environment. Are you sure your index isn't corrupted? 

Hi @Peter-Dave Sheehan The below query is pulling issues in Jira advanced search.

project = AHS and created > StartOfWeek('1d')

I test the script in the lower environment and production both but got just the table's heading. Our Jira version is 8.20.10 and the ScriptRunner version is 6.49.0. I performed reindex but nothing got changed. Can the issue be related to ScriptRunner's installed version?

Thank you for your help.

I'm out of ideas for what to do next.

What do you get when you run just this in the console?

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor

def user = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def searchService = ComponentAccessor.getComponent(SearchService)

ComponentAccessor.projectManager.projectObjects.each { project ->
def createdJql = "project = $project.key and created > StartOfWeek('1d')"
def resolvedJql = "project = $project.key and resolved > StartOfWeek('1d')"
def unresolvedJql = "project = $project.key and resolved is empty"

def createdJqlParseResult = searchService.parseQuery(user, createdJql)
def resolvedJqlParseResult = searchService.parseQuery(user, resolvedJql)
def unresolvedJqlParseResult = searchService.parseQuery(user, unresolvedJql)

def createdCount = searchService.searchCount(user, createdJqlParseResult.query)
def resolvedCount = searchService.searchCount(user, resolvedJqlParseResult.query)
def unresolvedCount = searchService.searchCount(user, unresolvedJqlParseResult.query)

log.info "project $project.key found $createdCount created issue, $resolvedCount resolved issues and $unresolvedCount unresolved issues"
}

@Peter-Dave SheehanIn the console from the Result tab it is displaying all project's names and logs I can see issues created, resolved, and unresolved information, like below.

2022-07-22 16:21:38,230 INFO [runner.ScriptBindingsManager]: project AHS found 3 created issue, 4 resolved issues and 74 unresolved issues

Thank you for your help.

That's good, then what if we change it back to this?

import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor

def user = ComponentAccessor.userManager.getUserByName('admin')
def searchService = ComponentAccessor.getComponent(SearchService)

ComponentAccessor.projectManager.projectObjects.each { project ->
def createdJql = "project = $project.key and created > StartOfWeek('1d')"
def resolvedJql = "project = $project.key and resolved > StartOfWeek('1d')"
def unresolvedJql = "project = $project.key and resolved is empty"

def createdJqlParseResult = searchService.parseQuery(user, createdJql)
def resolvedJqlParseResult = searchService.parseQuery(user, resolvedJql)
def unresolvedJqlParseResult = searchService.parseQuery(user, unresolvedJql)

def createdCount = searchService.searchCount(user, createdJqlParseResult.query)
def resolvedCount = searchService.searchCount(user, resolvedJqlParseResult.query)
def unresolvedCount = searchService.searchCount(user, unresolvedJqlParseResult.query)

log.info "project $project.key found $createdCount created issue, $resolvedCount resolved issues and $unresolvedCount unresolved issues"
}

 What does that do in the console?

If we get "0" counts, then the issue is with the 'admin' user not having permissions to search.

@Peter-Dave Sheehanmy bad, I'm sorry I added my full name instead of the username. I was confused with ".getUserByName". I updated with a username like sbaloch and it worked.

Thank you so much. I'll add the back email part and see how it looks. I'll let you know if I'll have an issue. Thank you for helping me out.

@Peter-Dave Sheehansorry to bother you. Issue created numbers are correct but Resolved and Unresolved are not. It is pulling all issues that are open regardless of creation date, same with the Resolved issues. For example in the AHS project 62 issues got created this week, 49 got resolved and 13 are still open, but in results I can see 62 issues got created, 68 got resolved and 48 are unresolved.

Is it possible to pull those issues that got created and resolved and are still open from this week? For example, 62 issues got created 49 were Resolved, and 13 are Unresolved. Just display this week's activity, not the old issues. Is that possible?

Thank you for your help.

Build the advanced search query according to your business needs and replace the appropriate JQL variable (resolvedJql or unresolvedJql). I would hope you understand JQL queries enough to achieve that if you have any jira experience.

Perhaps something like this:

def resolvedJql   = "project = $project.key and created > StartOfWeek('1d') and resolved is not empty"
def unresolvedJql = "project = $project.key and created > StartOfWeek('1d') and resolved is empty"
Like Shah Baloch likes this

Thank you so much, I'll test it on Monday. Thank you for your help.

Hi @Peter-Dave Sheehan it works, thank you so much for your help. Just one last thing I wanted to add to the top of the table. I tried to work around "email.setBody(writer.toString())" it didn't included the text. If I remove writer.toString and add text then it only sends the text. Is it possible to add a paragraph on top of the table like below?

"Hi,

Below are this week's created, resolved, and unresolved issues.

table

"

----

Is it possible to add something like this?

Thank you for your help

You can work within the markup builder to add a paragraph

builder.p "Hi,"
builder.p "Below are this week's created, resolved, and unresolved issues"
builder.table (style:"border-style:solid;border-width:thin") {
thead {
...

Or if you prefer to work with strings, extract the table from the string writer used in the markupbuilder, and add that to a custom string.

def table = writer.toString()
def body = "<p>Hi,<br>Below are this week's created, resolved, and unresolved issues</p>$table"
email.setBody(body)
Like Shah Baloch likes this

Thank you so much @Peter-Dave Sheehan I'll use one of these. Thank you again for your help.

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events