Solved

Report in PowerShell

Posted on 2013-11-18
18
339 Views
Last Modified: 2013-11-20
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?
0
Comment
Question by:Jerry Seinfield
  • 10
  • 8
18 Comments
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39658298
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
 

Author Comment

by:Jerry Seinfield
ID: 39658327
Thanks Spec

So, Should I save the code into a single script and change the names of the csv files?
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39658328
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:Jerry Seinfield
ID: 39658366
how can you import the exchange and admin tools snap in to this script?
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39658701
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
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39658731
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
 

Author Comment

by:Jerry Seinfield
ID: 39659633
i ran the script and got the error found as per attached screen shoot

Any ideas?
errorconsult.jpg
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39659653
Sorry about that. Remove the (dash) "-" infront of the Pause command on line 7. That should do it.
0
 

Author Comment

by:Jerry Seinfield
ID: 39659690
Another error, please see attached file
AnotherError.jpg
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39659755
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
 

Author Comment

by:Jerry Seinfield
ID: 39659903
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
 
LVL 53

Accepted Solution

by:
Will Szymkowski earned 500 total points
ID: 39660081
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
 

Author Comment

by:Jerry Seinfield
ID: 39660167
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
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39660240
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
 

Author Comment

by:Jerry Seinfield
ID: 39660272
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
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39660571
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
 

Author Comment

by:Jerry Seinfield
ID: 39660594
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
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39660652
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question