[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Powershell scripts that exports results to a CSV

Posted on 2013-11-18
7
Medium Priority
?
778 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 71

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
New Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

 

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 71

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

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

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

A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
To show how to create a transport rule 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 Mail Flow >> Rules tab.:  To cr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses

834 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