Friday, 22 September 2017

Block and Unblock access to Office 365 users using PowerShell

Blocking access to an Office 365 account prevents anyone from using the account to sign in and access all the services and data in your Office 365 tenant. We can use the Azure AD powershell cmdlet Set-MsolUser to block user from login into Office 365 service (Ex: Mailbox, Planner, SharePoint, etc).

Block and Unblock an Office user account:

We need to set the user associated property BlockCredential to block user access to Office 365 service.
Set-MsolUser -UserPrincipalName username@domain.com -BlockCredential $true
The following command unblock the blocked user.
Set-MsolUser -UserPrincipalName username@domain.com -BlockCredential $false

Block multiple Office 365 user accounts:

We can use the command Get-MsolUser to fetch set of required Azure AD users with proper filter and then pipe the results to Set-MsolUser cmdlet to block access to every user.
Get-MsolUser -All | Where {$_.Department -eq "Testing"} |
Set-MsolUser -BlockCredential $true

Block bulk user accounts by import CSV file:

We may required to block access to bulk of user accounts, in this case we can have user ids in csv. We need to import csv file, and then pass every user to Set-MsolUser cmdlet. Consider the csv file Block_Users.csv that has users with the column header UserPrincipalName.
Import-Csv 'C:\Block_Users.csv' | ForEach-Object {
$upn = $_."UserPrincipalName"
Set-MsolUser -UserPrincipalName $upn -BlockCredential $true
}

Export blocked user accounts to CSV file:

Run the following command to export all the users that have been blocked to access Office 365 services.
Get-MsolUser -All | Where {$_.BlockCredential -eq $True} |
Select DisplayName,UserPrincipalName, BlockCredential |
Export-CSV "C:\\Blocked_Users.csv" -NoTypeInformation -Encoding UTF8
Read More...

Hide and Un-hide users from GAL using Powershell

We can use the Exchange Powershell cmdlet Set-Mailbox to hide and un-hide mailbox users from Global Address List (GAL). We need to change the mailbox associated property HiddenFromAddressListsEnabled to hide user from GAL.

Before proceed, run the following command to load Exchange Online Powershell commands:
$365Logon = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $365Logon -Authentication Basic -AllowRedirection
Import-PSSession $Session

Hide and Un-hide a mailbox user from Global Address Book:

Run the following command to hide a single mailbox user.
Set-Mailbox -Identity username@domain.com -HiddenFromAddressListsEnabled $true
The following command un-hide the given mailbox user from GAL.
Set-Mailbox -Identity username@domain.com -HiddenFromAddressListsEnabled $false

Hide multiple mailbox users from GAL:

We can use the Get-Mailbox cmdlet to fetch set of required mailboxes by applying proper filter and then pipe the results to Set-Mailbox command to hide every mailbox from GAL.
Get-Mailbox -ResultSize Unlimited | Where {$_.Office -eq "Office1"} |
Set-Mailbox -HiddenFromAddressListsEnabled $true

Import mailbox users from CSV and hide from GAL:

We may required to hide bulk mailboxes from Global Address Book, in this case we can store the mailbox user ids in csv file and import csv in powershell using Import-Csv cmdlet and pass every mailbox to Set-Mailbox cmdlet. Consider the CSV file Hide_Mailboxes.csv which contains mailbox users with the column header UserPrincipalName.
Import-Csv 'C:\Hide_Mailboxes.csv' | ForEach-Object {
$upn = $_."UserPrincipalName"
Set-Mailbox -Identity $upn -HiddenFromAddressListsEnabled $true
}

Export hidden mailboxes to CSV file:

We can use the powershell cmdlet Export-csv to export all the hidden mailbox users to csv.
Get-Mailbox -ResultSize Unlimited | Where {$_.HiddenFromAddressListsEnabled -eq $True} |
Select DisplayName,UserPrincipalName, HiddenFromAddressListsEnabled |
Export-CSV "C:\\Hidden_MailBoxes_GAL.csv" -NoTypeInformation -Encoding UTF8
Read More...

Thursday, 21 September 2017

Find mailboxes hidden from the GAL using Powershell

