• Facebook
  • RSS Feed
  • Instagram
  • LinkedIn
  • Twitter
Jul 032011
 

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 & “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 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 – 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

 

 

EDIT:  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.

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.

  One Response to “Script to show archives with no user account”

  1. HI ROB I adapt yours script for PowerShell.
    – Just change the the SQL Connection (if windows Authenticate just change SQLSERVER to your server)
    – Run with a user that as SQL permission.
    – Need AD cmdlets

    #############################
    ## Connect to SQL(SQLSERVER) Database (EnterpriseVaultDirectory) # I use Windows Authenticate Creditencial
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = “server=SQLSERVER;database=EnterpriseVaultDirectory;trusted_connection=true;”
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $command.CommandTimeout = 200

    ## Query SQL – Get Vault Store Partition Root Path
    ## Just top 20 ## $Command.CommandText = “SELECT TOP 20 A.ArchiveName, T.SID FROM Archive A, Trustee T, Root R WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity”
    $Command.CommandText = “SELECT A.ArchiveName, T.SID FROM Archive A, Trustee T, Root R WHERE A.RootIdentity = R.RootIdentity AND R.OwningTrusteeIdentity = T.TrusteeIdentity”
    $Results = New-Object “System.Data.DataTable”
    $Results.Load($Command.ExecuteReader())
    ## The Variable $Results has the property ArchiveName,SID of all Archive.

    ## Create Array for Out-put
    $table = New-Object System.Data.DataTable
    [void] $table.Columns.Add( ‘ArchiveName’ )
    [void] $table.Columns.Add( ‘SID’ )
    [void] $table.Columns.Add( ‘SamAccountName_SID’ )

    ## Compare AD account (From EV SID) with AD Displayname (From EV ArchiveName)
    $results | % {
    IF (!((Get-ADUser -filter “SID -eq ‘$($_.sid)'”).SamAccountName -eq (Get-ADUser -filter “Displayname -eq ‘$($_.ArchiveName)'”).SamAccountName ))
    {
    $Row = $Table.NewRow()
    $Row[‘ArchiveName’] = $_.ArchiveName
    $Row[‘SID’] = $_.SID
    $Row[‘SamAccountName_SID’] = (Get-ADUser -filter “SID -eq ‘$($_.sid)'”).SamAccountName
    $table.Rows.Add($Row)
    }
    }

    $table | Out-GridView

 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)