• Facebook
  • RSS Feed
  • Instagram
  • LinkedIn
  • Twitter
Jun 302010
 

The other day I was asked how to do a query, or report, which shows archives which exist, but where the billing account has gone away – ie the user account in Active Directory has been deleted.

I think you see something like this in the Usage Report from the Vault Admin Console, though other people have said it says “Unknown” :

usage

So in that case I had already deleted the Active Directory account for Rahul.

I wrote the following vbscript to get this information out for usage :

‘ 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 & "rootcimv2")

            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 would need to replace the name of your Enterprise Vault Server on the line where it says “objConnection ….”

The output looks like this :

C:>cscript test4.vbs

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 – EVvaultadmin

jrnl2 – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

marcg – S-1-5-21-3254523304-3644258180-1199076991-2277 – EVmarcg

mrnew – S-1-5-21-3254523304-3644258180-1199076991-2276 – EVmrnew

pf1 – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

rahul – S-1-5-21-3254523304-3644258180-1199076991-2289 – ** Unknown **

richardg – S-1-5-21-3254523304-3644258180-1199076991-2274 – EVrichardg

Sabina Rogers – S-1-5-21-3254523304-3644258180-1199076991-1122 – EVSRogers

t123 – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

test1 – S-1-5-21-3254523304-3644258180-1199076991-2286 – EVtest1

Test2 – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

Testshared – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

Testshared2 – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

Testshared3 – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

vctest – S-1-5-21-3254523304-3644258180-1199076991-2287 – EVvctest

Wilcox, Rob – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

Wilcox2, Rob – S-1-5-21-3254523304-3644258180-1199076991-1136 – EVvaultadmin

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)