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 4 months, 1 week ago.

  • 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.

    • This reply was modified 4 months, 2 weeks ago by Profile photo of brian catlin brian catlin.
  • #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]

    • #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]

      ——–

    • #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]

  • #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

    ————

  • #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"

  • #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.

  • #48370
    Profile photo of brian catlin
    brian catlin
    Participant

    I found a work around to this but I would still like to understand what I am not understanding lol...

    here is my work around:

    ———————-

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

    ———————-

    it will print out useraccountcontrol attributes which you then have to know what they are. here is a list of what those mean below:

    UserAccouhntControl Attributes

    512 – Enable Account
    544 – Account Enabled – Require user to change password at first logon
    66048 – Enabled, password never expires

    514 – Disable account
    546 – Account Disabled – Require user to change password at first logon
    66050 – Disabled, password never expires

  • #48380
    Profile photo of Dan Potter
    Dan Potter
    Participant

    A little bit ago the PS gods instituted simplified syntax. It has it's advantages but it's important to know the old way first.

    As you figured out, you can't use multiple conditions without the filter in a scriptblock. I prefer to always write it out even if I'm matching one condition and I teach it that way.

    Anywho..I think you might be looking for https://technet.microsoft.com/en-us/library/ee617247.aspx

  • #48457
    Profile photo of brian catlin
    brian catlin
    Participant

    Thanks you.

  • #48461
    Profile photo of brian catlin
    brian catlin
    Participant

    I have developed a knack to get done what needs to be done by work around and by time and effort. Thats great for bosses and the company giving them what they need. However, I will be honest that sometimes leaves a sour paste in my mouth as I am the kid that grew up wondering and asking why the sky was blue and why this and why that. I have always been full of questions and I want to understand things fully.

    Yes it feels good to solve something but there is always that "why?" in fully understanding it and that is where I am at in powershell. I would love to go study with a mentor on subjects like that but that also is hard to do and find. So I self teach through videos and books and they help alot but for example I had a kindle book "Windows PowerShell 4th edition by Jason Helmick and Mike F Robbins, and no where in that book did it show or talk about if you can do multiple -filter commands (atleast not that I remember when reading it or found using the search function on "- filter" I have all CBT Nuggets but those are not so easy to search through so I didn't try. I also tried google and could not find what I wanted.

    The nearest I came was the help which feature "get-help" but the problem was that I was using the filter to feed my data from the csv and then when I tried to add a second condition it didnt seem to work.

    It was probably more from my lack of understanding the iteration and for each loop walk through but I am not sure as I did not figure it out that way and had to come up with something different that worked. I just understand simple powershell as I did not do it all the time thats why you see my pieces of code very lean and short. I try to keep it simple so I have an idea of what is going on as I can get lost on big chunks of stuff.

    Granted every single moment I have is being dedicated right now to ICND2 and Networking as I am moving into a network position but it seems like you get to wear more hats as it saves companies money so I still get to do everything on server side and even on PC side as well.

    Anyway I love this forum you guys are great and always try to assist. Thanks again!

You must be logged in to reply to this topic.