Introduction

This tutorial explains applying SQL queries using Jira with Rundeck.

Jira Setting

After setting up your Jira service desk dashboard you will get the below screen.

Click on the projects tab to create a new project

Next click on the Process management, next give your project name and automatically a project key will be generated as shown below:

After the project has been created your project name will be listed in the projects tab.

Next click on the Issues listed on the right corner of your screen as below:

Next click on the workflow under the Workflows tab as below:

In the workflow management page edit workflow for your project which will be listed with your project name as below:

Create the project workflow as required by adding the status and transition available in the workflow screen 

Click on the transition on which the script has been given then you will get the dialog box as below:

Next click the Post Functions

Next click the Add post function as below:

Next select the Script Post-function [ScriptRunner]:

Next click the custom script post-function:

Add the below curl command and click update:

def cmd = "curl -u devsat:zippyops http://192.168.5.118:8080/plugins/servlet/rundeck?issueKey=${issue.key}"

def proc = cmd.execute()

Next, create the custom fields, go to the settings icon on the right side of the screen, and select Issues:

Select the custom fields option under the Fields section as below:

Select the add custom field to add the custom field required for your project:

Click on standard and select the field type to enter your custom field data:

Next, click on the settings icon in the right corner of your screen and click on manage apps, and select the configuration under the Approvals section as below:

Next click on the add on the right side for adding your project for approvals:

After adding the project, it will be listed as below, next click on the configure option for your project:

Add the administrator name, group, and roles for approval:

Next, create an issue by selecting the project from the projects tab that has been created as below:

Rundeck Settings

Configure the rundeck settings from the manage apps page from the Jira dashboard as below:

Mention the “Jiraprojectkey:Rundeckprojectname|rundeckjobname” as below:

Eg: RUN:sqlquery|jirasqlquery

Install the atlassian-cli-9.0.0-distribution.zip from the below link and save in the path  

cd /var/lib/rundeck 

https://bobswift.atlassian.net/wiki/spaces/ACLI/pages/991494189/Downloads+-+Version+9.x

[root@rundeck]# ls

atlassian-cli-9.0.0  bootstrap  cli  com  data  index.html  lib  libext  logs  main.sh  META-INF  projects  reindexdb.sh  repository  var  work

[root@rundeck lib]# pwd

/var/lib/rundeck/atlassian-cli-9.0.0/lib

[root@rundeck lib]# ls

acli-9.0.0.jar

Save the belowscript file test.sh in the path /var/lib/rundeck/projects/sqlquery/demo1.sh

#!/bin/bash

issue=RUN-12

status=$(java -jar /var/lib/rundeck/atlassian-cli-9.0.0/lib/acli-9.0.0.jar --server http://192.168.5.118:8080 --user "tamilselvan" --password "zippyops" --action getFieldValue --issue "$issue" --field "Status" | tail -1)

mail=$(java -jar /var/lib/rundeck/atlassian-cli-9.0.0/lib/acli-9.0.0.jar --server http://192.168.5.118:8080 --user "tamilselvan" --password "zippyops" --action getFieldValue --issue "$issue" --field "Mail_Id" | tail -1)

Database=$(java -jar /var/lib/rundeck/atlassian-cli-9.0.0/lib/acli-9.0.0.jar --server http://192.168.5.118:8080 --user "tamilselvan" --password "zippyops" --action getFieldValue --issue "$issue" --field "Database" | tail -1)

filename=$(java -jar /var/lib/rundeck/atlassian-cli-9.0.0/lib/acli-9.0.0.jar --server http://192.168.5.118:8080 --user "tamilselvan" --password "zippyops" --action getFieldValue --issue "$issue" --field "filename" | tail -1)

#echo status=$status

#echo mail=$mail

#echo filename=$filename

Result=`mysql -h 192.168.4.115 -u admin -p'Jaya@1989' --skip-column-names -e "show databases like '$Database'"`

if [ $status == Approved ]; then

    if [ "$Result" == "$Database" ]; then

         java -jar /var/lib/rundeck/atlassian-cli-9.0.0/lib/acli-9.0.0.jar --server http://192.168.5.118:8080 --user "tamilselvan" --password "zippyops" --action getAttachment --issue "$issue" --file "/var/lib/rundeck/projects/sqlquery/$filename"

         file=$(echo $?)

         if [ $file == 0 ]; then

           mysql -h 192.168.4.115 -u admin -p'Jaya@1989' -D $Database < /var/lib/rundeck/projects/sqlquery/$filename >> /var/lib/rundeck/projects/sqlquery/output.txt

           var=$(echo $?)

           if [ $var == 0 ]; then

                echo "The output of the script attached in the jira issue $issue" | mail -s "Approved" -a /var/lib/rundeck/projects/sqlquery/output.txt $mail

           else

              echo "The attached file $filename is not vaild hence query not executed" | mail -s "Error" $mail

           fi

         else

            echo "The attached file $filename and name in the field is not same " | mail -s "filename Error" $mail

         fi

    else

       echo "The $Database not exits" | mail -s "Query has not be executed" $mail

    fi

else

    if [ $status == Rejected ]; then

      echo "The issue you have created is rejected $issue" | mail -s "Rejected" $mail

    fi

fi

  rm -rf /var/lib/rundeck/projects/sqlquery/output.txt

  java -jar /var/lib/rundeck/atlassian-cli-9.0.0/lib/acli-9.0.0.jar --server http://192.168.5.118:8080 --user "tamilselvan" --password "zippyops" --action transitionIssue --issue "$issue" --transition "Done"

Create a job in Rundeck by clicking the project and Add the key value in the Rundeck job for calling the custom field created in the Jira issue.

Select the workflow tab and add the workflow to run the job:

Check the status of the job execution in the activity section:

Relevant Blogs:

Rundeck with Nagios 

Rundeck - Adding nodes 

Rundeck - run task 

Rundeck - Installation and configuraion