?
Solved

Powershell scripts that exports results to a CSV

Posted on 2013-11-18
7
Medium Priority
?
790 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
  • 3
  • 2
6 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 72

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
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 

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 72

Accepted Solution

by:
Qlemo earned 2000 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  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

What is the biggest problem in managing an exchange environment today? It is the lack of backups, disaster recovery (DR) plan, testing of the DR plan or believing that it won’t happen to us.
Organisation is organized in a pattern to flow the day to day business, every application and system is interdepended on each other and when very important “Exchange Server downtime” happened.
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

607 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