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...

Tuesday, 29 August 2017

Get all Items from a SharePoint List using CSOM

In this article, I am going to write C# code to find all items in a SharePoint list with Client-side Object Model (CSOM). This code also solves the List View Threshold problem. The List View Threshold defines the maximum limit to retrieve a number of items in a single query call. By default this limit is set to 5000, and in OneDrive for Business this limit is 20000. So any list with more than 5000 items 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 list items 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 query.

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

The following C# based CSOM code fetch all items from a SharePoint online library. It will get 50 rows in every page. You can change the row limit as per your wish.
public static List<ListItem> GetAllListItemsInaList1()
{
    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: 50
        int rowLimit = 50;
        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...

Monday, 28 August 2017

Get Office 365 Groups in which a User is a Member using Graph Api

Office 365 Groups is developed with collaboration in mind. It becomes a base service for most of existing Office 365 tools that we use already ( like writing documents, planner plans, calendar meetings, teams chat and exchange mail), so finding which unified groups a user is a member of is inevitable in many scenarios.

The Microsoft Graph Api endpoint https://graph.microsoft.com/v1.0/me/memberOf gets and return all the groups where a current user is memberOf. But it returns not only the unified groups, it also lists security groups and dynamic groups. So, you need to apply proper filter to get only Office 365 groups using memberOf endpoint.

Retrieve Office 365 Groups in which current user is a member of

The following API returns all the unified groups in which I am member of.
https://graph.microsoft.com/v1.0/me/memberOf/$/microsoft.graph.group?$filter=groupTypes/any(a:a eq 'unified')

Find Office 365 Groups where an user is a member of

You can also use the same memberOf endpoint to retrieve all the unified groups where a specific user is a member. You need to just replace the /me/ part in the above url with the user's id /users/<user-id>/
https://graph.microsoft.com/v1.0/users/<user-id>/memberOf/$/microsoft.graph.group?$filter=groupTypes/any(a:a eq 'unified')
Note: You can call the memberOf endpoint without Admin Consent after applying unified filter and adding extra parameter (/$/microsoft.graph.group?$filter=groupTypes/any(a:a eq 'unified')) in memberOf endpoint url. If you call without this filter, you will receive the following Access Denied error when you don't have proper admin consent.
"error": {
    "code": "Authorization_RequestDenied",
    "message": "Insufficient privileges to complete the operation.",
}
Read More...