Apply SharePoint Online template using PnP PowerShell

Posted on

In this article we will define a template, and apply it for other SPO sites using PnP library
Please follow these steps :

    1. Set-up PnP module latest version from here
    2. Go to your SPO tenant, and create a SPO Site, you can also define (List structure, Theme, Pages, Content Types) because all of those will be part of the template.
    3. Make sure that the app catalog is activated on the tenant (There is no relation between app catalog and the tenant, but there is a bug found in January PnP package that required App catalog to be activated or a failure occurred)
    4. After having the site ready, run the below PowerShell command:
      $Creds = Get-Credential
      $SiteURL = ""
      Connect-PnPOnline -Url $SiteURL -Credentials $Creds
      Get-PnPProvisioningTemplate -Out C:\temp\demo1Template.xml
    5. You should see a progress running for a while to export all settings.
    6. Please open the XML file at the provisioned path (C:\temp\demo1Template.xml), and have a look, it is also doable to do medications through the file, by adding items, or fields, but you need to be careful not to crap the schema.
    7. Now it is time to import it to the destination site, Create a destination site on SPO with a default teamsite template
    8. Apply the new template by running the below command.
      $DestUrl = ""
      Connect-PnPOnline -Url $DestUrl -Credentials $Creds
      $template = Load-PnPProvisioningTemplate -Path "C:\temp\demo1Template.xml"
      Apply-PnPProvisioningTemplate -InputInstance $template

After this, refresh the destination site, and you should see the template applied.
I suggest to start first with a simple template, then apply more complexity to it. this approach should run smoothly also with SP2016, but I haven’t tried to import a template from SPO and import it to 2016 or vise versa


How to get warnings and alerts for your SharePoint Online Site storage limits ?

Posted on Updated on

SharePoint Online in Office 365 is allocated a quantity of storage that’s based on your number of users.
If you want to get a warning email when your site exceeds its limit, please run the below script using PowerShell, you need to make sure that the dlls are correctly referenced.

#Connect to tenant admin center using GA credentials
$username = ""
$password = ConvertTo-SecureString "" -AsPlainText -Force
$cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
Connect-SPOService -Url -Credential $cred
#Local variable to create and store output file
$filename = Get-Date -Format o | foreach {$_ -replace ":", ""}
$result = ""+$filename+".txt"
#SMTP and Inbox details
$smtp = ""
$from = ""
$to = ""
$subject = "Alert : PFA Site Collection Quota Usage details"
$body = "PFA quota usage details"
#Enumerating all site collections and calculating storage usage
$sites = Get-SPOSite -detailed
foreach ($site in $sites)
$percent = $site.StorageUsageCurrent / $site.StorageQuota * 100
$percentage = [math]::Round($percent,2)
Write-Output "$percentage % $($site.StorageUsageCurrent)kb of $($site.StorageQuota)kb $($site.url)" | Out-File $result -Append
#Sending email with output file as attachment
sleep 5
Send-MailMessage -SmtpServer $smtp -to $to -from $from -subject $subject -Attachments $result -body $body -Priority high

How to get useful Nintex Workflows Analytics from SharePoint?

Posted on Updated on

Nintex is still one of the most effective Workflow platforms, and it have complete suite with SharePoint.

Nintex with SharePoint On-Prem is still hard to be managed and maintained, this before it reaches its threshold very quickly, Nintex stated that its threshold is 30 Million WorkflowProgress items per Nintex Database. To understand what does this means I will quickly describe How Nintex data is stored in Databases, and this is for SharePoint 2016, SharePoint 2013, SharePoint 2010, & also SharePoint 2007.

Every Nintex worklflow is broken down to something named Workflow Instances, and each workflow instances is break down to workflow Progress.
Assume we have a Vacation Approval workflow, every time this workflow runs it generate a workflow instance,  if you re-run it on a single item 100 times, it will generates 100 workflow instances in the Nintex database. depends on the number of components it consist, workflow progress will be generated, so if this Workflow consists of 3 controls (Pause, Query list, Send Mail) then it will generate 3 WF progress records per run.

To get some useful info from the NinteDatabase, run the below commands:

create PROC [dbo].[WorkflowProgressProc] @db nvarchar(50)
IF DB_ID(@db) IS NULL /*Validate the database name exists*/
RAISERROR('Invalid Database Name passed',16,1)
WorkflowProgress INT,
WorkflowInstance INT,
size int,
NumOfSites int
declare @WorkflowProgress int,
@WorkflowInstance int,
@size int,
@NumOfSites int
select @WorkflowProgress= COUNT(*) from dbo.WorkflowProgress (nolock)
select @WorkflowInstance=COUNT(*) from dbo.WorkflowInstance (nolock)
select @size=size * 8.0 / 1024 from sys.master_files where name like @db
select @NumOfSites= count(*) from ( Select SiteID
From WorkflowProgress P inner join WorkflowInstance I
on (P.InstanceID = I.InstanceId)
group by I.SiteID) x
INSERT INTO #tmpBus values(@WorkflowProgress,@WorkflowInstance,@size,@NumOfSites)
select * from #tmpBus

After that, run the stored procedure and give the database name as a parameter

exec [WorkflowProgressProc] [NintexWF2013DB]

The result will be as follow:

