cancel
Showing results for 
Search instead for 
Did you mean: 

EV SQL Query to show Archive Size Info and User Mapping

shaun_whitehead
Level 3

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions

chriscolden
Level 4

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

View solution in original post

16 REPLIES 16

chriscolden
Level 4

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

shaun_whitehead
Level 3

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

chriscolden
Level 4

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

shaun_whitehead
Level 3

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
Level 6
Partner Accredited Certified
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
https://www.linkedin.com/in/alex-allen-turl-07370146

chriscolden
Level 4

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/ff730940.aspx) 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

shaun_whitehead
Level 3

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

 

Shaun

John_Santana
Level 6

Wow this is cool script :)

Thanks for sharing it here Chris.

Energy99
Level 3

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
Level 3

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
Level 3

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
Level 4
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

shaun_whitehead
Level 3

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

chriscolden
Level 4
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?

Energy99
Level 3

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

shaun_whitehead
Level 3

confirmed SQL 2005