Tuesday, 31 July 2018

Export OneDrive for Business Users Storage Report using Powershell

In this post, I am going to share Powershell script to find and export current storage size used by every OneDrive users, maximum storage quota and warning size of all OneDrive sites to CSV file. We can easily find OneDrive for Business (ODFB) sites storage details using SharePoint management powershell cmdlet Get-SPOSite.

Before proceed, Install and Connect SharePoint Online PowerShell Module. Run the below commands after replacing your SPO Admin site url to connect SPO Online service.
$AdminSiteURL="https://<your tenant name>-admin.sharepoint.com"
#Connect to SharePoint Online Admin Site
Connect-SPOService -Url $AdminSiteURL
The below command retrieves all personal sites and how much storage space (in MB) used by OneDrive users.
Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'" |
Select Owner, StorageUsageCurrent, Url

Export OneDrive for Business users storage size report to CSV

The below powershell script get and export all personal sites and storage details to csv file.
$Result=@()
#Get all OneDrive for Business sites
$oneDriveSites = Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'"
$oneDriveSites | ForEach-Object {
$site = $_
$Result += New-Object PSObject -property @{ 
UserName = $site.Owner
Size_inMB = $site.StorageUsageCurrent
StorageQuota_inGB = $site.StorageQuota/1024
WarningSize_inGB =  $site.StorageQuotaWarningLevel/1024
OneDriveSiteUrl = $site.URL
}
}
$Result | Select UserName, Size_inMB, StorageQuota_inGB, WarningSize_inGB, OneDriveSiteUrl |
Export-CSV "C:\\OneDrive-for-Business-Size-Report.csv" -NoTypeInformation -Encoding UTF8

CSV output of OneDrive for Business (ODFB) users storage report:

Export OneDrive Users Current Storage Size Report using Powershell

Friday, 20 July 2018

How to Reuse a Deleted SharePoint Site Name and Site Url

When you delete a SharePoint Site from Office 365, by default the deleted site will be retained in the Site Collection Recycle Bin for 93 days (Retention Period) and if you have deleted a site collection, then the site collection will be retained in Tenant Recycle Bin.

The deleted sites are automatically emptied from the Site Collection/Tenant Recycle Bin after the retention period. You can restore a deleted site before this retention period time end.

Scenario 1 :

Consider that you have deleted a team site and you want to create another site with same name and site url. In this case, you can create a site with the same name but you can’t reuse the deleted site url until the deleted site exists in Recycle Bin.

If you are creating another site collection from SharePoint Online Admin portal, then you will get the validation error The site collection already exists. Please enter a different address. . Under this error message you can also see the option Permanently delete the site collection from the recycle bin and continue., you can check this option and proceed to remove the deleted site from Recycle Bin and create a site collection with same site url.

Reuse deleted site collection url

Scenario 2:

Consider that you have a Office 365 group that named as TestO365Group and the O365 group should have a associated team site (https://o365domain.sharepoint.com/sites/TestO365Group). If you delete this O365 group, it will also delete its associated site, both the group and site will be moved into Recycle Bin. Now you can create another O365 group with same name and email alias (group email address), but it will not create the associated team site with same url and it will place a numeric number after the site name in the URL (For example: https://o365domain.sharepoint.com/sites/TestO365Group2). So you have to remove the deleted site first from Recycle Bin to have the same group site url.

Manually deleting the sites from Recycle Bin will be a tough task. So you can use the SharePoint Online Management Powerhsell cmdlet Remove-SPODeletedSite to remove the deleted sites from Recycle Bin.

Before proceed Install and Connect SharePoint Online PowerShell Module, then run the below command to remove a deleted site.
Remove-SPODeletedSite -Identity https://o365domain.sharepoint.com/sites/TestO365Group
If you don’t know the deleted site url, you can use the command Get-SPODeletedSite to list all the deleted sites with Url.

Thursday, 19 July 2018

Fix: The Call to GetCustomUI for RibbonID Microsoft.Excel.Workbook failed

Problem :

I have received the error message The Call to GetCustomUI() for RibbonID "Microsoft.Excel.Workbook" failed with the title Custom UI Runtime Error in Visual Studio Tools for Office Design-Time Adaptor for Excel when opening the Excel application. In my environment the below error comes when I open the Excel application that comes with Microsoft Office Professional Plus 2016.

Error - The Call to GetCustomUI for RibbonID Microsoft.Excel.Workbook failed

Solution :

After analyzing some time found that this error is coming due to the COM Add-In Visual Studio Tools for Office Design-Time Adaptor for Excel. So to fix/remove this error, we need to enable Developer tab and turn off the problematic COM Add-In.

Follow the below steps to disable COM Add-In :

  • In Excel, click File menu and select Options.
Step 1 - Fix The Call to GetCustomUI for RibbonID Microsoft.Excel.Workbook failed

  • In the Options menu select Customize Ribbon. In the right-hand side, you can see the Developer tab under Main Tabs, make sure that this tab is selected and click OK to save the changes.
Step 2 - Fix The Call to GetCustomUI for RibbonID Microsoft.Excel.Workbook failed

  • Now you can see the Developer tab menu. Under the Developer tab ribbon, click on COM Add-Ins
Step 3 - Fix The Call to GetCustomUI for RibbonID Microsoft.Excel.Workbook failed

  • In the COM Add-Ins window, look for the add in Visual Studio Tools of Office Design-Time Adaptor for Excel and Uncheck it (If you find multiple entries, uncheck all the entries).
Step 4 - Fix The Call to GetCustomUI for RibbonID Microsoft.Excel.Workbook failed

  • This will fix the issue, now you can close the Excel app and open it again to check the problem is solved or not.

Tuesday, 17 July 2018

Manage SharePoint Item Level Permissions using PowerShell

Occasionally we need to grant read permission for some set of users on certain document item and set edit permission to a particular user or group. To achieve this requirement, we need to add explicit permission for the particular list item. In this post I am going to share powershell scripts to add or remove item level permissions using CSOM (Client Object Model) and delete unique permissions from list item. To use CSOM in Powershell, we need to load the required Microsoft SharePoint Online SDK assembly files.

Summary:

Find a list item or set of list items :

The below powershell commands find a file item by its name, if you want to reset permissions for all list items you can set this caml query : $camlQuery.ViewXml = "<View Scope='RecursiveAll' />" and you can also write your own caml query to get different set of list items.
#Add required references to SharePoint client assembly to use CSOM 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")  

#Proivde your details: SharePoint Site Url, UserName and Password   
$SiteUrl="https://spotenant.sharepoint.com/sites/TestSite" 
$UserName = "admin@spotenant.onmicrosoft.com"
$Password = 'adminpassword'
$SecPwd = $(ConvertTo-SecureString $Password -asplaintext -force)  

#Connecting site web
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)  
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecPwd)  
$ctx.credentials = $credentials 
$ctx.Load($ctx.Web) 
$ctx.ExecuteQuery()

#Find list by Title
$list=$ctx.Web.Lists.GetByTitle("Documents") 
$ctx.Load($list) 
$ctx.ExecuteQuery()

#Find list item by Name
$itemName = "TestFile.txt"; 
$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery 
$camlQuery.ViewXml ="<view><query><where><eq><fieldref name='FileLeafRef'><value type='Text'>" + $itemName + "</value></fieldref></eq></where></query></view>" 
# If you want to set permissions for all list items, you can use the below line (caml query to fetch all items) after commenting above line.
# $camlQuery.ViewXml ="<View Scope='RecursiveAll' />"  
$allItems=$list.GetItems($camlQuery) 
$ctx.Load($allItems) 
$ctx.ExecuteQuery()

# You can use the result $allItems in below examples.

Set item level permissions for user and SharePoint group :

By default all list items inherit the permissions from parent list, so to add unique permission for a particular list item, first we need to stop inheriting permissions (break the inheritance) of the particular item.

Add permission for user account :

