11-09-2015 09:40 AM
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
11-09-2015 11:16 AM
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
11-10-2015 12:59 PM
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
11-10-2015 04:23 PM
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
11-10-2015 07:58 PM
11-10-2015 08:01 PM