| Create an "automation" user Div |
---|
| Estimated Time: 2 min |
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:
- From the Cog menu (Image Added) , choose User management.
- Click the Create User button.
- In the Create New User screen, set these values as follows. Your screen will look similar to the one shown on the right.
- Username =
automation - Password = <any value>
- Confirm = <same value entered for Password>
- Full Name =
Automation User - Email = <any email address>
- Send Notification Email = deselected
- Click Create to create the automation user account. The full details of that new user account will be displayed.
- From the Actions menu, choose Edit Groups.
- In the Type to start searching field of the Edit User Groups screen, start typing "jira-" and select the jira-administrators group.
- Start typing "jira-" again and select the jira-developers group.
- Click the Join selected groups button to make the automation user a member of those groups.
| Example Create User screen: Example Edit User Groups screen:
|
| | Create a JIRA project to receive the external data Div |
---|
| Estimated Time: 2 min |
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: - From the Projects menu, choose Create Project.
- In the Select Project Type screen, select Simple Issue Tracking and click Next.
- The second screen shows the Issue Types that will be used (Task and Sub-task). Click Submit.
- The third screen prompts you to supply a Name and Key for your new JIRA project.
- Use Onboarding as the Name.
- Use OB as the Key.
- Click Submit to create the project, with you set as its Project Lead.
- 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,
- Click the Cog menu () and choose Issues.
- In the left navbar, click Screens.
- Click the Configure link next to the OB: Simple Issue Tracking Create Issue Screen screen.
- On the Configure screen, start typing "Reporter" into the field at the bottom of the screen.
- Select that field to add it to the screen.
- 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.
- Click Cancel to close the Create Issue screen without creating an issue.
Info |
---|
Note that for JIRA 7, the "Simple Issue Tracking" Project Type is no longer available by default. If you are running Atlassian's latest release, for JIRA Core we'd recommend selecting the "Task management" Project Type instead (or selecting "Basic software development" for JIRA Software). The remaining steps should work the same way. |
| Create Issue screen:
Expand |
---|
title | View all screens for the Create Project Wizard... |
---|
| First screen:
Second screen:
Third screen:
|
Expand |
---|
title | View the Configure Screen screen... |
---|
|
|
|
| | Create a directory in which to save the CSV file Div |
---|
| Estimated Time: 2 min |
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: - Navigate to where you want to create the directory.
- On a Mac, this will likely be the /usr/local folder.
- On Windows, this will likely be your Documents or My Documents folder.
- Create a new directory called CLIOutput.
- Set the permissions so that anyone can write to it, including the mysql command line program you'll be using in the next step.
- Example commands for both Mac OS X/Linux and Windows are shown to the right. More information on the commands can be found here.
| Example commands for Mac OS X and Linux: If prompted to supply a password, supply the password associated with your user account (which typically is an Administrator account)
Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| cd /usr/local
sudo mkdir CLIOutput
sudo chmod 777 CLIOutput |
Example commands for Windows: Substitute your username wherever USERNAME appears below. Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| 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 Div |
---|
| Estimated Time: 2-3 min |
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.
Info |
---|
title | If you already have a CSV file to use |
---|
| You're welcome to use an existing CSV if you have one, however note that it must conform to particular requirements, namely: - Its first row should contain the names of the columns on subsequent rows. The first field (action) is mandatory as this indicates what CLI action should be performed. For this recipe, the action should be
createIssue . - Each subsequent row needs to contain the action field, followed by each field that is required. You can additional fields for fields that the JIRA project considers as optional.
|
To create a new CSV file, you can run a SQL query against your Examplegear database you created during the Preparation steps. - 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.
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 |
---|
| 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. |
|
|
- The import.csv file should now exist with the content shown to the right.
- At the prompt, execute the second command shown on the right to exit the mysql command line program.
| First command, to go into the mysql command line program: Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| mysql -u root mysql |
SQL Query: Code Block |
---|
language | sql |
---|
theme | DJango |
---|
linenumbers | true |
---|
| 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: Code Block |
---|
language | text |
---|
theme | DJango |
---|
title | import.csv file |
---|
linenumbers | true |
---|
| "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: Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| exit |
|
| | Create JIRA CLI command Div |
---|
| Estimated Time: 1 min |
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.
| Example command: Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| jira --action runFromCsv --file "/usr/local/CLIOutput/import.csv" --continue --common "--project Onboarding --type Task --reporter automation" |
|
| | Execute JIRA CLI command Div |
---|
| Estimated Time: 2 min |
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. | Example output: Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| Run: --project Onboarding --type Task --reporter automation --summary "New Hire - Jose Joaquin Munez" --action "createIssue" --description "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."
Issue OB-1 created with id 10001. URL: http://jira.examplegear.com/browse/OB-1
Run: --project Onboarding --type Task --reporter automation --summary "New Hire - Adele Denise Angers" --action "createIssue" --description "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."
Issue OB-2 created with id 10002. URL: http://jira.examplegear.com/browse/OB-2
Run completed successfully. 2 actions were successful from file: /usr/local/CLIOutput/import.csv |
Expand |
---|
title | View the issues created by JIRA CLI... |
---|
|
|
|
| | | Original command used to import from a CSV file: Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| 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): Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| 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): Code Block |
---|
language | text |
---|
theme | DJango |
---|
linenumbers | true |
---|
| 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" |
|
|