Sql Query for Pending Items.

[Tonaco_pt]

Hi All,

I'm look for a query that tells me how many pending items are in a mailbox. 

Now I parse the Full-yyMMdd-hhmm.csv files in the C:\Program Files (x86)\Enterprise Vault\Reports\Exchange Mailbox Archiving\ExchServer\Scheduled\Scheduled_YYYYMMdd_hhmm.

I like to check more then once a day because we have more then 10k users and lots of them start archive the item and move them before process ends.

We only archive at night so there are no more logs of "Scheduled" to parse and I always get the same report.

I thinks this information only stays in the mailbox, if I'm wrong does anyone know a query to find this pending Items?

Enterprise Vault version 11.0.1

[CConsult]

Hi,

you could create a MAPI session and ask for a message type "msg.type = Pending" but you will have to scan through the mailbox, thats quite an effort.

[Tonaco_pt]

Hi CConsult,

Thanks you.

I need to do this for more then 10 000 mailbox. I'm looking for something like "SELECT * FROM JournalArchive WHERE Indexcommited = '0'", but for manual archive.

[VirgilDobos]

Hi Tanaco,

I believe it is very important for you to educate the users not to move emails in archive pending state as it may lead to data corruption. You may send an email to all users conveying this information.

As far as I know, pending items are not recorded in the vault store database. If you use safety copies with the old mechanism, you could check the number of items with BackupComplete = 0 in the JournalArchive table, but I doubt this applies/helps in your case. 

The other thing you could do is to use the Pending Shortcut Timeout to reset the pending items in mailboxes every few days.

[ChrisLangevin · Accepted solution]

You can query for this, but you need to query Exchange, not SQL, because as you state, this information is kept in the mailbox. You need to search the mailbox for items with the EV pending item message class (IPM.Note.EnterpriseVault.Pending). The query looks like this if you query Exchange Web Services using PowerShell:

$ItemFilter = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.ItemSchema]::ItemClass, "IPM.Note.EnterpriseVault.Pending")
#...lots of other EWS stuff to set up the FindItems call...
$CurrentBatch = $exchService.FindItems($Folder.Id,$ItemFilter,$ItemView)

There's a lot of other setup to do both beforehand and after you get the results, and EWS limits you to searching only in a single folder, so you've got to worry about enumerating all the folders and looping through them to get the mailbox total. It's more of a pain than you'd expect.

--Chris

[Tonaco_pt]

HI ChrisLangevin,

Sorry for the delay.

Thanks for the confirmation that this  information is kept in the mailbox, and thanks for the command to search the mailbox.

I can't use this method because I need to query 10k+ mailbox, and I have mailbox with over 200 folders.

I'll continue to parse the last Archiving report.

[Tonaco_pt]

Hi VirgilDobos, sorry for the late response.

I'm trying to explain to all user, we send email and create a User Portal explain the Enterprise Vault, but we still have this problem.

[Tonaco_pt]

Hi Virgil Dobos, sorry for the late response.

I'm trying to explain to all user, we send email and create a User Portal explain the Enterprise Vault, but we still have this problem.