query by email address not working (output is blank to csv)

This topic contains 18 replies, has 4 voices, and was last updated by Profile photo of brian catlin brian catlin 2 months ago.

Viewing 15 posts - 1 through 15 (of 19 total)
  • Author
    Posts
  • #47977
    Profile photo of brian catlin
    brian catlin
    Participant

    I have the following script:

    if I remove the select-object pipe and export-csv pipe it will output all the properties onto the screen. However it does not work with the way it is now, and the csv is blank.

    ——————————————
    $Users=Import-Csv C:\scripts\TulsaUsers.csv
    foreach($u in $Users)
    {
    Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties * | Select-Object Name,title, manager |export-csv -NoTypeInformation c:\scripts\SL.csv
    }
    ——————————————

    I have been working on networking side so instead of getting better at ps I am probably getting worse but boss asked me to come up with script to pull all users by email address in a csv and then print out there name, email, title and manager.

    #47983
    Profile photo of brian catlin
    brian catlin
    Participant

    Ok this almost works:

    =================

    $Users=Import-Csv C:\scripts\TulsaUsers.csv
    foreach($u in $Users)
    {
    Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties * | Select-Object Name, mail,title, manager
    }

    =================

    only problem is that it outputs to the screen instead of the file so I have to build it where it prints out after it reads each csv line. In addition, the manager is kind of ugly its in that format CM=name,ou=users (basically the location) but I can live with that I guess though it would be nice if there was easy trick to truncate it. I know it can be done in excel itself as well.

    maybe it needs append instead or something I am still looking.

    #47991
    Profile photo of brian catlin
    brian catlin
    Participant

    Ok I found the answer or part of it and it did have to do with appending as I was rewriting over it each time.

    new code:

    ==============
    $Users=Import-Csv C:\scripts\TulsaUsers.csv
    foreach($u in $Users)
    {
    Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties * | Select-Object Name, mail,title, manager | export-csv -NoTypeInformation c:\scripts\SL.csv -Append
    }

    ==============

    only thing I don't like is that it gives the path of the manager so a neat trick to fix that would be cool to have.

    #47994
    Profile photo of Trevor Freedland
    Trevor Freedland
    Participant

    Hi Brian, I think the issue is that you are exporting to CSV in each loop iteration.

    Try this:

    $Users=Import-Csv C:\scripts\TulsaUsers.csv
    $csv = @()
    foreach($u in $Users)
     {
        $adInfo = Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties *
        $props = @{
            "Name"=$adInfo.Name
            "Title"=$adInfo.Title
            "Manager" = $adInfo.Manager
        }
        $obj = New-Object -TypeName psobject -Property $props
        $csv += $obj
     }
    
     $csv | Export-Csv -Path c:\scripts\SL.csv -NoTypeInformation
    

    Here I am performing the query, then building a PSObject with the desired properties and adding it to an array. Then when the queries are done, the array with all of the information is exported to a csv file.

    #48008
    Profile photo of Trevor Freedland
    Trevor Freedland
    Participant

    You can extract the username from the $adInfo.Manager field using regex. See updated code below:

    $Users=Import-Csv C:\Scripts\TulsaUsers.csv
    $csv = @()
    foreach($u in $Users)
     {
        $adInfo = Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties *
        $props = @{
            "Name"=$adInfo.Name
            "Title"=$adInfo.Title
            "Manager" = [regex]::Match($adInfo.Manager,"CN=(\w+),.*").Captures.groups[1].value
        }
        $obj = New-Object -TypeName psobject -Property $props
        $csv += $obj
     }
    
     $csv | Export-Csv -Path c:\scripts\SL.csv -Force -NoTypeInformation
    

    \w is a meta character that matches any alphanumeric character (0-9,A-Z,a-z)
    + is a quantifier meaning match the preceding element 1 or more times
    Using parenthesis makes it a group that can be isolated.

    #48069
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Another variation where you can avoid the += operator and manually generating the PSObject:

    $Users=Import-Csv C:\Scripts\TulsaUsers.csv
    
    $csv = foreach($u in $Users) {
        Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties * |
        Select Name,
               Title,
               @{Name="Manager";Expression={[regex]::Match($_.Manager,"CN=(\w+),.*").Captures.groups[1].value}}
     }
    
     $csv | Export-Csv -Path c:\scripts\SL.csv -Force -NoTypeInformation
    
    #48099
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Neat but seems a bit complicated.

    $_.manager.split('=,')[1]

    #48275
    Profile photo of brian catlin
    brian catlin
    Participant

    Thanks guys.

    I will look these over I appreciate the feedback.

    #48278
    Profile photo of brian catlin
    brian catlin
    Participant

    I have a question are you able to use two filters in PowerShell?

    like on my code I had:

    ————

    $Users=Import-Csv C:\scripts\TulsaUsers.csv
    foreach($u in $Users)
    {
    Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties * | Select-Object Name, mail,title, manager | export-csv -NoTypeInformation c:\scripts\SL.csv -Append

    ——————–

    if I also wanted to filter by if the account was enabled or not. can you add two filters? -Filter 'Enabled -eq $true'

    ————

    #48292
    Profile photo of brian catlin
    brian catlin
    Participant

    Btw I tested this one below and it had no errors but the manager column was blank. It had the top header of manager and then nothing under it.

    ————

    $Users=Import-Csv C:\Scripts\TulsaUsers.csv

    $csv = foreach($u in $Users) {
    Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -properties * |
    Select Name,
    Title,
    department,
    @{Name="Manager";Expression={[regex]::Match($_.Manager,"CN=(\w+),.*").Captures.groups[1].value}}
    }

    $csv | Export-Csv -Path c:\scripts\SL.csv -Force -NoTypeInformation

    ————

    #48294
    Profile photo of brian catlin
    brian catlin
    Participant

    I am not sure how to fit this into the code so could not get this small excerpt to work either.

    #48296
    Profile photo of brian catlin
    brian catlin
    Participant

    sorry the last post was in regards to:

    ——-

    Neat but seems a bit complicated.

    $_.manager.split('=,')[1]

    ——–

    #48306
    Profile photo of brian catlin
    brian catlin
    Participant

    To be honest and scrubbing some of the information before the issue I am having is that I had to come up with a way to get my data from CSV and then I wanted to check different things on it.

    for example I was able to fix that script of mine above and made it where it would filter by the email and then list properties. ok pretty good that worked.

    However I then needed to import the csv and then find out which of the users accounts was disabled.

    so I am now going to get-help -name get-aduser -full

    and will look at get-help -name import-csv -full

    I am having to use my filter to import the csv file because they keep giving me a list of emails to work with. so I will just start at the basics. I dont hvae time to go back through entire video series again so am trying to use the help files and old scripts I have as I take parts off them and try to build what I need. Hence I dont work with powershell much and why I stopped studying but now that I have stopped studying all of a sudden they want me to do these things since I did them in the past when I was studying all of the time and I am like "arrgggg"

    #48343
    Profile photo of Dan Potter
    Dan Potter
    Participant

    $_.manager.split('=,')[1]

    replaces

    [regex]::Match($_.Manager,"CN=(\w+),.*").Captures.groups[1].value

    Enter this into a shell to see how it works. string manipulation is a crucial first step in learning powershell.

    'cn=someone,ou=somewhere,dc=yahoo,dc=com'.split('=,')[1]

    #48358
    Profile photo of brian catlin
    brian catlin
    Participant

    Right now I am trying to understand something and I had this written down from over a year ago but it does not seem right.

    I have the following syntax:

    ————————-
    SYNTAX
    Get-ADUser [-AuthType {Negotiate | Basic}] [-Credential ] [-Properties ] [-ResultPageSize ] [-ResultSetSize ] [-SearchBase ] [-SearchScope {Base | OneLevel
    | Subtree}] [-Server ] -Filter []

    Get-ADUser [-Identity] [-AuthType {Negotiate | Basic}] [-Credential ] [-Partition ] [-Properties ] [-Server ] []

    Get-ADUser [-AuthType {Negotiate | Basic}] [-Credential ] [-Properties ] [-ResultPageSize ] [-ResultSetSize ] [-SearchBase ] [-SearchScope {Base | OneLevel
    | Subtree}] [-Server ] -LDAPFilter []

    ——————–

    If you look at the parameter -filter it does not have brackets around it which means its not optional it must be there correct?

    This means -filter must be listed but only for the first ADuser listed.

    I read down and see under description that it states to get more help on -filter type get-help about_activeDirectory_Filter so I am now reading about that and its given me some clues to what I want.

    They have examples but when I try to some something slight different its not working. Notice how I try to use two conditions the first I have to use in order to get my information out of the CSV where it can be understood and then I say and hey it needs to be enabled. I dont know if its my qoutes that are messing this up or I am breaking some type of rule.

    ——————————————-MY REWRITTEN CODE—–
    $Users=Import-Csv C:\scripts\Yammer.csv
    foreach($u in $Users)
    {
    Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -and 'Enabled -eq $' -properties *| Select-Object Name, samaccountname | export-csv -NoTypeInformation c:\scripts\YAM.csv -Append
    }

    ————————————————–

    ——————ERROR————

    Get-ADUser : A parameter cannot be found that matches parameter name 'and'.
    At line:4 char:60
    + Get-ADUser -Filter "emailaddress -eq '$($u.emailaddress)'" -and 'Enabled -eq $' ...
    + ~~~~
    + CategoryInfo : InvalidArgument: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    ———————————–

    next I worked on my quotes trying to mimic the examples and I got it where I was not getting errors:

    ————

    $Users=Import-Csv C:\scripts\Yammer.csv
    foreach($u in $Users)
    {
    Get-ADUser -Filter 'emailaddress -eq "$($u.emailaddress)" -and Enabled -eq $true' -properties *| Select-Object Name, samaccountname | export-csv -NoTypeInformation c:\scripts\YAM.csv -Append
    }

    ———–

    However, now my output to CSV is blank...

    My thinking is the following:

    1. get-aduser (but I am not giving it an aduser so we use the filter to say do this by email and here is the email from the csv
    2. next part is and in addition make sure that enabled is set to true. (this has to be flawed thinking here)
    3. next list the properties
    4. last step places this information in CSV

    I need to understand why my thinking is flawed. I know there is complex ways to do things but with my existing code I am missing some key process in my thinking.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic.