02-22-2013 02:19 AM
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:
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?
Solved! Go to Solution.
02-22-2013 03:18 AM
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
02-22-2013 03:18 AM
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
02-22-2013 03:49 AM
Works great only concern is is reports 10000 rows but if i ran my first query it shows 13.5 K rows?
02-22-2013 04:01 AM
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
02-22-2013 04:12 AM
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?
02-22-2013 04:15 AM
02-22-2013 05:21 AM
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
02-22-2013 05:23 AM
Cheers for all your help on thie Chris you have helped no end.
Shaun
02-26-2013 04:00 PM
Wow this is cool script :)
Thanks for sharing it here Chris.
03-01-2013 07:02 AM
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'.
03-01-2013 07:07 AM
I edited it as i had the same and pointed to
[EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry] EME,
03-01-2013 07:33 AM
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'.
03-01-2013 09:20 AM
03-01-2013 09:24 AM
yeah mine was an older version which was upgraded to 9.02 about 2 years back
03-01-2013 09:31 AM
03-01-2013 11:38 AM
Thanks guys, I went over and found the differences and corrected, everything is working great.
03-06-2013 06:32 AM
confirmed SQL 2005