We can easily get the list of all mailboxes that are currently hidden from Global Address List using the Exchange Powershell cmdlet Get-Mailbox. The Get-Mailbox cmdlet includes the property HiddenFromAddressListsEnabled and this property indicates whether the mailbox is hidden from GAL or not. So we can query the mailboxes with where filter by checking whether the property HiddenFromAddressListsEnabled is set to true or not.
Get-Mailbox -ResultSize Unlimited | Where {$_.HiddenFromAddressListsEnabled -eq $True}
You can run the following command if you want the output consist of only selected properties :
Get-Mailbox -ResultSize Unlimited | Where {$_.HiddenFromAddressListsEnabled -eq $True} |
Select DisplayName,UserPrincipalName, HiddenFromAddressListsEnabled
We can also export all the hidden mailbox users to csv by simply using Export-csv cmdlet:
Get-Mailbox -ResultSize Unlimited | Where {$_.HiddenFromAddressListsEnabled -eq $True} |
Select DisplayName,UserPrincipalName, HiddenFromAddressListsEnabled |
Export-CSV "C:\\Hidden_MailBoxes_GAL.csv" -NoTypeInformation -Encoding UTF8
Read More...

Tuesday, 19 September 2017

Set office 365 user's password to never expire using powershell

When you set password expiration policy for your Office 365 organization, it will apply to all Azure AD users. If you have requirements to set some individual user's password to never expire, you need to use Windows Powershell and you can achieve this by using the Azure AD Powershell cmdlet Set-MSOLUser.

Before proceed, connect to your online service by running the following command.
Import-Module MSOnline
$msolCred = Get-Credential
Connect-MsolService –Credential $msolCred

Set an individual user's password to never expire

Run the following command to set an user's password to never expire:
Set-MsolUser -UserPrincipalName <upn of user> -PasswordNeverExpires $true
For example, if the UserPrincipalName of the account is alexd@contoso.com, you can use below command:
Set-MsolUser -UserPrincipalName "alexd@contoso.com" -PasswordNeverExpires $true
You can find whether an user's password is set to never expire or not by running following command:
Get-MSOLUser -UserPrincipalName <upn of user> | Select DisplayName,PasswordNeverExpires

Set multiple users password to never expire

In some situations, we may required to update specific set of user's password to never expire. we can put the required user's upn in csv file and import the csv file and set passwordneverexpires setting. Consider the CSV file office365users.csv which contains users with the column header UserPrincipalName.
Import-Csv 'C:\office365users.csv' | ForEach-Object {
$upn = $_."UserPrincipalName"
Set-MsolUser -UserPrincipalName $upn -PasswordNeverExpires $true;
}
Read More...

Thursday, 14 September 2017

Find SharePoint List items with unique permissions using powershell

We can easily find and retrieve SharePoint list items which has unique permissions using CSOM in Powershell. In this script, we are going to use GitHub open source library Load-CSOMProperties.ps1 to fetch extra properties (ex: HasUniqueRoleAssignments) in SharePoint CSOM API. You can refer this post : How to load additional CSOM properties in PowerShell for more details.

The following Powershell script get all files (or list items) which has unique (or explicit) permission entries from a given SharePoint Online document library. To use CSOM in Powershell, we need to load the required Microsoft SharePoint Online SDK assembly files.
#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")

C:\Scripts\Load-CSOMProperties.ps1
  
$siteUrl="https://spotenant.sharepoint.com/sites/mysite1"
$UserName = "admin@spotenant.onmicrosoft.com"
$SecPwd = $(ConvertTo-SecureString 'myAdminPwd' -asplaintext -force) 
$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()
$list=$ctx.Web.Lists.GetByTitle("Documents")
$ctx.Load($list)
$ctx.ExecuteQuery()
$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml ="<View Scope='RecursiveAll' />";
$allItems=$list.GetItems($camlQuery)
$ctx.Load($allItems)
$ctx.ExecuteQuery()
 
foreach($item in $allItems)
{
Load-CSOMProperties -object $item -propertyNames @("HasUniqueRoleAssignments");
$ctx.ExecuteQuery();
if($item.HasUniqueRoleAssignments -eq $true)
{
Write-Host $item["FileRef"]
Write-Host "##############"
}
}
Read More...

Friday, 8 September 2017

How to load additional CSOM properties in a PowerShell script

We can use CSOM (client-side object model) in different programming and scripting languages to manage SharePoint On-Premises and SharePoint Online objects, as you know loading additional properties in client side object is very challenging task. In .NET C#, we can easily load specific values using Lambda Expressions with SharePoint CSOM API, but we should have proper knowledge to write lambda expression in Windows PowerShell.

