Useful Office 365 cmdlets To Generate SharePoint Online Reports

In this post, I’ll be showing you how to use Office 365 PowerShell cmdlets to generate SharePoint Online reports from your SharePoint Online tenant. And also, I’ll be discussing certain useful cmdlets that can be used for SharePoint Online site administration. Let’s get started.
 
Note: Before we get started, please ensure that you’ve configured your PC to run SharePoint Online (Office 365) cmdlets. If not, please take a look at this article in which I’ve already written about how to configure that. Also, make sure that you’re a member of the SharePoint Online administration role in Office 365.
 
1. Get-SPOSite -Detailed
 
This command will give a detailed list of all the site collections in your SharePoint Online tenant, as shown in the screenshot below.
 
 
 
2. To get a list of SharePoint groups in your tenant.
 
Syntax: Get-SPOSite | ForEach-Object {Get-SPOSiteGroup -Site $_.Url} |Format-Table
 
Running this command will generate the results, as shown in the image below. Also, please note that this command will display the default SharePoint groups as well as the custom SharePoint groups that were created manually.
 
 
 
 
 
3. Adding a user to the Site collection administrators group.
 
Before I go ahead and show the syntax for this, let me specify the variables here, so that it will be easy for us to use these in the command.
 
$tenant = “https://vigx-admin.sharepoint.com “--> This would be my tenant URL.
 
$site = “https://vigx.sharepoint.com/teams/test” This will be the SharePoint site collection URL.
 
$user =” [email protected]” This will be the UPN for the user who will be added as the SCA.
 
Since we have already specified all the variables, let’s go ahead and run the syntax.
 
Syntax: Set-SPOUser -Site $site -LoginName [email protected] -IsSiteCollectionAdmin $true
 
Check the screenshot below for reference:
 
 
 
So, this will add the user to the SCA group of a site collection.
 
4. To get the list of users in my SharePoint Online Tenant
 
Syntax: Get-SPOSite | ForEach-Object {Get-SPOUser -Site $_.Url}
 
Running this command will display the results, as shown in the screenshot below.
 
 
5. To get a report of the users in a site, their display names, permission levels and other properties
 
Before I go ahead and execute the command for this, let me specify the $site variable for the site in question.
 
$site = “https://vigx.sharepoint.com/teams/test” This will be the SharePoint site URL
 
Note: It’s not necessary that you need to keep specifying the variables every time in a command, unless you’re planning to use a different value apart from the one specified for that variable. PowerShell will automatically store it for you till the session is live.
 
Syntax: Get-SPOUser -Site $site | select * | Format-table -Wrap -AutoSize | Out-File G:\UsersReport.txt -Force -Width 360 -Append
 
 
Running this command will generate a report, as shown in the screenshot below.
 
 
6. To get a report of the all users in your SharePoint Online Tenant, their display names, permission levels and other properties
$tenant = “https://vigx-admin.sharepoint.com “--> This would be my tenant URL
 
Syntax:
 
Get-SPOSite | ForEach-Object {Get-SPOUser -Site $_.Url} | Format-Table -Wrap -AutoSize | Out-File G:\UsersReport.txt -Force -Width 360 -Append
 
 
Note: PowerShell might throw you some errors while running this command, but that can be safely ignored.
 
Running this command will generate a report, as shown below.
 
 
If you want to export the result to a CSV file, try running the below mentioned command.
 
Command 1 : For setting the headers in the CSV file:
 
"Display Name`tLoginName`tGroups" | Out-File C:\UsersReport.csv
 
 
 
Command 2: Once you’re done executing the first line, run the below syntax to get the report in the form a CSV file.
 
Syntax:
 
Get-SPOSite | ForEach-Object {Get-SPOUser -Site $_.Url -ErrorAction SilentlyContinue | %{ $_.DisplayName + "`t" + $_.LoginName
 
+ "`t" + $_.Groups | Out-File c:\UsersReport.csv -Force -Append}}
 
 
 
This will generate a CSV file, as shown in the image below:
 
 
7. To create a new SharePoint Group in a site collection.
 
Before I mention the syntax, let’s specify the necessary variables.
 
$tenant = “https://vigx-admin.sharepoint.com” Tenant URL
 
$site = “https://vigx.sharepoint.com/teams/test “--> Site collection URL
 
$group = "Test Site Owners2" SharePoint Group Name
 
$level = "Full Control" Permission level
 
Syntax:
 
New-SPOSiteGroup -Group $group -PermissionLevels $level -Site $site

 
 
Running the above command will create a new SharePoint Group in the targeted site collection and will give the results, as shown below.
 
 
8. To create an inventory of all the SharePoint site collections in your Tenant which has the information of the Site Name, URL, Quota, compatibility level, and other information. And, export the results to a CSV file.
 
Syntax:
 
Get-SPOSite -Detailed | Export-CSV -LiteralPath G:\SiteInventory.csv -NoTypeInformation
 
 
 
Running this command will generate a CSV file in specified path, as shown in the image below.
 
 
9. To get your SharePoint Online Tenant information
 
Syntax: Get-SPOTenant
 
This will give the complete tenant information, as shown in the image below.
 
 
10. To get the list of site templates in your SharePoint Online tenant
 
Syntax: Get-SPOWebTemplate
 
Running this command will give the list of site templates in SharePoint Online.
 
 
This concludes part 1 of this article. I’ll be creating part 2 of this article where I’ll be taking you through some more SharePoint Online PowerShell cmdlets that can help us generate useful reports, and also for site administration.
 
Thanks for reading this post.
 
Happy SharePointing!!!