none
Read csv or txt file to query ad... RRS feed

  • Question

  • I would like to take a list of e-mail addresses I have in a csv file and retrieve the sAMAccountName for each of the email addresses in the list.

    Can this be done?  VBS, VB, Powershell?

    Thanks,

    Dave


    Dave
    Thursday, March 24, 2011 4:33 PM

Answers

  • The script I posted compares the email address read from the file with two attributes of the user objects, the "mail" and "proxyAddresses" attributes. The "mail" attribute shows up on the in ADUC on the "General" tab of user properties, in the field labeled "E-mail". The "proxyAddresses" attribute is populated if you use Exchange. It is a multi-valued attribute (an array of values), in which each email address has a prefix, either "SMPT:" or "X400:" (upper case for the primary address, lower case for the others). That's why in the filter I look for an exact match with the "mail" attribute, but use a wildcard to specify that any value in the "proxyAddresses" attribute that ends with the specified value (no matter what the prefix).

    Oh wait, I now see my error. I used the "!" operator in my filter statement (which is the "Not" operator) instead of "|" , the "Or" operator. The filter should be:

      ' Filter on user objects with specified email address.
      strFilter = "(&(objectCategory=person)(objectClass=user)" _
        & "(|(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"

     

    Those characters are hard to distinguish at times. I apologize.

     


    Richard Mueller - MVP Directory Services
    Thursday, March 24, 2011 9:36 PM
    Moderator
  • Yep, that did the trick.  Now to just pipe it to a file.  Which I have done.

    Thanks for the help!

    Awesome!

    Dave


    Dave
    • Marked as answer by Dave Casson Monday, March 28, 2011 1:24 PM
    Friday, March 25, 2011 1:18 PM