In this post, I am going to share how to use GitHub open source library Load-CSOMProperties.ps1 to fetch extra properties in SharePoint CSOM API. It includes the following steps.
  1. Copy the required Powershell script code from this GitHub location Load-CSOMProperties.ps1 or you also copy the same script code at the end of this post.
  2. Once you copied the script, open Notepad (or Text Document) and paste the copied script, then save the file with .ps1 extension (Ex: Load-CSOMProperties.ps1).
  3. Now you can load the saved Powershell script file in Powershell console by just entering the file path. Ex: C:\Scripts\Load-CSOMProperties.ps1
  4. Once you loaded the Load-CSOMProperties.ps1 file in Powershell console, you can call the function Load-CSOMProperties anywhere in your script to load extra properties for any client side object.

Example 1:

The below powershell script load the additional properties 'Url' and 'Title' of the object $web along with default properties.
C:\Scripts\Load-CSOMProperties.ps1

$web = $ctx.Web
Load-CSOMProperties -object $web -propertyNames @("AllProperties", "Url", "Title")
$ctx.ExecuteQuery()

Example 2:

The below powershell script load the property HasUniqueRoleAssignments in every list item and check if the item has any unique permission entry or not.
C:\Scripts\Load-CSOMProperties.ps1

foreach($listItem in $allItems)
{
Load-CSOMProperties -object $listItem -propertyNames @("HasUniqueRoleAssignments");
$ctx.ExecuteQuery();
if($listItem.HasUniqueRoleAssignments -eq $true)
{
Write-Host $listItem["FileRef"]
}
}

Source of Load-CSOMProperties.ps1:

<#
.Synopsis
    Facilitates the loading of specific properties of a Microsoft.SharePoint.Client.ClientObject object or Microsoft.SharePoint.Client.ClientObjectCollection object.
.DESCRIPTION
    Replicates what you would do with a lambda expression in C#. 
    For example, "ctx.Load(list, l => list.Title, l => list.Id)" becomes
    "Load-CSOMProperties -object $list -propertyNames @('Title', 'Id')".
.EXAMPLE
    Load-CSOMProperties -parentObject $web -collectionObject $web.Fields -propertyNames @("InternalName", "Id") -parentPropertyName "Fields" -executeQuery
    $web.Fields | select InternalName, Id
.EXAMPLE
   Load-CSOMProperties -object $web -propertyNames @("Title", "Url", "AllProperties") -executeQuery
   $web | select Title, Url, AllProperties
