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:
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:
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.
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:
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.
Now you can check the CSV file in Excel:
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