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

Export the results of a JQL filter and send as CSV

Shamanth February 23, 2023

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!

3 answers

1 accepted

Suggest an answer

Log in or Sign up to answer
0 votes
Answer accepted
Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 27, 2023

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 February 27, 2023

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 February 28, 2023

@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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 28, 2023

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"
}
Like Akbar Gulaliyev likes this
Shamanth March 1, 2023

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


Like # people like this
Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 1, 2023

Nice! 

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

Like Shamanth likes this
Shamanth March 1, 2023

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

Akbar Gulaliyev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 24, 2023

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 August 25, 2023

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.
0 votes
Shamanth February 25, 2023

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

0 votes
Radek Dostál
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 24, 2023

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.

TAGS
AUG Leaders

Atlassian Community Events