#>
function global:Load-CSOMProperties {
    [CmdletBinding(DefaultParameterSetName='ClientObject')]
    param (
        # The Microsoft.SharePoint.Client.ClientObject to populate.
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0, ParameterSetName = "ClientObject")]
        [Microsoft.SharePoint.Client.ClientObject]
        $object,

        # The Microsoft.SharePoint.Client.ClientObject that contains the collection object.
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0, ParameterSetName = "ClientObjectCollection")]
        [Microsoft.SharePoint.Client.ClientObject]
        $parentObject,

        # The Microsoft.SharePoint.Client.ClientObjectCollection to populate.
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 1, ParameterSetName = "ClientObjectCollection")]
        [Microsoft.SharePoint.Client.ClientObjectCollection]
        $collectionObject,

        # The object properties to populate
        [Parameter(Mandatory = $true, Position = 1, ParameterSetName = "ClientObject")]
        [Parameter(Mandatory = $true, Position = 2, ParameterSetName = "ClientObjectCollection")]
        [string[]]
        $propertyNames,

        # The parent object's property name corresponding to the collection object to retrieve (this is required to build the correct lamda expression).
        [Parameter(Mandatory = $true, Position = 3, ParameterSetName = "ClientObjectCollection")]
        [string]
        $parentPropertyName,

        # If specified, execute the ClientContext.ExecuteQuery() method.
        [Parameter(Mandatory = $false, Position = 4)]
        [switch]
        $executeQuery
    )

    begin { }
    process {
        if ($PsCmdlet.ParameterSetName -eq "ClientObject") {
            $type = $object.GetType()
        } else {
            $type = $collectionObject.GetType() 
            if ($collectionObject -is [Microsoft.SharePoint.Client.ClientObjectCollection]) {
                $type = $collectionObject.GetType().BaseType.GenericTypeArguments[0]
            }
        }

        $exprType = [System.Linq.Expressions.Expression]
        $parameterExprType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
        $lambdaMethod = $exprType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2 -and $_.GetParameters()[1].ParameterType -eq $parameterExprType }
        $lambdaMethodGeneric = Invoke-Expression "`$lambdaMethod.MakeGenericMethod([System.Func``2[$($type.FullName),System.Object]])"
        $expressions = @()

        foreach ($propertyName in $propertyNames) {
            $param1 = [System.Linq.Expressions.Expression]::Parameter($type, "p")
            try {
                $name1 = [System.Linq.Expressions.Expression]::Property($param1, $propertyName)
            } catch {
                Write-Error "Instance property '$propertyName' is not defined for type $type"
                return
            }
            $body1 = [System.Linq.Expressions.Expression]::Convert($name1, [System.Object])
            $expression1 = $lambdaMethodGeneric.Invoke($null, [System.Object[]] @($body1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
 
            if ($collectionObject -ne $null) {
                $expression1 = [System.Linq.Expressions.Expression]::Quote($expression1)
            }
            $expressions += @($expression1)
        }


        if ($PsCmdlet.ParameterSetName -eq "ClientObject") {
            $object.Context.Load($object, $expressions)
            if ($executeQuery) { $object.Context.ExecuteQuery() }
        } else {
            $newArrayInitParam1 = Invoke-Expression "[System.Linq.Expressions.Expression``1[System.Func````2[$($type.FullName),System.Object]]]"
            $newArrayInit = [System.Linq.Expressions.Expression]::NewArrayInit($newArrayInitParam1, $expressions)

            $collectionParam = [System.Linq.Expressions.Expression]::Parameter($parentObject.GetType(), "cp")
            $collectionProperty = [System.Linq.Expressions.Expression]::Property($collectionParam, $parentPropertyName)

            $expressionArray = @($collectionProperty, $newArrayInit)
            $includeMethod = [Microsoft.SharePoint.Client.ClientObjectQueryableExtension].GetMethod("Include")
            $includeMethodGeneric = Invoke-Expression "`$includeMethod.MakeGenericMethod([$($type.FullName)])"

            $lambdaMethodGeneric2 = Invoke-Expression "`$lambdaMethod.MakeGenericMethod([System.Func``2[$($parentObject.GetType().FullName),System.Object]])"
            $callMethod = [System.Linq.Expressions.Expression]::Call($null, $includeMethodGeneric, $expressionArray)
            
            $expression2 = $lambdaMethodGeneric2.Invoke($null, @($callMethod, [System.Linq.Expressions.ParameterExpression[]] @($collectionParam)))

            $parentObject.Context.Load($parentObject, $expression2)
            if ($executeQuery) { $parentObject.Context.ExecuteQuery() }
        }
    }
    end { }
}
Read More...

Thursday, 7 September 2017

Get Item Level Permissions in SharePoint using CSOM

In this post, I am going to write C# code sample to get item level permissions for all list items using CSOM in SharePoint On-Premises/SharePoint Online library. Every list items should have permission entries only if they have unique (or explicit) permissions assigned. If an item or document doesn't have any unique permission entry, then the item's permissions will be derived from its parent library permission.

Retrieve Item Level Permissions For List Items with CSOM

The below CSOM based C# code find all list items for a given SharePoint Online list (or library) and gets the permissions for every items if an item has unique permission.
public static void Get_Item_Level_Permissions_For_All_List_Items()
{
    string sitrUrl = "https://spotenant.sharepoint.com/sites/mysite";
    using (var ctx = new ClientContext(sitrUrl))
    {
        //ctx.Credentials = Your Credentials
        ctx.Load(ctx.Web, a => a.Lists);
        ctx.ExecuteQuery();
        List list = ctx.Web.Lists.GetByTitle("Documents");
        var listItems = list.GetItems(CamlQuery.CreateAllItemsQuery());
        //load all list items with default properties and HasUniqueRoleAssignments property
        ctx.Load(listItems, a => a.IncludeWithDefaultProperties(b => b.HasUniqueRoleAssignments));
        ctx.ExecuteQuery();
        foreach (var item in listItems)
        {
            Console.WriteLine("List item: " + item["FileRef"].ToString());
            if (item.HasUniqueRoleAssignments)
            {
                //load permissions if item has unique permission
                ctx.Load(item, a => a.RoleAssignments.Include(roleAsg => roleAsg.Member.LoginName,
                    roleAsg => roleAsg.RoleDefinitionBindings.Include(roleDef => roleDef.Name,
                    roleDef => roleDef.Description)));
                ctx.ExecuteQuery();
                foreach (var roleAsg in item.RoleAssignments)
                {
                    Console.WriteLine("User/Group: " + roleAsg.Member.LoginName);
                    List<string> roles = new List<string>();
                    foreach (var role in roleAsg.RoleDefinitionBindings)
                    {
                        roles.Add(role.Description);
                    }
                    Console.WriteLine("Permissions: " + string.Join(",", roles.ToArray()));
                    Console.WriteLine("----------------");
                }
            }
            else
            {
                Console.WriteLine("No unique permission found");
            }
            Console.WriteLine("###############");
        }
    }
}
The above code first fetch the list items and then load the role assignments for every items, so it includes multiple server requests, alternatively we can also load the list items and its permissions in single server request call.
List list = ctx.Web.Lists.GetByTitle("Documents");
var listItems = list.GetItems(CamlQuery.CreateAllItemsQuery());

