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

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

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

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

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

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

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