EV SQL Query to show Archive Size Info and User Mapping

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

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

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?

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

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

Shaun

Wow this is cool script :-)

Thanks for sharing it here Chris.

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'.

I edited it as i had the same and pointed to 

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