Solved

Powershell scripts that exports results to a CSV

Posted on 2013-11-18
7
709 Views
Last Modified: 2013-12-11
Morning All experts

I need a scripts that can run against the entire exchange database that reports the full access rights of users which then exports to a csv file.

I also need the same for the send as permission.  I have the scripts to run against a individual  user but would really like to generate a report for the whole organisation.

Thanks everyone who looks at this question.  I just hope its not to hard.

Regards

Simon
0
Comment
Question by:cybersi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 

Author Comment

by:cybersi
ID: 39656898
So would it be something like this for send permission:

Get-Mailbox | Get-ADPermission | where {($_.ExtendedRights -like “*Send-As*”)} | FT –Wrap | Export-csv C:\permissions.csv

and this for full

Get-MailboxPermission –identity * | fl user, identity, accessrights| Export-csv C:\permissions.csv


??????

Thanks

Si
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39657441
cybersi, you never apply a format-* cmdlet prior to exporting. The format-* cmdlets generate text, and there is no relation to the info they are applied to. Use them only for screen output.

If you want to filter the properties to export, use select-object instead, and for checking what will be in a CSV, pipe to convertto-csv:
Get-MailboxPermission * | select User, Identity, AccessRights | convertto-csv

Open in new window

and bang, we are lost. AccessRights is an array, and you will not see the values :(
Somehow we need to unroll the array, and decide what to display. Luckily, we don't need more than just the name of the privileges, and so:
Get-MailboxPermission * | select User, Identity, @{n='AccessRights'; e={$_.AccessRights -join ","}} | convertto-csv

Open in new window

Replace ConvertTo-CSV with Export-CSV if you are satisfied.

The "Send As" part is similar. You should restrict to the properties necessary; it doesn't make sense to export the AD privileges (again, those are an array) and such.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:cybersi
ID: 39658055
Hi many thanks  for the above. I will try the above when I get in the office. It just goes to show how powerful power shell is if u know what you are doing. I would be lying if I Said I understand the syntax but I guess hopefully the more I use it eventually things will just click. I know its kinda sad but I do enjoy learning things like this. Was the same with HTML.
0
 

Author Comment

by:cybersi
ID: 39658607
Morning Qlemo

Great it works only problem is it reports back that i need to use the  "-ResultSize Unlimited" as only reports back the first 1000 entries.

with regard to the "send as" dor i just replace the 'access rights' with send as like this

Get-MailboxPermission * | select User, Identity, @{n='send as'; e={$_.sendd as -join ","}} | convertto-csv

?

Also it prompts for the path to the csv when usiing export-csv  can I just add the path at the end of the string for example export-csv c:\output.csv

?

Thanks again

Simon
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39658667
Sorry, Simon, for the sparse instructions.
You'll almost always need to add the -ResultSize Unlimited to many of the AD or Exchange related cmdlets acting on the whole set of users, because the default is to retrieve only the first 1000 elements. That is to keep from high resource consumption without intention.

The other task, getting users with "Send As" privilege, needs different commands with a similar processing.

"Combining" both requests you want to make, it looks like so:
Get-MailboxPermission * -ResultSize Unlimited |
  select User, Identity, @{n='AccessRights'; e={$_.AccessRights -join ","}} |
  Export-CSV C:\permissions.csv
Get-Mailbox -ResultSize Unlimited|
  Get-ADPermission |
  where { $_.ExtendedRights -like "*Send-As*" } |
  select-object User, Identity, InheritanceType |
  Export-csv C:\Send-As.csv 

Open in new window

Note that you can't use the same CSV file for both, as they use different objects, different structures and properties, and hence Export-CSV overwrites the file, there is no append.
To explain further why: Imagine you have an output of properties a and b, and then of properties c and d. How would you expect that to be combined? a is not c, it might be an integer while c is a string, or the like.

So always use different files for each Export-CSV.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
After hours on line I found a solution which pointed to the inherited Active Directory permissions . You have to give/allow permissions to the "Exchange trusted subsystem" for the user in the Active Directory...
In this video we show how to create an Address List in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Organization >> Ad…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

726 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