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


rs0:PRIMARY> db.auditmessage.distinct('messageProperties.appId');
[
	"EsrAppRecordGeneratorService",
	"EsrDataExportService",
	"EsrInboundDataWriterService",
	"EsrNotificationGenerator",
	"EsrReconciliationService",
	"cdc-rabbit-router",
	"esr-inbound-data-reader"
]

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.

db.auditmessage.aggregate([
{$match: {'messageProperties.appId' : {$exists:1}}},
{$group: {'_id': '$messageProperties.appId','count':{$sum:1}}},
{$project: {
  'appId'   : '$_id',
  'count'   : '$count',
  '_id':0
}},
{$sort: {'count':-1, 'appId':1}}
]);

{ "appId" : "EsrAppRecordGeneratorService", "count" : 137943 }
{ "appId" : "esr-inbound-data-reader", "count" : 135659 }
{ "appId" : "cdc-rabbit-router", "count" : 35400 }
{ "appId" : "EsrNotificationGenerator", "count" : 28855 }
{ "appId" : "EsrDataExportService", "count" : 3358 }
{ "appId" : "EsrInboundDataWriterService", "count" : 459 }
{ "appId" : "EsrReconciliationService", "count" : 92 }

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

db.auditmessage.aggregate([
{$match: {'messageProperties.receivedRoutingKey' : {$exists:1}}},
{$group: {'_id': '$messageProperties.receivedRoutingKey','count':{$sum:1}}},
{$project: {
  'receivedRoutingKey'   : '$_id',
  'count'   : '$count',
  '_id':0
}},
{$sort: {'count':-1, 'receivedRoutingKey':1}}
]);

{ "receivedRoutingKey" : "esr.apprecord.created", "count" : 137943 }
{ "receivedRoutingKey" : "esr.position.deleted", "count" : 48114 }
{ "receivedRoutingKey" : "esr.porpos.split", "count" : 37265 }
{ "receivedRoutingKey" : "esr.placementposition.updated", "count" : 36357 }
{ "receivedRoutingKey" : "esr.notification.created", "count" : 27989 }
{ "receivedRoutingKey" : "esr.position.reconciled", "count" : 23949 }
{ "receivedRoutingKey" : "esr.position.saved", "count" : 23478 }
{ "receivedRoutingKey" : "esr.asg.split", "count" : 2851 }
{ "receivedRoutingKey" : "esr.appfilegenerationcommand.create", "count" : 1902 }
{ "receivedRoutingKey" : "esr.notificationfilegenerationcommand.create", "count" : 1456 }
{ "receivedRoutingKey" : "esr.asg.address.split", "count" : 242 }
{ "receivedRoutingKey" : "esr.asg.person.split", "count" : 217 }
{ "receivedRoutingKey" : "esr.csv.invalid", "count" : 3 }

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

db.auditmessage.aggregate([
{$match: {'messageProperties.appId':{$exists:1},'messageProperties.receivedRoutingKey':{$exists:1}}},
{$group: {'_id': {
  'appId'      : '$messageProperties.appId',
  'routingKey' : '$messageProperties.receivedRoutingKey'
},'count':{$sum:1}}},
{$project: {
  'appId'      : '$_id.appId',
  'routingKey' : '$_id.routingKey',
  'count'      : '$count',
  '_id':0
}},
{$sort: {'count':-1, 'appId':1, 'routingKey':1}}
]);

