03-25-2013 11:26 AM
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,
Solved! Go to Solution.
03-25-2013 01:01 PM
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
03-25-2013 12:02 PM
Why not just upgrade to EV 10.0.3 and use the new Mailbox Archiving Report that is automatically generated after the archive run?
03-25-2013 12:06 PM
Just curious ... which table is the field in that you require?
03-25-2013 12:24 PM
03-25-2013 01:01 PM
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
03-26-2013 06:10 AM