In the previous part I’ve explained the Arabic dynamic distribution groups, in this part we will discuss the reporting.

Reporting

First question you will get after setting up the tenant and create the users will be: I need list of all users with a specific parameter.

The main cmdlet here is Export-Csv this cmdlet can be used to export the result to the CSV file:

I need list of all licensed users

Get-MsolUser | Where-Object {$_.isLicensed -eq "TRUE"} | Export-Csv d:\licensedUsers.csv

In the above cmdlet:

1. First part we are getting all users.

2. Second part is to filter users with parameter islicensed=true

3. Export the list of users to the CSV file.

Let’s open the result file in notepad:

clip_image002

We have a lot of problems:

1. Arabic is “?????” so the file isn’t useful. The default format is ASCII so we need to make it Unicode.

2. The first line is listing the .Net Object Type.

3. Also there a lot of not needed values like the first column.

So let’ make some modification to the cmdlet:

Get-MsolUser | Where-Object {$_.isLicensed -eq "TRUE"} | Select-Object -property "UserPrincipalName" | Export-Csv -Encoding unicode d:\licensedUsers.csv -NoTypeInformation

In the above cmdlet:

1. The third part is added for the filtered users we select the following properties: UserPrincipalName and Department.

2. In the last part for the exported CSV file we set the encoding to Unicode instead of ASCII (the default value).

3. And to remove the .Net value we have added –NoTypeInformation

And the result as follows:

clip_image004

Now the file can be useful for reporting, so let’s see more examples:

I need List of all users with department “حضانة”:

Don’t forget that you will need PowerShell ISE for Arabic support (review the previous parts).

Get-MsolUser -Department "حضانة" -all | Select-Object -Property "*princip*",displayname,department |Export-Csv -Encoding unicode -Path d:\حضانة.csv -NoTypeInformation

All the attributes can be used in Select-Object so we can decide what will be exported.

clip_image005

Let’s add Exchange to the equation:

I need list of users with department “حضانة” and with mailbox size for each one:

First we will need to connect to Exchange Online as explained in the previous part (Part IV: Dynamic Distribution Group)

Let’s try the following cmdlet:

Get-MsolUser -Department "حضانة" -all | ForEach-Object {Get-MailboxStatistics -Identity $_.userprincipalname} | Select-Object -Property displayname,"lastlogo*",totalitemsize,storagelimit* | Export-Csv -Path d:\حضانة.csv -NoTypeInformation -Encoding unicode

Note the following:

1. –all in the first part to get all users, when you have large number of users.

2. Second part we executed get-mailboxstatistics which will return a lot of information.

3. Third part we will select from the returned result only lastlogon and totatitemsize and storagelimit.

Let’s check the result:

clip_image007

Last thing that of course you can open the CSV file in Excel so you have more control:

1. Open Excel first.

2. Open> browse to the file.

3. Select “Delimited”

4. Under Delimiters > select Comma > then Finish.

clip_image009

Now you can check the CSV file in Excel:

clip_image011

In this part I’ve explained how to export specific list of users with specific attributes.

Previous Parts:

Part I: Arabic Problems

Part II: Users Bulk Creation

Part III: Users Bulk Modification

Part IV: Dynamic Distribution Group