SQL queries to investigate Sitecore EXM issues

getEXMClickEvents.sql

SELECT TargetPageUrl, ContactId FROM [xdb_collection].[Interactions]
CROSS APPLY
OPENJSON([xdb_collection].[Interactions].[Events]) WITH
(
OdataType varchar(200) '$."@odata.type"',
MessageId varchar(200) '$.MessageId',
TargetPageUrl varchar(200) '$.Url'
)
WHERE
OdataType = '#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent'
AND
MessageId = '<MessageId>'  -- insert message id here
GROUP by TargetPageUrl, ContactId
ORDER by TargetpageUrl
GO

getEXMEventsFromShard.sql

SELECT * FROM [xdb_collection].[Interactions]
CROSS APPLY
OPENJSON([xdb_collection].[Interactions].[Events]) WITH
(
OdataType varchar(200) '$."@odata.type"',
MessageId varchar(200) '$.MessageId'
)
WHERE
OdataType = '#Sitecore.EmailCampaign.Model.XConnect.Events.DispatchFailedEvent' -- please insert the type of event
AND
MessageId = '<MessageId>' -- insert message here

possibleEventTypes.txt

#Sitecore.EmailCampaign.Model.XConnect.Events.EmailClickedEvent
#Sitecore.EmailCampaign.Model.XConnect.Events.EmailOpenedEvent
#Sitecore.EmailCampaign.Model.XConnect.Events.EmailSentEvent
#Sitecore.EmailCampaign.Model.XConnect.Events.UnsubscribedFromEmailEvent