The below powershell commands remove the unique permissions from the given list item (or list items) and set Contribute permission for the given user account.
# $allItems - You can get the required list items using the commands from above step.
foreach($listItem in $allItems) 
{ 
# Break inherited permissions. By default, the permissions are inherited from the above level.
$listItem.BreakRoleInheritance($false, $false); 
$ctx.Load($listItem)

#Find the given site user account
$editUser = $ctx.Web.EnsureUser("alland@spotenant.onmicrosoft.com") 
$ctx.Load($editUser)
$ctx.ExecuteQuery()

# Providing edit (contribute permission) access to the given site user.
$editAccess = $ctx.Web.RoleDefinitions.GetByName("Contribute")   
$editRole = New-Object Microsoft.SharePoint.Client.RoleDefinitionBindingCollection($ctx)   
$editRole.Add($editAccess)   
$editPermission = $listItem.RoleAssignments.Add($editUser, $editRole)   
$ctx.Load($editPermission) 
$ctx.ExecuteQuery()
Write-Host "Edit permission granted for the user:" $editUser.Title -foregroundcolor Green 
} 

Add permission for group :

You can use the below powershell commands to grant read access for the given sharepoint group.
foreach($listItem in $allItems) 
{ 
$listItem.BreakRoleInheritance($false, $false); 
$ctx.Load($listItem)

#Fecth the SharePoint groups for the site                         
$spGroups=$ctx.Web.SiteGroups 
$ctx.Load($spGroups)         
#Fecth the specific SharePoint group
$readGroup = $spGroups.GetByName("Test Site Visitors"); 
$ctx.Load($readGroup)
$ctx.ExecuteQuery()

# Providing read permission access to the members of the group "Test Site Visitors".    
$readAccess = $ctx.Web.RoleDefinitions.GetByName("Read")   
$readRole = New-Object Microsoft.SharePoint.Client.RoleDefinitionBindingCollection($ctx)   
$readRole.Add($readAccess)           
$readPermission = $listItem.RoleAssignments.Add($readGroup, $readRole)   
$ctx.Load($readPermission)              
$ctx.ExecuteQuery()
Write-Host "Read access granted for the group 'Test Site Visitors'" -foregroundcolor Green 
} 

Remove item level permissions :

You can use the below csom based powershell commands to remove unique permissions from a particular SharePoint/SharePoint Online list item.

Remove user permission from list item :

foreach($listItem in $allItems) 
{ 
#Remove permissions for a given user
$spUser = $ctx.Web.EnsureUser("alland@spotenant.onmicrosoft.com") 
$ctx.Load($spUser)
$ctx.Load($listItem.RoleAssignments)
$listItem.RoleAssignments.GetByPrincipal($spUser).DeleteObject()
$ctx.ExecuteQuery()
Write-Host "Permissions removed for the given user:" $spUser.Title -foregroundcolor Green
}

Delete group permission from list item :

foreach($listItem in $allItems) 
{ 
#Remove permissions for a given site group
$spGroups=$ctx.Web.SiteGroups 
$ctx.Load($spGroups)         
$spGroup = $spGroups.GetByName("Test Site Visitors"); 
$ctx.Load($spGroup)
$ctx.Load($listItem.RoleAssignments)
$listItem.RoleAssignments.GetByPrincipal($spGroup).DeleteObject()
$ctx.ExecuteQuery()
Write-Host "Permissions removed for the given group:" $spGroup.Title -foregroundcolor Green
}

Delete all unique permissions :

You can use the following powershell commands to remove all the explicit permissions from a list item and reset broken inheritance (recover inheritance).
foreach($listItem in $allItems) 
{ 
$listItem.ResetRoleInheritance()
$ctx.ExecuteQuery()
Write-Host "Unique permissions removed successfully and inheritance recovered." -foregroundcolor Green 
}

Friday, 13 July 2018

How to add user into Organization Management role group via PowerShell and GUI

The Organization Management role is one of most important role in Exchange management. You should have added into this role group to do some important mailbox operations like mailbox searches. You can easily add an user to Organization Management role group either using Powershell or Exchange Admin center GUI.

Add user into Organization Management group using PowerShell:

You can use the Exchange powershell cmdlet Add-RoleGroupMember to add members to a management role group. Before proceed you need to connect Exchange On-premises or Online powershell module based on your environment. Run the following command to connect Exchange Online module.
$o365Cred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $o365Cred -Authentication Basic -AllowRedirection
Import-PSSession $Session
Once you have connected the Exchange powershell module, Run the below command to add the required user into the role group "Organization Management":
Add-RoleGroupMember "Organization Management" -Member "user@o365domain.com"

Add member into Organization Management group via Exchange Admin center GUI:

