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

Script to show archives with no user accounts

Created: 03 Jul 2011 • Updated: 05 Jul 2011 | 3 comments
Rob.Wilcox's picture
+1 1 Vote
Login to vote

 

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 3 CommentsJump to latest comment

Rob.Wilcox's picture

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.

+1
Login to vote
rendersr's picture

Hi,

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

 

0
Login to vote
John Santana's picture

thanks for sharing Rob !

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

0
Login to vote