SharePoint 2013/2016: How to Find Duplicate Records in SharePoint List

During one of my assignments I have come across a situation where we need to fix data issues in SharePoint Lists.

One of the issues that we found was presence of duplicate data. In order to fix that problem in hand I had developed a Powershell Script to find out duplicate data based on a specific or a group of columns.

For the sake of demo, I have added a SharePoint List with some duplicate records in it as shown below:

1

Now let’s look into the code to understand implementation details-

In Step 1 we are getting references of the Site and Web where the SharePoint List resides

In Step 2 we are splitting the list of columns based on which we want to find out the duplicate data

We can see there are two input variables “ColumnToValidate” and “ColumnToDisplay”. “ColumnToValidate” provides columns based on which duplicity needs to be checked while “ColumnToDisplay” contains the list of columns that needs to be the part of data export.

In Step 3 we are creating the export folder that will hold the CSV files exported with duplicate records

In Step 4 we are creating the list object that will give the handle on the list which needs to be validated

2

In Step 5 we are getting list of Items from SharePoint List and grouping them based on the validation columns

In Step 6 we are creating the directory for export files

In Step 7 we are exporting all the groups which is having item count greater than 1 (this logic identifies the duplicate items)

3

That is all for the code.

Now we will see the variation in outputs depending on the columns specified for duplicacy check

In Step 8 we specify the validation and display columns, for the first execution we will check duplicate values in “Title” column

In Step 9 we are calling the “Get-DuplicateListItems” function to find the duplicate values

4

After the function executed successfully we can see the following output.

In Step 10 we can see the output of this excution and can see 6 items found which duplicate in Title Column

5

In Step 11 we can see the CSV file that is exported by the execution considering “Title” Column to be validated.

6

In Step 12 we can see the output file and can notice duplicate values in “Title” Column

7

In Step 13 we have changed the list of columns to be validated. In this second execution I have added another column “Role”.

Now the list will be validated for duplicity based on the combination of “Title & Role” Columns

8

In Step 14 we can see the output of this excution and can see 4 items found which duplicate in “Title & Role” Columns

9

In Step 15 we can see the CSV file that is exported by the execution considering “Title & Role” Column to be validated

10

In Step 16 we can see the output file and can notice duplicate values in “Title & Role” Column

11

In Step 17 we have changed the list of columns to be validated. In this second execution I have added another column “Location”.

Now the list will be validated for duplicity based on the combination of “Title & Role & Location” Columns

12

In Step 18 we can see the output of this excution and can see 2 items found which duplicate in “Title & Role & Location” Columns

13

In Step 19 we can see the CSV file that is exported by the execution considering “Title & Role & Location” Column to be validated

14

In Step 20 we can see the output file and can notice duplicate values in “Title & Role & Location” Column

15

This is a very simple technique that can be used to fix one of the issues with SharePoint List data.

Hope you find it helpful.

SharePoint 2016/2013/Online- How to Apply Password Encryption for Component as Service using PowerShell

Recently I have developed a couple of PowerShell based components that will serve as data crawlers for federated data sources like External Web Services, SQL Server Databases, and Excel Workbooks & SharePoint Lists.

In order to authenticate the Service Accounts against all of these sources I had no choice but to embed the User Name and Passwords with in the PowerShell Code in plain text. It gets even worst when few of the Web Services could support only “Basic Authentication”.

Saving passwords in plain text to code files could lead us to the Compliance Issues and could get the solutions rejected eventually.

In order to fix this issue I have implemented a couple of mechanism to deal with each type of Authentication requirements.

In this article I will discuss the mechanism to authenticate the requests to SharePoint Lists.

In order to simplify this demo let’s consider a simple scenario where I am having a list “MyLocations” as shown below and I need to export its metadata using a PowerShell based component.

1

To keep the content crisp I will walk you through the specific section from code and skipping all the CSOM specific code which you can refer in my earlier articles if you like.

I have intentionally divided this implementation into two separate code files in order to keep the passwords safe from the developers. Intent is to get the Encryption File generated by the SharePoint Admins and provided these files to developers for so that they can use it in code directly as shown below.

In the following code snippet you can see the commands to encrypt password “12345678” and export it to a text file “BANSALP.txt”

2

This file would look like as shown below:

34

This way you can store passwords for all required service accounts in different text files without violating Security Compliance.

Now in order to pass this encrypted password to SharePoint for authentication we can make use of “System.Management.Automation.PSCredential” Class as shown below.

Here “Get-Content” Command let is used to read the content from “BANSALP.txt” file and “ConvertTo-SecureString” Command let to get the encrypted password as secure string

5

Once credential Object has been created we can assign this credential object to SharePoint Client Context “Credentials” Property

6

With this Client Context SharePoint Authenticates the incoming request based on the ACL of the requestor

Following is the outcome of the call that we have send to SharePoint:

7

I have exported the metadata to a “csv” file as well that would look like this.

8

Hope you find it helpful.

Create Windows Scheduler Task by PowerShell Automation

In this article we will see simple steps to configure windows scheduler tasks using PowerShell Automation.

