Scripting

How to Remove Duplicates items in SharePoint Online List and keep one by PowerShell ?

Posted on Updated on

Hello folks,

We have faced recently a critical situation with a customer:
He was running a certain PowerShell script to update a SharePoint Online list with Data, it reaches 7000 items, a certain issue occurred in the script, and he found that he as 20,000 items in the same list, 70% was duplicates.

He wants to solve the situation in a safe way with no risk, by removing duplicate items and keep the Original one.
To solve the situation I did the following, This solution can be applied on all SharePoint versions 2010, 2013, 2016, & SharePoint Online, as the script is written to work as Client Object Model.

      1. I took back-up from the List, if you use SharePoint On-premise version, you can take back-up of the site. If you are using SharePoint Online, you can use Sharegate, however this step is not mandatory.
      2. Export AllItems to Excel using the Excel action in the ribbon, and make sure the ID Column is part of the export.
      3.  Download and setup a free tool named Kutools for Excel from here , the page contains also description how to use it to mark the duplicated column.
      4. Now take of the IDs from the Excel, and paste it in a text file on your harddrive
        Ids
      5. Then run the below PowerShell Script
        Add-Type -Path "C:\Path\SPOnline\Microsoft.SharePoint.Client.dll"
        Add-Type -Path "C:\Path\SPOnline\Microsoft.SharePoint.Client.Runtime.dll"
        $siteurl = "https://tenant.sharepoint.com/sites/yourSite/"
        $UserName = "siteadmin@tenant.onmicrosoft.com"
        $SecurePassword = Read-Host -Prompt "Please enter your password" -AsSecureString
        $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $SecurePassword
        $SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)
        $context.Credentials = $SPOCredentials
        $web = $context.Site.RootWeb
        $context.Load($web)
        $context.Load($web.Lists)
        $context.ExecuteQuery()
        $listTitle = "List Name"
        $list = $Context.Web.Lists.GetByTitle($listTitle)
        $Context.Load($list)
        $Context.ExecuteQuery()
        $TargetSites = Get-Content "C:\temp\List-of-Ids.txt"
        foreach ($targetSite in $TargetSites)
        {
        $id=$targetSite.Split(";")[0]
        $caml = @"<View Scope="RecursiveAll"><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>$id</Value></Eq></Where></Query><RowLimit Paged="TRUE">1</RowLimit></View>"
        $query = new-object Microsoft.SharePoint.Client.CamlQuery
        $query.ViewXml = $caml
        $items = $list.GetItems($query)
        $Context.Load($items)
        $Context.ExecuteQuery()
        if ($items.Count -gt 0)
        {
        for ($i = $items.Count-1; $i -ge 0; $i--)
        {
        Write-Host "Deleted: " $items[$i].Id.ToString() -ForegroundColor Yellow
        }
        $Context.ExecuteQuery()
        }
        }
        #$context.Dispose()

     

    Now, the items will start to be deleted, you can change the PowerShell script to fits your need.
    This method is safe because you see exactly what do you want to delete before executing the deletion script

Advertisements

Display PowerShell Full Exception Message with (Out-String)

Posted on Updated on

I was requested to write a detailed PowerShell Script that can log the Caught exception as string.

Actually it wasn’t easy as it looks like at first.

I tried to use write-host with a lot of Exception properties, as below, and all didn’t work as expected, I never found the error location as the Out-of-the-box PowerShell exception.

This is what I expected to see, detailed Exception with location error

then I came across one article mentioned that I need to use Out-String during the catch { }, this gives me exactly the details for the  exception I’m looking for

try
{
$v= 9/0
}
catch
{
write-host ($_ | Out-String) -ForegroundColor Red
}

Now the Exception looks really detailed and meaningful.

Create Test Microsoft O365 Group and Delete it

Posted on Updated on

If you are involved in rolling-out of Microsoft Teams or groups recently, then you may need a test script to create temporary O365 Groups
Using the below PowerShell script, you will be able to do so

Pre-requisites:
——————
You should have :

  1. Client Id
  2. Client Secret
  3. tenant admin Url & Credentials

