How to retrieve complete set of permission changes while utilizing DeltaLinks (tokens) in SPO?

by Contributed | Oct 2, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Delta is, by default, a user-scoped API to drive sync-like behavior. It scopes its results down to the set of changes which are definitely impactful to the caller. It filters out changes which it knows are irrelevant to the caller. When the API cannot definitely determine relevance cheaply, e.g. when it needs to make a full permission enumeration to verify, it will include the result, even though it might not be relevant to the caller.

Delta attempts to scope permission-based changes to those relevant to the caller. If the caller’s access wasn’t altered by the permission change, the item may not be included in the delta results.

Clients which are trying to enumerate all permission changes should make sure the follow the recommendations in aka.ms/scanguidance. Namely, there are specific authentication requirements and specific Prefer headers that need to be provided, and failure to do so will result in permission changes being scoped down.

The only way to receive the complete set of changes is to use app-only authentication with the Sites.FullControl.All scope and pass header “Prefer”=”deltashowsharingchanges,hierarchicalsharing”.

Steps:

1] Create an App in AAD with Sites.FullControl.All Application permission, see screen shot below:

2] Sample powershell script to generate the Access token and the delta token link:

<#
Code Example Disclaimer:
Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.
THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object
code form of the Sample Code, provided that. You agree: (i) to not use Our name, logo, or trademarks to market Your software
product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the
Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or
lawsuits, including attorneys fees, that arise or result from the use or distribution of the Sample Code.
#>

cls

$host.Runspace.ThreadOptions = "ReuseThread"

Write-Host "STARTED at" (Get-Date).ToString() -f Green

$ClientID = "fa9737d5-5a3e-4fab-0000-000000000000"
$ClientSecret = "1JOe:M8HBBUz-0000000000000000000"
$scope= "https://graph.microsoft.com/.default"
$POSTURI =  "https://login.microsoftonline.com/d6f932a7-5f74-0000-0000-000000000000/oauth2/v2.0/token"

$body = @{grant_type="client_credentials";client_id=$ClientID;client_secret=$ClientSecret;scope=$scope}

$oauth = Invoke-RestMethod -Method Post -Uri $POSTURI -Body $body

$graphAccessToken = $oauth.access_token

Write-Host "Access token: $($graphAccessToken)"

$requestHeader = @{

  "Authorization" = "Bearer $graphAccessToken"
  "Content-Type" = "application/json"
  "Prefer" = "deltashowsharingchanges,hierarchicalsharing,deltatraversepermissiongaps,deltashowremovedasdeleted"
}

$Uri = "https://graph.microsoft.com/v1.0/sites/spotenant.sharepoint.com,df6ba610-b132-0000-0000-000000000000,e0dbcdc6-0637-4246-0000-000000000000/drive/root/delta?latest" 

$Result = (Invoke-RestMethod -Method Get -Headers $requestheader -Uri $Uri)

$deltaUri = $Result.'@odata.deltaLink'

Write-Host $deltaUri
Write-Host "DONE at" (Get-Date).ToString() -f Green

3] Copy the Access token and the deltaUri value output from the above script and use them in the following sample powershell script to retrieve the complete set of permission changes:

<#
Code Example Disclaimer:
Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.
THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object
code form of the Sample Code, provided that. You agree: (i) to not use Our name, logo, or trademarks to market Your software
product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the
Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or
lawsuits, including attorneys fees, that arise or result from the use or distribution of the Sample Code.
#>

cls

$host.Runspace.ThreadOptions = "ReuseThread"

Write-Host "STARTED at" (Get-Date).ToString() -f Green

$graphAccessToken = "copied from output of above sample powershell script"

$requestHeader = @{
  "Authorization" = "Bearer $graphAccessToken"
  "Content-Type" = "application/json"
  "Prefer" = "deltashowsharingchanges,hierarchicalsharing"
}

Write-Host

$deltaUri = "copied from output of above sample powershell script" #should look like sample below:
https://graph.microsoft.com/v1.0/sites/spotenant.sharepoint.com,df6ba610-b132-4fc7-0000-000000000000,e0dbcdc6-0637-4246-0000-000000000000/drive/root/delta?token=MzslMjM0OyUyMzE7Mzs3NDlhZjc4NC0zOWU0LTRlOTEtYmJkNy0wNzI5MjAxNTNlMGY7NjM3MzM2NDU1MzMyNDcwMDAwOzMxOTY4OTE4MjslMjM7JTIzOyUyMzA"

$deltaResult = (Invoke-RestMethod -Method Get -Headers $requestheader -Uri $deltaUri)

Write-Host $deltaResult.value
Write-Host