{ "appId" : "EsrAppRecordGeneratorService", "routingKey" : "esr.apprecord.created", "count" : 137972 }
{ "appId" : "esr-inbound-data-reader", "routingKey" : "esr.position.deleted", "count" : 48114 }
{ "appId" : "esr-inbound-data-reader", "routingKey" : "esr.porpos.split", "count" : 37265 }
{ "appId" : "cdc-rabbit-router", "routingKey" : "esr.placementposition.updated", "count" : 35416 }
{ "appId" : "EsrNotificationGenerator", "routingKey" : "esr.notification.created", "count" : 28015 }
{ "appId" : "esr-inbound-data-reader", "routingKey" : "esr.position.reconciled", "count" : 23948 }
{ "appId" : "esr-inbound-data-reader", "routingKey" : "esr.position.saved", "count" : 23478 }
{ "appId" : "esr-inbound-data-reader", "routingKey" : "esr.asg.split", "count" : 2851 }
{ "appId" : "EsrDataExportService", "routingKey" : "esr.appfilegenerationcommand.create", "count" : 1902 }
{ "appId" : "EsrDataExportService", "routingKey" : "esr.notificationfilegenerationcommand.create", "count" : 1456 }
{ "appId" : "EsrNotificationGenerator", "routingKey" : "esr.placementposition.updated", "count" : 866 }
{ "appId" : "EsrInboundDataWriterService", "routingKey" : "esr.asg.address.split", "count" : 242 }
{ "appId" : "EsrInboundDataWriterService", "routingKey" : "esr.asg.person.split", "count" : 217 }
{ "appId" : "EsrReconciliationService", "routingKey" : "esr.placementposition.updated", "count" : 92 }
{ "appId" : "esr-inbound-data-reader", "routingKey" : "esr.csv.invalid", "count" : 3 }
{ "appId" : "cdc-rabbit-router", "routingKey" : "esr.position.reconciled", "count" : 1 }

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

use audit;

db.auditmessage.find({
'messageProperties.receivedRoutingKey':'esr.porpos.split',
'messageProperties.headers.deaneryPostNumber':'SEV/RBA11/004/F1/001'
}).toArray();

In Reconciliation db, find an example reconciliation message.

use esrreconciliation;

db.positions.findOne();

{
	"_id" : ObjectId("5e6261fbc1793e5b56afa30b"),
	"positionId" : NumberLong(7290187),
	"positionNumber" : NumberLong(30809832),
	"deanery" : "",
	"nationalPostNumber" : "SEV/RVJ01/052/F2/004",
	"postId" : NumberLong(86573),
	"status" : "MATCHED",
	"type" : "POR",
	"deleted" : false,
	"versionNumber" : NumberLong(1),
	"_class" : "com.hee.tis.esr.reconciliation.entity.Position"
}

Find all reconciled positions linked to a specific NPN

use esrreconciliation;

db.positions.find({'nationalPostNumber':'SEV/RBA11/004/F1/001'}).toArray();

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

use audit;

db.auditmessage.find({
 'messageProperties.receivedRoutingKey':'esr.placementposition.updated',
 'jsonMessageBody.reconciledPositions.nationalPostNumber':'SEV/RBA11/004/F1/001'
}).toArray();

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

use audit;

db.auditmessage.find({
  'messageProperties.receivedRoutingKey':'esr.position.reconciled',
  'jsonMessageBody.nationalPostNumber':'SEV/RBA11/004/F1/001'
}).toArray();

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

use audit;

db.auditmessage.find({
  'messageProperties.receivedRoutingKey':'esr.apprecord.created',
  'messageProperties.headers.z-attempts':{$exists:0},
  'jsonMessageBody.nationalPostNumber':'SEV/RBA11/004/F1/001'
}).toArray();

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

use audit;

db.auditmessage.find({
 'messageProperties.receivedRoutingKey':'esr.apprecord.created',
 'messageProperties.headers.z-attempts':{$exists:1},
 'jsonMessageBody.nationalPostNumber':'SEV/RBA11/004/F1/001'
},{
 'jsonMessageBody.nationalPostNumber':1,
 'messageProperties.timestamp':1,
 'messageProperties.headers.x-exception-message':1,
 'messageProperties.headers.z-do-requeue':1,
 'messageProperties.headers.z-attempts':1
}).toArray();

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.


