Create an "automation" user

In this step, you will create a special user account to be used during automated CLI actions. This will make it clear that an automated process rather than a human created an issue or took some other action on an issue.

Log on to your JIRA instance as a JIRA Administrator and do the following:

1. From the Cog menu () , choose User management.
2. Click the Create User button.
3. In the Create New User screen, set these values as follows. Your screen will look similar to the one shown on the right.
4. Username = automation
5. Password = <any value>
6. Confirm = <same value entered for Password>
7. Full Name = Automation User
8. Email = <any email address>
9. Send Notification Email = deselected
10. Click Create to create the automation user account. The full details of that new user account will be displayed.
11. From the Actions menu, choose Edit Groups.
12. In the Type to start searching field of the Edit User Groups screen, start typing "jira-" and select the jira-administrators group.
13. Start typing "jira-" again and select the jira-developers group.
14. Click the Join selected groups button to make the automation user a member of those groups.

Example Edit User Groups screen:

Create a JIRA project to receive the external data

You are going to create a project to be used to track requests to onboard ExampleGear's new hires into their systems. To do this, log on to your JIRA instance as a JIRA Administrator and do the following:

1. From the Projects menu, choose Create Project.
2. In the Select Project Type screen, select Simple Issue Tracking and click Next.
3. The second screen shows the Issue Types that will be used (Task and Sub-task). Click Submit.
4. The third screen prompts you to supply a Name and Key for your new JIRA project.
   1. Use Onboarding as the Name.
   2. Use OB as the Key.
   3. Click Submit to create the project, with you set as its Project Lead.
5. To ensure that you can set the Reporter to the automation user, you need to modify the project's Create screen to include the Reporter field. To do this,
   1. Click the Cog menu () and choose Issues.
   2. In the left navbar, click Screens.
   3. Click the Configure link next to the OB: Simple Issue Tracking Create Issue Screen screen.
   4. On the Configure screen, start typing "Reporter" into the field at the bottom of the screen.
   5. Select that field to add it to the screen.
6. If you click Create and choose Onboarding as the Project and Task as the Issue Type, you will see a Create Issue screen like the one shown to the right. Only 3 fields (Project, Issue Type, and Summary) are required, as indicated by the red asterisk next to their field labels. There are 2 optional fields: Priority and Description.
   1. Click Cancel to close the Create Issue screen without creating an issue.

Create a directory in which to save the CSV file

In preparation for creating a CSV file, you must create a directory with permissions indicting that MySQL is allowed to write to it.

In your command window, do the following:

1. Navigate to where you want to create the directory.
   
   1. On a Mac, this will likely be the /usr/local folder.
   2. On Windows, this will likely be your Documents or My Documents folder.
2. Create a new directory called CLIOutput.
3. Set the permissions so that anyone can write to it, including the mysql command line program you'll be using in the next step.
4. Example commands for both Mac OS X/Linux and Windows are shown to the right. More information on the commands can be found here.

If prompted to supply a password, supply the password associated with your user account (which typically is an Administrator account)

cd /usr/local
sudo mkdir CLIOutput
sudo chmod 777 CLIOutput

Example commands for Windows:

Substitute your username wherever USERNAME appears below.

c:
cd\
cd users\USERNAME\documents
mkdir CLIOutput
c:\windows\system32\icacls users\USERNAME\documents\CLIOutput /grant "everyone":(OI)(CI)F

Create a CSV file

In this step, you will create a CSV file that will provide the data that the JIRA Command Line Interface (CLI) add-on will use to create JIRA tickets in your new Onboarding project.

To create a new CSV file, you can run a SQL query against your Examplegear database you created during the Preparation steps.

1. In your command window, type the first command shown to the right to go into the mysql command line program. The prompt will change to "mysql" to reflect all commands executed from this point on should be MySQL commands.

2. At the prompt, paste in the SQL query shown to the right and press Enter to execute them.

   Expand
   title Learn more about how this SQL query works...
   Panel The first SELECT statement builds a row that provides the column headings for the CSV file. The second SELECT statement retrieves information on employees that were hired recently: The first field (action) on the SELECT statement is an action field that indicates the JIRA CLI action to perform. The second and third fields (summary and description) represent the fields that are required when an issue is created in the Onboarding project. Both of these are built by concatenating hard-coded strings with data from the Employees table. Although you would typically use a WHERE clause such as WHERE DATEDIFF(NOW(),E.HireDate) <= 7, we are suggesting a different one just to ensure you get the correct data no matter when you complete this recipe. The last 4 lines of the SQL Query indicate to send the results from both SELECT statements to a file named /usr/local/CLIOutput/import.csv in comma-delimited format. You may need to adjust the path to reflect the directory you created in Step 3. Info title Production Usage In a Production environment you would need to exercise care to ensure that each execution of the SQL query to retrieve data from the external system produced the correct information, without any duplicate or missing information. We've omitted that here to keep the recipe simple.

   
   

3. The import.csv file should now exist with the content shown to the right.
4. At the prompt, execute the second command shown on the right to exit the mysql command line program.

mysql -u root mysql 

SQL Query:

USE examplegeardb;
SELECT 'action','summary','description'
UNION
SELECT 
    'createIssue' AS action,
    CONCAT('New Hire - ', E.FirstName, ' ', E.MiddleName, ' ', E.LastName) AS summary,
    CONCAT('This employee joined ExampleGear on ', DATE_FORMAT(E.HireDate, '%Y-%m-%d') , '. ', E.FirstName, ' will be in the ', D.DeptName, ' department, reporting to ', MGR.FirstName, ' ', MGR.LastName, ' as a ', E.JobTitle, '. Please onboard this new hire into the appropriate systems.') AS description    
