Jira Extraction API v2 (Tableau Edition)
- Atanas Mihalev
- Amy Biasella (Deactivated)
- Rafael Sanchez (Deactivated)
Motivation
JQL is the standard query language Atlassian supports for accessing JIRA issues. Here are some of important features of JQL
- there is broad and active community where people exchange ideas and tricks.
- it's portable across JIRA versions
- it's portable across underneath databases (Oracle, MySQL, etc.)
- average JIRA users are used to it
- it's fairly simple and very powerful
Development process
To get data from Jira into Tableau, users need:
To know which Jira to pull data from - LabopsJIRA, GPInfoJIRA, broadinstitute.atlassian.net (Cloud JIRA)
A JQL query in plain text (project = BQMS) or a known filter ID (filter=22038) that contains all the issues to be reported on.
A list of fields to report on (e.g. assignee, status, summary, resolution, any custom fields). The CodeAssistant can generate a copy/pasteable list of fields for you if you click "Show all fields from [labopsjira/gpinfojira/broadinstitute.atlassian.net]" near the top of the page.
Any optional transformations done to these fields (e.g. if you want a comma separated list to be exploded out on new lines)
Directions:
Paste the JQL into the "Plain jqlQuery" fieldin the CodeAssistant. Your browser will automatically replace spaces (and other special characters) with proper web-friendly sequences (for example space -> %20)
Enter a comma separated list of Fields and their transformations. e.g key, Summary, Assignee
Select a number of records to preview to make sure your query works. The CodeAssistant will automatically insert code friendly field names next to the field names you provided.
If the results look like what you want, use the code assistant's output of your choice. (e.g. use the "Normalized URL" for a one time downloadable TDE, or use the value in the Double encoded Url to set up a scheduled batch file to continuously update a TDE extract, or use the WebDataConnector SQL which can provide live data - Oracle 12 only.)
If you've created a TDE, add &agentName=XYZ to the end of the URL where XYZ is the agent name that will show up in the ETL dashboard.
Set up automating this TDE as mentioned below in the "Schedule a curl extract script" section.
Automation (curl)
Use curl scripts to automatically generate these extracts and deposit them on your Tableau server.
curl.exe "http://analytics.broadinstitute.org:8090/api/v2/JqlTableauExtractService?domain=labopsjira&jqlQuery=key%3DCR-3597&fields=key(key),customfield_10169(Instrument):concat(+%3E+)" -o c:\temp\test2.tde
Curl Installation for Windows (this needs to be done only once and has already been installed on Tableau-TS)
- Login to the server designated for curl extract scripts.
- Download (right click -> Save As) curl736.exe into folder you have write access to. (Curl for all kinds of OS can be found here http://curl.haxx.se/download.html but some of newer versions may not be as polished)
Schedule a curl extract script
- Run "Task Scheduler"
- Choose "Create Task" and follow the Wizard. Name your schedule
- Provide a "trigger" meeting your needs.
- In Action tab, in "Program/script" field paste the full path to your curl executable (for example "C:\Program Files (x86)\Utils\curl.exe"). In "Arguments" provide entire URL wrapped in double quotes.
- Run the new schedule manually and check if file has been properly generated and deposited.
For geeks: you can manage all your scheduled tasks via command line as well http://www.howtogeek.com/51236/how-to-create-modify-and-delete-scheduled-tasks-from-the-command-line/
- download TableauServerTabcmd-64bit-10-3-6.exe (51 MB) or appropriate newer version and install on your local Windows PC
Example CURL+CLI batch file for Windows
NOTE: it isn't always possible to edit an existing batch file. You can usually rename it, though, so you can edit a duplicate and then change the names to archive the old one.
:: Windows batch file for JQL to TDE conversions @echo off set TDE=TableauJQL_PFDemo set TDEPATH=C:\Local\%TDE%.tde :: call JQL, convert to TDE echo Using curl to call JQL Extract Service... :: Updated using code assistant 20180108 "C:\Program Files (x86)\Utils\curl.exe" -o %TDEPATH% "http://analytics:8090/api/v2/JqlTableauExtractService?domain=broadinstitute.atlassian.net&jqlQuery=project+%%3D+%%22POAMs+and+Findings%%22&fields=summary(Summary),reporter(Reporter),customfield_13911(Recommendation):concat" if ERRORLEVEL 1 (echo Error with JqlTableauExtractService, aborting. & goto :eof) :: upload TDE to Tableau Server echo. & echo Uploading to Tableau... ::older 9.3 version tab command ::set TABCMD="C:\tabcmd\Command Line Utility\tabcmd" set TABCMD="C:\Program Files\Tableau\Tableau Server\2018.1\extras\Command Line Utility\tabcmd" set TableauServer=https://tableau.broadinstitute.org set TableauUser=charles\tableau-reports ::%TABCMD% version echo. & echo Logging into Tableau Server... %TABCMD% login -s %TableauServer% -u %TableauUser% --password-file ts_password.txt echo. & echo Publishing TDE... %TABCMD% publish %TDEPATH% -n %TDE% --overwrite --project "GP Operations" echo. & echo Logging out of Tableau Server... %TABCMD% logout :eof echo. & echo Script finished.
Monitoring
You can see how your agent performed in Etl Dashboard service (you name your etl by adding an "agentName=???" parameter in the URL). In case of failure an email is automatically sent to reportingerrors@broadinstitute.org, etc.
Custom fields
JIRA keeps record of custom fields by their internal names which look like this "customfield_12345". And unfortunately this is the column name which JqlTableauExtractService expects you to provide. Luckily the CodeAssistant can do all these tedious lookups, Url-encoding (and much more) for you.
Other interesting services:
- http://analytics:8090/peekattable/SEQBLDR.jiradwh.CF_CATALOG?rows_limit=9999
- http://analytics:8090/peekattable/GPINFO.jiradwh.CF_CATALOG?rows_limit=9999
- Online URL Encoder http://www.url-encode-decode.com/
- Full catalog of all Custom Fields e.g. http://analytics:8090/peekattable/SEQBLDR.jiradwh.CF_CATALOG?rows_limit=9999
- Full catalog of ALL Fields e.g. http://analytics:8090/api/v2/fields_catalog?domain=labopsjira
Security considerations
This service utilize "analytics" user so anything not visible to "analytics" would not be visible to you either.
JqlTableauExtractService VS JIRA datamarts VS JiraWebConnector
| Features | JqlTableauExtractService | JIRA datamarts | JiraWebConnector |
|---|---|---|---|
| realtime | No (data as old as your last extract) | Yes | Yes |
| skills necessary | JQL, curling | SQL and familiarity with JIRA datamarts | JQL |
| Atlassian support | Yes | No | Yes |
| Portability (across DBs and JIRA versions) | Excellent | No (may require rewrites as internal structures change) | Excellent |
| Avg cost per feature | Low (parsing XML is relatively easy) | High (working around Jira's DB schema might be time consuming) | Low (parsing XML is relatively easy) |
| Is JIRA UI requred to be running | Yes | No (only JIRA DB is needed) | Yes |
| Advanced features | |||
| Transitions | No | Yes | No |
| Issue Ancestry | No | Yes | No |
| CLOBs handling and parsing | Somewhat | Yes | Somewhat |
| Overall versatility | Reasonable (primarily focused on list of issues) | Very good (can query many kinds of JIRA data like transitions) | Reasonable (primarily focused on list of issues) |
| App Lock | works only with Tableau | any app can use them via SQL | No lock - any app can consume it via DB-connection |
| Security | respects whatever permissions are granted to "analytics" user | read any data from any project, no limitations | respects whatever permissions are granted to "analytics" user |
Examples
Cascading field → one delimited string
Extract CascadingSelect Custom Field "Instrument" and collapse the 2 levels into a " > " separated String value
Expose what's the actual delimiter in a multi-value string
\x0A refers to LF-character, which is the same as \n (if you dare to check more RegEx magic try this)
Samples, Dates and Doubles
Extract samples, Dates and Doubles
- Convert a custom field "Number of Samples" into a Double
- Convert "created" into a Date
- Parse sample names (separated by <br/>\x0A) and convert them back into a ';' delimited string
Explode a delimited text field
E.g. Extract Samples and leave them "exploded" so that you can JOIN with other LIMS data sources
Fieldsinput: key(key),summary(Summary),reporter(Reporter),customfield_12662(Sample IDs):concat:split(\n<br/>\n):stripHtml
New development: more natural way to extract samples would be to just say "grab everything starting with SM- followed by 5 alphanumerics". This is what findAllMatch transformation is doing: findAllMatch(SM-(\p{Alnum}){5}). Eg, enter the following in fields to explode the sample IDs of BQMS tickets.
Note the regex needs <CM> to encode a comma (to return either 4 or 5 length alphanumeric strings).
key(key),summary(Summary),reporter(Reporter),customfield_12662(Sample IDs):concat:findAllMatch(SM-(\p{Alnum}){4<CM>5})
Name or Rename a field
Enforce a user-defined fieldName (so that column names are provided even if no data is available)
Multi-value fields (labels, etc.)
Extract Labels
Labels → Multiple columns (Bits, boolean)
Extract Labels and turn them into Bits (Boolean columns)
Labels → Multiple columns (Bits, integer)
Extract Labels, turn them into Bits and then into Ints
Links: Explode & return all columns
Extract Links and leave them "exploded"
Links: Explode & return selected column
Extract Links, then select 5th column (linked issue name) and leave them "exploded"
Links: selected column → String
Extract Links, then select 1st column (linked issue narrative) and collapse them into a String
HTML → rich text (w/HTML)
Extract rich text in HTML custom field
HTML → stripped rich text
Extract rich text in HTML custom field but strip HTML tags
Extract to Boolean
MultiSelect field → CSV field
Extract MultiSelect custom field, strip Html tags and collapse it back into a CSV value (to prevent a data 'explosion')
View unextracted JIRA XML
Look at raw XML response from JIRA before any extraction is performed by Tiger
Explode subtasks
Extract Subtasks and leave them "exploded" as List so that you can JOIN with other datasources
Multi-value field → collapsed CSV
Extract Subtasks and collapse them into a CSV
Extract Components and collapse them into a CSV
Explode Comments
Extract Comments (along with author and created) and leave them "exploded"
Collapse comments (CRLF-separated string)
Extract Comments and collapse them into a Carriage-return-line-format separated String
Extract large (>1000) datasets
Extract all LCSETs created in 2016.
Extract properly-structured data from a html-table
Preserve JOINability on sample-name by getting rid of trailing comments
Parse out not only the Sample-name but also capture the trailing comment in a separate column (viva la regex)
Parse sample-names scattered across Text-field
Extract data from Broad's Cloud Jira and transparently consume it as a relational table within the DB
CodeAssistant helps you build the URL by consulting to Jira metadata.
http://analytics:8090/api/v2/codeAssistant
*NOTE: Comma (,) has a special meaning - it separates field-descriptions. If you need a comma in regular expressions (for example in commands like split, findAllMatch) you will have to encode comma with <CM>