locked
Export users using AD attributes RRS feed

  • Question

  • Hi all
    I am trying to write the query in the below format but i am getting error, 

    In active directory in ADSIEdit, i have the below attributes for every user in every department.
    Department Number: 100
    Department: IT
    extension attribute08: |manager1|manager2|manager3|<user1>|

    I want to export all the users to csv file whose Titile or PersonalTitle in ADSIedit is "System Admin" or "System Engineer" or "System Analyst" for the department IT or for the department number 100 and which has manager2 in extension Attribute 08. I want to export their  userprincipal names and email address to csv file.

    $Export = “((extensionAttribute08 -like ‘*manager2*‘) -and (Department Number=100) -and (Department=IT) -and
    ((title -like ‘*System Admin*‘) -or (title -like ‘*System Engineer*‘) -or (title -like ‘*System Analyst*‘) -or (title -like ‘*System Administrator*‘)))”
    Get-ADUser -properties Name -ResultSetSize $NULL -Filter $Export | Select name | sort

    $Export = “((extensionAttribute08 -like ‘*manager2*‘) -and (Department Number=100) -and (Department=IT) -and
    ((title -like ‘*System*‘)))” Get-ADUser -properties Name -ResultSetSize $NULL -Filter $Export | Select name | sort

    Friday, December 13, 2019 4:40 PM

Answers

  • To make if a bit simple may be you can use below ..

    To get the direct reportees of a Manager try this

    Get-ADUser -Identity test_director -Properties directreports |
        Select-Object -ExpandProperty directreports |
        Get-ADUser -Properties mail |
        Select-Object SamAccountName, mail >c:\path.csv

    rest you can use manager2 directly

    • Marked as answer by Glenn Maxwell Tuesday, January 7, 2020 1:52 PM
    • Unmarked as answer by jrv Tuesday, January 7, 2020 2:48 PM
    • Marked as answer by Glenn Maxwell Tuesday, January 7, 2020 3:21 PM
    Friday, December 13, 2019 5:43 PM
  • To make if a bit simple may be you can use below ..

    To get the direct reportees of a Manager try this

    Get-ADUser -Identity test_director -Properties directreports |
        Select-Object -ExpandProperty directreports |
        Get-ADUser -Properties mail |
        Select-Object SamAccountName, mail >c:\path.csv

    rest you can use manager2 directly

    This answer does not create a CSV file and does not do what you asked.


    \_(ツ)_/


    • Edited by jrv Tuesday, January 7, 2020 4:02 PM
    • Marked as answer by jrv Tuesday, January 7, 2020 4:02 PM
    Tuesday, January 7, 2020 2:48 PM

