Heads up! ScriptRunner documentation is moving to docs.adaptavist.com. Adaptavist will keep this site up for a bit, but no future updates to documentation will be published here. ScriptRunner 6.20.0 will be the last release to link to scriptrunner.adaptavist.com for in-app help.

The Resources feature allows you to add connections to databases for use in scripts, and other places. For example:

ScriptRunner manages a connection pool, allowing database connections to be reused when future requests are required. A connection pool eliminates the need to close a connection after each use or specify connection details, such as passwords, in scripts. Instead of entering specific connection information, you can refer to the pool name entered when configuring the connection.

External Database Connection

There are two types of database connection:

  • External: Used to connect to any database, such as your sales or contacts database.

  • Local: Connects to the current database your Atlassian application is using.

The Resources page lists all previously configured database connections.

To set up a connection to an external database, you need to know the following information for the database:

  • The JDBC URL

  • Any required credential information (username and password)

  • The driver class

Your database administrator should be able to provide this information if you do not know it.

To set up an external database connection and make the database available to scripts:

  1. Navigate to ScriptRunner > Resources > Add New Item > Database Connection.

  2. Provide a name for the connection in Pool Name.

  3. Enter the JDBC URL of the database to which you wish to connect.

    For more information on sample JDBC URLs, see JDBC Driver Connection URL Strings.

  4. Provide the Driver Class Name.

    Click Show examples to see a list of common driver classes.

    external postgres
  5. Enter the username required to authenticate the database in User and the corresponding Password.

    The User and Password fields are not required if the information is provided in the URL.
  6. Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.

    This SQL query is not saved and is only used to test the connection.
  7. Again, optionally, you can set advanced connection pool properties, such as the maximum pool size. This may be useful in several cases:

    1. If you are using Data Center, by default, the database resource will take 10 connections per node in your cluster.

      If this totals too many connections, you can limit the maximum size by entering, for example: maximumPoolSize=3.
    2. If you would like to limit a database session to ten minutes, enter maxLifetime=600000 (ten minutes in milliseconds).

    3. If you see connections are growing, it could be that a prepared statement was not closed. Enter leakDetectionThreshold=2000 to report any connection that was borrowed from the pool for more than two seconds.

      Look for an exception in your logs beginning: java.lang.Exception: Apparent connection leak detected.

      If your database query is expected to take more than two seconds, then this is probably not a leak, and you should raise this value.

  8. If the preview is successful, click Add.

Other Drivers

If you need to use another database driver (like an Excel or CSV driver), copy it into the tomcat lib directory of your installation and restart. For example, this could be /opt/<application>/lib/.

You might want to use these to create a custom field that allows users to pick from a row in a spreadsheet.

In the example shown below, we are using a CSV driver. This makes available all CSV in the directory provided in the JDBC URL. So in /tmp, we have a CSV file called devs.csv.

csv preview

Local Database Connection

Where possible, you should always use an application’s API to retrieve data rather than its database.

To set up a local database connection and make the local database (the one that your Atlassian application is using) available to scripts, follow these steps:

  1. Navigate to ScriptRunner > Resources > Add New Item > Local Database Connection.

  2. Provide a name for the connection in Pool Name.

    For local connections, we recommend the name local.

  3. Optionally, enter a query into the SQL field to test receiving information from the database. Use Preview to test out different queries.

    This SQL query is not saved and is only used to test the connection.

    + SQL can be used to run queries harder to achieve using the API. For example, aggregate queries:

  4. If the preview is successful, click Add.

The local connection is always read-only (except in the case of H2, where this driver does not support it).

Use Database Resources in Scripts

Having set up a local connection, you can use it in a script as follows:

import com.onresolve.scriptrunner.db.DatabaseUtil

DatabaseUtil.withSql('local') { sql ->
    sql.rows('select * from project')
}