You can also add a member into required role group through Exchange On-premises/Online Admin center.
  • Go to Office 365 Admin center.
  • In the left navigation, expand Admin centers, and then select Exchange.
  • In the Exchange Administration Center (EAC), navigate to Permissions > Admin Roles.
  • Select the group "Organization Management" and then click on Edit icon.
  • In the Members section, click on Add (+) button.
  • Select the required users, click on Add, and then click on OK.
  • Click on Save to save the changes to the role group.
Steps to add member into Organization Management role group via Exchange Admin center GUI

The term 'Get-UnifiedGroup' is not recognized as the name of a cmdlet

When you run the powershell cmdlet Get-UnifiedGroup you might have received the following error.
Get-UnifiedGroup : The term 'Get-UnifiedGroup' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Get-UnifiedGroup
+ CategoryInfo          : ObjectNotFound: (Get-UnifiedGroup:String) [],CommandNotFoundException+ FullyQualifiedErrorId : CommandNotFoundException
The command Get-UnifiedGroup belongs to Exchange Online powershell module and you can use this cmdlet to view Office 365 groups in your cloud-based organization.

Solution 1:

Before running this cmdlet, first you have to Exchange Online module by running following commands:
$o365Cred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $o365Cred -Authentication Basic -AllowRedirection
Import-PSSession $Session

Solution 2:

You might have received the same error even after loaded the Exchange Online module. In this case, the error might occurs due to the user account that you used to connect Exchange Online may not have required permissions to run this cmdlet.

To run this cmdlet, the user account needs the permissions Mail Recipients (RoleType: MailRecipients) and View-Only Recipients (RoleType: ViewOnlyRecipients).

If you have global admin account (or other high privileged account), then you can find the permissions required to run Get-UnifiedGroup cmdlet by running following command.
PS C:\> Get-ManagementRole -Cmdlet "Get-UnifiedGroup"

Name                 RoleType
----                 --------
Mail Recipients      MailRecipients
View-Only Recipients ViewOnlyRecipients

Add user into required role group:

To grant the required permissions, you need to add the user into a role group which contains "Mail Recipients" or "View-Only Recipients", such as Organization Management / Recipient Management group, etc.

Run the following powershell command to add the user into the role group "Organization Management":
Add-RoleGroupMember "Organization Management" -Member "user@o365domain.com"
You can also add the required role through Exchange Admin center:
  • Go to Office 365 Admin center.
  • In the left navigation, expand Admin centers, and then select Exchange.
  • In the Exchange Administration Center (EAC), navigate to Permissions > Admin Roles.
  • Select the group "Organization Management" and then click on Edit icon.
  • In the Members section, click on Add (+) button.
  • Select the required users, click on Add, and then click on OK.
  • Click on Save to save the changes to the role group.

Monday, 9 July 2018

How to disable Clutter in Office 365 Mailbox using Powershell

Clutter is a new mailbox management feature in Office 365 and it analyzes how you process all your emails and moves the “un-important mails” out of your Inbox folder and places that into the Clutter folder. This feature will be useful to move marketing and news related messages from Inbox, but for the very long time we are using E-mail Rules to move and organize mails and it has been working good, so this feature may not needed for some users.

This Clutter feature can be turned on or off via a setting in Outlook Web App (OWA) and but not from within Outlook Desktop client (not even Outlook 2016). So we can use the Exchange Online powershell cmdlet Set-Clutter to enable or disable clutter feature in mailbox.

Before proceed, first we need to connect Exchange Online powershel module by running below commands:
$o365Cred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $o365Cred -Authentication Basic -AllowRedirection
Import-PSSession $Session
Run the below command to disable the clutter for single mailbox.
Set-Clutter -Identity “alexw@o365domain.com” -Enable $false
You can enable it by just passing $true value to –Enable parameter.
Set-Clutter -Identity “alexw@o365domain.com” -Enable $true
Run the below commands if you want to remove this clutter feature from all the users mailbox.
Get-Mailbox -ResultSize Unlimited | Set-Clutter -Enable $false
Note: After turning off the clutter feature, the clutter folder will still exist but no emails moved into this folder.

Disable Clutter for specific set of users :

