How get issue created, and resolved count weekly?

[Shah Baloch]

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.

[Peter-Dave Sheehan · Accepted solution]

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"){
  ...
}

[Shah Baloch]

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.

Any idea how can we fix this?

Thank you for your help.

[Peter-Dave Sheehan]

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

[Shah Baloch]

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

[Peter-Dave Sheehan]

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)

[Shah Baloch]

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

Thank you for your help.

[Peter-Dave Sheehan]

Another typo... line 21 or so

Instead of 

tr "Unresolved"

It should be

td "Unresolved"

[Shah Baloch]

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

[Peter-Dave Sheehan]

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"
}

[Shah Baloch]

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?

Thank you for your help.

[Peter-Dave Sheehan]

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

[Shah Baloch]

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

[Peter-Dave Sheehan]

Sorry.. typo again.

log.setLevel(Level.INFO) 

[Shah Baloch]

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

Thank you for your help.

[Peter-Dave Sheehan]

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

[Shah Baloch]

@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?

[Peter-Dave Sheehan]

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

If that's not working for you, then there is something different about your environment. Are you sure your index isn't corrupted? 

[Shah Baloch]

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.

[Peter-Dave Sheehan]

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"
}

    

[Shah Baloch]

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

[Peter-Dave Sheehan]

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.

[Shah Baloch]

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

[Shah Baloch]

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

[Peter-Dave Sheehan]

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"

[Shah Baloch]

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

[Shah Baloch]

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

[Peter-Dave Sheehan]

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)

[Shah Baloch]

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