Export API
The Export API makes it possible to download update records from R-Service in batches. The behavior of this API is similar to the Export functionality as exposed via the GUI.
Exporting records in batches through the API is the preferred way of building (one-way) integrations with, for example, a corporate data warehouse. It allows for easily managed integrations and performs better compared to sending separate GET REST API requests for each record.
This page is organized as follows:
- Generating an export file
- Initiating an export
- Polling for the export progress
- Downloading an export file
- Viewing the export log
Generating an export file
Data can be exported from R-Service in UTF-8 encoded comma-separated values (CSV) files, or as Microsoft Excel (.xlsx) files.
Each record in an export file is defined on a separate line. The first line of text is reserved for the column headers. Each column header references a field of the record type.
Warning: The names of the column headers do not change over time. However, no guarantee is given about the order of the columns, and new columns may be added by R-Service at any time. When processing an export file via automation, ensure to architect your solution such that it can deal with changing positions of a column, or the addition of new columns.
Security Consideration
To avoid spreadsheet injection attacks, the following characters at the start
of an exported field value are prefixed by the tab character:
=
, +
, -
or @
.
This prevents the execution of commands when an export file is opened in a
spreadsheet application such as Microsoft Excel and Google Sheets.
Relations to different accounts
When a trust relation has been established between two R-Service accounts, it is
possible for certain records of these accounts to be linked together. For
example, Widget Data Center may be providing their Expense Reporting service to
the Widget North America organization. After these organizations have
established a trust relation between their accounts, Widget Data Center is able
to register a Service Level Agreement and relate an Organization record of
Widget North America to it as its customer. If the id
of Widget North
America’s account is “wna”, then an export of Widget Data Center’s SLAs will
show the name of the customer’s Organization record followed by the @ symbol and
the id
of the customer’s account.
This will then look as follows:
Widget North America @wna
Initiating an export
The example below shows how an export of the Sites and Team records can be
extracted from an account which id
is “wdc”, and how this export can be
limited to records that were created or updated after December 31, 2012.
$ curl -H "Authorization: Bearer <oauth-token>" \ -H "X-4me-Account: wdc" \ -X POST \ -F "type=sites,teams" -F "from=20121231" \ -F "export_format=csv" -F "line_separator=lf" \ "https://api.r-service.tech/v1/export"
The response from the Export API is a token that acts as the unique identifier for the export job. This token can be used to retrieve information about the progress of the export job.
Status: 200 OK
{
"token": "68ef5ef0f64c012f2...e4598cdb41e68"
}
In case the from
parameter is provided and there are no created or updated
records since that time no export will be scheduled. The following response will
be provided:
Status: 204 No Content
It is important to note that the API user needs to have the Account
Administrator role to be able to export files using the
API. In the example above, the API user specifically needs to have the Account
Administrator role of the account which id
is “wdc”. The Authentication
section describes how the
api-token
of the API user can be found.
In the example above it would not be necessary to specify -H "X-4me-Account:
wdc"
provided that the Person record of the API user belongs to the account
which id
is “wdc”.
Parameters
The following parameters are supported:
- type
- Required enum — The type of the file to export. Valid values are:
-
- affected_slas
- agile_board_columns
- agile_boards
- app_offering_automation_rules
- app_offering_scopes
- app_offerings
- calendars
- ci_relations
- cis
- contracts
- custom_collection_elements
- custom_collections
- custom_views
- effort_classes
- email_templates
- flsas
- generic_ci_automation_rules
- generic_project_task_automation_rules
- generic_request_automation_rules
- generic_risk_automation_rules
- generic_task_automation_rules
- generic_workflow_automation_rules
- holidays
- invoices
- knowledge_articles
- missing_translations
- notes
- organizations
- organizations_contact_details
- organizations_time_allocations
- out_of_office_periods
- outdated_translations
- pdf_designs
- people
- people_contact_details
- people_roles
- problems
- product_backlog_items
- product_backlogs
- product_categories
- products
- project_categories
- project_risk_levels
- project_task_assignments
- project_task_template_assignments
- project_task_template_automation_rules
- project_task_templates
- project_tasks
- project_template_automation_rules
- project_templates
- projects
- releases
- request_template_automation_rules
- request_templates
- requests
- risk_severities
- risks
- satisfactions
- scim_group_automation_rules
- scim_user_automation_rules
- scrum_workspaces
- service_categories
- service_instances
- service_offering_activity_rates
- service_offering_effort_class_rates
- service_offering_ssr_rates
- service_offerings
- services
- shop_articles
- shop_order_lines
- short_urls
- sites
- skill_pools
- sla_coverage_groups
- sla_financial_details
- sla_financial_details_activities
- sla_financial_details_charges
- sla_notification_rules
- sla_notification_schemes
- slas
- sprint_backlog_items
- sprints
- standard_service_requests
- survey_answers
- survey_questions
- survey_responses
- surveys
- system_logs
- task_approvals
- task_template_approvals
- task_template_automation_rules
- task_templates
- tasks
- teams
- time_allocations
- time_entries
- timesheet_settings
- translations
- trusted_organizations
- ui_extensions
- workflow_template_automation_rules
- workflow_templates
- workflows
- shop_article_categories
- from
- Optional date — The date after which a record needs to have been created or updated in order to be included in the export.
- export_format
- Optional enum — The format of the file to export. Valid values are:
-
- csv
- xlsx
- line_separator
- Optional enum — The format of the file to export. Valid values are:
-
- lf
- crlf
Examples
This defines the start of the day on May 24, 2013 in the timezone of the API
user: from=20130524
This also defines the start of the day on May 24, 2013 in the timezone of the
API user: from=20130524T00:00:00
This is equal to 11pm on May 01, 2013 in Sydney (i.e. in the AEST time zone):
from=20130501T23:00:00-10:00
This is the start of the day on May 01, 2013 in UTC (coordinated universal time):
from=20130501T00:00:00Z
The export format as csv and the line separator as carriage return and line feed (CR and LF or 0x0D and 0x0A) control character:
- Export format:
export_format=csv
- Line separator:
line_separator=crlf
Rate limit
Due to performance considerations, some limitations apply when exporting data.
For partial exports the from
parameter has a maximum of 2 months backwards, with unlimited frequency.
Full exports can be performed multiple times per day unless the full export contains more than 10,000 records. In which case the full export for that record type can only be performed once per day.
Polling for the export progress
The export process runs as a background job. The token received from the export response can be used to retrieve information on the progress.
Five minutes after the job is completed, the progress information is not available anymore through the API.
Example:
$ curl -H "Authorization: Bearer <oauth-token>" \ -H "X-4me-Account: wdc" \ -X GET \ https://api.r-service.tech/v1/export/68ef5ef0f64c012f2...e4598cdb41e68
Depending on the state of the export job, one of the following responses is returned:
Queued
Status: 200 OK
{
"state": "queued"
}
The job is scheduled for execution.
Processing
Status: 200 OK
{
"state": "processing",
"type": "sites",
"line": 32
}
The job is being processed and the line
attribute shows how much progress has
been made.
Done
Status: 200 OK
{
"state": "done",
"url": "https://...export.zip?AWSAccessKeyId...",
"expires_at": "2016-06-28T02:56:11-06:00"
}
The job is complete and the response includes meta information about the export.
The url
attribute will contain a link to the export file in case a single type
was exported. When multiple type
s are exported at once, a ZIP file will be
created with a separate export file for each type
. The expires_at
attribute
shows when the url
will expire. By default this is 2 days after the export job
completed.
Warning: When exporting to XLSX format, type
s, for which more than 10.000
records are exported, are split into multiple files.
Warning: The name of the export file for each type
contains the type
and has the appropriate extension (.csv
or .xlsx
), for example
20211108-sites-123.csv
or 123-sites.csv
.
However, no other guarantee about the name of the export file is given.
Failed
Status: 200 OK
{
"state": "failed",
"rate_limit_exceeded": "requests"
}
The job has failed. Additional information is provided in the response body. One common reason for failure is when the Rate limit was exceeded.
More information on the failed job can still be found in the Export Log.
Not Found
Status: 404 Not Found
The job was completed more than 5 minutes ago, so no progress information is available anymore. Information on the completed job can still be found in the Export Log.
Downloading an export file
It was already mentioned above that, when the Export API is used to start an export, the immediate response from the Export API is a token that acts as the unique identifier for the export job.
Once the export has been completed, an email is sent to the API user. This email contains the hyperlink with which the export file can be downloaded. The header of this email contains the token that is the unique reference to the export job. This token makes it possible for automated scripts to find the correct email.
For example:
X-Mailer: R-Service Mailer
X-4me-Export: 68ef5ef0f64c012f2...e4598cdb41e68
X-4me-ExportID: 12345
Auto-Submitted: auto-generated
Viewing the export log
Each export job that is complete is entered in the System Logs of the Settings console. Log into R-Service as an Account Administrator and go to Settings => System Logs and select the Export Log view to see them all.