You can use the below powershell commands to remove clutter feature for multiple users.
$users = "user1@o365domain.com","user1@o365domain.com"
ForEach ($user in $users) {
Set-Clutter -Identity $user  -Enable $false 
}

Disable Clutter for bulk mailbox users from CSV :

Use the below powershell commands to disable clutter for bulk office 365 users by importing users from CSV file. Consider the CSV file Users.csv which contains set of mailbox users with the csv column header UserPrincipalName.
Import-Csv 'C:\Users.csv' | ForEach-Object {
$user = $_."UserPrincipalName"
Set-Clutter -Identity $user  -Enable $false
}

Wednesday, 4 July 2018

Find and Export all OneDrive for Business users using Powershell

In this post, I am going to explain how to find and export list of Office 365 users with OneDrive for Business site provisioned. We can easily extract OneDrive sites using SharePoint management powershell cmdlet Get-SPOSite.

Before proceed, Install and Connect SharePoint Online PowerShell Module. Run the below commands after replacing your SPO Admin site url to connect SPO Online service. You can refer this post to know more about how to get SPO Admin Site Url.
$AdminSiteURL="https://<your tenant name>-admin.sharepoint.com"
#Connect to SharePoint Online Admin Site
Connect-SPOService -Url $AdminSiteURL
When you simply run the cmdlet Get-SPOSite, it will list all sites in your SPO tenant but it will not list OneDrive users personal sites by default. So to retrieve personal sites along with normal spo sites , we have to run the Get-SPOSite cmdlet with the parameter -IncludePersonalSite as True.
Get-SPOSite -IncludePersonalSite $true -Limit All
The above command returns personal sites and it also includes normal sites, so we have to filter sites by url to get only personal sites. The below powershell script lists all personal sites in office 365 and its owner detail.
#Get OneDrive for Business Sites alone
Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'"

Export all OneDrive for Business users to CSV

The below powershell script get and export all personal sites and site owner name details to csv file.
$Result=@()
#Get all office 365 personal sites
$oneDriveSites = Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'"
$oneDriveSites | ForEach-Object {
$site = $_
$Result += New-Object PSObject -property @{ 
UserName = $site.Owner
OneDriveSiteUrl = $site.URL
}
}
$Result | Select UserName, OneDriveSiteUrl |
Export-CSV "C:\\OneDrive-for-Business-Users.csv" -NoTypeInformation -Encoding UTF8

CSV output of OneDrive for Business (ODFB) users:

Export OneDrive for Business Users (ODFB) Sites using Powershell

Convert CSV string to PS Object in PowerShell

You might have used the powershell command Import-Csv to import content from csv file. Sometimes we may required to convert csv (comma-separated-values) string content to object, we can easily achieve this using the cmdlet ConvertFrom-Csv.
$csv_content = "col1,col2`
val11,val12`
val21,val22"

$ps_object = $csv_content | ConvertFrom-Csv -Delim ','

Powershell Object:

PS C:\> $ps_object

col1  col2
----  ----
val11 val12
val21 val22

Find Installed Software’s Product code and Upgrade code using PowerShell

Sometimes we may required to find installed applications product and upgrade code for maintenance purpose or any other reason. In this post, I am going to share powershell script to get product id and upgrade code of installed tool.

Find Product Code:

The below command lists all the installed software’s name and product code
get-wmiobject Win32_Product | Select-Object @{ n='Name'; e={$_.Name}}, @{n='ProductCode'; e={$_.IdentifyingNumber}}
If you know the product name, you can just run the below command to get product code for a specific application.
$productName = “Name of your product”
$info = get-wmiobject Win32_Product | Where-Object { $_.Name –eq $productName}
$productCode = $info. IdentifyingNumber
In latest powershell (Windows 10 and later), we can use the command Get-Package to retrieve installed applications.
Get-Package | Select-Object @{ n='Name'; e={$_.Name}}, @{n='ProductCode'; e={$_.Metadata["ProductCode"]}}

Find Upgrade Code:

Once you find product code from above script, you can easily find upgrade code by using product code. Replace your product code in below script and run the commands to get upgrade code.
gwmi -Query "SELECT Value FROM Win32_Property WHERE Property='UpgradeCode' AND ProductCode='{your product code}'" | Format-Table Value