Report in PowerShell

Hi Experts,

I would like to be able to run a report that can identify any user accounts that’s missing the entry of “Employee” or “Contractor” in the AD extensionattribute1field.

On same report,  I would like to see the user accounts that's missing the entry of Employee or Contractor in the Exchange customattribute1 field?

I need this information exported to a CSV. The script should read all users accounts in a domain/forest, and verify both [AD/Exchange] fields [or attributes]

Can anyone help me with this request?
Jerry SeinfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will SzymkowskiSenior Solution ArchitectCommented:
This will be a 2 step process as the commands do not accept piped info between each other...

import-module activedirectory
Add-pssnapin Microsoft.Exchange.Management.Powershell.Admin
Get-aduser -filter * -properties * | ? {$_.extensionAttribute1 -ne "Employee" -or $_.extensionAttribute1 -ne "contractor"} |
select name, samaccountname, extensionAttribute1 |
out-file "c:\Attributes.csv" -append

-pause -s5

get-mailbox -resultSize "unlimited" | ? {$_.CustomAttribute1 -ne "Employee" -or $_.CustomAttribute1 -ne "contractor" |
select name,samaccountname, CustomAttribute1 |
out-file "c:\attributes.csv" -append

Open in new window


Will.
0
Jerry SeinfieldAuthor Commented:
Thanks Spec

So, Should I save the code into a single script and change the names of the csv files?
0
Will SzymkowskiSenior Solution ArchitectCommented:
Copy and paste this into notepad and then save the file with .ps1 file extension. You can have the same csv file as this script will "append" the result and not over write them. If you want results to be in separate csv files just create 2 different csv file names and run the script.

Also make sure that if you are running this from your workstation you need to have exchange tools and admin tools install so that the snapin will install into powershell.

Will.
0
Hey MSSPs! What's your total cost of ownership?

WEBINAR: Managed security service providers often deploy & manage products from a variety of solution vendors. But is this really the best approach when it comes to saving time AND money? Join us on Aug. 15th to learn how you can improve your total cost of ownership today!

Jerry SeinfieldAuthor Commented:
how can you import the exchange and admin tools snap in to this script?
0
Will SzymkowskiSenior Solution ArchitectCommented:
The script is already to import the active directory module and exchange snapin. What I was saying was if you are running this from a workstation then you need to make sure that you have admin tools installed a long with exchange management tools as well. If you don't, the script will error out when trying to add the module/snapin.

Will.
0
Will SzymkowskiSenior Solution ArchitectCommented:
If you are still unsure about doing this you can just simply run this script directly on the exchange server. This will add the active directory module to the EMS which will then allow you to run the AD portion and the Exchange portion of the script.

Will.
0
Jerry SeinfieldAuthor Commented:
i ran the script and got the error found as per attached screen shoot

Any ideas?
errorconsult.jpg
0
Will SzymkowskiSenior Solution ArchitectCommented:
Sorry about that. Remove the (dash) "-" infront of the Pause command on line 7. That should do it.
0
Jerry SeinfieldAuthor Commented:
Another error, please see attached file
AnotherError.jpg
0
Will SzymkowskiSenior Solution ArchitectCommented:
Ahhhhh.....on line 9 change to the following...


get-mailbox -resultSize "unlimited" | ? {$_.CustomAttribute1 -ne "Employee" -or $_.CustomAttribute1 -ne "contractor"}

I forgot the "}" bracket at the end of line 9.

There you go. Hopefully no more errors!

Will.
0
Jerry SeinfieldAuthor Commented:
Will, please see the third error found. I ran the same script from a Domain controller and also from a exchange servers. same error found on both servers

In addition to this, the csv file was created, but see comments below

1. After running the script on domain controller, the results shows all users whether they have or not the employee or contractor as part of extensionattribute1 and the accounts that do not have the property set[expected results].

2. After running the script of the exchange server, is returning only the accounts where CustomAttribute1 is not set either to contract or employer which is good

Question for you

Can I get 2 different CSV files one for each search or query? I want to see the results expected for each query and see if the results are reliable

Please remember extensionAttribute1 is a field on each user's ad account properties, and CustomAttribute1 is an exchange field on each user's mailbox properties

The report only should displays AD accounts/mailboxes where those attributes are not equal to Contractor or Employee, so in theory it should report only service/test accounts or those accounts that for some reason these attributes were not properly set.

I am OK if you can show something like.

File1

name,  samaccountname, extensionAttribute1


File2

name,  samaccountname, CustomAttribute1
thirderror.jpg
0
Will SzymkowskiSenior Solution ArchitectCommented:
Error message3

1. first message is related to not being able to install the Exchange Management Snapin
2. It does not like the pause command (not sure why, you can remove it entirely)
3. The 3rd error is due to the 1st one because it did not add the snapin to the console

2 Scripts below

Active Directory Script
import-module activedirectory
Add-pssnapin Microsoft.Exchange.Management.Powershell.Admin
Get-aduser -filter * -properties * | ? {$_.extensionAttribute1 -ne "Employee" -or $_.extensionAttribute1 -ne "contractor"} |
select name, samaccountname, extensionAttribute1 |
export-csv "c:\firstfile.csv"

Open in new window


Exchange Script
get-mailbox -resultSize "unlimited" | ? {$_.CustomAttribute1 -ne "Employee" -or $_.CustomAttribute1 -ne "contractor"} |
select name,samaccountname, CustomAttribute1 |
export-csv "c:\secondfile.csv"

Open in new window


Will.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry SeinfieldAuthor Commented:
Hi WIll,

the second script did not work[run it from dc and exchange server], did not return any values and the csv is not created

please see attached file

Any ideas?
lasterror.jpg
0
Will SzymkowskiSenior Solution ArchitectCommented:
The error message stating "get-mailbox" is not a recognized command means that the snapins are not loaded. I see that you are using regular powershell for this. For the Exchange one please make sure that you are running this in the Exchange Management Shell.

Will.
0
Jerry SeinfieldAuthor Commented:
Thanks Will,

My last question

This is helpful, thank you; but is there any other field(s) that you can include that would help determine if the account is a “service account”, “mailbox account” as oppose to a “user” account ?  Can the email address be included as well?

please advise
0
Will SzymkowskiSenior Solution ArchitectCommented:
Yes you can add multiple columns if you want. You can add emailaddress column to each script.

For the Active Directory (first script)
Line 4 replace the current line of code with below
select name, samaccountname, extensionAttribute1, mail |


For the Exchange (second script)
Line 2 replace the current line of code with below
select name,samaccountname, CustomAttribute1, PrimarySmtpAddress |

Will.
0
Jerry SeinfieldAuthor Commented:
with the samaccount name we can get all the names of accounts[regular, service accounts, etc]

Is there an attribute in AD that we can use as reference to indicate the samaccountname is a service account type?
0
Will SzymkowskiSenior Solution ArchitectCommented:
For Exchange you can use the attribute "DisplayName" for the full name. For Active Directory you can use the "Name" attribute.

For more attributes use the commands below to view different attributes for each commands...

Get-aduser -identity <username> -properties * | get-member
Active Directory

Get-Mailbox -identity <username> | get-member
Exchange

Those above commands will give you all of the attributes for each respective cmdlet. Just add them to the line of code where the others are separating them with a "," (comma).

Will.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.