cancel
Showing results for 
Search instead for 
Did you mean: 
Rob_Wilcox1
Level 6
Partner

 

A colleague of mine asked about this the other day.  He said I had produced a script a long time ago which showed archives in Enterprise Vault which had no primary user account, ie they were archives where the Active Directory account had been deleted.

The script might be helpful to others, so here it is :

' Declarations

strComputer = "."
' Open connection to SQL
strQuery = "SELECT A.ArchiveName, T.SID FROM Archive A, Trustee T, Root R WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity"

' Execute Query
wscript.echo strquery
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Driver={SQL Server};server=evault1;Database=EnterpriseVaultDirectory;Trusted_Connection=yes"

objRecordset.Open strQUery, objConnection
If objRecordset.EOF Then
    Wscript.Echo "Record cannot be found."
    wscript.quit
end if

Do while not objRecordSet.Eof
' Process results
            strSid = objRecordSet("SID")
            Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
            Set objSID = objWMIService.Get("Win32_SID='" & strSID & "'")
            if objSID.AccountName <> "" then
                        WScript.Echo objRecordSet("ArchiveName") & " - " & objRecordSet("SID") & " - " & objSID.ReferencedDomainName & "\" & objSID.AccountName
            else
                        wscript.echo objRecordSet("ArchiveName") & " - " & objRecordSet("SID") & " - " & "** Unknown **"
            end if

            objRecordSet.MoveNext
Loop

' Tidy up
objRecordset.Close
objConnection.Close

You will need to modify the script so that it objConnection.Open talks to the Enterprise Vault Directory Database in your environment. [In my lab my EV server also runs SQL]

Here is some sample output when the script is run :

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

SELECT A.ArchiveName, T.SID FROM Archive A, Trustee T, Root R WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity

Abbassi, Salem - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
jrnl2 - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
marcg - S-1-5-21-3254523304-3644258180-1199076991-2277 - EV\marcg
mrnew - S-1-5-21-3254523304-3644258180-1199076991-2276 - EV\mrnew
pf1 - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
rahul - S-1-5-21-3254523304-3644258180-1199076991-2289 - ** Unknown **
richardg - S-1-5-21-3254523304-3644258180-1199076991-2274 - EV\richardg
Sabina Rogers - S-1-5-21-3254523304-3644258180-1199076991-1122 - EV\SRogers
t123 - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
test1 - S-1-5-21-3254523304-3644258180-1199076991-2286 - EV\test1
Test2 - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
Testshared - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
Testshared2 - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
Testshared3 - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
vctest - S-1-5-21-3254523304-3644258180-1199076991-2287 - EV\vctest
Wilcox, Rob - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin
Wilcox2, Rob - S-1-5-21-3254523304-3644258180-1199076991-1136 - EV\vaultadmin

Comments
Rob_Wilcox1
Level 6
Partner

Stop Press .. Stop Press ... (always wanted to write that!)

 

 

A colleague of mine (Mr P Juster) has pointed out that the SQL Query could be better.  The first enhancement he suggested is as follows :

Instead of :

strQuery = "SELECT A.ArchiveName, T.SID FROM Archive A, Trustee T, Root R WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity"

Use :

SELECT a.archivename, a.sid FROM archiveview a left outer join trustee t on T.TrusteeIdentity = a.OwningTrusteeIdentity

With this modification you will now see archives where the billing field is BLANK as well as the original issue of the billing field referencing a SID for an Active Directory account which has been deleted.

The second one, perhaps for another day, is to limit the list of archives to just mailbox archives.

rendersr
Level 5
Partner Accredited

Hi,

Looks great... This is something i'll give a try tomorrow :)

John_Santana
Level 6

thanks for sharing Rob !

Version history
Last update:
‎07-03-2011 12:19 AM
Updated by: