Script to show archives with no user accounts

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 :

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

Published 14 years ago

Version 1.0

nouseraccount.txt1 KB

Enterprise Vault

Information Governance

Reporting

Troubleshooting

Knowledge Base Article

Script to show archives with no user accounts

3 Comments