From experience in Nintex, the threshold can be much more, even till 50 millions item if you have powerful servers. but other than this, you will face alot of delays and Nintex workflow errors.

Also, to get more details about SharePoint Sites vs WorkflowProgress numbers.

Select I.SiteID, Count(P.WorkflowProgressID) NoWFProgress,Count(distinct I.InstanceID) NoWFInstance , MAX(p.TimeStamp) Last_Activity
From WorkflowProgress P inner join WorkflowInstance I (nolock)
on (P.InstanceID = I.InstanceId)
group by I.SiteID
order by I.SiteID

This will give you a good figures about Nintex Analytics in your SharePoint environment.

“Access Denied” to Access Requests list or “Request approval failed” when you process a pending request in SharePoint Online

Posted on Updated on

I have run through a problem today during my work on one of the clients, and this sometimes happened mainly with sites that is migrated to SharePoint Online




This works perfectly fine !

Draw PowerBI Map from IP Addresses

Posted on

I had a request from a client, where he has an Excel sheet with list of IP addresses as well as other information, and he want to convert those IP address to the equivalent country and list in PowerBI.

Some Analytical tool like Splunk have this is a built in function, but for PowerBI you need to build it your self, and we will rely on a free web-service to implement this, so lets start

  1. Go to
  2. You should see the default data, which is your data retrieved by your IP address.
  3. Data displayed will be Country Code, Country Name, Region Code, Region Name, City, Zip Code(if applicable), Time Zone, Latitude, Longitude, and Metro Code
  4. if you entered specific IP address, correspondence data will be displayed, example:
  5. Now, lets get back to PowerBI, Open your , Click Get Data, and then select Blank Query
  6. Click View, then Advanced Query, and add the Query in the right text
  7. Copy paste this Query and enter it in the text box of the Advanced Query
    Source = (#"IP Address" as text) => let
    Source = Json.Document(Web.Contents("" & #"IP Address")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
    #"Promoted Headers"
  8. Click Done, and rename the function with a meaningful name like : fn_GetRegion
  9. Now, you need to create a column with this function, to do so, from Edit Queries go to Add Column > Invoke Custom Function
  10. Enter the data as shown below
  11. The column will appear at the end of your table, now we need to Expand the column by click on the double arrow icons as in the snapshot below
  12. Now, all the columns is appeared clearly.
  13. and you can use it to draw your maps.

Thanks to guavaq who shows us the solution in this post

List SharePoint Online Sites in PowerShell using PnP

Posted on Updated on

How to Retrieving List of SharePoint Online Sites in PowerShell using PnP.

Currently PnP is considered to be the most modern way to deal with SharePoint Online and On-Prem, because it provides one interface for both

Quick handy script to do that, and you can take the output to write it in CSV file or import it in SharePoint List, or store it in any other repository

1. You need first to download PnP PowerShell release, from here

2. Download the library, and un-zip the file
3. Now you will need to relocate the module files, you should find it in this paths
C:\Program Files (x86)\SharePointPnPPowerShellOnline\Modules\SharePointPnPPowerShellOnline\SharePointPnPPowerShellOnline.psd1 

$pnpmodulepath="C:\Program Files (x86)\SharePointPnPPowerShellOnline\Modules\SharePointPnPPowerShellOnline\SharePointPnPPowerShellOnline.psd1"
Import-Module $pnpmodulepath -Force
#Specify tenant admin and site URL
$User = ""
$Password = ConvertTo-SecureString -String "password" -AsPlainText -Force
$Tenant = ""
$CurrentCred = New-Object Management.Automation.PSCredential ($User, $Password)
Connect-PnPOnline -TenantAdminUrl $Tenant -Url $Tenant -Credentials $CurrentCred
$AllSites = Get-PnPTenantSite
foreach ($site in $AllSites )
if($site.Url.Contains("")) #This if condition to dismiss public sites, but you can remove it
Connect-PnPOnline -Url $Site.Url -Credentials $CurrentCred
Write-Host ($site.Url +";"+
$site.Title +";"+
$site.Lcid +";"+
$site.LastContentModifiedDate +";"+
$site.LockState +";"+
$site.StorageMaximumLevel +";"+
$site.StorageUsage +";"+

Upload file to SharePoint Online using PowerShell

Posted on

This script help you to upload your file or document to SharePoint library using PowerShell

  1. Download SharePoint Online client library from here , and install it. (If you already have it, please ignore this step)
  2. Create a folder named Temp on your C: drive, and put the Excel/CSV file in it
  3. Modify the parameters below and run the below script.

#Specify tenant admin and site URL
$User = ""
$Password = "YourPassword"
$SiteURL = ""
$Folder = "C:\Temp"
$DocLibName = "DocumentLibraryName"
#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,(ConvertTo-SecureString $Password -AsPlainText -Force));
$Context.Credentials = $Creds
#Retrieve list
$List = $Context.Web.Lists.GetByTitle($DocLibName)
#Upload file
Foreach ($File in (dir $Folder -File))
$FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)
$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$FileCreationInfo.Overwrite = $true
$FileCreationInfo.ContentStream = $FileStream
$FileCreationInfo.URL = $File
$Upload = $List.RootFolder.Files.Add($FileCreationInfo)
#Upload completed