How to Export Data from BMC Helix ITSM: API, SQL & Tools

How to Export Data from BMC Helix ITSM: API, SQL & Tools

Exporting data from BMC Helix ITSM requires choosing between three practical paths: the AR System REST API for programmatic extraction, direct SQL queries against the underlying database, or BMC's own tooling (Helix Data Manager, Smart Reporting). Each method has real constraints around pagination limits, authentication token lifetimes, and server-side query caps that will cause silent failures mid-export if you don't plan for them.

This guide covers each extraction method in detail, documents the API limits and edge cases specific to BMC Helix ITSM, and includes a failure mode reference for the problems most likely to break bulk exports at scale.

The three ways to get data out of BMC Helix ITSM

BMC Helix ITSM (formerly BMC Remedy) stores data in AR System forms backed by a relational database (Microsoft SQL Server or Oracle). That architecture gives you three extraction paths:

1. AR System REST API — Programmatic access to any form's entries via HTTP. Works for both SaaS and on-premises. Best for targeted, repeatable exports and cross-platform integration workflows.
2. Direct SQL queries — Querying the underlying database tables directly. On-premises only. Best for bulk foundation data exports and one-time large-scale extractions.
3. BMC tooling — Helix Data Manager for ITSM-to-ITSM migrations, Smart Reporting for ad-hoc data pulls, and Atrium Integrator for ETL-style jobs.

Foundation data refers to the reference records that underpin the ITSM process data: companies, sites, people, support groups, and categorizations. These are typically the first records to migrate because process data (incidents, changes) depends on them for foreign key integrity.