This article can be useful in conjunction with some of the earlier articles that I have written on Process Automation using PowerShell

Before getting into the code, let see the Windows Task Manager to understand where we can find tasks which are getting created by the code

Type “Task Manager”

1

You can see the highlighted section where you can find newly created Tasks

2

The code is pretty simple and based on standard PowerShell Command-lets as described below:

3

In Step 1, we are defining Trigger for the Task by using “New-ScheduledTaskTrigger” Command-Let. It could be any permissible Time Unit as shown below

4

In Step 2 & 3 we are setting up User Name and Password that the Task will be used as Owner Account to run the defined action

In Step 4 we are defining the action by using “New-ScheduledTaskAction” Command-Let that needs to be executed by the task when trigger reached, in this demo this task will execute a PowerShell script

Finally in Step-5 we are registering the tasks by using “Register- ScheduledTask” Command-Let. This Command-Let takes Name, Trigger, UserName, Password and Action as input parameter (as defined in above steps)

Once the script gets executed a new task will be added to the Windows Task Scheduler

5

We can see the new task added in Task Scheduler Window as shown below:

6

If we double click the this task, it will open the task configuration screen that we can use verify the task properties as explained below-

General Tab: Here we can see the name of the Task & User Account that will be used to execute the defined action

7

Triggers Tab: Here we can see the Trigger defined for this task

8

Actions Tab: Here we can see the action defined for this task. In this demo we have specified a PowerShell Script to be executed whenever respective trigger achieved

9

In case if you make any changes to this task Windows will present you a password dialog to confirm the User Credentials, If asked enter the respective credentials

10

This is a simple implementation that allows you to configure Windows Tasks using PowerShell Automation.

That is all for this demo.

Hope you find it helpful.

SharePoint 2016/2013 : Event Log Monitoring by PowerShell Automation

This article is based on the requirement I recently encounter where I was required to monitor a specific exception type and if it occurs the Admins should be notified at the same time.

In this demo I am considering a scenario that if ever we have encountered an Event ID “1101” that would mean the SharePoint Site or any related service is down and in that case the Administrators will get Email Notifications automatically.

1

In order to accomplish this let start with writing a PowerShell function “Monitor-Event-Logs” as shown in Step 1

In Step 2 we are making use of “Get-EventLog” commandlet by instructing it to get the top 1 latest Application Log where the Event ID = “1101”

We can check for Event Object for null and if it returns the data  we prepare the Email Content comprising of relevant data in as shown in Step 3

In Step 4 we are sending Email Notification to the Administrators by using another generic function “Send-Email”

23

There is an external function “Execute-Process” that will call the “Monitor-Event-Logs” function by passing required Event ID as shown in Step 5

In Step 6 we are initializing the required variables pointing to the email ids of the respective contact persons

Finally in Step 7 we will call the “Execute-Process” function that will drive the whole mechanics.

4

Once the function gets executed we can see the email arrived notifying the error to the administrators as shown below:

56

To make this process more intuitive I got this script scheduled using Windows Task Scheduler to run on a specific time intervals and scan the logs for specific Event IDs.

In my actual implementation I design the “Monitor-Event-Logs” function to accept an array of Event IDs to be monitored so you can try it that way depending on your requirements.

Hope you find it helpful.

 

 

 

SharePoint 2016/2013/Online: Sites Health Monitoring by PowerShell Automation

It is not uncommon that we need to perform health monitoring on SharePoint Sites to ensure that we have stable and healthy SharePoint Farms and to ensure maximum possible availability of all the sites.

In this article we will discuss a simple yet powerful automation technique using PowerShell that will try ping the Site and if gets failed it will send the email notifications to the designated SharePoint Admins for the Site.

With this background let’s start with the demo…

In this demo we have two SharePoint Sites in Question as follows-

The On-Premise site is up and running while SharePoint Online Site is down and unavailable due to some technical reasons

In order to automate this monitoring process I have written a simple function in PowerShell as explained below-

Step 1: Create an Object of System.Net.WebClient Class. This object will provide us the methods to deal with Site Pages

Step 2: Make use of DownloadString Method of this class to download the html of the respective Site Page

Step 3: Check for Possible Error Messages that SharePoint Page would generally contains in case of Site is not accessible by making use of Wild Card Search using Contains Method

Step 4 & 5: We are sending emails to the SharePoint Admins Informing that a specific site is down, so that they can take appropriate actions accordingly.

1

Step 6: This is the initiation function that is having an array of SharePoint Site URLs that requires monitoring.

Step 7: This function will call for Step 1 to Step 5 for each SharePoint Site URL in a periodic manner and keep on circulating the notifications in case of health issues with SharePoint Sites.

2

Once this automation script executes we can see the following email notification for SharePoint Online Site which is currenly down:

4

And there will be no emails for SharePoint On-Premise Site since it is healthy and active as shown below:

3

That is all for this demo.

Hope you find it helpful.

SharePoint 2016 Administration : Configure Managed Paths using PowerShell

Managed Paths in SharePoint are used to specify the paths in the URL namespace of Web Applications to be shared by the Site Collections hosting by the Web Application.

