jonbartels / mirth_daily_volume_report.sql

Last active December 15, 2023 21:13

I kept forgetting how to do dynamic SQL to query all the MC d_ tables so I saved it this time; returns XML

	WITH channel_query AS (
	SELECT dc.local_channel_id,
	c.name,
	format('SELECT ' \|\|
	'%L::TEXT as channel_name, ' \|\|
	'date_trunc(''day'', received_date)::TEXT as day, ' \|\|
	'count(*)::BIGINT as cnt FROM d_m%s' \|\|
	' WHERE received_date >= now() - INTERVAL ''%s DAYS'' GROUP BY 1, 2', c.name,
	dc.local_channel_id, 90) as query
	FROM d_channels dc

jonbartels / dumpQuartzJobsFromMirthConnect.js

Created May 6, 2023 14:36

A hastily written script to enumerate the Quartz scheduler jobs from Mirth Connect in a JS reader or JS step and return JSON

	var dump = {};

	var factory = new org.quartz.impl.StdSchedulerFactory();
	var allSchedulers = factory.getAllSchedulers();

	dump.schedulerCount = allSchedulers.size();

	dump.schedulers = [];

	var iter = allSchedulers.iterator();

jonbartels / report_on_paid_mirth_connectors.sql

Last active May 12, 2023 01:56

Report on Usage of Paid Extension Connectors in Mirth Connect

	with channel_xml as (
	select
	name,
	xmlparse(document channel) as channel_xml
	from channel c
	)
	, destination_connector_xml as (
	SELECT
	name as channel_name,
	unnest(xpath('//destinationConnectors/connector/name/text()', channel_xml))::TEXT as connector_name,

jonbartels / mirth_connect_events_by_channel_name.sql

Created May 25, 2023 21:06

Query Mirth Connect event table by event name and pick out channel name and channel ID from the attributes. Postgres 13 and newer

	WITH attribute_rows AS (select e.name,
	e.date_created,
	xml_element.*
	from "event" e,
	xmltable('//linked-hash-map/entry'
	passing (e."attributes"::xml)
	columns "key" text PATH 'string[1]',
	"value" text PATH 'string[2]'
	) as xml_element
	where e.name like '%Deploy%'

jonbartels / Dockerfile

Created May 26, 2023 00:55

Use SumoLogic logging in Mirth Connect. Shows the JARs to load to get MC to tolerate the Sumo appender for Log4j2

	FROM nextgenhealthcare/connect:4.3.0 as connect

	#clone some boilerplate from MC image
	ENV LANG en_US.UTF-8
	ENV LANGUAGE en_US:en
	ENV LC_ALL en_US.UTF-8

	#TODO using a tarball and ADD also sets us up to get these artifacts from artifactory instead of from git
	#TODO the parent entrypoint.sh does something with a custom-extensions directory, review it and see if it does this unpacking for us
	#TODO I still think ADD is best here since it can fetch from remote URLs, it sets us up to use artifactory

jonbartels / mc_certs.sql

Last active December 22, 2023 16:44

Mirth Connect channels using SSL certs by cert

	with channel_xml as (
	select
	name,
	xmlparse(document channel) as channel_xml
	from channel c
	)
	, destination_connector_xml as (
	SELECT
	name as channel_name,
	unnest(xpath('//destinationConnectors/connector/name/text()', channel_xml))::TEXT as connector_name,

jonbartels / mirthSourceTransformer.js

Last active January 12, 2024 18:17

Alert receiver channel which does not alert if a message queued

	//can use this to get the message content and check state and send attempts
	// we have channelId and messageId
	// pass metadataIds as null -> the connector message returned will then have one or many connector messages. #0 is the source, and source has connectorMessage.getDestinationIdMap(); which we can iterate to turn our connector name into the numeric metadata id
	var shouldAlert = true;
	var metaDataIds = null;
	var alertedChannelId = msg['channelId'];
	var messageId = parseInt(msg['messageId']);
	var messageController = Packages.com.mirth.connect.server.controllers.messageController.getInstance();
	var message = messageController.getMessageContent(alertedChannelId, messageId, metaDataIds);
	var sourceMessage = message.getConnectorMessages().get(0);

jonbartels / mirth-channel-error.sql

Created February 2, 2024 20:20

Show Mirth Connect errors by destination with PID.3 and PV1.19 from source message with short error and detail description

	select
	dm.message_id, status,connector_name ,
	to_char(received_date AT TIME ZONE 'US/Eastern', 'YYYY-MM-DD hh24:mi') \|\| ' US/Eastern' as message_date,
	(regexp_matches(mc.content, 'PID\\|(?:.?\\|){2}(.?)\\|'))[1] as pid_3_mrns,
	(regexp_matches(mc.content, 'PV1\\|(?:.?\\|){18}(.?)\\|'))[1] as pv1_19_visit_id,
	(regexp_matches(mc_error.content, '.ERROR MESSAGE:\s+(.?)', 'n'))[1] as error_message,
	(regexp_matches(mc_error.content, '.DETAILS:\s+(.?)', 'n'))[1] as detail_message
	from mirth.public.d_mm76 dm
	inner join d_mc76 mc on mc.message_id = dm.message_id and mc.metadata_id = 0 and mc.content_type = 1
	inner join d_mc76 mc_error on mc_error.message_id = dm.message_id and mc_error.metadata_id = dm.id and mc_error.content_type = 12

jonbartels / Mirth Thread Dumps.md

Last active October 4, 2024 19:10

Thread Dumps for Mirth Connect

When to take thread dumps

You suspect long running, slow, or stuck connections
You suspect infinite loops
You have problems that are only fixed by redeploying channels or restarting Mirth
You have high memory usage
When Mirth just seems generally backed up and not right

How to take a thread dump

jonbartels / mirth-connections.js

Created November 19, 2024 20:01

Script to read the connection status data from Mirth and generate a useful data structure

	//Credit to Mike Klemens and Chris Gibson in Mirth Slack

	// Get the server ID using ConfigurationController
	var serverInstance = Packages.com.mirth.connect.server.controllers.ConfigurationController.getInstance();
	var serverId = serverInstance.getServerId();

	//Use Dashboard Status plugin
	var defController = Packages.com.mirth.connect.server.controllers.DefaultExtensionController.create();
	var monitor = defController.getServicePlugins().get(com.mirth.connect.plugins.dashboardstatus.DashboardConnectorStatusServletInterface.PLUGIN_POINT);
	connectorListener = monitor.getConnectorListener();