All replies

  • Solutions could use any of the languages you list. Any script or program would need to search AD for the object(s) that have the specified email address. Here is a VBScript program that uses ADO to search AD (not tested):

    Option Explicit
      
    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strMail
    Dim strEmail, strFile, objFSO, objFile
    
    Const ForReading = 1
    
    ' Specify file name.
    strFile = "c:\scripts\emails.csv"
    
    ' Open file for reading.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)
    
    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoCommand.ActiveConnection = adoConnection
    
    ' Search entire Active Directory forest.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("rootDomainNamingContext")
    strBase = "<LDAP://" & strDNSDomain & ">"
    
    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName"
    
    ' Read each line of the file.
    Do Until objFile.AtEndOfStream
      strEmail = Trim(objFile.ReadLine)
      ' Skip blank lines.
      If (strEmail <> "") Then
        ' Filter on user objects with specified email address.
        strFilter = "(&(objectCategory=person)(objectClass=user)" _
          & "(!(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"
    
        ' Construct the LDAP syntax query.
        strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      
        adoCommand.CommandText = strQuery
        adoCommand.Properties("Page Size") = 200
        adoCommand.Properties("Timeout") = 30
        adoCommand.Properties("Cache Results") = False
      
        ' Run the query.
        Set adoRecordset = adoCommand.Execute
        ' Enumerate the resulting recordset.
    
        If adoRecordset.EOF Then
          Wscript.Echo strEmail & " no user found"
        End If
    
        Do Until adoRecordset.EOF
          ' Retrieve values and display.
          strName = adoRecordset.Fields("sAMAccountName").Value
          Wscript.Echo strEmail & ", " & strName
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
     
        Loop
        adoRecordset.Close
      End If
    Next
    
    ' Clean up.
    adoConnection.Close

     


    Richard Mueller - MVP Directory Services
    Thursday, March 24, 2011 4:54 PM
    Moderator
  • Or Powershell (needs the ActiveRoles Management Shell for Active Directory from Quest) to an Excel file from a text file list of email addresses:

    Function Show-Msgbox {
      Param([string]$message=$(Throw "You must specify a message"),
          [string]$button="okonly",
          [string]$icon="Question",
          [string]$title="Message Box"
         )
        
    # Buttons: OkOnly, OkCancel, AbortRetryIgnore, YesNoCancel, YesNo, RetryCancel
    # Icons: Critical, Question, Exclamation, Information
      [reflection.assembly]::loadwithpartialname("microsoft.visualbasic") | Out-Null
      [microsoft.visualbasic.interaction]::Msgbox($message,"$button,$icon",$title)
     }

    Function Show-Inputbox {
     Param([string]$message=$(Throw "You must enter a prompt message"),
           [string]$title="Input",
           [string]$default
           )
          
     [reflection.assembly]::loadwithpartialname("microsoft.visualbasic") | Out-Null
     [microsoft.visualbasic.interaction]::InputBox($message,$title,$default)

    }

    $a = Get-Date
    $NOW = $a.ToShortTimeString()

    #$ErrorActionPreference = "SilentlyContinue"
    $d=Show-Inputbox -message "Enter A EMAIL Address List" `
    -title "User EMAIL Address" -default "Email"

    # Create Excel.Application object
    $xl = New-Object -comobject Excel.Application
    # Show Excel
    $xl.visible = $true
    $xl.DisplayAlerts = $False
    # Create a workbook
    $wb = $xl.Workbooks.Add()
    $ws = $wb.Worksheets.Item(1)
    # Get sheets
    $ws1 = $wb.worksheets | where {$_.name -eq "sheet1"} #<------- Selects sheet 1
    $ws2 = $wb.worksheets | where {$_.name -eq "sheet2"} #<------- Selects sheet 2
    $ws3 = $wb.worksheets | where {$_.name -eq "sheet3"} #<------- Selects sheet 3
     
    # Activate sheet 2
    $ws2.activate()
    Start-Sleep 1
    # Activate sheet 3
    $ws3.activate()
    # Activate sheet 1
    $ws1.activate()
    Start-Sleep 1
    $row = 2
    $i = 1
    $ws1.Cells.Item(1, 1) = "Users Logon Nmae"
    $ws1.Cells.Item(1, 2) = "Full Name"
    $ws1.Cells.Item(1, 3) = "Type"
    $ws1.Cells.Item(1, 4) = "EMail Address"
    $range = $ws1.range("1:1")
    $range.font.bold = "true"
    $range = $ws1.range("A:A")
    $range.font.bold = "true"

    IF (Test-Path "C:\Project\$d.txt") {
    $C = Get-Content "C:\Project\$d.txt" | Measure-Object
    $strU = $C.Count
    $strUsers = Get-Content "D:\SAVtoSEP\$d.txt"
    foreach ($strUser in $strUsers) {
    $a = Get-Date
    $NOW = $a.ToShortTimeString()
    Write-Host $strUser `t$NOW `t$i " of " $strU
    #$ws1.Cells.Item($row, 1) = $strUser
    $a = Get-QADUser -email $strUser | select Name,DisplayName,type,SamAccountName
    $ws1.Cells.Item($row, 1) = $a.DisplayName
    $ws1.Cells.Item($row, 2) = $a.Name
    $ws1.Cells.Item($row, 3) = $a.Type
    $ws1.Cells.Item($row, 4) = $a.SamAccountName
    $row++
    $i++
     }
    $d = $ws1.UsedRange
    $D.EntireColumn.AutoFit()
    }



    Thursday, March 24, 2011 5:35 PM
  • Solutions could use any of the languages you list. Any script or program would need to search AD for the object(s) that have the specified email address. Here is a VBScript program that uses ADO to search AD (not tested):

    Option Explicit
    
     
    
    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strMail
    
    Dim strEmail, strFile, objFSO, objFile
    
    
    
    Const ForReading = 1
    
    
    
    ' Specify file name.
    
    strFile = "c:\scripts\emails.csv"
    
    
    
    ' Open file for reading.
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)
    
    
    
    ' Setup ADO objects.
    
    Set adoCommand = CreateObject("ADODB.Command")
    
    Set adoConnection = CreateObject("ADODB.Connection")
    
    adoConnection.Provider = "ADsDSOObject"
    
    adoConnection.Open "Active Directory Provider"
    
    Set adoCommand.ActiveConnection = adoConnection
    
    
    
    ' Search entire Active Directory forest.
    
    Set objRootDSE = GetObject("LDAP://RootDSE")
    
    strDNSDomain = objRootDSE.Get("rootDomainNamingContext")
    
    strBase = "<LDAP://" & strDNSDomain & ">"
    
    
    
    ' Comma delimited list of attribute values to retrieve.
    
    strAttributes = "sAMAccountName"
    
    
    
    ' Read each line of the file.
    
    Do Until objFile.AtEndOfStream
    
     strEmail = Trim(objFile.ReadLine)
    
     ' Skip blank lines.
    
     If (strEmail <> "") Then
    
      ' Filter on user objects with specified email address.
    
      strFilter = "(&(objectCategory=person)(objectClass=user)" _
    
       & "(!(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"
    
    
    
      ' Construct the LDAP syntax query.
    
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
    
     
    
      adoCommand.CommandText = strQuery
    
      adoCommand.Properties("Page Size") = 200
    
      adoCommand.Properties("Timeout") = 30
    
      adoCommand.Properties("Cache Results") = False
    
     
    
      ' Run the query.
    
      Set adoRecordset = adoCommand.Execute
    
      ' Enumerate the resulting recordset.
    
    
    
      If adoRecordset.EOF Then
    
       Wscript.Echo strEmail & " no user found"
    
      End If
    
    
    
      Do Until adoRecordset.EOF
    
       ' Retrieve values and display.
    
       strName = adoRecordset.Fields("sAMAccountName").Value
    
       Wscript.Echo strEmail & ", " & strName
    
       ' Move to the next record in the recordset.
    
       adoRecordset.MoveNext
    
     
    
      Loop
    
      adoRecordset.Close
    
     End If
    
    Next
    
    
    
    ' Clean up.
    
    adoConnection.Close

     


    Richard Mueller - MVP Directory Services
    Thanks for the script.  So far it is not working.  it says the user cannot be found and I copy and pasted my em

    Ok I tried this against my own account copying and pasting my email address from my account and it did not find the user.  Any ideas?

    Great script though.  I will need to pipe the results to another txt file but the primary thing is figuring out why it says a valid email cannot find the user.

    Thanks again,

    Dave


    Dave
    Thursday, March 24, 2011 6:33 PM
  • This should work in PowerShell:

    001
    002
    003
    004
    005
    006
    007
    008
    009
    010
    011
    012
    013
    $Users =  @()
    $Search = New-Object DirectoryServices.DirectorySearcher([ADSI]'')
    foreach ($Email in Get-Content "C:\scripts\email.txt")
    {
    $user = New-Object -TypeName PSObject
    $Search.Filter="(&(objectClass=User)(proxyAddresses=smtp:$Email))"
    $objUsers = $Search.FindOne()
    $SamName = ($objUsers.Properties).samaccountname[0]
    $user |Add-Member -MemberType NoteProperty -Name "Email" -Value $Email -Force
    $user |Add-Member -MemberType NoteProperty -Name "SamAccount" -Value $SamName -Force
    $Users += $user
    } 
    $Users | Export-Csv -Path "Users.csv" -NoTypeInformation

    Karl


    http://unlockpowershell.wordpress.com
    -join("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Thursday, March 24, 2011 7:41 PM
  • The script I posted compares the email address read from the file with two attributes of the user objects, the "mail" and "proxyAddresses" attributes. The "mail" attribute shows up on the in ADUC on the "General" tab of user properties, in the field labeled "E-mail". The "proxyAddresses" attribute is populated if you use Exchange. It is a multi-valued attribute (an array of values), in which each email address has a prefix, either "SMPT:" or "X400:" (upper case for the primary address, lower case for the others). That's why in the filter I look for an exact match with the "mail" attribute, but use a wildcard to specify that any value in the "proxyAddresses" attribute that ends with the specified value (no matter what the prefix).

    Oh wait, I now see my error. I used the "!" operator in my filter statement (which is the "Not" operator) instead of "|" , the "Or" operator. The filter should be:

      ' Filter on user objects with specified email address.
      strFilter = "(&(objectCategory=person)(objectClass=user)" _
        & "(|(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"

     

    Those characters are hard to distinguish at times. I apologize.

     


    Richard Mueller - MVP Directory Services
    Thursday, March 24, 2011 9:36 PM
    Moderator
  • Yep, that did the trick.  Now to just pipe it to a file.  Which I have done.

    Thanks for the help!

    Awesome!

    Dave


    Dave
    • Marked as answer by Dave Casson Monday, March 28, 2011 1:24 PM
    Friday, March 25, 2011 1:18 PM
  • Where is this script posted.  i seem to have misplaced it.  not sure how I did that but i did.

    Thanks,

    Dave


    Dave
    Tuesday, January 17, 2012 4:24 PM
  • The script I posted in this thread is not posted anywhere else. Here is the corrected script that worked for you:

     

    Option Explicit
       
    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strMail
    Dim strEmail, strFile, objFSO, objFile

    Const ForReading = 1

    ' Specify file name.
    strFile = "c:\scripts\emails.csv"

    ' Open file for reading.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory forest.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("rootDomainNamingContext")
    strBase = "<LDAP://" & strDNSDomain & ">"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName"

    ' Read each line of the file.
    Do Until objFile.AtEndOfStream
        strEmail = Trim(objFile.ReadLine)
        ' Skip blank lines.
        If (strEmail <> "") Then
            ' Filter on user objects with specified email address.
            strFilter = "(&(objectCategory=person)(objectClass=user)" _
                & "(|(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"

            ' Construct the LDAP syntax query.
            strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      
            adoCommand.CommandText = strQuery
            adoCommand.Properties("Page Size") = 200
            adoCommand.Properties("Timeout") = 30
            adoCommand.Properties("Cache Results") = False
       
            ' Run the query.
            Set adoRecordset = adoCommand.Execute
            ' Enumerate the resulting recordset.

            If adoRecordset.EOF Then
                Wscript.Echo strEmail & " no user found"
            End If

            Do Until adoRecordset.EOF
                ' Retrieve values and display.
                strName = adoRecordset.Fields("sAMAccountName").Value
                Wscript.Echo strEmail & ", " & strName
                ' Move to the next record in the recordset.
                adoRecordset.MoveNext
     
            Loop
            adoRecordset.Close
        End If
    Next

    ' Clean up.
    adoConnection.Close

    -----

     


    Richard Mueller - MVP Directory Services
    Tuesday, January 17, 2012 4:35 PM
    Moderator
  • Thanks.  For some reason it is not working now.  It runs out of memory aor goes into a Not Responding state and I cannot find my original code I modded to export to a file.  Argh.  I really appreciate you posting it again.

    Getting a timeout error on line 57 character 5 of the code I am using.  Any ideas?

    Option Explicit
     
    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strMail
    Dim strEmail, strFile, objFSO, objFile
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim fso, f
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile("C:\emailsres.txt", ForWriting, True)

    'Const ForReading = 1

    ' Specify file name.
    'strFile = "D:\test\source.txt"
    strFile = "C:\emails.csv"

    ' Open file for reading.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory forest.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("rootDomainNamingContext")
    strBase = "<LDAP://" & strDNSDomain & ">"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName"

    ' Read each line of the file.
    Do Until objFile.AtEndOfStream
      strEmail = Trim(objFile.ReadLine)
      ' Skip blank lines.
      If (strEmail <> "") Then
         ' Filter on user objects with specified email address.
      strFilter = "(&(objectCategory=person)(objectClass=user)" _
        & "(|(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"

        ' Construct the LDAP syntax query.
        strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
     
        adoCommand.CommandText = strQuery
        adoCommand.Properties("Page Size") = 200
        adoCommand.Properties("Timeout") = 30
        adoCommand.Properties("Cache Results") = False
     
        ' Run the query.
        Set adoRecordset = adoCommand.Execute
        ' Enumerate the resulting recordset.

        If adoRecordset.EOF Then
          Wscript.Echo strEmail & " no user found"
        End If

        Do Until adoRecordset.EOF
          ' Retrieve values and display.
          strName = adoRecordset.Fields("sAMAccountName").Value
          f.write strEmail & ", " & StrName & VBCRlf
          'Wscript.Echo strEmail & ", " & strName
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
     
        Loop
        adoRecordset.Close
      End If
    Loop 'Next

    ' Clean up.
    adoConnection.Close

     

    Dave


    Dave

    • Edited by Dave Casson Tuesday, January 17, 2012 5:45 PM
    Tuesday, January 17, 2012 4:40 PM
  • The script I posted in this thread is not posted anywhere else. Here is the corrected script that worked for you:

    Option Explicit
       
    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strMail
    Dim strEmail, strFile, objFSO, objFile

    Const ForReading = 1

    ' Specify file name.
    strFile = "c:\scripts\emails.csv"

    ' Open file for reading.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoCommand.ActiveConnection = adoConnection

    ' Search entire Active Directory forest.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("rootDomainNamingContext")
    strBase = "<LDAP://" & strDNSDomain & ">"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName"

    ' Read each line of the file.
    Do Until objFile.AtEndOfStream
        strEmail = Trim(objFile.ReadLine)
        ' Skip blank lines.
        If (strEmail <> "") Then
            ' Filter on user objects with specified email address.
            strFilter = "(&(objectCategory=person)(objectClass=user)" _
                & "(|(mail=" & strEmail & ")(proxyAddresses=*" & strEmail & ")))"

            ' Construct the LDAP syntax query.
            strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      
            adoCommand.CommandText = strQuery
            adoCommand.Properties("Page Size") = 200
            adoCommand.Properties("Timeout") = 30
            adoCommand.Properties("Cache Results") = False
       
            ' Run the query.
            Set adoRecordset = adoCommand.Execute
            ' Enumerate the resulting recordset.

            If adoRecordset.EOF Then
                Wscript.Echo strEmail & " no user found"
            End If

            Do Until adoRecordset.EOF
                ' Retrieve values and display.
                strName = adoRecordset.Fields("sAMAccountName").Value
                Wscript.Echo strEmail & ", " & strName
                ' Move to the next record in the recordset.
                adoRecordset.MoveNext
     
            Loop
            adoRecordset.Close
        End If
    Next

    ' Clean up.
    adoConnection.Close

    -----


    Richard Mueller - MVP Directory Services

    Hi Richard,

    When I execute this script - I get the following error: A referral was returned from the server, code: 8007202B, Source: Provider. Could not figure out how I can fix this.

    Do you have any suggestions for me?

    Thank you!


    BlueSky2010
    Please help and appreciate others by using forum features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Thursday, October 4, 2012 5:14 PM