Solved

Powershell scripts that exports results to a CSV

Posted on 2013-11-18
7
673 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
7 Comments
 
LVL 24

Expert Comment

by:Mohammed Khawaja
Comment Utility
0
 

Author Comment

by:cybersi
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:cybersi
Comment Utility
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
Comment Utility
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 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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

The curse of the end user strikes again      

You’ve updated all your end user’s email signatures. Hooray! But guess what? They’re playing around with the HTML, adding stupid taglines and ruining the imagery. Find out how you can save your signatures from end users today.

Join & Write a Comment

Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
In this video we show how to create an Accepted Domain 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 >> Ac…
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…

763 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