cancel
Showing results for 
Search instead for 
Did you mean: 

Listing archives for a particular user (backend database)

spiker
Level 4

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

8 REPLIES 8

JesusWept3
Level 6
Partner Accredited Certified

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
https://www.linkedin.com/in/alex-allen-turl-07370146

spiker
Level 4

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
Level 6
Employee Accredited

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

spiker
Level 4

thanks
i'll test this tomorrow

JesusWept3
Level 6
Partner Accredited Certified

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?

https://www.linkedin.com/in/alex-allen-turl-07370146

spiker
Level 4

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
Level 6
Employee Accredited

Spiker,

What version of Enterprise Vault are you running?

spiker
Level 4

Version 9