db.auditmessage.find({
  'messageProperties.receivedRoutingKey':'esr.apprecord.created',
  'messageProperties.headers.z-attempts':{$exists:0},
  'jsonMessageBody.nationalPostNumber':'SEV/RBA11/004/F1/001'
},{
  'jsonMessageBody.nationalPostNumber':1,
  'messageProperties.timestamp':1
}).toArray();

Find position record (in exporter db) by postId

use esrdataexport;

db.positions.find({'postId':88113}).toArray();
[
	{
		"_id" : ObjectId("5e68fcb4afedb92b695c71a4"),
		"positionId" : NumberLong(7290266),
		"positionNumber" : NumberLong(30815784),
		"deanery" : "",
		"postId" : NumberLong(88113),
		"deleted" : false,
		"versionNumber" : NumberLong(0),
		"_class" : "com.hee.tis.esr.esrdataexport.entity.Position"
	}
]

Find generated apprecords for a DPN

use esrdataexport;

db.generatedapprecord.find({
  'appRecord.deaneryNumber':'SEV/RBA11/004/F1/001'
}).toArray();

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.

use esrdataexport;

db.generatedapprecord.aggregate([
  {$addFields: {fk: {$objectToArray: "$$ROOT.position"}}},
  {$lookup: {   from: 'positions',
                localField: 'fk.1.v',
                foreignField: '_id',
                as: 'positions'   }},
  {$addFields: {"position": {$arrayElemAt: [ "$positions", 0 ] }}},
  {$project: {"fk":0, "positions":0}},
  {$match: {"position.positionId":{ $in:[6908443]   }}}
]).toArray();

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

use audit;

db.auditmessage.aggregate([
{$match:{
  'messageProperties.appId' : 'esr-inbound-data-reader',
  'messageProperties.headers.sourceFile' : /^DE_SEV/
}},
{$group: {
    '_id' : '$messageProperties.headers.sourceFile',
    'timestamp' :  {$max:'$messageProperties.headers.eventSourceTimestamp'}
}},
{$project: {
    'sourceFile' : '$_id',
    'timestamp'  :  1,
    '_id' : 0
}},
{$sort: {
  "timestamp"  : -1,
  "sourceFile" : 1
}},
{$project: {
    'sourceFile' : '$sourceFile',
    'timestamp'  : '$timestamp'
}},
{$limit: 10}
]);

{ "sourceFile" : "DE_SEV_RMC_20200626_00000393.DAT", "timestamp" : ISODate("2020-06-26T13:45:20.155Z") }
{ "sourceFile" : "DE_SEV_RMC_20200625_00000392.DAT", "timestamp" : ISODate("2020-06-25T13:45:17.002Z") }
{ "sourceFile" : "DE_SEV_RMC_20200624_00000391.DAT", "timestamp" : ISODate("2020-06-24T13:45:18.588Z") }
{ "sourceFile" : "DE_SEV_RMC_20200619_00000386.DAT", "timestamp" : ISODate("2020-06-19T13:45:15.797Z") }
{ "sourceFile" : "DE_SEV_RMC_20200618_00000385.DAT", "timestamp" : ISODate("2020-06-18T13:45:18.520Z") }
{ "sourceFile" : "DE_SEV_RMC_20200617_00000384.DAT", "timestamp" : ISODate("2020-06-17T13:45:20.429Z") }
{ "sourceFile" : "DE_SEV_RMC_20200616_00000383.DAT", "timestamp" : ISODate("2020-06-16T13:45:19.141Z") }
{ "sourceFile" : "DE_SEV_RMC_20200613_00000380.DAT", "timestamp" : "2020-06-13T13:45:16.886246Z" }
{ "sourceFile" : "DE_SEV_RMC_20200612_00000379.DAT", "timestamp" : "2020-06-12T13:45:17.491777Z" }
{ "sourceFile" : "DE_SEV_RMC_20200611_00000378.DAT", "timestamp" : "2020-06-11T13:45:15.123758Z" }

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

use audit;

