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 : 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/2013 Administration : How to Create Host Header Site Collections

A host-named site collection allows you to address a site collection with a unique DNS name, such as http://site001.prashant.com.

SharePoint decides the mapping of the Host Name with the Site Collection at the time of initializing SPSite Object. During the initialization Process SharePoint queries the SPWebApplication object to see if Host Header details are associated with the Site Collection and if there is no information returned on Host Headers then it becomes evident that this is a typical site collection.

If we inspect the ULS logs for Site Collections with Host Headers, we will see the entries like Site lookup found the host header site http://site001.prashant.com/Pages/default.aspx on the other hand if we inspect the same logs for Site Collections without Host Headers Looking up the additional information about the typical site http://serverName:Port/sites/site001

In this article we will look into the steps to create Host Header based Site Collections as per the following process –

1

Step 1: Add DNS Entry

  • Search for DNS on Windows Server

2

  • Select the Domain Name
  • Right Click and Select “New Host”

3

  • Enter new Host Name
  • Enter IP Address of the Hosting Server
  • Click Add Host

4

This will add a new Host Name entry in the DNS Catalog

5

 Step 2: Create Site Collection

Since we don’t have Web UI available to create the Host Header based Site Collections so the only option we are left with PowerShell only.

Following is the PowerShell command that we can use to create a Host Header based Site Collection-

New-SPSite “http://developerWorkspace001.PRASHANT.LOCAL” -HostHeaderWebApplication “http://developmentportal.prashant.local:2016” -Name “Developers Workspace 001” -OwnerAlias “Prashant\Administrator” -Template “DEV#0”

In this command we have the following parameters and their values:

1.1.png

Once you execute the PowerShell we can see the following output

6

We can verify the creation of the new Host Header Site Collection by going to

Central Administrator -> Application Management -> View All Site Collections

7

 

 

 

 

Step 3: Add Site Binding

Following is the PowerShell command that we can use to Add the Site Binding in IIS-

New-WebBinding -Name “Development – Portal” -IPAddress “*” -Port 2016 -HostHeader “developerWorkspace001.PRASHANT.LOCAL”

In this command we have the following parameters and their values:

2.1

Once the PowerShell executed successfully we can see the following output on the command prompt

8

We can verify the creation of Site Binding in the IIS by using following steps:

  • Search for IIS on Windows Server

9

  • Select the Web Application and click on Bindings Links as shown below

10

  • On the Site Bindings Window we can see a new Site Binding Added to the IIS

11

  • Select the Site Binding and Click Edit button to open Edit Site Binding window

12

This shows that Binding is created successfully.

Now lets’ visit the Host Header Site Collection by visiting the following URL:

http://developerWorkspace001.PRASHANT.LOCAL

 

Enter the credential when asked for

13

 

And there we are

14

 

That is all for this demo.

Hope you find helpful.

SharePoint 2016/2013 Administration : How to Create Host Header Web Applications

When we deal with Web Application URLs in SharePoint Environment it is quite important to review the URL Structure. By default when we create any SharePoint Web Application, it takes server name as default while preparing the URL structure as follows-

http:// <Server Name>:<Port>

Using Host Header we can overwrite this default behavior of comprising Web Application URL by SharePoint Engine.

In this article we will look for the Steps to create Web Applications with Host Headers.

If we look for the Web Application List created we can see only two Web Applications created as shown in the figure below:

1

If we closely look for Web Application SharePoint -80 which has been created with defaults and that is why if you see to the URL you will notice the URL structure is http:// <Server Name>:<Port>

Now in order to create the Host Header based Web Applications we need to follow the below steps:

2

Add DNS Entry for Host Header

  •  Search for DNS on Windows Server

3

  • Right Click on the Domain Name
  • Choose New Host

4

  • Specify the Name Host Header
  • Specify IP address to the Server
  • Click Add Host Button

5

6

Create Web Application Using PowerShell

As we know that default Authentication Mode for a Web Application is Classic when it is created using PowerShell. But here we have to create a Web Application in Claims Based Authentication Mode and that is why it is necessary to initialize the authentication provider by calling New-SPAuthenticationProvider command which return a fresh object referring Claims Based Authentication

$authenticationProvider = New-SPAuthenticationProvider

Then we need to call for New-SPWebApplication command to create a new Web Application utilizing Host Header as below-

New-SPWebApplication -Name “Development – Portal”  -Port 2016 -HostHeader “developmentportal.PRASHANT.LOCAL”  -URL “http://developmentportal.PRASHANT.LOCAL&#8221;  -ApplicationPool “SharePoint – 2016” -ApplicationPoolAccount (Get-SPManagedAccount “Prashant\Administrator”)  -AuthenticationProvider  $authenticationProvider

9

Once we get this PowerShell command executed using SharePoint Management Shell successfully, we can see the details of the new Web Application created just now

7

Similarly we can go back to Central Administration to look for List of Web Applications and can be able to locate this newly created Web Application

It is important to note the URL of the newly created Web Application and see the Host Header is now present in the Web Application URL Structure as needed.

8

And we are done!!

Hope you find it helpful.

SharePoint 2013 Administration : Enable & Modify Continuous Crawl

SharePoint 2013 includes a new type of crawl by the name Continuous Crawl that ensures the maximum possible refreshness of SharePoint Index.

1

The Continuous Crawl has got the ability to run in parallel and does not expect any previous crawl to get completed before the current one launched.

The Continuous crawl is available only for the SharePoint Index of type SharePoint Sites.

Once started a Continuous Crawl can be Paused or Stopped or Disabled

The default craw interval for Continuous Crawl is 15 minutes but this can be set to any appropriate value.

Since we don’t have any User Interface in SharePoint 2013 to set this value so we are left with only possibility i.e. by using PowerShell.

Enable Continuous Crawl : Central Administration

  • Go to Central Admin and Click on Manage Service Applications

2

  • Click on Search Service Application

3

  • On Search Administration Page click on the Content Sources link on the left navigation

7.png

  • Select SharePoint Index that you want to enable with Continuous Crawl

5

  • On the SharePoint Index Settings Page, scroll down to Crawl Schedule section and select Enable Continuous Crawls radio button and save the settings.

6

Enable Continuous Crawl : PowerShell

$serviceApp = Get-SPEnterpriseSearchServiceApplication

$contentSource = Get-SPEnterpriseSearchCrawlContentSource -SearchApplication $serviceApp -Identity “Local SharePoint sites”

Set-SPEnterpriseSearchCrawlContentSource -Identity $contentSource -EnableContinuousCrawls $True

Modify Default Interval of Continuous Crawl : PowerShell

$serviceApp = Get-SPEnterpriseSearchServiceApplication

$serviceApp.SetProperty(“ContinuousCrawlInterval”, 5)

$serviceApp.Update()

Hope you find it helpful.