Useful EV SQL Queries

I’m posting useful Enterprise Vault SQL Queries here, so I have them easily accessible to me.  I hope you find them useful too.

Determine the oldest & youngest items archived across all EV archives…

USE EnterpriseVaultDirectory
SELECT ArchiveName, OldestArchivedDateUTC, YoungestArchivedDateUTC
FROM EnterpriseVaultDirectory.dbo.IndexView
ORDER BY OldestArchivedDateUTC

Example:

EVoldestandyoungest

For Discovery/Documentation

List all EV Servers, Aliases, and EV Versions…

USE EnterpriseVaultDirectory
SELECT ComputerNameAlternate AS Hostname, ComputerName AS Alias, VaultSoftwareVersion AS 'EV Version'
FROM ComputerEntry
ORDER BY ComputerNameAlternate

Example:

EVservers

List all Vault Stores, and associated Archives, Databases, and SQL Servers…

USE EnterpriseVaultDirectory
SELECT VaultStoreName AS 'Vault Store', ArchiveName AS Archive, DatabaseDSN AS 'Database', SQLServer AS 'SQL Server'
FROM VaultStoreEntry vse, Archive a
WHERE vse.VaultStoreEntryId = a.VaultStoreEntryId
GROUP BY VaultStoreName, ArchiveName, DatabaseDSN, SQLServer

Example:

EVvsinfo

Determine number of mailboxes never enabled for archiving (i.e. eligible for archiving)…

USE EnterpriseVaultDirectory
SELECT COUNT(*) As 'Eligible Mbx Archives'
FROM ExchangeMailboxEntry
WHERE MbxArchivingState = 0

Example:

eveligibleforarchiving

Determine number of mailboxes being archived (i.e. active users)…

USE EnterpriseVaultDirectory
SELECT COUNT(*) As 'Active Mbx Archives'
FROM ExchangeMailboxEntry
WHERE MbxArchivingState = 1

Example:

evactivearchiving

Determine number of mailboxes no longer being archived (e.g. no longer being archived, leavers)…

USE EnterpriseVaultDirectory
SELECT COUNT(*) As 'Inactive Mbx Archives'
FROM ExchangeMailboxEntry
WHERE MbxArchivingState = 2

Example:

evdisabledarchiving

More useful queries to follow 🙂

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s