db.auditmessage.aggregate([
  {$match:{'messageProperties.headers.z-exception-type':{$exists:1}}},
  {$group:{'_id':'$messageProperties.headers.z-exception-type', 'count':{$sum:1}}},
  {$sort:{'count':-1}}
]);

{ "_id" : "java.time.format.DateTimeParseException", "count" : 77152 }
{ "_id" : "org.springframework.dao.DataAccessResourceFailureException", "count" : 62924 }
{ "_id" : "java.lang.RuntimeException", "count" : 50551 }
{ "_id" : "org.everit.json.schema.ValidationException", "count" : 10755 }
{ "_id" : "org.springframework.core.convert.ConverterNotFoundException", "count" : 6880 }
{ "_id" : "org.springframework.amqp.AmqpRejectAndDontRequeueException", "count" : 2143 }
{ "_id" : "org.springframework.data.mongodb.UncategorizedMongoDbException", "count" : 788 }
{ "_id" : "org.springframework.core.convert.ConversionFailedException", "count" : 523 }
{ "_id" : "org.springframework.web.client.ResourceAccessException", "count" : 450 }
{ "_id" : "java.lang.IllegalStateException", "count" : 289 }
{ "_id" : "org.springframework.transaction.CannotCreateTransactionException", "count" : 2 }
{ "_id" : "org.springframework.transaction.HeuristicCompletionException", "count" : 2 }
{ "_id" : "javax.ws.rs.NotAuthorizedException", "count" : 1 }

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

use audit;

db.auditmessage.aggregate([
{$match:{'messageProperties.headers.z-exception-type':{$exists:1}}},
{$group:{'_id':'$messageProperties.headers.z-exception-type', 'latest':{$max:'$created'}}},
{$sort:{'latest':-1}}
]);

{ "_id" : "org.springframework.amqp.AmqpRejectAndDontRequeueException", "latest" : ISODate("2020-06-26T14:52:19.529Z") }
{ "_id" : "org.everit.json.schema.ValidationException", "latest" : ISODate("2020-06-26T14:50:09.663Z") }
{ "_id" : "java.lang.RuntimeException", "latest" : ISODate("2020-06-26T14:33:49.327Z") }
{ "_id" : "org.springframework.data.mongodb.UncategorizedMongoDbException", "latest" : ISODate("2020-06-26T14:00:10.182Z") }
{ "_id" : "org.springframework.core.convert.ConverterNotFoundException", "latest" : ISODate("2020-06-22T11:40:15.042Z") }
{ "_id" : "org.springframework.transaction.CannotCreateTransactionException", "latest" : ISODate("2020-06-21T05:00:35.433Z") }
{ "_id" : "org.springframework.core.convert.ConversionFailedException", "latest" : ISODate("2020-06-17T15:46:16.606Z") }
{ "_id" : "org.springframework.web.client.ResourceAccessException", "latest" : ISODate("2020-06-11T10:12:34.605Z") }
{ "_id" : "java.time.format.DateTimeParseException", "latest" : ISODate("2020-06-11T08:34:59.609Z") }
{ "_id" : "java.lang.IllegalStateException", "latest" : ISODate("2020-06-08T17:25:14.124Z") }
{ "_id" : "org.springframework.transaction.HeuristicCompletionException", "latest" : ISODate("2020-06-08T11:43:41.969Z") }
{ "_id" : "javax.ws.rs.NotAuthorizedException", "latest" : ISODate("2020-05-22T08:03:00.481Z") }
{ "_id" : "org.springframework.dao.DataAccessResourceFailureException", "latest" : ISODate("2020-04-14T13:45:47.209Z") }

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

use audit;

db.auditmessage.aggregate([
{$match:{'messageProperties.headers.x-original-routingKey':{$exists:1}}},
{$group:{'_id':'$messageProperties.headers.x-original-routingKey', 'latest':{$max:'$created'}}},
{$sort:{'max':-1}}
]);

