/
OIE / Mirth Query

OIE / Mirth Query

Use the OIE / Mirth Query panel in Sql Loader to build Mirth / Open Integration Engine message queries without writing SQL by hand.

Supported databases: PostgreSQL, SQL Server, MySQL, and Oracle.

Enable the panel

  1. Open Sql Loader and select a connection to a Mirth / OIE database.

  2. Click Options.

  3. Turn on Show OIE / Mirth UI.

The left Mirth Query panel appears.

Tip

After a successful Test on the connection, HL7Spy may detect a Mirth database (D_CHANNELS) and turn the UI on for you. You can turn it off anytime under Options → Show OIE / Mirth UI.

Quick start

  1. Click the zoom button next to Channel and pick a channel (Select Mirth Channel).

  2. Optionally narrow Connector, Status, Range, Search, and Content Type.

  3. Optionally add Meta Data filters (Show / Operator / Value).

  4. Click Execute to generate and run the query, or Gen SQL to put the SQL in the editor without running it.

Generated SQL is written to the Last OIE / Mirth Query region in the SQL editor. You can edit it and run again with the main Sql Loader Execute (F5).

Filters

Channel

Required. Opens Select Mirth Channel.

  • Browse by Id, Group Name, Name, or Channel Id.

  • Star favorites so they sort to the top.

  • Use Config search to find channels by text in the channel XML config (for example a port or host).

Range

How far back to look:

Option

Meaning

Option

Meaning

Most Recent Messages

Newest messages, limited by the number box (default 1000)

Last Hour / Last 4 Hours / Today / Last 24 Hours

Time windows

Custom Range

Shows Start and End date/time

For non–Most Recent ranges, leave the limit blank for no row cap, or set a number to limit results.

Search

Optional text search in message content. Matching messages are found, then all selected connectors for those messages are returned (similar to Mirth Admin search).

Connector

Multi-select, or leave (All). Use the refresh button if connectors change after a deploy.

Status

Multi-select, or leave (All). Includes Mirth statuses (Received, Filtered, Transformed, Queued, Sent, Error, Pending) plus Has Error (only messages with an error code).

Content Type

Which message body to load:

Option

Behavior

Option

Behavior

Auto (default)

Prefer Raw; if missing, use Encoded. Connectors without either still appear

Raw, Processed Raw, Transformed, Encoded, Sent, Response, Response Transformed, Processed Response

Only connectors that have that type

None

Metadata only — no message body

Hover the Content Type control for a short description of each choice.

Meta Data

The grid under the filters lets you:

Column

Purpose

Column

Purpose

Show

Include this custom metadata column in the results (SELECT)

Column

Mirth custom metadata name (from D_MCM*)

Operator

Equals, Contains, Starts With, Is Null, Is Empty, and similar

Value

Filter value (not needed for Is Null / Is Empty style operators)

Show and filtering are independent: you can filter on a column without showing it, or show it without filtering.

Reorder rows with drag-and-drop or the up/down buttons. Refresh reloads column names from the database for the selected channel.

Columns (Settings)

Click Settings (or Configure Global Columns on a results grid header when the Mirth panel is visible).

In Mirth Column Options:

  • Choose which global columns appear (message, connector, content fields, and so on).

  • Set Alias names and column order.

  • Use CONTENT always last so CONTENT stays at the end of the SELECT list.

Custom Meta Data columns are controlled from the Meta Data Show checkboxes, not this dialog.

Toolbar buttons

Button

What it does

Button

What it does

Settings

Open column options

Gen SQL

Build SQL from the filters into Last OIE / Mirth Query region (does not run)

Execute

Build SQL and run it

While a query is running, Execute and Gen SQL are disabled.

Tips

  • SSH: If the connection uses SSH, the generated SQL comment notes (SSH). Tunnel settings live on the connection, not on the Mirth panel.

  • Per connection: Channel selection and Meta Data filters are saved with that connection.

  • Shared settings: Range, search text, Most Recent limit, and global columns are shared across connections in the app.

  • No custom metadata: If refresh finds none, define metadata columns in Mirth / OIE, or confirm the channel’s D_MCM* tables exist.

  • Results: When the Mirth UI is on, CONTENT_TYPE cells can show as 1 (RAW) style names instead of bare numbers.

Typical examples

Last 1000 messages on a channel (Raw or Encoded body)

Select channel → Range = Most Recent Messages → Limit = 1000 → Content Type = Auto → Execute.

Errors only for one destination

Select channel → pick the destination under Connector → Status = Error (or Has Error) → Execute.

Find messages containing a patient ID

Select channel → enter the ID in Search → Execute.

Filter by custom metadata and show it in results

Add a Meta Data row → set Operator/Value → check Show → Execute.