locked
Query Active AD user RRS feed

  • Question

  • Hi Everyone,

    I need to query all active AD users with the following condition:

     - Only return active users with user ID that starts with a letter follow by numbers (eg x12345) except for user ID that starts with IT follow by numbers (eg IT12345).

    - I need this info pipe into CSV format

    Thanks so much



    Tuesday, September 16, 2014 9:29 PM

Answers

  • Sam - he has 600,000 users.  Will it finish by New Years?

    ¯\_(ツ)_/¯

    You're right, filtering is built into the cmdlet, how's:

    Get-ADUser -Filter { name -like '^[a-zA-Z]\d+' } | Export-CSV .\myUsers.csv -NoTypeInformation
    unless you want to find a way to read user objects one at a time..


    Sam Boutros, Senior Consultant, Software Logic, KOP, PA http://superwidgets.wordpress.com (Please take a moment to Vote as Helpful and/or Mark as Answer, where applicable) _________________________________________________________________________________ Powershell: Learn it before it's an emergency http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx

    • Marked as answer by phathuynh27 Monday, September 22, 2014 11:05 AM
    Tuesday, September 16, 2014 11:38 PM
  • Thanks everyone.  So there is no simple way to query active accounts that starts with say M follow by numbers (eg.  M12345, M22222, M33333, G12345, G34567 etc..)?  I am PS newbie :).  Thanks again.

    This is the easiest for you to understand:

    Get-ADUser -Filter * |
       
    Where {$_.name -match '^[a-zA-Z]\d+' } |
           
    Export-CSV .\myUsers.csv -NoTypeInformation

    That is the easiest way.

    Other methods will work.Have you tried this method?


    ¯\_(ツ)_/¯

    • Marked as answer by phathuynh27 Monday, September 22, 2014 11:05 AM
    Thursday, September 18, 2014 2:41 PM
  • Hi.  The above only return 4 accounts.  There should be more than 200 accounts.

    What you asked for is a match for a name that starts with one character and then has exactly 5 numbers.  Are you sure this is what you account names are?

    Do this:

    Get-ADUser -Filter * | select name, samaccountname

    Look carefully att all of the names and see why the rules you gave us may have been incorrect.  I suspect you want samname and not name.


    ¯\_(ツ)_/¯

    • Marked as answer by phathuynh27 Monday, September 22, 2014 11:05 AM
    Saturday, September 20, 2014 4:39 PM

All replies

  • Hi,

    Okay, so what's your question? You'll need to use Get-ADUser, Where-Object, and Export-Csv.


    Don't retire TechNet! - (Don't give up yet - 13,085+ strong and growing)

    Tuesday, September 16, 2014 10:36 PM
  • Don't forget RegEx.


    ¯\_(ツ)_/¯

    Tuesday, September 16, 2014 11:03 PM
  • try:

    Get-ADUser -Filter * |
        Where { $_.name -match '^[a-zA-Z]\d+' } | 
            Export-CSV .\myUsers.csv -NoTypeInformation


    Sam Boutros, Senior Consultant, Software Logic, KOP, PA http://superwidgets.wordpress.com (Please take a moment to Vote as Helpful and/or Mark as Answer, where applicable) _________________________________________________________________________________ Powershell: Learn it before it's an emergency http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx


    • Edited by Sam Boutros Tuesday, September 16, 2014 11:15 PM
    Tuesday, September 16, 2014 11:14 PM
  • Sam - he has 600,000 users.  Will it finish by New Years?

    ¯\_(ツ)_/¯

    Tuesday, September 16, 2014 11:25 PM
  • Sam - he has 600,000 users.  Will it finish by New Years?

    ¯\_(ツ)_/¯

    You're right, filtering is built into the cmdlet, how's:

    Get-ADUser -Filter { name -like '^[a-zA-Z]\d+' } | Export-CSV .\myUsers.csv -NoTypeInformation
    unless you want to find a way to read user objects one at a time..


    Sam Boutros, Senior Consultant, Software Logic, KOP, PA http://superwidgets.wordpress.com (Please take a moment to Vote as Helpful and/or Mark as Answer, where applicable) _________________________________________________________________________________ Powershell: Learn it before it's an emergency http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx

    • Marked as answer by phathuynh27 Monday, September 22, 2014 11:05 AM
    Tuesday, September 16, 2014 11:38 PM
  • I don't think so. "like" only supports wildcards.

    http://www.computerperformance.co.uk/powershell/powershell-get-aduser-filter.htm

    Here is the limit of the syntax for that:

    Syntax:
    The following syntax uses Backus-Naur form to show how to use the PowerShell Expression Language for this parameter.
    
    <filter> ::= "{" <FilterComponentList> "}"
    
    <FilterComponentList> ::= <FilterComponent> | <FilterComponent> <JoinOperator> <FilterComponent> | <NotOperator> <FilterComponent>
    
    <FilterComponent> ::= <attr> <FilterOperator> <value> | "(" <FilterComponent> ")"
    
    <FilterOperator> ::= "-eq" | "-le" | "-ge" | "-ne" | "-lt" | "-gt"| "-approx" | "-bor" | "-band" | "-recursivematch" | "-like" | "-notlike"
    
    <JoinOperator> ::= "-and" | "-or"
    
    <NotOperator> ::= "-not"
    
    <attr> ::= <PropertyName> | <LDAPDisplayName of the attribute>
    
    <value>::= <compare this value with an <attr> by using the specified <FilterOperator>>
    
    


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 12:01 AM
  • Thanks but this result in an empty spreadsheet.


    Wednesday, September 17, 2014 3:20 PM
  • You cannot use RegEx in an AD Command.

    You have to get all users and post filter.  It will be very slow if you have a lot of users. You may also be explain it incorrectly.

    Post some examples of User Ids.


    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 3:29 PM
  • How do you define "active" users. If you mean enabled, you should at least filter on that to reduce the resultset a bit.

    Otherwise, you could use an LDAP filter to get only "ITxxxx" users. For example:

    Get-ADUser -LDAPFilter "(&(sAMAccountName>=it)(sAMAccountName<=ita))"

    -----

    I assume by User ID you mean the "pre-Windows 2000 logon name", so I filtered on "sAMAccountName". If you mean the "Common Name" of the user, replace this with "cn". Also, this is not perfect. The filter will return all users where logon id starts with "IT" followed by any character less that "a", which means any digit, but the next characters could still be anything. So in theory you could retrieve "IT2zzz" Also, you would need to filter separately on each possible letter. To include a clause to restrict this to "enabled" users, you would add another clause as follows:

    Get-ADUser -LDAPFilter "(&(sAMAccountName>=it)(sAMAccountName<=ita)(!userAccountControl:1.2.840.113556.1.4.803:=2))"

    -----

    Also, you could filter on several letters at once by "Or"ing clauses (using the "|" symbol for "OR"). For example, to find all users where logon ID matches either "ITxx" or "Axx" or "Bxx" or "Cxx", you could use the following:

    Get-ADUser -LDAPFilter "(|((&(sAMAccountName>=it)(sAMAccountName<=ita)))((&(sAMAccountName>=a)(sAMAccountName<=aa)))((&(sAMAccountName>=b)(sAMAccountName<=ba)))((&(sAMAccountName>=c)(sAMAccountName<=ca))))"

    -----

    OK, it's ugly, but it is better than waiting until New Year's, and it should work. I am not aware of any similar PowerShell filters. Note that only the operators "=", ">=", and ">=" are allowed (besides "&", "|", and "!") in LDAP syntax filters, so you cannot use ">" or "<". You could also add back in the clause for "enabled". I am not aware of any limit to the length of the LDAPFilter. Obviously, this is more suited to a script, so you can troubleshoot until the parentheses are matched, and you can modify for different initial letters, or even keep adding to the filter.

    Edit: I guess my point is you should always filter as much as possible on the DC, to restrict the size of the resultset as much as possible, unless you have few users.


    Richard Mueller - MVP Directory Services


    Wednesday, September 17, 2014 4:24 PM
  • That's tricky but a good suggestion even if it is a bit messy.

    ¯\_(ツ)_/¯

    Wednesday, September 17, 2014 4:27 PM
  • I use "Apache Directory Studio" as a LDAP Browser to refine queries like this before adding to a PowerShell script.
    Thursday, September 18, 2014 10:01 AM
  • I would add "(&(objectCategory=person)(objectClass=user))" to the query, so it is indexed properly.

    It will improve the performance

    Here's an example of this in action that beings back active accounts that aren't expired.

    You may want to read up on userAccountControl (as this is a special bitwise operator).

    512 means "Normal Account". Normal meaning an account that isn't disabled, and has the password set to expire.

    $now = (Get-Date).ToFileTime() 
    $ldapFilter = "(&(objectCategory=person)(objectClass=user)(userAccountControl=512)(accountexpires>=$now))"
    
    Get-ADUser -ldapfilter $ldapFilter -properties displayName, mail, accountExpires -SearchBase "OU=Users,DC=Contoso,DC=com" | 
      Select-Object userPrincipalName, displayName, mail, distinguishedName, enabled, @{Expression={[datetime]::fromfiletime($_.accountExpires)};Label="AccountExpiration"} | 
        Export-CSV ".\Export.csv" -NoType


    Thursday, September 18, 2014 10:09 AM
  • I would add "(&(objectCategory=person)(objectClass=user))" to the query, so it is indexed properly.

    It will improve the performance

    Here's an example of this in action that beings back active accounts that aren't expired.

    You may want to read up on userAccountControl (as this is a special bitwise operator).

    512 means "Normal Account". Normal meaning an account that isn't disabled, and has the password set to expire.

    $now = (Get-Date).ToFileTime() 
    $ldapFilter = "(&(objectCategory=person)(objectClass=user)(userAccountControl=512)(accountexpires>=$now))"
    
    Get-ADUser -ldapfilter $ldapFilter -properties displayName, mail, accountExpires -SearchBase "OU=Users,DC=Contoso,DC=com" | 
      Select-Object userPrincipalName, displayName, mail, distinguishedName, enabled, @{Expression={[datetime]::fromfiletime($_.accountExpires)};Label="AccountExpiration"} | 
        Export-CSV ".\Export.csv" -NoType



    Unfortunately you didn't read the original question.

    ¯\_(ツ)_/¯

    Thursday, September 18, 2014 10:11 AM
  • Unfortunately you didn't read the original question.

    ¯\_(ツ)_/¯

    In what way?

    I wasn't answering the question regarding filtering what the user ID starts with.  (Which was covered by Richard).

    I was adding extra information on indexed queries, and including an full example of a basic LDAP query (as the OP may not be that familiar with the syntax).  I also included the AccountExpired option in-case these should be ignored as well.

    Thursday, September 18, 2014 11:59 AM
  • Thanks everyone.  So there is no simple way to query active accounts that starts with say M follow by numbers (eg.  M12345, M22222, M33333, G12345, G34567 etc..)?  I am PS newbie :).  Thanks again.
    Thursday, September 18, 2014 1:48 PM
  • Thanks everyone.  So there is no simple way to query active accounts that starts with say M follow by numbers (eg.  M12345, M22222, M33333, G12345, G34567 etc..)?  I am PS newbie :).  Thanks again.

    This is the easiest for you to understand:

    Get-ADUser -Filter * |
       
    Where {$_.name -match '^[a-zA-Z]\d+' } |
           
    Export-CSV .\myUsers.csv -NoTypeInformation

    That is the easiest way.

    Other methods will work.Have you tried this method?


    ¯\_(ツ)_/¯

    • Marked as answer by phathuynh27 Monday, September 22, 2014 11:05 AM
    Thursday, September 18, 2014 2:41 PM
  • Hi.  The above only return 4 accounts.  There should be more than 200 accounts.
    Saturday, September 20, 2014 4:32 PM
  • Hi.  The above only return 4 accounts.  There should be more than 200 accounts.

    What you asked for is a match for a name that starts with one character and then has exactly 5 numbers.  Are you sure this is what you account names are?

    Do this:

    Get-ADUser -Filter * | select name, samaccountname

    Look carefully att all of the names and see why the rules you gave us may have been incorrect.  I suspect you want samname and not name.


    ¯\_(ツ)_/¯

    • Marked as answer by phathuynh27 Monday, September 22, 2014 11:05 AM
    Saturday, September 20, 2014 4:39 PM
  • Possibly, you want to match $_.sAMAccountName instead of $_.name.

    Richard Mueller - MVP Directory Services

    • Proposed as answer by jrv Saturday, September 20, 2014 10:07 PM
    Saturday, September 20, 2014 5:40 PM
  • Perfect!  Thanks everyone for your help as always.  
    Monday, September 22, 2014 11:05 AM