EV9 SQL query to retrieve several user info

[GertjanA]

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?

Thanks!

Gertjan

[Rob_Wilcox1]

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]

WHERE

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 · Accepted solution]

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]

INNER JOIN (

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]

Thanks Rob and Michel,

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

[GertjanA]

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!