Export the results of a JQL filter and send as CSV

[Shamanth]

Hi,

I would like to know if ScriptRunner has the capability to export the results of a given JQL filter to a CSV and send it as an email to interested parties. I want to create this as a scheduled job that runs automatically.

Thank you!

[Peter-Dave Sheehan · Accepted solution]

Why do you need CSV specifically?

You could use the vanilla filter subscription functionality.

Just make sure you create column specifications for your filter then create a group subscription with a recurring schedule.

Jira will send an email to those recipients with a nice table view of all the matching issues.

If you're going to do this as a script will involve a lot of complex scripting tasks.

• You probably need to make sure to chunk the filter content so that you don't retrieve all issues at once (a large filter may cause your jira to run out of memory trying to manipulate the content of a large filter in one chunk).
• You need to either hardcode the list of custom fields to export or find a way to access the stored list of columns. But not all custom filed types lend themselves to easy conversion to a single csv column, so the script would need to be smart about that.
• Creating and sending an email is relatively trivial, but attaching a file is more complex

I don't have a ready function that does all this. And this is a larger effort than I'm prepared to do for free ;)

[Shamanth]

Hey @Peter-Dave Sheehan , thank you very much for your feedback!

I specifically wanted the CSV because I have a few external users who needs access to our data and they will be uploading it to their systems. Unfortunately, I cannot onboard them to Jira due to licensing limits. So I wanted to automate this task on a weekly basis.

I knew that this CSV generation is a complicated task but anyways I just thought of asking here maybe if someone has any suggestions or ideas for this.

Anyways, It seems like I should export manually and send it to them for now.

Thanks again Pete!

[Shamanth]

@Peter-Dave Sheehan Can we do this? 

We could make use of the CSV export link of the search results and download the CSV file onto a path in the server. I have a working code snippet that can fetch the file and send it as an email.

The CSV URL is something like this: hxxps://mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/10317/SearchRequest-10317.csv

Maybe if we could develop a code that can access this link and download it onto a desired path, the rest of the task is easy for me.

[Peter-Dave Sheehan]

That's a decent idea.

You can look at this other response of mine for how you can access an internal url from groovy.

Here is a snippet for sending emails. You'll have to find out how to attach the file you access from above:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.mail.Email
import com.atlassian.mail.MailException
import com.atlassian.mail.MailFactory
import com.atlassian.mail.queue.SingleMailQueueItem
import com.atlassian.mail.server.MailServerManager
import com.atlassian.mail.server.SMTPMailServer

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

if (!mailServer || MailFactory.settings.sendingDisabled) {
    log.warn "Unable to send. No server found or sending is disabled"
    return 'Disabled'
}

Email email = new Email('to1@example.com to2@example.com')
email.from = mailServer.defaultFrom
email.subject = "your subject"
email.body = "your body"

try {
    SingleMailQueueItem item = new SingleMailQueueItem(email)
    ComponentAccessor.mailQueue.addItem(item)
    return "Success"
} catch (MailException e) {
    return "Failed"
}

[Shamanth]

I did it @Peter-Dave Sheehan ! YaY

Here is my entire code.

import com.atlassian.jira.component.ComponentAccessor;

import com.atlassian.jira.user.ApplicationUser;

import com.atlassian.sal.api.net.Request;

import com.atlassian.sal.api.net.TrustedRequest;

import com.atlassian.sal.api.net.TrustedRequestFactory;

import javax.ws.rs.core.UriBuilder;

import java.net.URI;

import com.onresolve.scriptrunner.runner.customisers.PluginModule

import com.atlassian.jira.component.ComponentAccessor

import java.io.File

import com.atlassian.mail.Email

import com.atlassian.mail.server.MailServerManager

import javax.mail.internet.MimeBodyPart

import javax.mail.internet.MimeMultipart

import javax.activation.DataSource

import javax.activation.FileDataSource

import javax.activation.DataHandler

def mailServerManager = ComponentAccessor.getComponent(MailServerManager)

def recipient = "myemai@company.com" // Replace with the email address of the recipient

def subject = "Example Email" // Replace with the subject of the email

def body = "This is an example email." // Replace with the body of the email

@PluginModule

TrustedRequestFactory trustedRequestFactory

String url = "hxxps://mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/10307/SearchRequest-10307.csv";

URI uri = UriBuilder.fromUri(url).build();

ApplicationUser currentUser = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser();

TrustedRequest request = trustedRequestFactory.createTrustedRequest(Request.MethodType.GET, url);

request.addTrustedTokenAuthentication(uri.getHost(), currentUser.getName());

String response = request.execute();

def file1 = new File('var/lib/jira/scripts/export1.csv')

file1.write(response)

def dataSource = new FileDataSource(new File('var/lib/jira/scripts/export1.csv'))

if (dataSource) {

    def multipart = new MimeMultipart()

    def textPart = new MimeBodyPart()

    textPart.setText(body)

    multipart.addBodyPart(textPart)

    def attachmentPart = new MimeBodyPart()

    attachmentPart.setDataHandler(new DataHandler(dataSource))

    attachmentPart.setFileName(dataSource.getName())

    multipart.addBodyPart(attachmentPart)

    def email = new Email(recipient)

    def mailServer = mailServerManager.defaultSMTPMailServer

    email.setSubject(subject)

    email.setMultipart(multipart)

    def threadClassLoader = Thread.currentThread().contextClassLoader

    Thread.currentThread().contextClassLoader = mailServer.class.classLoader

    mailServer.send(email)

    Thread.currentThread().contextClassLoader = threadClassLoader

}

else {

    log.error("Attachment not found")

}

[Peter-Dave Sheehan]

Nice! 

If you feel I've earned it, please remember to flag this thread as "accepted"

[Shamanth]

@Peter-Dave Sheehan Thank you very much for the code snippets!

[Akbar Gulaliyev]

Hello @Shamanth ! can you please tell how to find directory where the csv is saved? (def file1 = new File('var/lib/jira/scripts/export1.csv' in your case)

is export1.csv file name standard, or it can be different?

Thanks beforehand!

[Shamanth]

Hi @Akbar Gulaliyev ,

The response from the filter output is written to a file by these lines:

def file1 = new File('var/lib/jira/scripts/export1.csv')

file1.write(response)

export1.csv is just a name that I have given. And this file is stored in "var/lib/jira/scripts/".

And I have kinda moved on from this solution since I found a better one where I integarte the REST API and the Power Query of Excel which can anonymously pull data from Jira with just one click. So the CSV is no longer needed.

[Shamanth]

Hello again @Peter-Dave Sheehan !

Do you have any suggestions or codes with you that can do this job for me on ScriptRunner?

Thanks in advance :)

[Radek Dostál]

You would need to code that job to do the jql search, construct your .csv out of it, and then add it as an attachment to an email to send to the recipients.

At the same time though, this is possible to do without any plugins, all you'd need to do is do the .csv export and capture the HTTP request (which will be in a static format that can be reused). This gives you the file source. How to send that email is the remaining part, but a cron job might do it as well provided you can create a new mail with it and pass it to the mail server.

As far as I know ScriptRunner doesn't have any such automated .csv export without coding it yourself. If you don't specifically need .csv you can always set up the out of box filter subscription, which will send a csv-formatted html mail with the search result. Not specifically a .csv file though.