FROM employees E
INNER JOIN employees as MGR ON E.managerID = MGR.EmployeeID
INNER JOIN departments AS D ON E.DeptID = D.DeptID
WHERE E.HireDate BETWEEN '2015-05-10' AND'2015-05-16'
INTO OUTFILE '/usr/local/CLIOutput/import.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

Example CSV file:

"action","summary","description"
"createIssue","New Hire - Jose Joaquin Munez","This employee joined ExampleGear on 2015-05-12. Jose will be in the Marketing department, reporting to Robert Brennan as a Manager. Please onboard this new hire into the appropriate systems."
"createIssue","New Hire - Adele Denise Angers","This employee joined ExampleGear on 2015-05-10. Adele will be in the Marketing department, reporting to Jose Munez as a Marketing Specialist. Please onboard this new hire into the appropriate systems."

Second command, to exit the mysql command line program:

exit

Create JIRA CLI command

Use the command shown on the right to invoke the JIRA CLI to read the import.csv file and use its data to create new JIRA issues in the Onboarding project.

• jira indicates to invoke the JIRA CLI client.
• The runFromCSV action indicates to read the data in the specified CSV file and, for each row, run the action specified in that row's data. It augments the CSV data with the parameters supplied in the --common parameter, which extends up to the final double quote (") character.
• The other parameters are used as follows:
  
  • --file indicates where to locate the CSV file by specifying the fully qualified path to that file. This should match what was specified in the INTO OUTFILE clause of the second SELECT statement in Step 4.
  • --continue indicates to continue processing the CSV file even if an error is encountered.
  • --project identifies the name of the project in which to create the issues. This can be the project name, key or id.
  • --type defines the name of the Issue Type to be used on each Create Issue action. This can be the issue type's name or id.
  • --reporter identifies the user account to set as the Reporter on each Create Issue action. This is the username of the Automation User user account.

Execute JIRA CLI command

In this step, you'll actually execute the JIRA CLI command to create new issues in the Onboarding project per the import.csv file.

In your command window, type the command from the previous step and press Enter.

The command will run, showing the result of processing the CSV data.

If you like, you can open the URL shown in the output in your web browser. You should see screens similar to what is shown to the right.

Bonus Tip

Div
class time
Status title Optional Estimated Time: 2-3 min

If you will be importing data from an external system with a database schema that is documented and easy to navigate, you can save a bit of time by using the runFromSql action rather than the runFromCsv action, and thus skipping Step 4 above.

With the runFromSql action, you can provide the SQL query in-line or in a file.

All of our Atlassian CLI Clients ship with a PostgreSQL jdbc driver. For other DBMS types, you will need to manually upload the database driver to the lib/jdbc directory within the CLI client installation directory. The MySQL jdbc driver can be downloaded from here.

The syntax is a bit different based on whether you use the --host parameter or the --url parameter. In the table below, necessary parameters are shown with a and unnecessary parameters are shown with a .

Parameter	--host	--url
host
driver
database
port
dbUser
dbPassword

Both variations of the commands to execute the runFromSql action are shown to the right.

jira --action runFromCsv --file "/usr/local/CLIOutput/import.csv" --continue --common "--project Onboarding --type Task --reporter automation" 

Command used to import from a SQL query (using --host parameter):

jira --action runFromSql --sql "SELECT 'action','summary','description' UNION SELECT 'createIssue' AS action, CONCAT('New Hire - ', E.FirstName, ' ', E.MiddleName, ' ', E.LastName) AS summary, CONCAT('This employee joined ExampleGear on ', DATE_FORMAT(E.HireDate, '%Y-%m-%d') , '. ', E.FirstName, ' will be in the ', D.DeptName, ' department, reporting to ', MGR.FirstName, ' ', MGR.LastName, ' as a ', E.JobTitle, '. Please onboard this new hire into the appropriate systems.') AS description     FROM employees E INNER JOIN employees as MGR ON E.managerID = MGR.EmployeeID INNER JOIN departments AS D ON E.DeptID = D.DeptID WHERE E.HireDate BETWEEN '2015-05-10' AND'2015-05-16'" --host "localhost" --driver "mysql" --database "examplegeardb" --port "3306" --dbUser "dbuser" --dbPassword "mypassword" --continue --common "--project Onboarding --type Task --reporter automation" 

Command used to import from a SQL query (using --url parameter):

jira --action runFromSql --sql "SELECT 'action','summary','description' UNION SELECT 'createIssue' AS action, CONCAT('New Hire - ', E.FirstName, ' ', E.MiddleName, ' ', E.LastName) AS summary, CONCAT('This employee joined ExampleGear on ', DATE_FORMAT(E.HireDate, '%Y-%m-%d') , '. ', E.FirstName, ' will be in the ', D.DeptName, ' department, reporting to ', MGR.FirstName, ' ', MGR.LastName, ' as a ', E.JobTitle, '. Please onboard this new hire into the appropriate systems.') AS description     FROM employees E INNER JOIN employees as MGR ON E.managerID = MGR.EmployeeID INNER JOIN departments AS D ON E.DeptID = D.DeptID WHERE E.HireDate BETWEEN '2015-05-10' AND'2015-05-16'" --url "jdbc:mysql://localhost/examplegeardb" --driver "mysql" --port "3306" --dbUser "dbuser" --dbPassword "mypassword" --continue --common "--project Onboarding --type Task --reporter automation"