Here are some mongo queries that might come in handy when maintaining TIS-ESR interface.

General Stuff

A mongo instances has several databases, each database has a number of collections, each collections has a number of documents. The documents with a collection can have different structure. The structure tends to be enforced by the programs using Mongo rather than Mongo enforcing the structure.

rs0:PRIMARY> show dbs;
admin                 0.000GB
audit                 0.983GB
config                0.000GB
esrdataexport         0.017GB
esrinbounddatareader  0.001GB
esrinbounddatawriter  0.000GB
esrreconciliation     0.005GB
local                 0.632GB
transdb               0.000GB

rs0:PRIMARY> db.getName();
audit

rs0:PRIMARY> use esrdataexport;
switched to db esrdataexport

rs0:PRIMARY> db.getName();
esrdataexport

rs0:PRIMARY> show collections;
basePendingExport
counters
deanery
generatedRecord
generatedapprecord
generatednotrecord
pendingexport
pendingnotificationexport
positions
shedLock

It’s useful to find an example document within a collection.

rs0:PRIMARY> db.positions.findOne();
{
	"_id" : ObjectId("5e6261feea8eff5ad15a5b26"),
	"positionId" : NumberLong(7024414),
	"positionNumber" : NumberLong(26457700),
	"deanery" : "PEN",
	"postId" : NumberLong(9897),
	"deleted" : false,
	"versionNumber" : NumberLong(0),
	"_class" : "com.hee.tis.esr.esrdataexport.entity.Position"
}

Useful Queries

Audit Messages

A copy of every rabbit message sent around the system is copied to the audit database. The esr microservice is specified by the property ‘messageProperties.appId’. Generally, messages are sent to the ‘main.exchange' first and are then routed using the ‘routing key’. The ‘routing key' is specified by the property ‘messageProperties.receivedRoutingKey’.

rs0:PRIMARY> db.auditmessage.distinct('messageProperties.receivedRoutingKey');
[
	"esr.appfilegenerationcommand.create",
	"esr.apprecord.created",
	"esr.asg.address.split",
	"esr.asg.person.split",
	"esr.asg.split",
	"esr.csv.invalid",
	"esr.notification.created",
	"esr.notificationfilegenerationcommand.create",
	"esr.placementposition.updated",
	"esr.porpos.split",
	"esr.position.deleted",
	"esr.position.reconciled",
	"esr.position.saved"
]

Show all distinct appIds

If the processing of a message fails, the processing application can specify that it’s retried. If a message is rejected, additional information is attached the the failed message and that is stored in the audit database too.

We can find the number of message associated with each appId.

We can find the number of messages associated with each routingKey.

We can find the pairs of appId and receivedRoutingKey and count them too.

In audit, find all 'porpos' messages for specific DPN

In Reconciliation db, find an example reconciliation message.

Find all reconciled positions linked to a specific NPN

Find all 'placement update' messages for a specific NPN.
These messages relate to CDC events for placement updates and creates in TIS

find all 'esr.position.reconciled' messages for a specific NPN

Find 'esr.position.reconciled' messages for a specific NPN - exclude any rejected.

find 'esr.position.reconciled' messages for a specific NPN that ARE rejected but only bring back only:

_id
NPN
timestamp
x-exception-message
z-do-requeue
z-attempts

Find messages for ‘esr.apprecord.created' that aren't reject messages for a specific NPN, bring back _id, NPN and timestamp. Note: '_id’ is always returned unless you explicitly say not to.

Find position record (in exporter db) by postId

Find generated apprecords for a DPN

Mongo doesn’t really support joins like an RBBMS - but you can do it if you are really, really keen.

Find me all the generatedapprecords for positionId joining to the positions collection on positionId and bring back it’s fields too.

Find last 10 source files ingested for deanery ‘SEV’ ordered, latest first.

Find the different exceptions of the rejected messages and count how many of each - most frequent first.

Find the latest exception of each time, ordered by most recent.

Find the last time we had a message of each ‘routing key', show latest first.

Find the very latest reject message (I’ve filtered out the jsonMessageBody as that contains personal information).

Find a single audit messages that is associated with a rejection that are created after a specified date.

TIS21 Confluence Space

Example Mongo Queries

General Stuff

Useful Queries

Audit Messages