07-01-2013 08:24 AM
Hello,
I need a method via SQL to list the archives belonging to a user.
Currently I am executing:
SELECT DefaultvaultId FROM ExchangeMailboxEntry where MbxNTUser = xxx
However there has been a policy in place previously where some users have had a new archive created when their existing archive exceeded a certain size. So these archives are no longer in the ExchangeMailboxEntry table.
I also tried the following but it also returns other users that I have permission to access (i.e. not just my own):
Ideally I want to execute a SQL query providing a NT Login id and retrieving all the archives assoictaed with that user (past and present).
Is this possible?
Solved! Go to Solution.
07-01-2013 02:47 PM
Try this, as it should work for what you want to do, assuming the two archives share the same Billing Owner, which by default is the Users account, except for things like Journals and Shared Archives where you manually specify them yourself.
CREATE TABLE #TempSID(SID varchar(255)) INSERT INTO #TempSID (SID) SELECT T.SID FROM Archive A, Root R, Trustee T WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity GROUP BY T.SID HAVING (COUNT(T.SID) > 1) SELECT A.ArchiveName "Archive Name", R.VaultEntryId "Archive ID", VSE.VaultStoreName "Vault Store", TS.SID FROM Archive A, Root R, VaultStoreEntry VSE, Trustee T, #TempSID TS WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity AND T.SID = TS.SID AND A.VaultStoreEntryId = VSE.VaultStoreEntryId DROP TABLE #TempSID
07-01-2013 02:47 PM
Try this, as it should work for what you want to do, assuming the two archives share the same Billing Owner, which by default is the Users account, except for things like Journals and Shared Archives where you manually specify them yourself.
CREATE TABLE #TempSID(SID varchar(255)) INSERT INTO #TempSID (SID) SELECT T.SID FROM Archive A, Root R, Trustee T WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity GROUP BY T.SID HAVING (COUNT(T.SID) > 1) SELECT A.ArchiveName "Archive Name", R.VaultEntryId "Archive ID", VSE.VaultStoreName "Vault Store", TS.SID FROM Archive A, Root R, VaultStoreEntry VSE, Trustee T, #TempSID TS WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity AND T.SID = TS.SID AND A.VaultStoreEntryId = VSE.VaultStoreEntryId DROP TABLE #TempSID
07-02-2013 02:07 AM
We have used a migration utility that has changed the billing information field to be the utlity service account therefore it doesn't represent the users nt account.
Can you think of another viable solution?
Is there a table containing historical archives that the users have previously archived to?
07-08-2013 02:17 PM
Spiker,
You can use this query to list all the archives and its MbxNTUser:
SELECT dbo.Archive.RootIdentity, dbo.Archive.VaultStoreEntryId, dbo.Archive.ArchiveName, dbo.Root.VaultEntryId, dbo.ExchangeMailboxEntry.MbxNTUser FROM dbo.ExchangeMailboxEntry INNER JOIN dbo.Trustee ON dbo.ExchangeMailboxEntry.objectSidIdentity = dbo.Trustee.TrusteeIdentity RIGHT OUTER JOIN dbo.Archive INNER JOIN dbo.Root ON dbo.Archive.RootIdentity = dbo.Root.RootIdentity ON dbo.Trustee.TrusteeIdentity = dbo.Root.OwningTrusteeIdentity WHERE dbo.Root.Type = 9 AND dbo.ExchangeMailboxEntry.MbxNTUser = 'NTUserLogin'
Just replace 'NTUserLogin' by the username you want to find. If for some reason the user was deleted from AD or the mailbox removed from the ExchangeMailboxEntry table, MbxNTUser will return NULL. In that case, you might want to remove "AND dbo.ExchangeMailboxEntry.MbxNTUser = 'NTUserLogin'" and add "AND dbo.Archive.ArchiveName LIKE '%User Name%'"to the WHERE clause.
There is also a view in the Enterprise Vault directory database called dbo.ArchiveView that you might want to take a look if you want to get more details on each archive.
Hope this helps !!
07-08-2013 02:24 PM
thanks
i'll test this tomorrow
07-08-2013 02:29 PM
Thats just a modification of your original query though, no?
I.e. it won't return the results you want because the EME entries will have been lost?
07-09-2013 12:50 AM
The EME will have an entry that references each users current default archive id. ([DefaultVaultId])
It doesn't store any history of archives that the same user may have had in the past
I need to find the archive id's of these additional archives.
The above query will not work regardless as my schema doesn't have a column
ExchangeMailboxEntry.objectSidIdentity
07-09-2013 05:53 AM
Spiker,
What version of Enterprise Vault are you running?
07-09-2013 09:16 AM
Version 9