Video Screencast Help

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