cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query

Ronen_Tzimbel
Level 6
Partner Accredited

Hello,
  
does anyone have an SQL query that give me the total size and number of items per archive (user) and which Provisioining group they belonging ?


Many Thanks Ronen

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

You can use the following query, but note that it will only return data for archives that have existing mailboxes. so for instance if a mailbox is deleted from exchange but you still have the archive, it will NOT report on that archive, due to the fact that it will be removed from the ExchangeMailboxEntry table and no longer be associated with a provisioning group.

Also note that you will have to modify this query and have it run against each of your vault stores, also it will take some time, returning 5,000 rows it took 1m 15 seconds to run, so if you have considerably more archives it will take considerably longer

 

SELECT  EME.MbxDisplayName "Mailbox Name",
	PTG.DisplayName "Provisioning Group",
	COUNT(S.ItemSize) "No. Items Archived",
	SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
	SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)"
		
FROM	yourVaultstore.dbo.Saveset S,
	yourVaultStore.dbo.SavesetProperty SP,
	yourVaultStore.dbo.ArchivePoint AP,
	EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
	EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
	    
WHERE	S.SavesetIdentity = SP.SavesetIdentity
  AND   S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND	AP.ArchivePointID = EME.DefaultVaultId
  AND   EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId

GROUP BY EME.MbxDisplayName, PTG.DisplayName
ORDER BY PTG.DisplayName, EME.MbxDisplayName  
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

2 REPLIES 2

MichelZ
Level 6
Partner Accredited Certified

For the Archive Size, use usage.asp

http://<evserver>/EnterpriseVault/usage.asp

 

Cheers
Michel


cloudficient - EV Migration, creators of EVComplete.

JesusWept3
Level 6
Partner Accredited Certified

You can use the following query, but note that it will only return data for archives that have existing mailboxes. so for instance if a mailbox is deleted from exchange but you still have the archive, it will NOT report on that archive, due to the fact that it will be removed from the ExchangeMailboxEntry table and no longer be associated with a provisioning group.

Also note that you will have to modify this query and have it run against each of your vault stores, also it will take some time, returning 5,000 rows it took 1m 15 seconds to run, so if you have considerably more archives it will take considerably longer

 

SELECT  EME.MbxDisplayName "Mailbox Name",
	PTG.DisplayName "Provisioning Group",
	COUNT(S.ItemSize) "No. Items Archived",
	SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
	SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)"
		
FROM	yourVaultstore.dbo.Saveset S,
	yourVaultStore.dbo.SavesetProperty SP,
	yourVaultStore.dbo.ArchivePoint AP,
	EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
	EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
	    
WHERE	S.SavesetIdentity = SP.SavesetIdentity
  AND   S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND	AP.ArchivePointID = EME.DefaultVaultId
  AND   EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId

GROUP BY EME.MbxDisplayName, PTG.DisplayName
ORDER BY PTG.DisplayName, EME.MbxDisplayName  
https://www.linkedin.com/in/alex-allen-turl-07370146