{ "_id" : "tis.placement.updated", "latest" : ISODate("2020-06-26T14:33:49.327Z") }
{ "_id" : "esr.apprecord.created", "latest" : ISODate("2020-06-26T14:52:19.529Z") }
{ "_id" : "esr.position.reconciled", "latest" : ISODate("2020-06-26T13:45:42.778Z") }
{ "_id" : "esr.position.saved", "latest" : ISODate("2020-03-11T15:02:11.303Z") }
{ "_id" : "esr.appfilegenerationcommand.create", "latest" : ISODate("2020-06-26T13:00:10.190Z") }
{ "_id" : "esr.placementposition.updated", "latest" : ISODate("2020-06-22T11:40:15.042Z") }
{ "_id" : "esr.notificationfilegenerationcommand.create", "latest" : ISODate("2020-06-26T14:00:10.182Z") }
{ "_id" : "tis.post.created", "latest" : ISODate("2020-04-01T15:57:09.957Z") }
{ "_id" : "esr.asg.split", "latest" : ISODate("2020-06-26T13:45:20.663Z") }
{ "_id" : "esr.porpos.split", "latest" : ISODate("2020-06-26T13:45:21.182Z") }
{ "_id" : "esr.notification.created", "latest" : ISODate("2020-06-26T14:13:38.378Z") }
{ "_id" : "tis.placement.created", "latest" : ISODate("2020-06-26T10:50:26.168Z") }
{ "_id" : "esr.position.deleted", "latest" : ISODate("2020-06-25T13:45:20.608Z") }

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

use audit;

db.auditmessage.find({
  "messageProperties.headers.z-attempts":{$exists:1}
},{
  'jsonMessageBody':0
}).sort({"created":-1}).limit(1).toArray();