The right choice depends on whether you have database access (SaaS tenants don't), whether you need a repeatable pipeline, and how much data you're moving.

How does the AR System REST API work for data export?

BMC recommends using the REST APIs to integrate third-party applications with BMC Helix ITSM. The supported HTTP methods are GET, PUT, POST, and DELETE. For data export, you'll primarily use GET requests against form entry endpoints.

The base endpoint pattern for reading entries is:

GET /api/arsys/v1/entry/{FormName}

URL parameters control filtering, field selection, pagination, and sorting. The core parameters are q (qualification/filter), fields, limit, offset, and sort.

A typical export request looks like this:

GET /api/arsys/v1/entry/HPD:IncidentInterface?q=('Status'="Assigned")&fields=values(Incident Number,Description,Assignee)&limit=200&offset=0&sort=Create Date.asc
Authorization: AR-JWT <your-token>

Key forms you'll target for common ITSM data exports:

For bulk exports, use qualification queries rather than single-entry lookups by Request ID. The assoc parameter can retrieve related entries (e.g., work logs associated with an incident) in a single call rather than requiring a separate query per related form — use it where available to reduce total API call count.

Which fields are always returned vs. require explicit selection: The Entry ID (Request ID) and a small set of system fields are returned by default. All other fields, including custom fields, require explicit inclusion in the fields parameter. When in doubt, test with fields=values(*) to discover available fields, then narrow to only what you need in production exports.

Authentication: JWT tokens and the one-hour cliff

If the credentials are valid, the AR System server generates a JSON Web Token (JWT). A single JWT token is valid for one hour by default. The expiry window is configurable server-side (between 1 minute and 12 hours), but most production instances retain the default. This one-hour default is the single most common cause of failed bulk exports — a script runs fine for 45 minutes, then every subsequent request returns HTTP 401.

To authenticate, POST credentials to the login endpoint:

POST /api/jwt/login
Content-Type: application/x-www-form-urlencoded
 
username=ExportUser&password=<password>

The token comes back as a plain-text string in the response body. Use it in subsequent requests via the Authorization: AR-JWT <token> header.

A single JWT token is valid across multiple AR System servers in the same server group, which matters for high-availability on-premises deployments.

Modern Helix SaaS deployments also support SSO-based authentication using identity provider tokens passed via SAML or OAuth flows configured through Helix Platform. For scripted exports, service account JWT authentication remains the most reliable approach. SSO token lifetimes are governed by your IdP configuration, not the AR System default — verify expiry before building refresh logic.

For multi-tenant SaaS deployments, include the x-ar-provider header to specify the tenant context when your credentials have access to multiple tenants. Omitting this header in a multi-tenant environment can result in requests routing to the wrong tenant's data silently.

By default, access to the REST API is over HTTPS only. HTTP access returns HTTP 403 Forbidden regardless of credential validity.

Practical advice for token management:

• Build token refresh logic into any long-running export script. Re-authenticate every 50 minutes to maintain a buffer before expiry.
• Always release tokens when done by calling POST /api/jwt/logout. Unreleased tokens consume AR System license slots — on installations with limited floating licenses, leaked tokens can block other users.
• Log the token acquisition timestamp so refresh timing is deterministic regardless of script execution speed.

Pagination: offset-based with hard ceilings

Pagination in the AR System REST API is offset-based with no cursor mechanism. You paginate by incrementing the offset parameter with each request:

GET /api/arsys/v1/entry/HPD:IncidentInterface?limit=200&offset=0
GET /api/arsys/v1/entry/HPD:IncidentInterface?limit=200&offset=200
GET /api/arsys/v1/entry/HPD:IncidentInterface?limit=200&offset=400

The three-layer ceiling on returned records: Even if you request limit=5000, the server caps the response at the lowest of three values:

1. Your requested limit parameter value
2. The individual user's Search Preferences setting (configured per user in ITSM)
3. The server's Max-Entries-Per-Query setting (BMC recommends no more than 2,000 entries; administrators may set this as low as 300)

If you're getting truncated results without an error, check all three. Silent truncation — where the API returns HTTP 200 with a partial dataset and no indication that records were dropped — is the most dangerous failure mode in bulk exports.

Offset drift during live exports: Because pagination is offset-based rather than cursor-based, records inserted or deleted between paginated requests cause drift. If 10 records are inserted before offset 400, your next request at offset=400 will skip those 10 records. If records are deleted, you'll see duplicates. For live systems, either export during a maintenance window, filter by a stable date range, or sort by an immutable field (Entry ID, Create Date) and accept that very recent records may require a delta pass.

Behavior when offset exceeds total record count: The API returns an empty array with HTTP 200 — not an error. Your termination condition must check len(entries) == 0, not a status code.

Null sort behavior and pagination gaps: Sorting on fields that contain NULL values produces inconsistent ordering across pages. If a field used for sort contains NULLs, records with NULL values may appear at different positions depending on database engine (SQL Server and Oracle sort NULLs differently), causing records to shift between pages during iteration. Use Entry ID or Create Date as sort fields — both are system-populated and never NULL.

What are the API rate limits for BMC Helix ITSM?

Rate limit thresholds are not publicly documented and are configurable per tenant. Unlike Zendesk (which publishes a 700 requests/minute ceiling) or Salesforce (which documents daily API call budgets per edition), BMC does not publish standard rate limits. Your AR System administrator sets these values, and they vary by instance.

Practical observations:

• SaaS tenants have BMC-managed throttling that is not visible or configurable by tenant administrators. Throttling is applied at the connecting application level.
• On-premises installations expose thread count and concurrent user caps in ar.cfg. These are your effective rate ceiling.
• There is no Retry-After header returned on throttled requests. Throttled requests either slow down, return errors, or queue silently — you cannot distinguish throttling from network latency without monitoring response time trends.
• Administrators can apply per-application throttling controls that differ from global instance limits.

Safe defaults for uncharted environments: Start at 2–3 requests per second. Monitor p95 response latency across a 500-request window. If latency trends upward consistently, back off by 50% and re-test. Use narrow fields selections — pulling 5 fields vs. all fields on a 500-record batch can reduce payload size by 80%+, meaningfully reducing server-side processing time even before hitting thread limits.

Qualification syntax quirks that break exports

The AR System query language used in the q parameter is neither SQL nor OData. It uses its own qualification syntax with single-quoted field names:

q=('Status'="Assigned" AND 'Create Date' > "1700000000")

Epoch timestamps, not ISO 8601: Dates are passed as Unix epoch timestamps (seconds since January 1, 1970 UTC). Passing ISO 8601 strings (e.g., "2024-01-01") will return zero results with HTTP 200 — no error, just an empty dataset. Convert all dates to epoch before constructing qualifications.

URL encoding is mandatory: Characters not permitted in URLs (spaces, quotes, parentheses) must be percent-encoded. Improperly encoded qualifications are a primary cause of empty result sets that appear successful. Encode before sending, not after.

Field names are case-sensitive in qualifications. 'Status' and 'status' are different fields. When in doubt, use the exact field label from Developer Studio or the form schema endpoint.

Version-specific behavior: Helix 22.x and 23.x have API differences, particularly around how custom fields are addressed in the fields parameter and how the assoc parameter handles related entries. Test qualification behavior and field addressing in your specific version before building production pipelines. BMC's release notes for each version document breaking changes to API behavior.

Field types and JSON serialization

Understanding how AR System field types serialize in JSON responses prevents data loss in downstream processing:

Selection fields returning integer IDs rather than string labels is a common data quality issue. The integer maps to an enum defined on the field — you need the field's enum definition to decode it. Retrieve field metadata from GET /api/arsys/v1/fields/{FormName} to map integer values to labels.

How to export foundation data with direct SQL

For on-premises deployments, direct database access is often the fastest way to pull large volumes of data — especially foundation data like companies, sites, people, and support groups. BMC officially documents SQL queries to export BMC Remedy foundation data from your BMC Remedy ITSM environment using Microsoft SQL Server or Oracle database clients.

SQL Server caveat: Exported data may contain NULL values shown as NULL string literals in spreadsheet columns. These are treated as invalid data by most ITSM import tools. Replace NULL strings with empty strings or appropriate defaults before importing.

Oracle syntax difference: Replace the SQL function CHAR() with CHR() in all documented queries. Additionally, Oracle sorts NULLs at the end of ascending sorts by default; SQL Server sorts them at the beginning. If sort order matters for your import sequence, add explicit NULLS FIRST or NULLS LAST clauses.

Direct SQL caveats:

• AR System form names don't map 1:1 to table names. Underlying table names are often truncated or aliased. Use Developer Studio or the arschema command-line tool to map form names to physical table names before writing queries.
• Attachment fields store binary data in separate tables or as file system references — not inline in the main record table. SELECT * on an incident table will not retrieve attachment content.
• Workflow doesn't fire on direct SQL reads. There is no AR System audit trail of the export. For regulated environments or compliance requirements, this matters.
• SaaS tenants have no direct database access. The REST API is the only programmatic extraction path for SaaS.

BMC provides documented SQL queries for common foundation data exports (People, Companies, Sites, Support Groups) in their official migration documentation. Use those queries as a starting point rather than reverse-engineering table structures.

BMC Helix Data Manager: the official migration tool

BMC Helix Data Manager (HDM) is BMC's purpose-built tool for migrating data between ITSM environments. A Migration Pack contains the instructions passed to the HDM Engine when performing import and export operations.

HDM is designed for ITSM-to-ITSM migrations — for example, upgrading from Remedy 9.1 to Helix ITSM 22.1, or migrating between two Helix instances. It supports delta/incremental migration for changes and deletions between source and target systems.

The typical HDM workflow:

1. Register and validate source and target BMC Helix ITSM systems.
2. Discover the data dictionary for source and target; import migration packs, server references, and data updates.
3. Copy migration packs for source and target data dictionary.
4. Run migration jobs — full or incremental.

HDM connects at the database tier for data movement, which makes it fast but requires direct database connectivity between source and target systems. It cannot operate in network-isolated or SaaS-to-SaaS scenarios without VPN or database tunneling.

When HDM doesn't fit

If you're migrating out of BMC Helix to a different platform (ServiceNow, Jira Service Management, Freshservice, Zendesk), HDM won't help. It is a BMC-to-BMC tool. For cross-platform migrations, extract data via the REST API or direct SQL, transform it to match the target schema, and load it through the target platform's import mechanisms.

Exporting CMDB data

CMDB data in BMC Helix uses a class-based hierarchy rooted at BMC.CORE:BMC_BaseElement. CI classes inherit from this base and add class-specific attributes. Exporting CMDB data requires understanding this hierarchy:

• BMC.CORE:BMC_BaseElement — All CIs (base attributes: Reconciliation Identity, Name, Status, Class)
• BMC.CORE:BMC_ComputerSystem — Servers and computers
• BMC.CORE:BMC_Product — Software products
• BMC.CORE:BMC_LogicalSystemComponent — Logical components

Dataset filtering: BMC CMDB maintains multiple datasets (e.g., BMC.ASSET for asset management, BMC.DISCOVERY for auto-discovered CIs). Filter by dataset using the DatasetId field in your qualification: q=('DatasetId'="BMC.ASSET"). Omitting dataset filtering returns CIs from all datasets, including staging and discovery datasets that may not represent production state.

Reconciliation Identity (ReconciliationIdentity field): This is the canonical unique identifier for a CI across datasets. Use it — not the Entry ID — when preserving CI identity across a migration, since Entry IDs are system-generated and will change in the target environment.

Relationships between CIs are stored in BMC.CORE:BMC_BaseRelationship (and subclasses). Export relationships separately and preserve the source/target Reconciliation Identity values to reconstruct the relationship graph in the target system.

CMDB customizations — schema vs. data: If the source CMDB has custom CI classes or custom attributes, these are schema definitions, not data records. They must be reimplemented using CMDB Class Manager in the target system before importing CI data. Custom attributes on standard classes must also be extended in the target before records referencing those fields can be imported.

For workflow customizations (forms, active links, filters), use BMC Developer Studio to export overlaid and custom workflow to a definition file, then import those definitions to the target system.

Exporting attachments from BMC Helix ITSM

Attachments in BMC Helix ITSM are stored in attachment fields and retrieved separately from entry data. The REST API returns attachment metadata with an entry, but file content requires a second request per attachment:

GET /api/arsys/v1/entry/{FormName}/{EntryId}/attach/{FieldName}

The response contains raw binary file data. There is no batch download endpoint.

The N+1 call problem at scale: Exporting 50,000 incidents with an average of 2 attachments per incident requires a minimum of 150,000 API calls (50k entries + 100k attachments). At 2.5 requests/second with no errors, that is approximately 16.7 hours of continuous API calls, before accounting for token refresh overhead, retry logic, and network latency variance.

For on-premises deployments, pulling attachments directly from the file system or database BLOB storage is orders of magnitude faster. The file system path for attachments is configurable in AR System; check ar.cfg for the FileAttachmentRootDir setting. The REST API attachment path is only necessary for SaaS tenants or environments where direct file system access is not permitted.

Smart Reporting: quick exports without code

BMC Helix ITSM Smart Reporting provides a no-code path for ad-hoc data exports. Build a report against any form, then export results to CSV or Excel. You can also export the underlying SQL query from the SQL Statement tab for use in BMC Helix Dashboards or external tools.

Smart Reporting works well for:

• One-time data audits
• Datasets under approximately 50,000 rows (report timeouts are common above this threshold)
• Business users who need data without engineering involvement

Smart Reporting does not support:

• Automated or scheduled exports
• Attachment data export
• Preserving relational links between forms
• Programmatic triggering or API-driven orchestration

Failure mode reference: what breaks bulk exports and why

The following failure modes are specific to BMC Helix ITSM bulk exports and are not obvious from documentation alone:

Building a reliable bulk export script

# Pseudocode for a resilient BMC Helix ITSM export
 
BATCH_SIZE = 500       # Stay well under Max-Entries-Per-Query
TOKEN_REFRESH = 2700   # Refresh every 45 minutes (buffer before 60-min expiry)
REQUEST_DELAY = 0.4    # ~2.5 requests/second
SORT_FIELD = "Create Date"  # Never NULL; consistent ordering across pages
 
token = authenticate()
offset = 0
last_auth = now()
checkpoint_file = load_checkpoint()  # Resume from last successful offset
 
offset = checkpoint_file.last_offset or 0
 
while True:
    # Token refresh
    if (now() - last_auth) > TOKEN_REFRESH:
        logout(token)
        token = authenticate()
        last_auth = now()
    
    response = get_entries(
        form="HPD:IncidentInterface",
        qualification="'Create Date' > \"1700000000\"",
        fields=["Incident Number", "Description", "Status", "Assignee"],
        # Narrow field selection: reduces payload 60-80% vs. SELECT *
        limit=BATCH_SIZE,
        offset=offset,
        sort=f"{SORT_FIELD}.asc"
    )
    
    entries = response.entries
    
    if len(entries) == 0:
        break  # HTTP 200 with empty array = end of result set
    
    # Detect silent truncation
    if len(entries) < BATCH_SIZE and offset + len(entries) < response.total_count:
        log_warning(f"Possible truncation at offset {offset}: got {len(entries)}, expected {BATCH_SIZE}")
    
    write_to_file(entries)
    offset += len(entries)
    
    # Checkpoint after every batch
    save_checkpoint(offset)
    
    sleep(REQUEST_DELAY)
 
logout(token)

Key design decisions:

• Checkpoint every batch. If the script fails at offset 45,000, resume from the last checkpoint rather than restarting from zero.
• Use the fields parameter aggressively. Pulling all fields when you need five will significantly increase response times on large result sets — server-side serialization and network transfer scale with field count and record size.
• Sort by a never-NULL field (Entry ID or Create Date) to ensure consistent ordering across paginated requests and avoid NULL-sort pagination gaps.
• Always call logout. Implement a finally block so the token is released even if the script fails.
• Log offset checkpoints with timestamps. This gives you a performance baseline and makes it easy to estimate remaining runtime.

HTTP error code reference

Note: Rate limiting does not produce a consistent status code across BMC Helix versions or tenants. Throttled requests may return 429, 503, or 500 depending on the server configuration. There is no standard Retry-After header. Treat any 5xx as potentially throttle-related and implement exponential backoff.

Choosing the right export method

For cross-platform migrations out of BMC Helix to another service management tool, the REST API is almost always the right choice. It works for both SaaS and on-premises, respects AR System permissions and audit logging, and produces structured JSON that can be transformed to match any target schema.

What makes BMC Helix exports harder than other ITSM platforms

BMC Helix has specific friction points that distinguish it from platforms like Zendesk, Freshservice, or ServiceNow:

• Undocumented rate limits mean you cannot capacity-plan an export timeline with confidence until you've tested in your specific environment. Zendesk and Salesforce publish limits; BMC does not.
• Offset-based pagination without cursors breaks correctness guarantees on live systems. Cursor-based APIs (like Zendesk's incremental export) are immune to this problem.
• Proprietary qualification syntax — not SQL, not OData — with silent failure on malformed queries. A mistyped field name returns HTTP 200 with zero results, not an error.
• Attachment export is N+1 with no batch download endpoint. There is no equivalent to, for example, Zendesk's attachment bulk export or ServiceNow's export sets.
• Selection fields may return integer enum IDs rather than string labels, requiring a separate field metadata fetch to decode.
• Form-to-table name mapping is not intuitive and requires Developer Studio or command-line tooling to resolve.
• CMDB class hierarchy requires understanding inheritance structure before exports produce coherent datasets.
• Version differences between Helix 22.x and 23.x affect API field addressing and assoc parameter behavior — undated export scripts may fail silently after version upgrades.

These are implementation realities, not dealbreakers. They are the specifics that turn a "should take a weekend" estimate into a multi-week project if not accounted for in advance.

When to bring in help

If you're exporting data for a one-time report or small integration, the REST API with a script built on the patterns above will get you there. If you're running a full platform migration — especially moving off BMC Helix to a different ITSM tool — the complexity compounds: hundreds of forms, relational integrity between tickets and CMDB CIs, attachments, work logs, custom fields, enum decoding, and data validation before import.

ClonePartner builds and operates extraction pipelines for BMC Helix environments, including attachment handling, field mapping, and data transformation for cross-platform migrations.