DatabaseUtil.withSql takes two arguments:

  1. The name of the connection as defined by you in the Pool Name parameter when adding the connection (in this example local),

  2. A closure. The closure receives an initialized groovy.lang.Sql object as an argument. See executing SQL for more information on executing queries. The benefit of using a closure is that it is returned the connection to the pool after execution.

    Compare with the alternative method of executing a query.

The withSql method returns whatever the closure returns, so as another example, you could get the number of projects using:

import com.onresolve.scriptrunner.db.DatabaseUtil

def nProjects = DatabaseUtil.withSql('local') { sql ->
    sql.firstRow('select count(*) from project')[0]
}

LDAP Connection

Adding an LDAP resource allows you to query your LDAP servers in a similar way to database connections.

Use an LDAP resource to:

  • Validate that a username provided in a custom field is a member of an LDAP group.

  • Write a REST endpoint to list office addresses.

  • In a Leavers workflow, use a post function to mark a user as having left the company.

To set up an LDAP connection, and make the connection available to scripts:

  1. Navigate to ScriptRunner > Resources > Add New Item > LDAP Connection.

  2. Provide a name for the connection in Pool Name.

  3. Enter the Host.

  4. Optionally, check Use TLS to use TLS/SSL encryption.

  5. Enter the Port the LDAP connection is using.

  6. Enter the base dn into the Base field.

  7. Add the User dn.

  8. Enter the LDAP Password.

    Contact your directory services administrator for LDAP details. If you have set up an LDAP server as an application User Directory, and it’s the same LDAP server, you can copy and paste the values.
  9. Click Add.

    Clicking Preview validates that a successful connection and query can be made to the LDAP server.

external ldap

Use LDAP Resources in Scripts

Having set up an LDAP connection, you can use it in a script as follows:

This example uses the LDAP connection with the Pool Name corporate.

import com.onresolve.scriptrunner.ldap.LdapUtil
import org.springframework.ldap.core.AttributesMapper

import javax.naming.directory.SearchControls

def cnList = LdapUtil.withTemplate('corporate') { template ->
    template.search("", "(sn=Smi*)", SearchControls.SUBTREE_SCOPE, { attributes ->
        attributes.get('cn').get()
    } as AttributesMapper<String>)
}

// cnList now contains the list of common names of users whose surnames begin with "Smi"...

LdapUtil.withTemplate takes two arguments:

  1. The name of the connection as defined by you in the Pool Name parameter when adding the connection (in this example corporate),

  2. A closure. The closure receives a org.springframework.ldap.core.LdapOperations object as an argument.

See spring ldap for more information on querying. Where the documentation refers to an LdapTemplate, this is equivalent to the above-mentioned LdapOperations.

Slack Connection

Configure a Slack connection to integrate ScriptRunner with Slack. Setting up a Slack resource means you can refer to the Connection Name of the Slack resource in scripts throughout ScriptRunner, without the need to specify complex connection information or passwords.

For example, you want to set up a post function to send a Slack message to the project manager after a ticket transitions to Done.

Create a Slack App

First, you will need to navigate to Slack api to create an app with a name and a Slack domain. Here we walk through creating a simple slack app which allows a bot to post a message to a user.

We suggest setting up your Slack app on a development workspace first.
  1. Navigate to Slack’s Your Apps page and click Create an App.

  2. Enter an App Name, for example, ScriptRunner Integration.

  3. Choose your Development Slack Workspace. You’ll need to either sign in to your workspace or create a new workspace to add it as your development workspace.

  4. Navigate to OAuth & Permissions and click Add an OAuth Scope under Bot Token Scopes.

    These scopes tell your app the permissions that it has access to, such as sending messages or viewing rooms.

    For example, add the im:write scope if you want your Slack bot to post a message to a user. You can edit this later to add extra permissions, such as chat:write and chat:write:public to post to a channel.

  5. Once you have added at least one scope, navigate to App Home.

  6. Click Edit to change the Display Name and Default username for your bot (the app uses the bot user to post in the workspace). The Display Name is the name that will be displayed on Slack when a message is sent.

    slack-bot-example

  7. Click Save.

  8. Navigate back to the OAuth and Permissions tab and click Install to Workspace to install the app.

    slack-example-app

  9. The Bot Oauth Access Token now displays at the top of the OAuth & Permissions page. This is the token the feature needs to send messages, click the Copy button.

    slack-bot-token