//load all list items with default properties and HasUniqueRoleAssignments property and also
//load permissions of every items 
ctx.Load(listItems, a => a.IncludeWithDefaultProperties(b => b.HasUniqueRoleAssignments),
    permsn => permsn.Include(a => a.RoleAssignments.Include(roleAsg => roleAsg.Member.LoginName,
            roleAsg => roleAsg.RoleDefinitionBindings.Include(roleDef => roleDef.Name,
            roleDef => roleDef.Description))));
ctx.ExecuteQuery();
foreach (var item in listItems)
{
    Console.WriteLine("List item: " + item["FileRef"].ToString());
    if (item.HasUniqueRoleAssignments)
    {
        foreach (var roleAsg in item.RoleAssignments)
        {
            Console.WriteLine("User/Group: " + roleAsg.Member.LoginName);
            List<string> roles = new List<string>();
            foreach (var role in roleAsg.RoleDefinitionBindings)
            {
                roles.Add(role.Description);
            }
            Console.WriteLine("Permissions: " + string.Join(",", roles.ToArray()));
            Console.WriteLine("----------------");
        }
    }
    else
    {
        Console.WriteLine("No unique permission found");
    }
    Console.WriteLine("###############");
}
Read More...

Wednesday, 30 August 2017

Get all files from a SharePoint Document Library using CSOM

In this post, I am going to write CSOM based C# code to retrieve all files in a SharePoint library using Client-side Object Model. Using this CSOM code you can easily fetch all the files from document library even it has files more than List View Threshold limit.

The List View Threshold defines the maximum limit to retrieve a number of documents in a single request. By default this limit is set to 5000 rows, and in OneDrive for Business (ODFB) this limit is 20000. So any library with more than 5000 files will return an error (Ex: The number of items in this list exceeds the list view threshold, which is 5000 items).

By using CSOM, you can retrieve documents page by page by setting row limit, this will avoid the list view threshold error as we are querying only certain amount of rows in a single request.

Retrieve all documents from a SharePoint library which has more than 5000 items

The following C# code fetch all files from a SharePoint online library. It will get 100 rows in every page. You can change the row limit as per your wish. In CAML query, we are setting the view as <View Scope='RecursiveAll'>, thus gets the documents from root folder and its sub folders by recursively.
public static List<ListItem> GetAllDocumentsInaLibrary()
{
    List<ListItem> items = new List<ListItem>();
    string sitrUrl = "https://spotenant.sharepoint.com/sites/yoursite";
    using (var ctx = new ClientContext(sitrUrl))
    {
        //ctx.Credentials = Your Credentials
        ctx.Load(ctx.Web, a => a.Lists);
        ctx.ExecuteQuery();

        List list = ctx.Web.Lists.GetByTitle("Documents");
        ListItemCollectionPosition position = null;
        // Page Size: 100
        int rowLimit = 100;
        var camlQuery = new CamlQuery();
        camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
            <Query>
                <OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy>
            </Query>
            <ViewFields>
                <FieldRef Name='Title'/><FieldRef Name='Modified' /><FieldRef Name='Editor' />
            </ViewFields>
            <RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";
        do
        {
            ListItemCollection listItems = null;
            camlQuery.ListItemCollectionPosition = position;
            listItems = list.GetItems(camlQuery);
            ctx.Load(listItems);
            ctx.ExecuteQuery();
            position = listItems.ListItemCollectionPosition;
            items.AddRange(listItems.ToList());
        }
        while (position != null);
    }           
    return items;
}
Read More...