All replies

  • Try this and see if that gives you the desired result

    Get-ADUser -Filter {extensionAttribute08 -Like "*manager2*"} -and (Department Number=100) -and (Department=IT) -properties extensionAttribute08, "department Number","department" >c:\path.csv

    If that works then you can do the same for Title as well and export the same.

    Kindly mark it as answer if that helped

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster.]

    Friday, December 13, 2019 5:08 PM
  • I am confused

    1. My manager is Manager2, in AD for Manager2 under organization tab i can see myname under Direct Reports.

    Below are my AD attributes

    Department Number: 100
    Department: IT
    extension attribute08: |manager1|manager2|manager3|<myname>|

    Do i need to use query like |*manager2*| or |manager2|< because i want to export users who are directs to manager2 

    2. for manager 1 i need to pull his org, under manager1 there can be many managers and many employees how can i export all their list who are under his org


    Friday, December 13, 2019 5:35 PM
  • To make if a bit simple may be you can use below ..

    To get the direct reportees of a Manager try this

    Get-ADUser -Identity test_director -Properties directreports |
        Select-Object -ExpandProperty directreports |
        Get-ADUser -Properties mail |
        Select-Object SamAccountName, mail >c:\path.csv

    rest you can use manager2 directly

    • Marked as answer by Glenn Maxwell Tuesday, January 7, 2020 1:52 PM
    • Unmarked as answer by jrv Tuesday, January 7, 2020 2:48 PM
    • Marked as answer by Glenn Maxwell Tuesday, January 7, 2020 3:21 PM
    Friday, December 13, 2019 5:43 PM
  • Thanks alot 
    for direct reports i want to export those users who has the tile System Admin,System Engineer,System Analyst,System Administrator
    will the below syntax work for me for direct reports. please correct me

    Get-ADUser -Identity manager2@contoso.com -Properties directreports, ((title -like ‘*System Admin*‘) -or (title -like ‘*System Engineer*‘) -or (title -like ‘*System Analyst*‘) -or (title -like ‘*System Administrator*‘)))”  |Select-Object -ExpandProperty directreports | Get-ADUser -Properties mail | Select-Object SamAccountName, mail >c:\path.csv

    for manager1 org will the below syntax work for me
    this is my AD attribute-->extension attribute08: |manager1|manager2|manager3|<user1>|

    $Export = “((extensionAttribute08 -like ‘*manager1*‘) -and (Department Number=100) -and (Department=IT) -and ((title -like ‘*System Admin*‘) -or (title -like ‘*System Engineer*‘) -or (title -like ‘*System Analyst*‘) -or (title -like ‘*System Administrator*‘)))” Get-ADUser -properties Name, mail -ResultSetSize $NULL -Filter $Export | Select-Object SamAccountName, mail >c:\path.csv

    or will the below syntax work for me do i need to use title or personal title and how shud i use Personal Title match

    Get-ADUser -Filter * -Properties Department, DepartmentNumber, ExtensionAttribute08, PersonalTitle |Where-Object {
        ($_.Department -like "IT" -or $_.DepartmentNumber -eq 100) -and
        ($_.ExtensionAttribute10 -match 'Manager2') -and
        ($_.PersonalTitle -match 'System Admin|System Engineer|System Analyst | System Administrator')} | Export-Csv c:\output.csv -NoTypeInformation

    if i just need to use any employees personal title with System then shud i use |*System*|

    Saturday, December 14, 2019 4:50 PM
  • Hi Experts

    Please help me with the syntax


    Tuesday, December 17, 2019 5:02 AM
  • To make if a bit simple may be you can use below ..

    To get the direct reportees of a Manager try this

    Get-ADUser -Identity test_director -Properties directreports |
        Select-Object -ExpandProperty directreports |
        Get-ADUser -Properties mail |
        Select-Object SamAccountName, mail >c:\path.csv

    rest you can use manager2 directly

    This answer does not create a CSV file and does not do what you asked.


    \_(ツ)_/


    • Edited by jrv Tuesday, January 7, 2020 4:02 PM
    • Marked as answer by jrv Tuesday, January 7, 2020 4:02 PM
    Tuesday, January 7, 2020 2:48 PM
  • To create a CSV file use Export-Csv

    help export-csv -online


    \_(ツ)_/

    Tuesday, January 7, 2020 4:02 PM
  • To create a CSV file use Export-Csv

    help export-csv -online


    \_(ツ)_/

    if we give the path as >C:\path.csv also we can get the output exported as csv without using export-csv.. tested the same and it worked fine and works fine everytime
    Tuesday, January 7, 2020 4:07 PM
  • To create a CSV file use Export-Csv

    help export-csv -online


    \_(ツ)_/

    if we give the path as >C:\path.csv also we can get the output exported as csv without using export-csv.. tested the same and it worked fine and works fine everytime

    The output is a text file. It does not have delimiters and cannot be read as a CSV. Please check again carefully. A CSV is NOT a plain text file. Some simple outputs may appear to work but actually don't work.

    To understand why this is and what a CSV file is please read the following.

    https://en.wikipedia.org/wiki/Comma-separated_values

    I objected to you marking and reasoning because it will confuse and mislead others.  Posts in tehis forum are public and are sueful for others trying to gain a clear understanding of the technology.  Bad guesses and bad posts are not helpful to others.


    \_(ツ)_/

    Tuesday, January 7, 2020 4:18 PM
  • To create a CSV file use Export-Csv

    help export-csv -online


    \_(ツ)_/

    if we give the path as >C:\path.csv also we can get the output exported as csv without using export-csv.. tested the same and it worked fine and works fine everytime

    The output is a text file. It does not have delimiters and cannot be read as a CSV. Please check again carefully. A CSV is NOT a plain text file. Some simple outputs may appear to work but actually don't work.

    To understand why this is and what a CSV file is please read the following.

    https://en.wikipedia.org/wiki/Comma-separated_values

    I objected to you marking and reasoning because it will confuse and mislead others.  Posts in tehis forum are public and are sueful for others trying to gain a clear understanding of the technology.  Bad guesses and bad posts are not helpful to others.


    \_(ツ)_/

    I just gave it a try and it worked as below


    Tuesday, January 7, 2020 4:25 PM
  • Adding a CSV extension to a file does not make it a CSV file.  CSV files are not Excel files.  Excel has been associated with files with a CSV extension. If you take any file and change the extension to CSV Excel will try to open it. A CSV file requies a header and delimiters between fields. Please read the link I posted to learn what a CSV file is.

    You are making wild assumptions because you do not have any training in data or programming so things look like they work when they don't/

    To test your file just import it.

    PS C:\scripts> get-process > test.csv
    PS C:\scripts> import-csv test.csv
    WARNING: One or more headers were not specified. Default names starting with "H" have been used in place of any missing headers.
    

    Wat you will get is a text file with one field and no header.  This will not import in any usable way,  You may be happy with this but your information is incorrect and will cause others a lot of pain if they follow your lead.


    \_(ツ)_/

    Tuesday, January 7, 2020 4:46 PM