Link to home
Start Free TrialLog in
Avatar of Ron Shorts
Ron ShortsFlag for United States of America

asked on

Powershell - getting input from CSV File

I'm trying to get a list of all users that are mail enabled and not disabled.  

I have the PS command below, but I'd like to instead of getting all users, to pull from a list of UPN names.  Any help is appreciated!

Get-ADUser -Filter {(mail -ne "null") -and (Enabled -eq "true")} -Properties Surname,GivenName,mail | Select-Object Name,Surname,GivenName,mail | Export-CSV mailusers.csv
SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ron Shorts

ASKER

oBdA - sorry, meant to get from sAMAccountName, here is what I'm using

Import-Csv -Path "C:\Temp\UPN.csv" | ForEach-Object {
      Get-ADUser -Filter "(sAMAccountName -eq '$($_.sAMAccountName)') -and (mail -ne 'null') -and (Enabled -eq 'true')" -Properties Surname, GivenName, mail | Select-Object Name, Surname, GivenName, mail
} | Export-Csv -NoTypeInformation -Path "mailusers.csv"

This is the error message I receive:

Get-ADUser : The search filter cannot be recognized
At C:\Temp\CSV-Input-MailenabledUsers.ps1:2 char:2
+     Get-ADUser -Filter "(sAMAccountName -eq '$($_.sAMAccountName)') -and (mail -ne  ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser
 
Get-ADUser : The search filter cannot be recognized
At C:\Temp\CSV-Input-MailenabledUsers.ps1:2 char:2
+     Get-ADUser -Filter "(sAMAccountName -eq '$($_.sAMAccountName)') -and (mail -ne  ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser
 
Get-ADUser : The search filter cannot be recognized
At C:\Temp\CSV-Input-MailenabledUsers.ps1:2 char:2
+     Get-ADUser -Filter "(sAMAccountName -eq '$($_.sAMAccountName)') -and (mail -ne  ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Needed the header line :)  Thank you!!
oBdA - one more question, this give me the output perfectly.  However, I'm really trying to identify which users are NOT mail enabled from the list, is there anyway to make this output the "null" or empty mail attribute?
Just change (mail -ne 'null') to (mail -eq 'null') ?! It might require to be (mail -eq '') instead, though.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Much appreciated!
oBdA -

From your script, I added some properties to get whether the account is disabled and dn for OU location, here is what I have:

Import-Csv -Path C:\Temp\meu.csv | ForEach-Object {
      Get-ADUser -Filter "(sAMAccountName -eq '$($_.sAMAccountName)')" -Properties userPrincipalName, Surname, GivenName, mail, Enabled, distinguishedName  | Select-Object userPrincipalName, Name, Surname, GivenName, mail, Enabled, distinguishedName
} | Export-CSV mailusers.csv -NoTypeInfo

From this, is there a way to list next to each user if they are a part of a certain AD group or two groups?

For example, the the next column after "distinguishedName" have two additional columns, that read the specific group "GroupA" and then another "Group B" in the column after?

Thanks again!