function Test-Group {
##Get Access Token
$body = @{
scope="https://graph.microsoft.com/.default";
client_id=$AppID;
client_secret=$AppSecret;
grant_type="client_credentials";
}
$accessToken = (Invoke-RestMethod `
-Uri https://login.microsoftonline.com/$AADDomain/oauth2/v2.0/token `
-Method POST `
-Body $body `
-Headers @{"Accept" ="application/json"; "Content-Type"="application/x-www-form-urlencoded"}).access_token
##Create Group
$body = @{
displayName="$prefix$suffix";
description="$prefix$suffix";
groupTypes= @("Unified");
mailEnabled="true";
mailNickname="$prefix$suffix";
securityEnabled="false";
visibility="Private";
#"owners@odata.bind"=@("https://graph.microsoft.com/v1.0/users/5a97df74-a0c9-4fb9-8f9f-26bd29d4bf06");
}| ConvertTo-Json
$NewGroup = (Invoke-WebRequest `
-Uri https://graph.microsoft.com/v1.0/groups `
-Method POST `
-Body $body `
-Headers @{"Authorization" = "Bearer $accessToken"; "Accept" ="application/json"; "Content-Type"="application/json"} )
$NewGroupId = ($NewGroup.Content | ConvertFrom-Json).Id
##Check if group was provisioned
$success = $false
$time = [System.Diagnostics.Stopwatch]::StartNew()
for ($i=0; $i -lt 60; $i+=5) {
Start-Sleep -s 5
try{
Invoke-WebRequest `
-Uri https://graph.microsoft.com/v1.0/groups/$NewGroupId/drive `
-Headers @{"Authorization" = "Bearer $accessToken"; "Accept" ="application/json";} | Out-Null
$success = $true
break
}catch [System.Net.WebException]{
If ($_.Exception.Response.StatusCode.Value__ -ne 404){
throw
}
}
}
if($success){
##Delete Group, you can leave it or keep it based on your needs
# Invoke-RestMethod `
# -Uri https://graph.microsoft.com/v1.0/groups/$NewGroupId `
# -Method Delete `
# -Headers @{"Authorization" = "Bearer $accessToken"; "Accept" ="application/json";} | Out-Null
} else {
Write-Host ModernGroup failed to provision in ([Math]::floor($time.Elapsed.TotalSeconds)) seconds -ForegroundColor Red
Write-Host GroupId: $NewGroupId -ForegroundColor Red
Write-Host CorrelationId: $NewGroup.Headers.'request-id' -ForegroundColor Red
Write-Host Date: $NewGroup.Headers.'Date' -ForegroundColor Red
}
}
$AppID = 'xxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxxx' #Put your AppId
$AppSecret = '**********************************' #Put your Secret
$AADDomain = 'yourtenant.onmicrosoft.com'
for ($i=0; $i -lt 1; $i++) {
$suffix = (Get-Date).Ticks
$prefix = "MSFT_Test_"
Test-Group
}

At the End you will have a test group created

Get SharePoint Online Event Receivers

Posted on Updated on

In this article we will get list of Event receivers attached to SiteCollection or Web Levels.
Please follow these steps :

  1. Make sure Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll
  2. Open PowerShell ISE & Run the Below script after setting the target parameter
    # Paths to SDK. Please verify location on your computer.
    Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
    Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
    #declare the variables
    $tenantSite = "https://tenant-admin.sharepoint.com"
    #read file
    $siteurl = "https://tenant.sharepoint.com/teams/site1"
    $UserName = "admin@tenant.onmicrosoft.com"
    $SecurePassword = Read-Host -Prompt "Please enter your password" -AsSecureString
    $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $SecurePassword
    $SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
    Connect-SPOService -Url $tenantSite -Credential $Credentials
    $context = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)
    $context.Credentials = $SPOCredentials
    $web = $context.Site.RootWeb #You can make it only $context.Site if you want to get the Events on Site Collection level
    $context.Load($web)
    $context.Load($web.EventReceivers)
    $context.ExecuteQuery()
    $eventReceivers = $web.EventReceivers
    foreach($eventReceiver in $eventReceivers)
    {
    Write-Host $eventReceiver.ReceiverId " " $eventReceiver.EventType.ToString() " " $eventReceiver.ReceiverName
    }
  3. Output will be as follow

Of course the above script can be modified to be compatible with If you want to verify your results, you can use SharePoint Client Browser (SPCB)
It is a free helpful open source tool that can give you nice insights about your Site/Web event receivers and it supports 3 versions (SP2013, SP2016, & SPOnline)

 

Get SharePoint Admins for All SharePoint Online Sites

Posted on Updated on

In this article we will get list of all SharePoint Online Sites, and then List Site Admins using PnP library
Please follow these steps :

      1. Set-up PnP module latest version from here https://github.com/SharePoint/PnP-PowerShell/releases
      2. Run the Below script after setting the target parameter
        #Connect to SPO tenant
        $CurrentCred = Get-Credential
        Connect-SPOService "https://admin-admin.sharepoint.com" -Credential $CurrentCred
        #Get all Site collections
        $sites = Get-SPOSite -Limit All
        foreach ($site in $sites)
        {
        Connect-PnPOnline -Url $site.Url -Credential $CurrentCred
        $admins = Get-PnPSiteCollectionAdmin | select Title #You can also add ,Email
        $allAdmins=""
        foreach($admin in $admins)
        {
        $allAdmins += $admin.Title +";" #You can have also $admin.Email
        }
        Write-host ($site.Url+","+$allAdmins) -ForegroundColor Green
        ($site.Url+","+$allAdmins) >> "C:\temp\SiteCollectionAdmins.csv"
        }

Then you will have a CSV file with SiteUrl & Site Admin Name delemited by ;

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 https://github.com/SharePoint/PnP-PowerShell/releases
    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 = "https://test.sharepoint.com/sites/demo1/"
      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 = "https://test.sharepoint.com/sites/destination1/"
      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 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)
AS
IF DB_ID(@db) IS NULL /*Validate the database name exists*/
BEGIN
RAISERROR('Invalid Database Name passed',16,1)
RETURN
END<
BEGIN
CREATE TABLE #tmpBus
(
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)
END
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.