Export EV info to CSV via SQL Query

[daksh]

Hello,
We are running Enterprise Vault 10.0.1
I am trying to export EV size information for each mailbox via SQL query, and here is what I have:

----------
SELECT A.ArchiveName,
       COUNT(S.IdTransaction) "Num. Items Archived",
       SUM(S.ItemSize)/1024 "Archived Items Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVSUMEVStore1_1.dbo.ArchivePoint AP,
       EVVSUMEVStore1_1.dbo.Saveset S,
       EVVSUMEVStore1_1.dbo.SavesetProperty SP
WHERE  S.SavesetIdentity = SP.SavesetIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
GROUP BY A.ArchiveName
----------
Above query works, but I also need SamAccountName.
Can anyone guide me?

Idea here is to script the query and export to CSV file and email the file daily...

Thank you,

[TonySterling]

Why not just upgrade to EV 10.0.3 and use the new Mailbox Archiving Report that is automatically generated after the archive run?

[Rob_Wilcox1]

Just curious ... which table is the field in that you require?

[daksh]

Rob, I do not know which table has SamAccountName, that is what I am looking for... @Tony, Thanks for the tip, did not know about automatic reporting in 10.0.3, will have to test that in our test env... Thanks!

[JesusWept3 · Accepted solution]

OK So a couple of things, really the NTUsername that you're looking for is in the ExchangeMailboxEntry table, and that won't always be present for "Orphaned" archives that no longer have an entry with in the EME table.

You could look at the billing account assigned to each archive, but if you have a single billing account assigned to multiple archives, it may skew your number, also the billing accounts held in the database are SID's, so you would have to do a lookup against AD to determine the actual name of the SID, either through an LDAP query or through an ADSI linked server in SQL

One query you could use is the following, but it will only return "active" exchange mailbox archives

SELECT EME.MbxDisplayName "Mailbox Name",
       (EME.MbxNTDomain + '\' + EME.MbxNTUser) "Username",
       COUNT(S.IdTransaction) "Num. Items Archived",
       SUM(S.ItemSize)/1024 "Archived Items Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME
       EVVSUMEVStore1_1.dbo.ArchivePoint AP,
       EVVSUMEVStore1_1.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = EME.DefaultVaultId
GROUP BY EME.MbxDisplayName

[daksh]

Hello JesusWept3, Thank you for the query, it looks good. I have a way to get "non-active" mailboxes, so this will work. Thanks!