Set up the Slack Resource

  1. Navigate to ScriptRunnerResourcesAdd New ItemSlack Connection.

  2. Provide a name for the connection in Connection Name. For example slack or if you use multiple workspaces use the workspace name.

  3. Enter the Bot OAuth Token for your Slack workspace in Token.

    For help getting your Bot OAuth Token see Create a Slack App above.
  4. Click Preview to validate the connection using the token provided.

  5. Click Add. Your Slack connection is now configured.

  6. To test the connection, navigate to the ScriptRunnerScript Console.

  7. Use the simple script below to test that the bot can post to a user and/or a channel.

    We recommend you test your slack connection using your own user details, or set up a test slack room.

    When sending a message to a Slack channel the scope chat:write is required…​ for a public channel char:write_public is required. To send a direct message the scope users:read.email is required.

import com.onresolve.scriptrunner.slack.SlackUtil

SlackUtil.message(
    "slack",                         // Identifier you provided when creating the resource
    "acme-developers",               // channel name or ID, or user email
    "Hi, this is the message text"   // Message text
)
Accepted values for the channel argument (the second argument) are public or private channel name (or ID), or a user’s email address, or ID, to send a direct message.

The full list of allowed parameters that can be used to format your Slack messages can be found here.

Remember that a Slack bot requires certain scopes to message a user and/or channel. If these test scripts fail, check the Bot Token Scopes under OAuth & Permissions for your Slack app.

  • chat:write: send messages to channels

  • chat:write.public: send messages to channels the bot user user isn’t a member of.

  • files:write: upload files

  • users:read and users:read.email: send direct messages to a user

  • chat:write.customize: send messages as bot user with a customized username and avatar

Slack Examples

Having set up your Slack connection, you can now use it in your ScriptRunner scripts using the resource Connection Name.

Using Blocks and Attachments

The following example sends a message using blocks and attachments. In this case the message text provided is used as a fallback option.

import com.onresolve.scriptrunner.slack.SlackUtil

SlackUtil.message("slack",
    "acme-developers",
    "This is the fallback message text",
    [
        username   : "ScriptRunner Bot",
        blocks     : [
            [
                type: 'section',
                text: [
                    type: 'plain_text',
                    text: 'Message text'
                ]
            ]
        ],
        attachments: [
            [
                pretext: 'Introductory text',
                text   : 'Text message as part of attachment'
            ],
        ]
    ]
)
Use the block kit builder to preview your messages.

Uploading a File

Upload file to a Slack channel. Scope files:write is required. You can either upload a file to a channel (if the bot user is part of this channel) or to a specific user:

import com.onresolve.scriptrunner.slack.SlackUtil

SlackUtil.upload("slack",
    "acme-developers",
    new File("/tmp/file.txt"),
    [
        text           : 'Fallback text',
        initial_comment: 'Comment about the file being uploaded',
    ]
)
To send an attachment the app must be added to the channel, which you can do by clicking the Add apps link from the channel details, then the More menu.
The full list of allowed parameters can be found here.

In a Post Function

As another example, you can add the following to a post function to send a message to the project manager with the issue priority and summary after it is created.

import com.onresolve.scriptrunner.slack.SlackUtil

SlackUtil.message("slack",
    "acme-developers",
    "${issue.priority.name} priority issue created: *${issue.summary}*"
)

Have questions? Visit the Atlassian Community to connect, share, and learn with other Atlassian users and experts, including Adaptavist staff.

Ask a question about ScriptRunner for JIRA, Bitbucket Server, or Confluence.

Want to learn more? Check out courses on Adaptavist Learn, an online platform to onboard and train new users for Atlassian solutions.