Write-Host "DONE at" (Get-Date).ToString() -f Green

[ANNOUNCEMENT] Productivity for Humans video series

by Contributed | Oct 2, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Have you been struggling to stay productive during these challenging times? Looking for tools and solutions that will empower you to do better work while also improve your mental wellbeing? Watch our new “Productivity for Humans” video series to hear from everyday Humans of IT members about how they’re using M365 solutions to help them stay productive!

This series kicks off two extremely powerful personal stories:

• Episode 1: Meet the Strant Family – In this episode, Megan and Loryan Strant, both MVPs and IT Professionals in Melbourne, Australia share about how their family uses M365 tools to help them stay productive while living with neurodiversity (ADHD, Asperger’s and Autism). Please click on the link to access the video.

Episode 1: The Strant Family

• Episode 2: Meet Jeff Stokes – In this episode, Jeff Stokes, an IT professional in Acworth, GA shares about how he uses M365 tools to stay productive despite living with permanent memory loss following a traumatic brain injury.
   
   

   

  Episode 2: Jeff Stokes

   

Want to be inspired by more Humans of IT-related video content? Be sure to keep an eye out for our new dedicated page in the Microsoft Virtual Hub (coming soon, by mid-Oct!) where you can get access to additional resources to help you navigate your personal human-centered productivity journey!

Do YOU use M365 tools and solutions to empower yourself or help your loved ones be more productive? Share your stories with us in the comments below, or apply to be a guest blogger on our Humans of IT blog!

We look forward to having you join us on a human-centered productivity journey.

#HumansofIT

#ProductivityforHumans

Paginating results in Synapse SQL

by Contributed | Oct 2, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Pagination is important technique in SQL that enables you to return partial results of the query by specifying a range of rows that should be returned. It is commonly used when you need to get the partial results of the queries that return a large number of rows in the result in the pages with size, 10, 25, 50, or 100.

In this article you will see how to do pagination on large amount of data in Synapse SQL.

First let’s see some pagination methods in SQL language.

OFFSET/FETCH

T-SQL language has OFFSET/FETCH clause that enables you to skip some number of rows and take few of remaining (similar to TOP clause). An example of query with OFFSET/FETCH clause is shown in the following code:

SELECT *
       FROM dbo.Supplier AS data
ORDER BY data.S_SUPPKEY
OFFSET (@offset) ROWS
FETCH NEXT (@fetch) ROWS ONLY;

Unfortunately, this method is still not available in Synapse SQL.

TOP offset+fetch

To get the rows between @offset and @offset+@fetch, we can first select TOP @offset+@fetch records from query (for example TOP 110 records if the offset is 100 and fetch is 10), then get bottom @fetch records from that row set (for example rows between 100 and 110).

The query that can return rows between @offset and @offset+@fetch is shown in the following example:

SELECT *
FROM (
       SELECT TOP (@fetch) *
       FROM
              (SELECT TOP(@offset+@fetch) *
                      FROM dbo.Supplier
                      ORDER BY S_SUPPKEY ASC
              ) AS topFO
       ORDER BY S_SUPPKEY DESC
     ) AS bottomF
ORDER BY S_SUPPKEY ASC

Subquery topFO is a set of rows that contain top @offset+@fetch rows according to the desired sort criterion. Now we need to get @fetch bottom records by reverting the order of this rowset and getting TOP @fetch rows as bottomF subquery. Note that we don’t have BOTTOM operator in TSQL so we need to temporarily revert the sort order and get TOP rows in opposite direction. These are the rows that we need but now in the wrong sort order, so we need to change sort order again in the surrounding query.

It might be strange that you need to write a query that sorts results three times (I’m calling this Salto mortale technique), but we will not have big performance impact because second and third sort worn on the smaller amount of already sorted rows.

ROW_NUMBER window

ROW_NUMBER window function can return order number of the row in the rowset by specified sort criterion. We can use this info to return only the rows that have ROW_NUMBER in the range between @offset and @offset+@fetch:

SELECT TOP (@fetch) *
FROM
( SELECT ROW_NUMBER() OVER(ORDER BY S_SUPPKEY) AS RowNum, *
       FROM dbo.Supplier AS tr) AS data
WHERE @offset < RowNum
ORDER BY data.S_SUPPKEY

Inner sub-query will return rows with positions and outer query will filter them out.

Performance test

The important question what method we should choose to implement queries with pagination. I have created procedures that select and paginate rows from tables by specified criterion and return 10-row pages with offsets 30, 300, and 2000. I have executed these tests on dbo.Customer table from TPCH 1TB database with 150.000.000 rows. Using the following code:

