Office 365 Management Activity API Registration and example using powershell

Here is a short post that explains how to register an Azure AD application for Office 365 Management Activity API. Also a powershell example to get the access token using authorization code and invoking rest method to do your work with management API.

Step 1:

Register application in Azure AD

a. Go to Azure Active Directory –> App Registration –> New application registration

b. Enter name and sign on url and click create

c. Click on the app you just created. Click Key

d. Create a client secret and save.

e. Under API Access click on Required permissions to add Office 365 Management API and select adequate permissions


Step 2:

Note: Your application id is the client id and key you created in step 1.d is client secret. Replace those and redirecturi in the script below

Note: modify the uri in the invoke-restmethod at the bottom of the script as per your requirement

Activity API operations

All API operations are scoped to a single tenant and the root URL of the API includes a tenant ID that specifies the tenant context. The tenant ID is a GUID. For information about how to get the GUID, see Get started with Office 365 Management APIs.{tenant_id}/activity/feed/{operation}

Highlighted content web part – Pull content across site collections

Below is a quick guide on how to pull content across site collections in modern site experience web parts. In this example I am trying to pull content from a traditional team site to a modern communications site by using the Highlighted content web part.


  • Edit the Communications site home page and add a highlighted content web part.

  • Edit the web part and modify the filter options as shown below:

Make sure to change the source to “All Sites”

Add metadata filters as shown below:

Get the content type id by browsing to the url of the content type, get the highlighted guid as shown below


  • Publish the page

Using Data Entry Forms in Excel Online

There are 2 ways you can achieve data entry forms in excel.

The traditional way of forms in excel client does not work in excel online. You can also find documentation of this feature not being available in excel online here.

But, you might be able to leverage Microsoft Forms service to create forms in excel online and publish, share within your organization. The service is still in public preview and your first release users should have access once enabled. Note that you would need to enable Microsoft Forms services at the tenant level from admin portal as explained here.

Forms are very intuitive and business users should very easily be able to create, publish and share their data forms/surveys. You can find getting started details here

If the service is turned off, you’d get below error message – “Your account is not enabled for Microsoft Forms”

After you have enabled Forms at the tenant level, when you open a new or existing excel sheet you should see “Forms” option in the ribbon as shown below:


 When you click on the New Form, it redirects you to the only if the service is enabled at tenant level else gives the above error.

Create a form with Microsoft Forms



SQL best practices for SharePoint 2013/2016

  1. Database File Separation Recommendations

Storage is one of the keys to performance. It is recommended that the following database file separation be used (separate disks, separate LUNs), in priority order, where possible:

Priority Database File SAN Optimization
1 TempDb data Write
2 TempDb log Write
3 Content DB logs Write
4 Service Apps DB log Write
5 Search Crawl DB log Write
6 Content DB data Read/Write
7 Service Application DB Data Read/Write
7 Usage and Health data Read/ Write
8 Search Analytics Database Read/ Write
9 Search Property Database Write

2. High Performance Mode

  • Operating System Level Power Plan should be: High Performance
  • Default setting is Balanced – this throttles CPU performance when memory consumption is low
  • Ensure BIOS-level Power Savings mode is disabled

3. Disk Allocation

  • Default disk allocation unit for most drives is 4k
  • SQL uses extents to write data to the disk
    • Each extent is composed of eight 8k pages
    • Each extent is a total of 64k
  • Format drives for 64k allocation unit
    • Will create performance improvements for operations that access bulk disk sectors (restores, database creation, etc)

4. SQL Server Instance Settings

  • Collation
  • Fill Factor
  • Max Degree of Parallelism (MAXDOP)
  • Maximum Memory

When preparing the SQL server to host SharePoint databases there are a few recommended practices to consider:


We support any CI collation for the SQL instance (for master, tempdb databases). However we recommend using Latin1_General_CI_AS_KS_WS as the instance default collation (master, tempdb databases). However, we do not support changing the default collation (Latin1_General_CI_AS_KS_WS) for SharePoint databases to any other collations (CI, AS, KS, WS). Any pre-created databases must have this collation before they can be mounted in SharePoint.

Supportability regarding SQL collation for SharePoint Databases and TempDB

Fill Factor:

Fill factor is used to determine how much free space is required for an index page. This is necessary in order to keep the index as compact as possible while as the same time preventing performance delays when splitting data to a new page after the current page fills up. For SharePoint, a server-wide setting of 80 is optimal to support growth and minimize fragmentation.


The max degree of parallelism option controls the number of processors that can be used to run a single Microsoft® SQL Server statement using a parallel execution plan. The default value for this configuration is 0 and indicates that all available processors can be used. Setting MAXDOP to 1 is mandatory for SharePoint 2013, otherwise the Configuration Wizard fails to complete successfully.

Maximum Memory

Use Dynamic Memory Management to set min and max memory levels for SQL

This is critical for servers that run more than one instance – otherwise one instance can consume all memory leaving none available for the OS and other instances

5. SQL Server Service Account

  • Do not provide this account Domain or Local Admin Privilege
  • Local Security Policy -> User Rights Assignments:
    • Perform Volume Maintenance Tasks
    • Lock Pages in Memory

Perform Volume Maintenance Tasks – In SQL Server, data files can be initialized instantaneously. This allows for fast execution of file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously. This can be especially useful on improving SQL server restart times – SQL has to re-provision tempDB on every reboot, so this will improve the length that operation takes.

Lock Pages in Memory – This setting should not be treated as an absolute best-practice. There are pros/cons to turning this on, and these risks should be evaluated to confirm alignment with IT goals. If operating system is experiencing memory pressure, SQL will trim memory allocation, which can create throttle SQL performance to give enough memory back to the OS to allow OS-level operations to complete. LPIM prevents SQL from releasing memory back to the OS, so the OS-related operations will be bottlenecked at the expense of SQL transaction performance. This should be tested to confirm the impact in the environment. If there is extensive memory pressure and this setting is turned on, there will be extensive paging to the disk which will also impact performance. Microsoft Support KB: How to enable the “locked pages” feature in SQL Server 2012

6. Configure SQL Aliases

  • Two strategies are available: DNS Alias or SQL Client Alias
    • DNS Alias can only be used if SQL is running on default port
    • SQL Client alias can be used to connect to an instance on a non-default port
  • Use on of the above methods to obfuscate the SQL connection string from SharePoint
    • This removes dependencies to a particular FQDN and allows us to move the databases to a different location if needed (DR solutions or hardware changes)
  • Neither of these strategies is an inherent security mechanism

7. Harden SQL Server

  • Do not use Mixed-Mode Authentication if possible
    • Mixed-mode authentication will be required for Access Services 2013
    • Ensure Windows Password Policies are being enforced on any required SQL logins
  • Configure firewall rules to only accept traffic from known hosts
  • Configure all incoming connections with SSL and Kerberos
  • Encrypt data-at-rest with Transparent Data Encryption

The following blog post describes steps for implementing SSL and TDE

Change SQL Server to a non-default port – This can be considered a relatively light hardening step. As the above blog post mentions, port scanning can easily reveal the correct SQL port. However, this can be implemented successfully following the steps in this article:

8. TempDB Configuration

  • Prioritize to fastest available disks for data and log files
    • Target: RAID-10
  • Only one log file is needed
  • Pre-size tempDB to 10% of largest database (20GB for 200GB ContentDB)
  • Avoid auto-growth on tempDB
    • Default auto-growth settings will only grow one tempDB data file at a time – BAD
    • Proactively manage database file sizes or consider Trace Flag 1117 which forces all data files to grow evenly when auto-growth is triggered

9. TempDB Allocation

  • Allocate a tempDB data file for each logical processor available to the server (Max of 8)

10. Model Database

  • SharePoint uses the Model Database for:
    • Default database/log size
    • Recovery model
  • SharePoint does not use this for:
    • Auto-growth configuration
    • Collation

Auto-growth settings on the modelDB are ignored by SharePoint databases, so we must configure auto-growth on the SharePoint databases after they are created

10. Auto-growth

  • Treat auto-growth as a protection for unexpected growth in the database
  • Regular capacity planning exercises by the SharePoint Admin team should estimate capacity for each database
  • Databases should be pre-grown by DBA to a size that will support 6-12 months of workload (depending on storage availability)
  • Set auto-growth to fixed values to avoid dynamic growth operations
  • Turn on database file instant initialization to improve growth-operation performance (details on slide 7)

For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow. It is recommended to monitor file sizes and available space inside the files and grow the files proactively during maintenance windows to an appropriate size. This helps you avoid file fragmentation and moves the file growth to times when it will not interfere with your database’s normal operations. – This diagram can help with sizing each database for your environment

11. Index Fragmentation and Statistics

SharePoint Health Analyzer rules evaluates the health of database indexes and updates index statistics daily for the following databases:

  • Configuration databases
  • Content databases
  • Managed Metadata Service database
  • User Profile Service Application Profile databases
  • User Profile Service Application Social databases
  • Word Automation Services databases
  • App Management Service database
  • Machine Translation Service Database
  • Subscription Settings

Maintenance plans should be created to defragment indices on all other databases manually. Additionally, these other databases should have AUTO_UPDATE_STATS turned ON

12. Database Maintenance Plans

  • Database Fragmentation
  • DBCC CheckDB

Database Maintenance plans should be created to run regular database integrity checks, as well as to defragment any indices that need it.

DBCC CheckDB should be run on a weekly basis

The following table describes the recommended resolution for various fragmentation levels:

Fragmentation level

Defragmentation method

Up to 10%

Reorganize (online)


Rebuild (online)


Rebuild (offline)

13. Anti-Virus Exclusions

  • Exclude all SQL file extensions from real-time scanning
    • *.mdf; *.ndf; *.ldf; *.bak
  • If using SQL clustering, also exclude
    • <$windir>/cluster
    • Witness disk

Cloud SSA On-boarding Script Error: “Response status code does not indicate success: 503 (Service Unavailable).”

Error: “Response status code does not indicate success: 503 (Service Unavailable).” 

If you sees this error while running the Cloud SSA on-boarding script, your tenant is more than likely placed on a lock preventing on-boarding to happen at the moment as it is in the process of or about to be moved.

There is no solution to this error other than waiting until after the tenant is moved unless you had previously on-boarded the tenant earlier.  If so, you’d be able to comment out the following section from the on-boarding script to get around the issue:


You can see the above issue manifest with a different error as well, as shown below:

error message; failure to call, prepared push tenant Failed to call
PreparePushTenant, error was Excpption calling “ExecuteQuery” with “0” argument(s): “Invalid URI: The URI is empty”

Microsoft Graph API using powershell

Step 1:

Register an app in Azure

Azure Active Directory -> App registration -> Add a new app

Once created, take note of the redirect uri, client id and client secret key

Note: key will have to be created once the app is created.

Step 2:

Below powershell is an example of retrieving user object by leveraging the Microsoft Graph API