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

[brian-catlin]

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.

[brian-catlin]

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.

[brian-catlin]

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.

[trevor-freedland]

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.

[trevor-freedland]

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.

[rob-simmers]

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

[dan-potter]

Neat but seems a bit complicated.

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

[brian-catlin]

Thanks guys.

I will look these over I appreciate the feedback.

[brian-catlin]

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’

---

[brian-catlin]

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

---

[brian-catlin]

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

[brian-catlin]

sorry the last post was in regards to:

---

Neat but seems a bit complicated.

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

---

[brian-catlin]

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”

[dan-potter]

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

replaces

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]

[brian-catlin]

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.

[brian-catlin]

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

[dan-potter]

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

[brian-catlin]

Thanks you.

[brian-catlin]

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!