SQL to get messageid for archived item.

[GertjanA]

Hello all, SQL guru's,

Is it possible to run a query which shows exchangemessageid, original item date/time, archived date/time

I do not need to have futher info, only the above mentioned. Customer wants this for reassurance that what comes into the Journal Mailboxes is actually archived within 24 hours...

This is EV11.0.1 CHF5, archiving from Journal Mailboxes from Exchange 2013 CU12

[CConsult]

Hi,

actually you could try to check this with exchange/powershell.

For example check timestamp of mails with get-messageTrackingLog, to get the time they've entered the journal mailbox.You can also get the messageid with this.

Enterprise Vault has to Logon to the Journalmailbox to convert them to shortcuts, or completely delete them.

But here is the tricky part. I am not sure if you can check "last access or last change" of a mail, and I am pretty sure EV databases do not use the messageids of exchange to track them. This would make manipulation much easier.

..............

For the EV SQL way I would not know where to get the original date...

[CConsult]

Now that I think of it.... you could check if there are items in the journalmailbox that are older then one day :) and aren not of the Message Type Shortcut. 

[Rob_Wilcox1 · Accepted solution]

Message ID is not stored in EV SQL databases..

It's stored in the index for an archive though.

[GertjanA]

Hello CC/Rob,

In the Journal Mailboxes, I should see only pending. (you do not want 'archived' shortcuts in a journal mailbox :) ). I thought itemid might be messageid, but alas.

I believe customer is looking for 'proof' that what goes into Journal Mailboxes, goes into EV.

Not sure why, but customer is king. (almost). I'll do some further digging. I believe you can get the messageid shown in a DA search, that might assist.

[CConsult]

Hi Gertjan,

I am interested in a solution.

Please let me know if you have found one.

[GertjanA]

Hello CC. this might prove to be not possible in the end. as the messageid is stored in te indexes. I am now investigating getting only sender, subject, itemdate and archived date from sql.

will post what is found