Solved

Report in PowerShell

Posted on 2013-11-18
18
335 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now