Custom Partition Report for Vault 10.x for Exchange

[amagewick]

Hello Everyone,

Been trying to get a solution through writing SQL query but it just isn't quite meeting my expectations.  I want a report that shows the size taken on the disk per partition.

I have tried 2 methods so far and neither one give me correct numbers.

Query 1 looked in the PartitionDB at the Saveset table
grouped by column IdPartition and sum on the column ItemSize

Query 2 looked at the fingerprint DB at the MemberTable_XXX tables
union all of them together into temp table, then grouped by column PartitionIdentity and sum by column StoreSize

I contacted support but they said the best bet would be to go here, which makes sense.

Has anyone done this successfully?

[AndrewB]

this really isnt going to come from EV. you should query it either via Windows (WMI or something) or from your storage side (LUN sizes or whatever you use.)

[JesusWept3]

Yup as Andy stated WMI is the way to go, SQL queries will be just way off the mark, not even close http://msdn.microsoft.com/en-us/library/windows/desktop/aa394592(v=vs.85).aspx

[amagewick]

Well then what is the point in all of this size data in the SQL databases?

I wanted to be able to pull something quickly from SQL, and pulling it from WMI or Windows is NOT quick at all.

[AndrewB]

your analogy is like saying you want to go to walmart to book a flight. you have to go to expedia or orbitz to book a flight because that's where flights are booked.

[JesusWept3 · Accepted solution]

It is absolutely quick!!

in fact its probably a lot quicker than doing a SUM against the messages

So the reason for the item sizes in EV is for EV Quota Usage and how much data you have taken from exchange, none of the reports will truly reflect on how much data is being used on disk because of single instancing, the size of the DVS, DVSCC, DVSSP etc

But honestly, try a query such as this in a large environment and it will take quite a while
 

SELECT  VSE.VaultStoreName "Vault Store",
        PE.PartitionName "Partition Name",
        PE.PartitionRootPath "Partition Path",
        COUNT(S.IdTransaction) "No. Archived Items",
        SUM(CAST(S.ItemSize AS BIGINT))/1024 "Size Of Items (MB)"
FROM    EnterpriseVaultDirectory.dbo.Archive A,
        EnterpriseVaultDirectory.dbo.Root R,
        EnterpriseVaultDirectory.dbo.VaultStoreEntry VSE,
        EnterpriseVaultdirectory.dbo.PartitionEntry PE,
        EVVSYourStore_1.dbo.ArchivePoint AP,
        EVVSYourStore_1.dbo.Saveset S
WHERE   S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND   AP.ArchivePointId = R.VaultEntryId
  AND   R.RootIdentity = A.RootIdentity
  AND   A.VaultStoreentryId = VSE.VaultStoreEntryId
  AND   S.IdPartition = PE.IdPartition
  AND   VSE.VaultStoreEntryId = PE.VaultStoreEntryId
GROUP BY VSE.VaultStoreName, PE.PartitionName, PE.PartitionRootPath
ORDER BY VSE.VaultStoreName

[GertjanA]

Hello, I use a powershell/wmi query as attached. Found that somewhere.

I use a txt file that lists the servers I want to query. It has builtin send function, you need to define a sender (can be anything, not necessarily an existing mailbox), the recipient, subject and smtp-server. This creates an HTML page. It looks for folders (in the script, Name like %EVIndex% (and %EVStorage%), and makes a nice HTML view.

EVindex and EVStorage are the names we use here for locations, can differ at your site.

Result file will be stored in a subfolder called OLD.

[John_Santana]

Thanks for sharing the great information and the SQL + Powershell script guys !