[
	{
		"_id" : ObjectId("5ef60ba3bb50531376f2c7cd"),
		"version" : NumberLong(1),
		"messageProperties" : {
			"headers" : {
				"z-exception-from-application" : "EsrDataExportService",
				"z-do-requeue" : "false",
				"x-exception-message" : "We currently have a Deleted Position with Position Id: [6874516], Position Number: [24312910] which means we've received a POR/POS with a Delete indicator. Rejecting message from queue",
				"x-original-routingKey" : "esr.apprecord.created",
				"z-validation-errors" : [ ],
				"eventSourceTimestamp" : ISODate("2020-06-26T14:52:19Z"),
				"z-attempts" : "1",
				"correlationId" : "799f3f41-c810-4e20-a41d-3843f86b0a58",
				"x-original-exchange" : "main.exchange",
				"z-exception-type" : "org.springframework.amqp.AmqpRejectAndDontRequeueException",
				"x-exception-stacktrace" : "org.springframework.amqp.rabbit.support.ListenerExecutionFailedException: Listener method 'public void com.hee.tis.esr.esrdataexport.listener.AppRecordCreatedListener.processCreatedAppRecord(java.lang.String,java.lang.String,java.time.Instant)' threw exception\n\tat org.springframework.amqp.rabbit.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:228)\n\tat org.springframework.amqp.rabbit.listener.adapter.MessagingMessageListenerAdapter.invokeHandlerAndProcessResult(MessagingMessageListenerAdapter.java:148)\n\tat org.springframework.amqp.rabbit.listener.adapter.MessagingMessageListenerAdapter.onMessage(MessagingMessageListenerAdapter.java:133)\n\tat org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:1579)\n\tat org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.actualInvokeListener(AbstractMessageListenerContainer.java:1498)\n\tat jdk.internal.reflect.GeneratedMethodAccessor75.invoke(Unknown Source)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)\n\tat java.base/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat com.hee.tis.esr.common.errorsupport.MessageUtils.lambda$createValidatingMethodInterceptor$0(MessageUtils.java:42)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.retry.interceptor.RetryOperationsInterceptor$1.doWithRetry(RetryOperationsInterceptor.java:91)\n\tat org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:287)\n\tat org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:180)\n\tat org.springframework.retry.interceptor.RetryOperationsInterceptor.invoke(RetryOperationsInterceptor.java:115)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)\n\tat org.springframework.amqp.rabbit.listener.$Proxy150.invokeListener(Unknown Source)\n\tat org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:1486)\n\tat org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:1477)\n\tat org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.executeListener(AbstractMessageListenerContainer.java:1421)\n\tat org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.doReceiveAndExecute(SimpleMessageListenerContainer.java:963)\n\tat org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.receiveAndExecute(SimpleMessageListenerContainer.java:913)\n\tat org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.access$1600(SimpleMessageListenerContainer.java:81)\n\tat org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer$AsyncMessageProcessingConsumer.mainLoop(SimpleMessageListenerContainer.java:1284)\n\tat org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer$AsyncMessageProcessingConsumer.run(SimpleMessageListenerContainer.java:1190)\n\tat java.base/java.lang.Thread.run(Unknown Source)\nCaused by: org.springframework.amqp.AmqpRejectAndDontRequeueException: We currently have a Deleted Position with Position Id: [6874516], Position Number: [24312910] which means we've received a POR/POS with a Delete indicator. Rejecting message from queue\n\tat com.hee.tis.esr.esrdataexport.service.AppRecordPersistentService.checkPositionDeleted(AppRecordPersistentService.java:144)\n\tat com.hee.tis.esr.esrdataexport.listener.AppRecordCreatedListener.processCreatedAppRecord(AppRecordCreatedListener.java:66)\n\tat com.hee.tis.esr.esrdataexport.listener.AppRecordCreatedListener$$FastClassBySpringCGLIB$$6b1bff8b.invoke(<generated>)\n\tat org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)\n\tat com.hee.tis.esr.esrdataexport.listener.AppRecordCreatedListener$$EnhancerBySpringCGLIB$$d7da12cc.processCreatedAppRecord(<generated>)\n\tat jdk.internal.reflect.GeneratedMethodAccessor78.invoke(Unknown Source)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)\n\tat java.base/java.lang.reflect.Method.invoke(Unknown Source)\n\tat org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:171)\n\tat org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:120)\n\tat org.springframework.amqp.rabbit.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:53)\n\tat org.springframework.amqp.rabbit.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:220)\n\t... 28 more\n"
			},
			"timestamp" : ISODate("2020-06-26T14:52:19Z"),
			"messageId" : "543c5274-77ea-47b1-9722-98c45ce2e5bf",
			"appId" : "EsrAppRecordGeneratorService",
			"type" : "APP_RECORD",
			"contentType" : "application/json",
			"contentLength" : NumberLong(0),
			"contentLengthSet" : true,
			"priority" : 0,
			"redelivered" : false,
			"receivedExchange" : "ex.error",
			"receivedRoutingKey" : "esr.apprecord.created",
			"deliveryTag" : NumberLong(1850),
			"deliveryTagSet" : true,
			"consumerTag" : "amq.ctag-lL4ywmvhbux_KrYpINiARw",
			"consumerQueue" : "esr.queue.audit.raised",
			"receivedDeliveryMode" : "PERSISTENT",
			"finalRetryForMessageWithNoId" : false,
			"publishSequenceNumber" : NumberLong(0),
			"lastInBatch" : false
		},
		"created" : ISODate("2020-06-26T14:52:19.529Z"),
		"_class" : "com.hee.tis.esr.esraudit.domain.AuditMessage"
	}
]

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

use audit;

db.auditmessage.aggregate([ 
  {$match:{
    'messageProperties.headers.z-exception-type':{$exists:1},
    "created":{$gt:new ISODate("2020-06-30T00:00:00.000Z")} 
  }}, 
  {$limit:1}
]).toArray();

Example Mongo Queries

General Stuff

Useful Queries

Audit Messages