cancel
Showing results for 
Search instead for 
Did you mean: 

Enterprise Vault - Identify Old Archives

btrowdy1
Level 2

Does anyone have an SQL query that provides the last access date of an archive? Alternativley, if that is not available, would you know how to Query the last time an item was added into a archive?

 

I have a number of orphaned archives (with no primary billing account). A number of archives have individual accounts that have privilleges to read the archive, but I want to clean the inactive ones up.

 

Thanks

5 REPLIES 5

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

this script will give you a bunch of stats about your archives including the date the archive was last updated. just change <YourMbxVaultStoreDB> to the name of your mailbox vault store database

hope it helps!

SELECT
MbxDisplayName AS 'Mailbox',
ExchangeComputer AS 'Exchange Server',
MbxItemCount AS '#Items (Mailbox)',
VS1.ArchivedItems AS '#Items (Archive)',
MbxSize/1024 AS 'Mbx Size (MB)',
VS1.ArchivedItemsSize/1024 AS  'Archive Size(MB)',
(mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',
VS1.CreatedDate AS 'Archive Created',
VS1.ModifiedDate AS 'Archive Updated',
MbxExchangeState AS 'Exchange State'
 
FROM
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,
EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,
EnterpriseVaultDirectory.dbo.ExchangeMailboxStore AS EMS,
<YourMbxVaultStoreDB>.dbo.ArchivePoint AS VS1
 
WHERE
EME.DefaultVaultID  = VS1.ArchivePointID AND
EME.MbxArchivingState = 1 AND
EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity

btrowdy1
Level 2

Thanks AndrewB, this is definatley a huge help.

 

You seem to be a wizzard writting SQL statements, unfortunatley I am not. Any chance you can include the above query and only return results for billing accounts with unresolved SIDS?

 

Here is an example query I found online, however.... I believe this looks at all permissions on the archive, not just the unresolved billing account SIDS.

 

USE EnterpriseVaultDirectory

 SELECT A.ArchiveName, R.VaultEntryID, T.SID

 FROM Archive A, Root R, Trustee T

 WHERE A.RootIdentity = R.RootIdentity

   AND R.OwningTrusteeIdentity = T.TrusteeIdentity

   AND R.VaultEntryID NOT IN

       (SELECT EME.DefaultVaultID FROM ExchangeMailboxEntry EME, Root R WHERE R.VaultEntryID = EME.DefaultVaultID)

 

 

Thanks in advance

 

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

i didn't write that script but what you could do is comment out (or remove completely) the second to last line since EME.MbxArchivingState = 1 is only returning enabled archives. then copy the results to excel and sort them however you need to. if that doesnt help then we can take it a step further 

JimmyNeutron
Level 6
Partner Accredited
Check this tool out from the downloads section of the forum: https://www-secure.symantec.com/connect/downloads/get-all-list-all-archives-and-all-relevent-information

JimmyNeutron
Level 6
Partner Accredited
This one shows archives with no user accts: https://www-secure.symantec.com/connect/downloads/script-show-archives-no-user-accounts