We have got two kinds of Managed Paths that can be created with in SharePoint namely Wildcard and Explicit.

Explicit Managed Paths

Explicit Managed Paths may be used when we are not planning to create Site Collections under a specific Managed Path. We will see the creation of the Explicit Managed Path in this article and it would be like

http://sp-2016-dev/prashant-explicit

In this case we cannot have more one site collection pointing to this URL.

Wildcard Managed Paths

Wildcard Managed Paths may be used when we are planning to create Sites or Sub Sites under a specific Managed Path. We will see the creation of the Wildcard Managed Path in this article and it would be like

http://sp-2016-dev/prashant-wildcard/

In this case we can have more one site collection pointing to the managed path specified by this URL

In this article we will be walking through the steps and PowerShell Scripts involved in creating Managed Paths in SharePoint 2016

  • Launch Central Administration Site
  • Choose “Manage Web Applications”

1

  • Select the Web Application in context of which you want to create the Managed Paths
  • Choose “Manage Paths” Ribbon Control

2

 

We can see the UI for “Define Managed Paths” screen in the Modal Dialog and this is the place where we can add required managed paths for our Web Application.

3

But since this article is about automating the SharePoint Processes so we will see how to do it using PowerShell in the following sections-

PowerShell Command

Wildcard Managed Paths

  • Launch SharePoint 2016 Management Shell
  • Run the following PowerShell Command to add a Wildcard Managed Path

New-SPManagedPath “Prashant-Wildcard” -WebApplication http://sp-2016-dev

4

We can go back to “Define Managed Path” screen to verify if the new Managed Path has been added for the specific Web Application

5

Explicit Managed Paths

  • Launch SharePoint 2016 Management Shell
  • Run the following PowerShell Command to add a Wildcard Managed Path

New-SPManagedPath “Prashant-Explicit” -WebApplication “http://sp-2016-dev” –Explicit

6

We can go back to “Define Managed Path” screen to verify if the new Managed Path has been added for the specific Web Application

7

Now finally we can verify the availability of the new created Managed Paths to the Site Collections created under the roof of Web Application http://sp-2016-dev

  • Go to Application Management
  • Choose “Create Site Collections”

8

On the “Create Site Collection” screen under “Web Site Address” section we can see the newly created Managed Paths both Wildcard and Explicit type

9

That is all for this demo.

Hope you find it helpful.

SharePoint 2016 Administration : How to configure SharePoint Designer Settings using PowerShell

SharePoint Designer being a very powerful tool can be used to perform many operations inside SharePoint Environment, creating Workflows, External Content Types, Master Pages & Page Layouts Branding are to name a few of them.

Since Microsoft already announced that there will be no new version of SharePoint Designer to be released for SharePoint 2016 and the existing SharePoint Designer would be fully functional with it so it even becomes more important to understand the governance around this excellent tool.

Being a tool with reputation to perform so many actions it is utmost important to work around proper governance plan per Web Application basis to control this little beast.

In this article we will be going to walk through the steps and PowerShell Scripts involved in configuring the SharePoint Designer settings.

Before we start configuring the settings for SPD lets’ see what will happen if these settings are not enabled on Web Application Level

Search for SharePoint Designer and Launch it

1

Now try opening any Site using SharePoint Designer and boom

Got an error: Access Denied!

2

This is due to the fact that SPD usage permission has been revoked already we would be looking an obvious SharePoint Error saying access denied even if the user has full access the Site itself. This is because of the fact that SharePoint manages permissions for SPD and Sites separately.

Now in order to enable permissions for SPD we can go along with the following steps-

  • Launch Central Administration Site
  • Click on “General Application Settings”

3

  • Click on “Configure SharePoint Designer Settings” Link under SharePoint Designer Section

4

Select Web Application for which you want enable SharePoint Designer Settings

And in the screenshot below we can see there are four properties that need to be configured to get SPD up and running.

5Since we are looking for automating SharePoint Configurations using PowerShell so we will configure it by PowerShell way only.

Since this article is about implementing automation for SharePoint configurations using PowerShell, so we will go along with PowerShell only.

PowerShell Commands

  • Get Web Application Object that you want to enabled with SPD usage

$webAppObj = Get-SPWebApplication -Identity http://sp-2016-dev

  • Set AllowDesigner, AllowRevertFromTemplate, AllowMasterPageEditing, ShowURLStructure properties to “true” if you want all SPD features enabled for the Web Application

6

$webAppObj.AllowDesigner = $true

$webAppObj.AllowRevertFromTemplate = $true

$webAppObj.AllowMasterPageEditing = $true

$webAppObj.ShowURLStructure = $true

  • After setting the required properties don’t forget to call Web Application Update Method to save the changes back to database.

 $webAppObj.Update()

Execution

  • Launch SharePoint 2016 Management Shell
  • Run the above commands

7

Once the commands get executed we can go back to “SharePoint Designer Settings” Page to verify the changes to the properties persist.

8Now try connecting to the Site again

9

And since the permissions has been enabled to the Web Application for SPD Access you can be able to connect to the site with the Web Application as shown in the screen shots below-

10

11

That is all for this demo.

Hope you find it helpful.