declare @offset int = 10;
EXEC page_row_number 'dbo.Customer', 'C_CUSTKEY', @offset, 10
EXEC page_offset_fetch 'dbo.Customer', 'C_CUSTKEY', @offset, 10
EXEC page_salto_mortale 'dbo.Customer', 'C_CUSTKEY', @offset, 10

set @offset = 300;
EXEC page_salto_mortale 'dbo.Customer', 'C_CUSTKEY', @offset, 10
EXEC page_row_number 'dbo.Customer', 'C_CUSTKEY', @offset, 10
EXEC page_offset_fetch 'dbo.Customer', 'C_CUSTKEY', @offset, 10

set @offset = 5000;
EXEC page_offset_fetch 'dbo.Customer', 'C_CUSTKEY', @offset, 10
EXEC page_salto_mortale 'dbo.Customer', 'C_CUSTKEY', @offset, 10
EXEC page_row_number 'dbo.Customer', 'C_CUSTKEY', @offset, 10

Since OFFSET/FETCH is still not available in Synapse SQL, I have run the test on Azure SQL Managed Instance and the results are displayed in the following Query Store UI:

First three results are ROW_NUMBER methods with offsets 10, 300, and 5000. It seems that this method provides worst results. Salto mortale (or TOP offset+fetch with switching order) seems providing even slightly better results than native OFFSET/FETCH but with minor perf difference.

 I also double-checked the results using Query Performance Insights library and found average duration, execution count and spent CPU time for these queries:

The conclusions are:

• The best performance we are getting with TOP offset+fetch query (a.k.a. Salto mortale query), but similar perf we can expect with OFFSET/FETCH.
• Performance don’t depend on offset. Regardless of offset most of the time is probably spent on sorting entire data set to find what is the TOP offset+fetch rows in the desired sort order or position using ROW_NUMBER by desired sort order.

If you want to try this experiment, the code of the test procedures is below:

CREATE OR ALTER PROCEDURE page_row_number (@table varchar(200), @column sysname, @offset varchar(20), @fetch varchar(20))
AS BEGIN
       DECLARE @sql NVARCHAR(4000) = N'
       SELECT TOP (' + @fetch + ') * /* Row number ' + @table + ' offset: '+@offset+' */
       FROM
       ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @column + ') AS RowNum, *
              FROM ' + @table + ' AS tr) AS data
       WHERE ' + @offset + ' < RowNum
       ORDER BY data.' + @column + '';

       EXEC sp_executesql @tsql = @sql;
END
GO
CREATE OR ALTER PROCEDURE page_offset_fetch (@table varchar(200), @column sysname, @offset varchar(20), @fetch varchar(20))
AS BEGIN
       DECLARE @sql NVARCHAR(4000) = N'
       SELECT * /* Offset fetch ' + @table + ' offset: '+@offset + ' */
              FROM ' + @table + ' AS data
       ORDER BY data.' + @column + '
       OFFSET ('+@offset+') ROWS
       FETCH NEXT ('+@fetch+') ROWS ONLY;';

       EXEC sp_executesql @tsql = @sql;
END
GO
CREATE OR ALTER PROCEDURE page_salto_mortale (@table varchar(200), @column sysname, @offset int, @fetch int)
AS BEGIN
       DECLARE @sql NVARCHAR(4000) = N'
       SELECT * /* Salto mortale ' + @table + ' offset: '+ CAST(@offset AS VARCHAR(20)) + '*/
       FROM (
              SELECT TOP ('+CAST(@fetch AS VARCHAR(20))+') *
              FROM
                      (SELECT TOP('+CAST( (@offset+@fetch) AS VARCHAR(20))+') *
                             FROM ' + @table + '
                             ORDER BY ' + @column + ' ASC) AS topFO
              ORDER BY ' + @column + ' DESC
       ) AS topF
       ORDER BY ' + @column + ' ASC';

       EXEC sp_executesql @tsql = @sql;
END

Maximizing your ROI from Microsoft 365 Enterprise with Microsoft Surface

by Contributed | Oct 2, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Microsoft solutions empower employees through a combination of software, services, and the ideal endpoint device. A major focus of this combination is to provide the best value to our customers. Microsoft Surface recently commissioned Forrester Consulting to develop a Total Economic Impact^TM study to understand the impact to organizations of using Microsoft Surface hardware devices with Microsoft 365. The insights from customer interviews and an international survey highlight the benefits to business managers as well as IT professionals. The results address how the combination of Surface devices and Microsoft 365 improves productivity, collaboration, and employee satisfaction and supports more efficient and cost effective device deployment, management, and security.

Download the full study here: