none
Little help with ldap filter- i have what i need just need little modifications RRS feed

  • Question

  • First off I am a newbie at powershell but I am very resourceful and was able to compile the command below to get what I want for the most part  it runs great Give me the output i want... Except certain users as myself are not listed because some of our accounts password are set to never expire. As you can see in the command below the useraccountcontrol is set to 512 which is an enabled account if I recall. I also tried 66048 which is 512+65536 where 65536 is = to accounts set to not expire. but then it only displays us with those attributes.

    Is there a way in one command like below where it can search for all users with the 512 and the 65536 useraccount control all in one command? Any other recommendations? Thank you

    get-aduser -ldapfilter "(&(&(objectCategory=person)(objectclass=user)(mail=*)(givenname=*)(sn=*)(useraccountcontrol=512)))" -Properties givenName, sn,distinguishedname,samaccountname,mail, enabled,employeeid | Select givenName, sn,distinguishedname,samaccountname,mail,division,employeeid | sort-object -property givenname | Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation

    Tuesday, June 9, 2015 8:42 PM

Answers

  • userAccountControl is an integer that is treated as a series of bits. You can query bits in an LDAP query by using a bitwise filter, as documented here:

    https://support.microsoft.com/en-us/kb/269181

    So in your case you want:


    (userAccountControl:1.2.840.113556.1.4.803:=512)(!userAccountControl:1.2.840.113557.1.4.803:=65536)
    

    That is, you want accounts with ADS_UF_NORMAL_ACCOUNT (512, 0x200) set but ADS_UF_DONT_EXPIRE_PASSWORD (65536, 0x10000) not set.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, June 9, 2015 9:06 PM
    Moderator

All replies

  • First off I am a newbie at powershell but I am very resourceful and was able to compile the command below to get what I want for the most part  it runs great Give me the output i want... Except certain users as myself are not listed because some of our accounts password are set to never expire. As you can see in the command below the useraccountcontrol is set to 512 which is an enabled account if I recall. I also tried 66048 which is 512+65536 where 65536 is = to accounts set to not expire. but then it only displays us with those attributes.

    Is there a way in one command like below where it can search for all users with the 512 and the 65536 useraccount control all in one command? Any other recommendations? Thank you

    get-aduser -ldapfilter "(&(&(objectCategory=person)(objectclass=user)(mail=*)(givenname=*)(sn=*)(useraccountcontrol=512)))" -Properties givenName, sn,distinguishedname,samaccountname,mail, enabled,employeeid | Select givenName, sn,distinguishedname,samaccountname,mail,division,employeeid | sort-object -property givenname | Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation

    • Merged by AnnaWY Thursday, June 18, 2015 5:09 AM Duplicated
    Tuesday, June 9, 2015 8:43 PM
  • userAccountControl is an integer that is treated as a series of bits. You can query bits in an LDAP query by using a bitwise filter, as documented here:

    https://support.microsoft.com/en-us/kb/269181

    So in your case you want:


    (userAccountControl:1.2.840.113556.1.4.803:=512)(!userAccountControl:1.2.840.113557.1.4.803:=65536)
    

    That is, you want accounts with ADS_UF_NORMAL_ACCOUNT (512, 0x200) set but ADS_UF_DONT_EXPIRE_PASSWORD (65536, 0x10000) not set.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, June 9, 2015 9:06 PM
    Moderator

  • Thanks for reply here is what I just ran and the output was blank also just to be clear I want users that have 512 which is normal account OR 65536 which is also accounts that are not set to expire? So is the command below accurate for that? Thanks

    get-aduser -ldapfilter "(&(&(objectCategory=person)(objectclass=user)(mail=*)(givenname=*)(sn=*)(userAccountControl:1.2.840.113556.1.4.803:=512)(!userAccountControl:1.2.840.113557.1.4.803:=65536)))" -Properties givenName, sn,distinguishedname,samaccountname,mail, enabled,employeeid | Select givenName, sn,distinguishedname,samaccountname,mail,division,employeeid | sort-object -property givenname | Export-Csv -Path c:\output1\ldapfilter11000000000000.csv -NoTypeInformation

    Tuesday, June 9, 2015 9:10 PM
  • Help Search-AdAccount -full

    https://technet.microsoft.com/en-us/library/ee617247.aspx?f=255&MSPPError=-2147217396

    Try learning before you try inventing.  It willsave you a lot of time.

    Get-AdUser does not need these: "(&-&(objectCategory=person)(objectclass=user"  Because it only looks at user accounts.

    Search-AdAccount has all of the switches you mention.

    Search-AdAccount -AccountDisabled | Get-Aduser -properties *

    Learn to format your code so it is readable.

    Here is an example:

    $props = @(
        'givenName',
        'sn',
        'distinguishedname',
        'samaccountname',
        'mail',
        'division',
        'employeeid'
    )
    $filter = "mail -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq $true"
    
    get-aduser -filter $filter -Properties $props |
        Select $props | 
        sort-object -property givenname | 
        Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation


    \_(ツ)_/




    • Edited by jrv Tuesday, June 9, 2015 9:15 PM
    Tuesday, June 9, 2015 9:14 PM
  • Search-AdAccount -user -PasswordNeverExpires | Get-Aduser


    \_(ツ)_/

    Tuesday, June 9, 2015 9:19 PM
  • TO search for multiple conditions you would need to use "-OR" or use the LDAP "|" to OR the filter conditions.

    \_(ツ)_/

    Tuesday, June 9, 2015 9:20 PM
  • Thanks for reply and again I need this to done soon i will definitely need to learn coding myself but i wont learn overnight and need this asap.

    Here is what I tried.. Again it works if i only have useraccountcontrol=512 but i want it set to where it would display users who have either useraccountcontrol-512 or 65536... Some modification to make sure the command below works would be great. Thanks

    get-aduser -ldapfilter "(&(&(objectCategory=person)(mail=*)(givenname=*)(sn=*)(useraccountcontrol=512)))" -OR -ldapfilter "(useraccountcontrol=65536)" -Properties givenName, sn,distinguishedname,samaccountname,mail, enabled,employeeid | Select givenName, sn,distinguishedname,samaccountname,mail,division,employeeid | sort-object -property givenname | Export-Csv -Path c:\output1\ldapfilterz.csv -NoTypeInformation

    Tuesday, June 9, 2015 9:59 PM
  • To add here is something i tried with the help of someone else and it worked except now for whatever reason it is displaying users from all OUs which I do not want.  So now how can i include a search base to include two OU's? For example i'll need the output to be from users in either of these two OUsCN=users,dc=example,dc=org and also OU=consultants,dc=example,dc=org


    get-aduser -ldapfilter "(&(&(objectCategory=person)(objectclass=user)(mail=*)(givenname=*)(sn=*)))" -Properties useraccountcontrol,givenName, sn,distinguishedname,samaccountname,mail, enabled,employeeid | Where-Object {($_.useraccountcontrol -eq 512 -or $_.useraccountcontrol -eq 66048)} | Select UseraccountcontrolgivenName, sn,distinguishedname,samaccountname,mail,division,employeeid | sort-object -property givenname | Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation

    Thanks<o:p></o:p>

    Tuesday, June 9, 2015 10:13 PM
  • Sorry  - I don't think any of us have time to teach you PowerShell and Active Directory overnight.  I recommend calling a consultant.

    I gave you all of the answers.  You just do not know enough about the technology to understand.  Your uesiton is also not very clear.  What is it that you are trying to do.  FDOn't use code to explain since you do not understand code.  Just explain in simple language what you want to do.


    \_(ツ)_/

    Tuesday, June 9, 2015 10:18 PM
  • I want to export all users that meet ALL of the following requirements

    1. Has a first name

    2. Has a last name

    3. Account is enabled

    4. No service accounts including calendar, test accounts etc, so I would like to specify a couple OUs to only search in 2 OUs which are CN=users,dc=example,dc=com or OU=Consultants,dc=example,dc=com

    5. has a mailbox account

    And then sort output by first name or givenname in the first column in an excel file. Again I did get it to what I wanted except accounts that have passwords that are set to never expire.

    I know I don't know much about the technology powershell am a novice and I will start learning coding myself but unfortunately I don't know that right now so any input from you or anyone would be great in the meantime so I can get this accomplished and then start learning to build own scripts from scratch myself.

    Tuesday, June 9, 2015 10:24 PM
  • That is a much bigger project that you initially posted.

    The code I posted does 90% of that.  You will need to study it to learn how and why.  Once you understand then learn the use SearchBase of Get-AdUSer to limit OUs.

    Keep trying. You will eventually learn the basics.  If you take a course or get a book it will be sooner.  Guesswork will take more than a year if you have no systems of programming experience.

    Start here: https://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx?f=255&MSPPError=-2147217396

    Do all of the examples and watch the videos.  You can become quite adept over a long weekend.


    \_(ツ)_/

    Tuesday, June 9, 2015 10:33 PM
  • Thanks for all the references.

    And speaking of your code below, I ran that and it didn't work, I don't notice any typos or anything like that. Do you mind checking it out? Thanks and here was the error when i ran it

    "

    Get-ADUser : Error parsing query: 'mail -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq True' Error Message: 'syntax error' at position: '76'.
    At line:12 char:11
    + get-aduser <<<<  -filter $filter -Properties $props |
        + CategoryInfo          : ParserError: (:) [Get-ADUser], ADFilterParsingException
        + FullyQualifiedErrorId : Error parsing query: 'mail -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq True' Error Message: 'syntax error' at position: '76'.,Microsoft.ActiveDirectory.Management.Comman 
       ds.GetADUser"

    $props = @(
        'givenName',
        'sn',
        'distinguishedname',
        'samaccountname',
        'mail',
        'division',
        'employeeid'
    )
    $filter = "mail -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq $true"

    get-aduser -filter $filter -Properties $props |
        Select $props | 
        sort-object -property givenname | 
        Export-Csv -Path c:\output1\ldapfilteronline00000000.csv -NoTypeInformation

    Tuesday, June 9, 2015 10:38 PM
  • This works for me:

    $props = @(
        'givenName',
        'sn',
        'distinguishedname',
        'samaccountname',
        'mail',
        'division',
        'employeeid'
    )
    $filter = {emailaddress -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq $true -and PassWordNeverExpires -eq $true}
    
    get-aduser -filter $filter -Properties $props |
        Select $props | 
        sort-object -property givenname | 
        Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation


    \_(ツ)_/



    • Edited by jrv Tuesday, June 9, 2015 11:07 PM
    Tuesday, June 9, 2015 11:03 PM
  • Thanks yes that code worked great, only problem is it searched for all OUs 

    The only 2 Ous i want it to search in is CN=Users and OU=Consultants so I modified your script to look like this which I thought would work but it did not,. Please see in bold below which is what I added onto your code.

    $props = @(
        'givenName',
        'sn',
        'distinguishedname',
        'samaccountname',
        'mail',
        'division',
        'employeeid'
    )
    $filter = {emailaddress -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq $true}
    'CN=Users,dc=example,dc=com','OU=Consultants,dc=example,dc=com' | ForEach-Object {
    $props.SearchBase =$_
    get-aduser -filter $filter -Properties $props |
        Select $props | 
        sort-object -property givenname | 
        Export-Csv -Path c:\output1\ldapfiltermicrosoft1.csv -NoTypeInformation
        }

    Wednesday, June 10, 2015 2:54 PM
  • $props = @(
        'givenName',
        'sn',
        'distinguishedname',
        'samaccountname',
        'mail',
        'division',
        'employeeid'
    )
    
    $filter = {emailaddress -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq $true}
    
    $ou = @(
        'CN=Users,DC=example,DC=com', 
        'OU=Consultants,DC=example,DC=com'
    )
    
    $ou | ForEach-Object {
        Get-ADUser -Filter $filter -Properties $props -SearchBase $_ |
            Select $props
    } | 
    Sort-Object -Property givenname | 
    Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation

    Wednesday, June 10, 2015 3:33 PM
  • Wow excellent, thanks to both of you and who ever else helped me out, I think I got what I need with this last edit.

    Nice stuff I really need to learn this to create on my own

    Wednesday, June 10, 2015 4:07 PM
  • Hate to ask one last thing but I will so with this last code I am trying to also include a filter for example let's say after this code runs I dont want any names that start with abc, and dfg for example in the first name how can i add that to the code i tried some things but just isn't working. If someone can help include that in the code below.

    Again here is the code below i want to also include a line that does not include first names that start with "ABC, DFG, and GHI" for example. Thanks again.

    $props = @(
        'givenName',
        'sn',
        'distinguishedname',
        'samaccountname',
        'mail',
        'division',
        'employeeid'
    )
    
    $filter = {emailaddress -like '*' -and givenname -like '*' -and sn -like '*' -and Enabled -eq $true}
    
    $ou = @(
        'CN=Users,DC=example,DC=com', 
        'OU=Consultants,DC=example,DC=com'
    )
    
    $ou | ForEach-Object {
        Get-ADUser -Filter $filter -Properties $props -SearchBase $_ |
            Select $props
    } | 
    Sort-Object -Property givenname | 
    Export-Csv -Path c:\output1\ldapfilter00000000.csv -NoTypeInformation

    Wednesday, June 10, 2015 5:23 PM
  • Forget my previous question i actually don't need to filter anything out.

    New question so with the output I have now, I need to put the data in a sql table format, is there an easy way to just run something to look at the filename it outputted from the code and format it as a sql table based on columns is reported back?

    Thanks

    Thursday, June 11, 2015 5:22 PM
  • Forget my previous question i actually don't need to filter anything out.

    New question so with the output I have now, I need to put the data in a sql table format, is there an easy way to just run something to look at the filename it outputted from the code and format it as a sql table based on columns is reported back?

    Thanks


    You need to open a new question since this question is also a completely different topic.

    \_(ツ)_/

    Thursday, June 11, 2015 5:30 PM