none
Query text file to match to the closet display name RRS feed

  • Question

  • So I have a text file of names that are similar (but don't match exactly) to the DisplayName field in AD. I want to powershell to 1) find the closet name that matches the DisplayName from each name listed in the text file and then 2) export the email address for each of those corresponding users. So far I have the following:

    Add-PSSnapin Quest.ActiveRoles.ADManagement 
    $DisplayName = get-content c:\DisplayName.txt
    $DisplayName | foreach {
        Get-QADUser -DisplayName $_ {DisplayName -like '$_'} } | Select mail | Export-Csv c:\mailaddress.csv -NoTypeInformation

    It keeps failing and I know I am missing something, but I am not sure what that something is. 

    Any help would be appreciated!!

    Thursday, April 19, 2018 10:11 PM

Answers

  • Another option to consider is Ambiguous Name Resolution (ANR). I don't know about Get-QADUser, but it is supported by Get-ADUser. ANR finds matches using displayName, cn, sn, givenName, mail, sAMAccountName and others. See this Wiki for details:

    https://social.technet.microsoft.com/wiki/contents/articles/22653.active-directory-ambiguous-name-resolution.aspx

    If you use this LDAP filter with Get-ADUser:

    -LDAPFilter "(anr=S Britensen)"
    it will find users where any of the supported attributes start with the string "S Britensen", but also where either givenName starts with "S" and sn starts with "Britensen", or givenName starts with "Britensen" and sn starts with "S". This will only help you if givenName (first name) and sn (surname or last name) are populated in your environment.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Proposed as answer by jrv Friday, April 20, 2018 2:17 PM
    • Marked as answer by Bill_StewartModerator Wednesday, May 30, 2018 6:35 PM
    Friday, April 20, 2018 2:11 PM
    Moderator

All replies

  • If you use the -like comparison operator you have to provide something with a "wild card". Otherwise you compare actually for equality. If you don't have it already in your text file you have to add it in the query ...
    {DisplayName -like '*$_*'} 


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Thursday, April 19, 2018 10:41 PM
  • Thanks!<u1:p></u1:p><o:p></o:p>

    So it look like it ran without errors this time, but it doesn't seem to find any matches because the csv file is blank that gets exported.<o:p></o:p>

    $DisplayName = get-content c:\DisplayName.txt<o:p></o:p>

    $DisplayName | foreach {Get-QADUser -DisplayName {DisplayName -like '*$_*'}} | Select mail | Export-Csv c:\mailaddress.csv -NoTypeInformation

    So an example of what is in the text file versus what is found in AD would be similar to:

    Text File: S Britensen

    <o:p></o:p>

    AD DisplayName: Sam Britensen

    OR

    Text File: Micha Semklin

    AD DisplayName: Michael Semklin (437)

    Any ideas on what else I might be able to try?

    Friday, April 20, 2018 1:14 PM
  • I don't use the Quest module, but with Get-ADUser mail is not a default property. It must be specified with the -properties parameter to be available after the pipe. This might be your problem.

    Also, the -Like operator will work in your second example, but not your first.

    {Sam Britensen -Like "*S Britensen*"}

    is $False. I can't think of any workaround for that case. Finally, DisplayName is an optional attribute of user objects in AD. It may not even have a value. Maybe you mean to use Name or cn, the Relative Distinguished Name of the user.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Friday, April 20, 2018 1:44 PM
    Moderator
  • First: Please do not paste formatted text here. Use the formatting options instead the post editor box provides. Especially for code you post. Thanks.

    You can test the functionality for your self on the PS command prompt like this:

    'Sam Britensen' -like 'S Britensen'  # will not "match"
    'Sam Britensen' -like '*S Britensen*'  # will not "match" either
    'Sam Britensen' -like '*S*Britensen*'  # will "match"

    If you have in your text file the already composed string like "S Britensen" you would have to change that first before you're able to use it for comparison.

    You could use something like this to construct the comparison pattern:

    'S Britensen' -replace '\s+','*' -replace '^(.*)$', '*$1*'


    Best regards,

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Friday, April 20, 2018 1:53 PM
    Friday, April 20, 2018 1:53 PM
  • Another option to consider is Ambiguous Name Resolution (ANR). I don't know about Get-QADUser, but it is supported by Get-ADUser. ANR finds matches using displayName, cn, sn, givenName, mail, sAMAccountName and others. See this Wiki for details:

    https://social.technet.microsoft.com/wiki/contents/articles/22653.active-directory-ambiguous-name-resolution.aspx

    If you use this LDAP filter with Get-ADUser:

    -LDAPFilter "(anr=S Britensen)"
    it will find users where any of the supported attributes start with the string "S Britensen", but also where either givenName starts with "S" and sn starts with "Britensen", or givenName starts with "Britensen" and sn starts with "S". This will only help you if givenName (first name) and sn (surname or last name) are populated in your environment.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Proposed as answer by jrv Friday, April 20, 2018 2:17 PM
    • Marked as answer by Bill_StewartModerator Wednesday, May 30, 2018 6:35 PM
    Friday, April 20, 2018 2:11 PM
    Moderator
  • If you use the -like comparison operator you have to provide something with a "wild card". Otherwise you compare actually for equality. If you don't have it already in your text file you have to add it in the query ...
    {DisplayName -like '*$_*'} 


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    This cannot ever work.  You must use quotes.

    -Filter "DisplayName -like '*$_*'"

    Without double quotes the expansion cannot work.


    \_(ツ)_/

    Friday, April 20, 2018 2:17 PM
  • ANR is the best way to get a near match that is not necessarily a substring and will work on nearly all possibilities.


    \_(ツ)_/

    Friday, April 20, 2018 2:18 PM
  • Thank you so much!!
    Friday, April 20, 2018 2:55 PM
  • Hi Cryptic Sanguine,

    The below function will perform the processing for you, if no match is found on the full string it will remove the last character and try again. It will only return the first match in each case. You'll need to add en export piece to it but I think this should do the bulk of the query for you. I've left in some write-host commands to help you find your way around what's happening. Sorry it's not very tidy, it's something I put together a while back 

    function validate($displayname){
    $match = $null
        write-host processing $displayname
    
        :loop foreach($user in $users){
    
            if($user.DisplayName -like $displayname + '*'){
                
                $match = $user.DisplayName ##change to smtp address
                break :loop
            }
    
         }
    ##loop breaks here##
        if($match){
            return($match)}
        else{
    
        if($displayname.Length -eq 1){
            $displayname = "No Match"
            write-host "no match"
            return($displayname)
        }
    
        $displayname = $displayname.TrimEnd(($displayname[($displayname.Length - 1)]))
        validate($displayname)
    }
    }
    
    ##########################################################################
    
    $DisplayNames = get-content c:\DisplayName.txt
    $users = Get-QADUser 
    
    foreach($displayname in $displaynames){
    
    $Match = validate($displayname)
    write-host $displayname matches $match
    }


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Friday, April 20, 2018 3:14 PM
  • I've learned something cool new today. Thanks.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Friday, April 20, 2018 3:39 PM
  • This cannot ever work.  You must use quotes.

    Of course as usual you're right. ;-)  That's because I didn't think while using the code from the OP.

    Thanks


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Friday, April 20, 2018 3:45 PM
  • This cannot ever work.  You must use quotes.

    Of course as usual you're right. ;-)  That's because I didn't think while using the code from the OP.

    Thanks


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    We will forgive you this time but .... ;)


    \_(ツ)_/

    Friday, April 20, 2018 3:58 PM