Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Listing archives for a particular user (backend database)

Created: 01 Jul 2013 • Updated: 31 Jan 2014 | 8 comments
This issue has been solved. See solution.

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):

SELECT t.TrusteeIdentity, a.RootIdentity, ar.ArchiveName, ar.VaultStoreEntryId FROM Trustee t 
INNER JOIN ACE a on t.TrusteeIdentity = a.TrusteeIdentity
INNER JOIN Archive ar on a.RootIdentity = ar.RootIdentity
WHERE  t.SID ='xxx' AND a.ACEType = 2
 

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?

Operating Systems:

Comments 8 CommentsJump to latest comment

JesusWept3's picture

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
SOLUTION
spiker's picture

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?

GabeV's picture

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 !!

“Success is not final, failure is not fatal: it is the courage to continue that counts.”–Winston Churchill

JesusWept3's picture

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?

spiker's picture

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

GabeV's picture

Spiker,

What version of Enterprise Vault are you running?

“Success is not final, failure is not fatal: it is the courage to continue that counts.”–Winston Churchill