Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

EV9 SQL query to retrieve several user info

Created: 03 Apr 2013 • Updated: 04 Apr 2013 | 4 comments
GertjanA's picture
This issue has been solved. See solution.

Hello all,

I need to find archives I can delete, based on a list delivered by the customer.
The only usefull criteria to find these archives I have from this list is the MbxNTUser value from the ExchangeMailboxEntry table.

Ideally, I'd like to create a query to create the following list:

ArchiveName, StorageServer, Exchange server, Items in Archive, Archive size.
This list should be based on the value of MBXNtUser obviously.

I am in no way an SQL expert. I can read a query, but creating one is beyond my skills at the moment. Unfortunately the DBA's are not willing to assist.

I have seen several bits and pieces I can use, but ideally i'd like to have one query to get me that information. Does anyone have such a query at hand perhaps? If it takes to much time, could you then perhaps point me to what I need to use to 'link' the different tables to get the related information?



Operating Systems:

Comments 4 CommentsJump to latest comment

Rob.Wilcox's picture

How about this:

SELECT  [MbxNTUser], [ArchiveName], 

( Select Top 1 ComputerName 

 From [EnterpriseVaultDirectory].[dbo].[ArchiveView], [EnterpriseVaultDirectory].[dbo].[view_vaultstore_StorageServer]

 Where [EnterpriseVaultDirectory].[dbo].[ArchiveView].ArchiveName = [ArchiveName] AND

   [EnterpriseVaultDirectory].[dbo].[view_vaultstore_StorageServer].VaultStoreEntryID = [EnterpriseVaultDirectory].[dbo].[ArchiveView].vaultstoreentryid

) AS [Storage Server],

( select top 1 ExchangeComputer

from [EnterpriseVaultDirectory].[dbo].[ArchiveView], [EnterpriseVaultDirectory].[dbo].view_ExchangeMailbox_By_Archive

where [EnterpriseVaultDirectory].[dbo].[ArchiveView].ArchiveName = [EnterpriseVaultDirectory].[dbo].view_ExchangeMailbox_By_Archive.MbxDisplayName

)  as  [ExchangeServer],

ArchivedItems, CAST(ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize

FROM view_ListVaults, [EnterpriseVaultDirectory].[dbo].[ArchiveView], [EnterpriseVaultDirectory].[dbo].[Root], [EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry]


view_ListVaults.ArchivePointId = [EnterpriseVaultDirectory].[dbo].[ArchiveView].[VaultEntryId] AND

[EnterpriseVaultDirectory].[dbo].[ArchiveView].[RootIdentity] = [EnterpriseVaultDirectory].[dbo].[Root].[RootIdentity] AND

[EnterpriseVaultDirectory].[dbo].[Root].[VaultEntryId] = [EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry].[DefaultVaultId] 
It does assume that the ArchiveName and MbxDisplayName are the same.
MichelZ's picture

or this one:

select ArchiveName, MbxNtUser, ComputerNameAlternate, ExchangeComputer, ItemCount, ItemSize from

(select r2.vaultentryid, sum(ItemCount) as ItemCount, sum(ItemSize) as ItemSize from EnterpriseVaultDirectory.dbo.[Root]


select vaultid, count(IdTransaction) as ItemCount, sum(itemsize) as ItemSize from saveset

inner join vault on saveset.vaultidentity = vault.vaultidentity

group by vaultid) vs

on root.VaultEntryId = vs.vaultid

left join EnterpriseVaultDirectory.dbo.root r2 on root.containerrootidentity = r2.rootidentity

where r2.containerrootidentity IS NULL

group by r2.vaultentryid) base

inner join EnterpriseVaultDirectory.dbo.root on base.vaultentryid = root.vaultentryid

inner join EnterpriseVaultDirectory.dbo.archive on root.rootidentity = archive.rootidentity

inner join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry on ExchangeMailboxEntry.DefaultVaultId = base.vaultentryid

inner join EnterpriseVaultDirectory.dbo.ExchangeMailboxStore on ExchangeMailboxStore.MbxStoreIdentity = ExchangeMailboxEntry.MbxStoreIdentity

inner join EnterpriseVaultDirectory.dbo.ExchangeServerEntry on ExchangeServerEntry.ExchangeServerIdentity = ExchangeMailboxStore.ExchangeServerIdentity

inner join EnterpriseVaultDirectory.dbo.VaultStoreEntry on VaultStoreEntry.VaultStoreEntryId = Archive.VaultStoreEntryId

inner join EnterpriseVaultDirectory.dbo.StorageServiceEntry ON StorageServiceEntry.ServiceEntryId = VaultStoreEntry.StorageServiceEntryId

inner join EnterpriseVaultDirectory.dbo.ComputerEntry ON ComputerEntry.ComputerEntryId = StorageServiceEntry.ComputerEntryId

--where mbxntuser = 'centera1'

GertjanA's picture

Thanks Rob and Michel,

I'll try both, to see which one is the fastest :-)

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS

GertjanA's picture

Rob's query is unfortunately not usable as archivenames are not always equal to displaynames.

Michel's query throws an error. I decided to use one of the 'default' queries I have, in addition to using export 'usage reports'.

Thanks for the help!

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS