Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

EV SQL Query to show Archive Size Info and User Mapping

Created: 22 Feb 2013 • Updated: 22 Feb 2013 | 16 comments
This issue has been solved. See solution.

I have 2 sql queries, 1 which shows a list of all my archives and their sizes:

SELECT [ArchiveName], ArchivedItems, CAST(ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize, ModifiedDate From view_ListVaults INNER JOIN [EnterpriseVaultDirectory].[dbo].[ArchiveView] ON view_ListVaults.ArchivePointId = [EnterpriseVaultDirectory].[dbo].[ArchiveView].[VaultEntryId]

and one which shows 

Mailbox name and the excahneg info for that archive:

 

SELECT EME.MbxDisplayName "Mailbox Name",
  ESE.ExchangeComputer "Exchange Server",
       EMS.Name "Exchange Database",
       EME.AdMbxDN "AD Details",
       VSE.VaultStoreName "Vault Store"
FROM   ExchangeMailboxEntry EME,
       ExchangeMailboxStore EMS,
       ExchangeServerEntry ESE,
       Root R,
       Archive A,
       VaultStoreEntry VSE
WHERE  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
  AND  EME.DefaultVaultId = R.VaultEntryId
  AND  R.RootIdentity = A.RootIdentity
  AND  A.VaultStoreEntryId = VSE.VaultStoreEntryId
ORDER BY ESE.ExchangeComputer, EMS.Name, EME.MbxDisplayName

Ideally i would like to combine these 2 to show the archive name size or archive number of items modified data and the billing username or smtp adderss associated with the archive.

Any help please?

 

Operating Systems:

Comments 16 CommentsJump to latest comment

chriscolden's picture

Hi Shaun,

I've had a go at merging your two queries. Run the following against the vault store database you want to see the archives for. If you have multiple you will have to run this a few times.

SELECT	EME.MbxDisplayName "Mailbox Name",
		ESE.ExchangeComputer "Exchange Server",
		EMS.Name "Exchange Database",
		EME.AdMbxDN "AD Details",
		VSE.VaultStoreName "Vault Store",
		AV.ArchiveName,
		vLV.ArchivedItems,
		CAST(vLV.ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize,
		vLV.ModifiedDate
From	view_ListVaults vLV,
		[EnterpriseVaultDirectory].[dbo].[ArchiveView] AV,
		[EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry] EME,
		[EnterpriseVaultDirectory].[dbo].[ExchangeMailboxStore] EMS,
		[EnterpriseVaultDirectory].[dbo].[ExchangeServerEntry] ESE,
		[EnterpriseVaultDirectory].[dbo].[Root] R,
		[EnterpriseVaultDirectory].[dbo].[Archive] A,
		[EnterpriseVaultDirectory].[dbo].[VaultStoreEntry] VSE
WHERE	vLV.ArchivePointId = AV.VaultEntryId
		AND EME.MbxStoreIdentity = EMS.MbxStoreIdentity
		AND EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
		AND EME.DefaultVaultId = R.VaultEntryId
		AND R.RootIdentity = A.RootIdentity
		AND A.VaultStoreEntryId = VSE.VaultStoreEntryId
		AND R.RootIdentity = AV.RootIdentity

 

I hope this helps you, if it does please mark as a solution.

Many Thanks,

Chris Colden

Chris Colden

SOLUTION
shaun.whitehead's picture

Works great only concern is is reports 10000 rows but if i ran my first query it shows 13.5 K rows?

chriscolden's picture

Ok,

If you run the your first query you get 13.5K rows. What about if you run your second one? Is it 10,000 by any chance?

I think the reason for this might be that some users have got more than one archive, or you have archives which no longer have a user associated with it. Ie, the user is no longer provisioned ect.

If thats the case I know what the problem is, I will just need to make it display all rows from archive view linking the user information if it's avaiable, which shouldnt be hard.

Let me know your findings

Chris

Chris Colden

shaun.whitehead's picture

This is correct  - If you run the your first query you get 13.5K rows. What about if you run your second one? Is it 10,000 by any chance?

there are alot of orphaned archived that are due for deleted, so the diferrence is about correct.

Any chance you can get them to show?

JesusWept3's picture

The second reason is what's going on
The first query pulled from the archive table, the second pulled from the exchangemailboxentry table, however if a user leaves the company or is disabled, the vault identity that links the two is removed from EME making the query return "active" archiving users

One thing you won't get from the reports though is billing account name (you'd only get a SID) and you won't get the smtp address as that is not stored in any of the databases for ev

chriscolden's picture

Ok so here is an updated query for you. It should now show all the archives and display NULL's where there is no information for the mailbox.

I have pulled out the SID for you too, you can use PowerShell (http://technet.microsoft.com/en-us/library/ff73094...) to turn them into a username if the account still exists, otherwise you will have unknown sid's

 

SELECT EME.MbxDisplayName AS [Mailbox Name], ESE.ExchangeComputer AS [Exchange Server], EMS.Name AS [Exchange Database], EME.ADMbxDN AS [AD Details],
       T.SID AS OwningSID,
       VSE.VaultStoreName AS [Vault Store],
       AV.ArchiveName,
       vLV.ArchivedItems,
       CAST(vLV.ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize,
       vLV.ModifiedDate
FROM view_ListVaults AS vLV
LEFT OUTER JOIN EnterpriseVaultDirectory.dbo.ExchangeMailboxStore AS EMS
INNER JOIN EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME ON EMS.MbxStoreIdentity = EME.MbxStoreIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE ON EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.Root AS R ON EME.DefaultVaultId = R.VaultEntryId
INNER JOIN EnterpriseVaultDirectory.dbo.Archive AS A ON R.RootIdentity = A.RootIdentity
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry AS VSE ON A.VaultStoreEntryId = VSE.VaultStoreEntryId
RIGHT OUTER JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS AV ON R.RootIdentity = AV.RootIdentity ON vLV.ArchivePointId = AV.VaultEntryId
LEFT OUTER JOIN EnterpriseVaultDirectory.dbo.Trustee AS T ON R.OwningTrusteeIdentity = T.TrusteeIdentity
ORDER BY ESE.ExchangeComputer,
         EMS.Name,
         EME.MbxDisplayName ASC

 

I've done some small amount of testing on the above, so hopefully it will get what you want, but might be worth checking a few yourself to see it matches what you expect.

Chris

Chris Colden

shaun.whitehead's picture

Cheers for all your help on thie Chris you have helped no end.

 

Shaun

John Santana's picture

Wow this is cool script :-)

Thanks for sharing it here Chris.

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

Energy99's picture

Were running EV 8.0 SP5 and this query is exactly what I'm looking for but unfortunately I get the following error:

Msg 207, Level 16, State 1, Line 11

Invalid column name 'ExchangeServerIdentity'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'Name'.

Msg 207, Level 16, State 1, Line 18

Invalid column name 'Name'.

shaun.whitehead's picture

I edited it as i had the same and pointed to 

 

[EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry] EME,

 

 

 

SELECT EME.MbxDisplayName "Mailbox Name",
        EME.MbxNTUser "NT User",
ESE.ExchangeComputer "Exchange Server",
EMS.Name "Exchange Database",
VSE.VaultStoreName "Vault Store",
AV.ArchiveName,
vLV.ArchivedItems,
CAST(vLV.ArchivedItemsSize/1024 AS decimal(20, 0)) AS ArchivedItemsSize,
vLV.ModifiedDate,
        EME.AdMbxDN "AD Details"
From view_ListVaults vLV,
[EnterpriseVaultDirectory].[dbo].[ArchiveView] AV,
[EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry] EME,
[EnterpriseVaultDirectory].[dbo].[ExchangeMailboxStore] EMS,
[EnterpriseVaultDirectory].[dbo].[ExchangeServerEntry] ESE,
[EnterpriseVaultDirectory].[dbo].[Root] R,
[EnterpriseVaultDirectory].[dbo].[Archive] A,
[EnterpriseVaultDirectory].[dbo].[VaultStoreEntry] VSE
WHERE vLV.ArchivePointId = AV.VaultEntryId
AND EME.MbxStoreIdentity = EMS.MbxStoreIdentity
AND EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity
AND EME.DefaultVaultId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND A.VaultStoreEntryId = VSE.VaultStoreEntryId
AND R.RootIdentity = AV.RootIdentity

 

Energy99's picture

Error message is a little different now..

Msg 207, Level 16, State 1, Line 21

Invalid column name 'ExchangeServerIdentity'.

Msg 207, Level 16, State 1, Line 4

Invalid column name 'Name'.

 

 

chriscolden's picture

Hi guys,

Changes were made in the database for exchange 2010 at ev 9. I'm guessing your both running earlier versions? I'll need to spin up my ev 8 lab to correct this.I'll have a look over the weekend and post a reply on Monday.

Chris

Chris Colden

shaun.whitehead's picture

yeah mine was an older version which was upgraded to 9.02 about 2 years back

chriscolden's picture

That's odd, I was on an ev 9.0.2 database when I joint that query for you. Sql 2008 r2. Wonder if that might have been the difference. I would have thought the way you have joined it well be omitting results. What sql version are you running?

Chris Colden

Energy99's picture

Thanks guys, I went over and found the differences and corrected, everything is working great.