Browse Tag

SharePoint 2013

Create SharePoint Subsites with Custom Permissions & Templates Using Powershell

powershell2I had a situation recently in which we were performing a migration from SharePoint Services 2.0 up to SharePoint 2013. When testing the path of copying the sites directly from 2.0 to 2013 they didn’t come across very clean. One of the main reasons was the way permissions was done in 2.0 does not directly match 2013. If we would have performed a migration like this they would have had a working environment but it would have been very hard to manage moving forward. Knowing that we didn’t want to copy the sites we decided to try to create all the new sites prior to the migration and then only copy the content.

When performing our pre-migration analysis we found around 2,000 sites and subsites. We performed multiple information architecture planning workshops to figure out the new site structure for 2013. Once we had the agreed upon new site structure we could then map the sites that needed to be migrated to their new locations. Once we had this kind of data I knew I could automate the creation of all the sites. Now I never like to start my scripts from scratch if I don’t have to and there are a lot of very smart people out there posting helpful info. I found some scripts online and put them together with my own special inserts and use cases. To give the proper credit to where I got this script started.

  • This is a great codeplex solution from @PhillipChilds that will create subsite structure as a CSV. This was a good start but would require me to enter all the information for the SharePoint groups that I needed.
  • I then found this script from @PointBeyond which would create the 3 default SharePoint groups I was looking for.

So now I had a script that would create subsites based on a CSV and create 3 default SharePoint groups if stated, awesome!

My last issue I ran into was the ability to apply a custom site template on the subsite. (here is a list of available site templates in SharePoint 2013 thanks to @vladcatrinescu) When you try to create a subsite using Powershell and pass through a custom site template it will not actually apply the template. The way to get this to work was to apply the site template after the subsite was created. So now all templates being applied whether default or custom will be applied after the site is created. Here is a little trick on how to get a custom site template ID without using Powershell.

I also included the enabling and disabling of a few site features.

  • Minimal Download Strategy will be disabled
  • Getting Started will be disabled
  • Publishing will be enabled

Here is a link to the script and the CSV to get started.

16235-illustration-of-a-green-download-button-pv

There are other areas that I would like to expand on this script but if you have any ideas please let me know!

Adjusting Email Notifications For SharePoint 2013 Task Lists

When looking at the settings of a SharePoint 2013 task list you no longer have the option to enable email notifications.  This used to be found under List Settings -> Advanced Settings -> Send e-mail when ownership is assigned.

This is what it looks like in 2010.

tasknotifications

This can be still be turned on using Powershell in SharePoint 2013.  Here is the powershell to perform that task along with a link back to the source of the script.  Thanks to the creator of the script karimSP!

$site=Get-SPSite "http://sharepoint2013/"
$web=$site.OpenWeb()
$list=$web.Lists.TryGetList("Tasks")
if($list -ne $null)
{
 $list.EnableAssignToEmail =$true
 $list.Update()
}

Adjusting when the assigned to task email is fired

20  Fortunately we have the ability to change the types of event that fire these emails in powershell.  The primary use case I used this for is too only send task emails on creation of the item (Add).

The available options for when notifications are sent are listed in the SPEventType enumeration and they are:

Member Name Description
Add Additions to the list or list item. (0x00000001)
Modify All changes made in a list or list item. (0x00000002)
Delete Deletion of a list or list item. (0x00000004)
Discussion Changes in Web discussions. (0x00000FF0)
All All events pertaining to the list or list item. (-1)

 

Powershell to update the events

I have included variables at the top of the script that you can enter depending on the location of the task list(s) along with which ones you want to update.  Make sure you run the powershell to activate the assigned to email, the script above, prior to running this script.

##################################################################
# NAME: SharePoint2013TaskAlertEventTypes.ps1
# DESCRIPTION: Script to adjust SharePoint 2013 task list email notifications on different events
# AUTHOR: Drew Madelung
# LAST UPDATED: 11/11/2015
##################################################################

# ***IMPORTANT*** The powershell to enable assigned email notifications must be done prior to this script being ran

Add-PSSnapin Microsoft.SharePoint.Powershell

# Set variables - EXAMPLE variables are set
$sitecollectionUrl = "http://sharepoint2013"
# Eventtypes can be: All, Add, Modify, Delete, Discussion
$eventType = "Add"

# Optional variables - if not used, all event types for all assigned to task alert email notifcations on the listed site will be updated
$subsiteUrl = "/subsite"
$listUrl = "lists/tasks"

$site = Get-SPSite $sitecollectionUrl

# Get subsite if entered
if ($subsiteurl -eq "") { $web = $site.OpenWeb() } else { $web = $site.OpenWeb($subsiteUrl) }

$a = $web.Alerts

foreach ($alert in $a)
{
 $alerttemplate = $alert.DynamicRecipient
 if ($alerttemplate -eq "AssignedTo") {
 if ($listUrl -eq "") {
 $alert.EventType = [Microsoft.SharePoint.SPEventType]::$eventType
 $alert.Update()
 Write-Host 'Updated event type to' $eventType 'for the list' $alert.list
 }
 
 else {
 if ($alert.ListUrl -eq $listUrl) { 
 $alert.EventType = [Microsoft.SharePoint.SPEventType]::$eventType
 $alert.Update()
 Write-Host 'Updated event type to' $eventType 'for the list' $alert.list
 } 
 } 
 } 
}
$web.Dispose()
$site.Dispose()            Write-Host 'Updated event type to' $eventType 'for the list' $alert.list
            }
            
        else {
            if ($alert.ListUrl -eq $listUrl) { 
              $alert.EventType = [Microsoft.SharePoint.SPEventType]::$eventType
              $alert.Update()
              Write-Host 'Updated event type to' $eventType 'for the list' $alert.list
            }    
        }    
    }    
}
$web.Dispose()
$site.Dispose()

Link to download powershell script

16235-illustration-of-a-green-download-button-pv

If anyone has any thoughts or suggestions for this script please let me know!

Exporting SharePoint Group Members to Excel Without PowerShell

I was at a client recently and was not allowed to run any powershell commands but needed a list of accounts that existed in a SharePoint Group in a table format.  I also was not allowed site collection administration permission.  I tried a few different options in which trying to manipulate the list view of the group and using Excel data connections to get back to SharePoint but no option worked very cleanly.

What I ended up using was a REST call to get the users and then downloading the XML response and opening it with Excel.  Use this link to learn about the available REST api’s for users & groups

Here are the steps….

1.  Get a client that you can use to test REST calls

2.  Construct the REST call to get a list of users by group

The structure looks like this:  https://siteurl/_api/web/sitegroups/getbyid(groupid)/users

  • To get the group ID simply navigate to the members page of your SharePoint Group and look at the number at the end of the URL  Here is the URL of my “Product Members” group:  https://concurrencyinc.sharepoint.com/sites/products/_layouts/15/people.aspx?MembershipGroupId=9

Here is my call:  https://concurrencyinc.sharepoint.com/sites/products/_api/Web/SiteGroups/GetById(9)/Users

  • We will see the users returned in the entry area of the response.

ExportSPGroup1

3.  Download the XML response

  • In the Advanced REST Client click Save as file and then Download in the response section.

ExportSPGroup2

4.  Change the file type

  • The file will download as a .text-plain file type.  Edit the filename and change it to a .xml file type.

ExportSPGroup3

5.  Open with Excel!

  • In Excel browse and pick out the new .xml file you created and select open this file as an XML table.

ExportSPGroup8

  • Take a second and look at your pretty data.

ExportSPGroup4

6.  Remove duplicates

  • The data comes across in a way that there are 2 rows for each user.  We can clean that up by removing the duplicates based on the login name.  First click anywhere in the imported table and under the Data tab click Remove Duplicates.

ExportSPGroup5

  • Click the Unselect All button then scroll down and check ns4:LoginName

ExportSPGroup6

  • That will remove your duplicate logins and you will have emails and logins that you can use whatever way you need.

ExportSPGroup7

 

Handy appendix?

I know this post is titled how to get a list without powershell but I wanted to just include this down here as this is an easier approach if you have the ability.  Here are the commands to get a list of users in a SharePoint Group via powershell.

  • Get-SPSite http://server/sites/yoursite | Select -ExpandProperty RootWeb | Select -ExpandProperty Groups | Where {$_.Name -EQ “group name”} | Select -ExpandProperty Users | Select Name, Email| Export-Csv c:\scripts\users.txt

Here is the command to do it with SharePoint Online

  • Get-SPOUser -Site https://contoso.sharepoint.com/sites/finance -Group “group name”

Handy links!

Issues Moving SharePoint Access Apps Between Farms

I hit an issue recently that was a little tricky in regards to Access Services and SharePoint 2013.  (Not Access Services 2010)  The issue arose when saving an Access app that was created in a development farm and trying to publish it to production.  Here are the steps to create an Access app package for an on-premises Access app.  What was unique about this situation that moving apps in any other direction worked.  You were able to go Prod->Prod, Prod->Dev, Dev->Dev but Dev->Prod failed.  You were also able to create new Access apps in both environments.

When deploying the app to a site in the production environment these were the errors that were received…

————–

DacManagerPeer.Install: Exception thrown installing DAC. Exception {Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. —> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Internal Error.  The internal target platform type Sql110DatabaseSchemaProvider does not support schema file version ‘2.5’.     at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateExtensionManagerFromModel(SqlPackage package)

————–

DacManager.ExecuteDacOperation: DAC operation failed with exception {Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. —> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Internal Error.  The internal target platform type Sql110DatabaseSchemaProvider does not support schema file version ‘2.5’.     at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateExtensionManagerFromModel(SqlPackage package) 

————–

I had never heard of DAC as it relates to SharePoint (it’s not Dedicated Administrator Connection) before so I had to do some digging.  From the sound of the error it sounded like software was mismatched between environments.  I first double-checked all of the install tasks to ensure they were completed – Here is a great white paper on setting up Access Services.  While going through this I saw that one of the prerequisites referenced DAC, the Data-Tier Application Framework.

image

So I went and looked at both environments and sure enough there were multiple versions of the Data-Tier Application Framework across all servers (APP & SQL) in both environments.  There were versions 10 up to 12 with no standardization.  I then went ahead and installed the latest version of the DACFramework.msi file that I could find in the download center which was from June 22nd 2015 and version 13+.  I received the same error after this install so I started to dig deeper by going into the Access app package.  I first changed the extension of the app package to .zip.

image

And I found these files:

image

Great start, time to compare between apps build in Prod vs Dev!  At first glance nothing stood out between the manifest and .xml files, but then I started looking into the .dacpac file as I had never heard of this before.  Here is what I found:

  • A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.

It was now making more sense.  Access Services in 2013 uses an entire SQL DB per app you create.  So SharePoint is utilizing the logic behind the DAC framework to package and move databases between environments.  (This took software on the SQL server out of the picture).  But I must go deeper so lets open up this file…. was able to use the same logic as before and convert the extension to .zip to open it up.

image

Yay! More files to open and compare. I first looked at Origin.xml and nearly found what I was looking for.

Dev

image
Prod

image

Dev has a newer DAC version than Prod.  Ok is there anything else?  What is this schema error?  Opening up the model.xml file I found the answer.

image

Inside of the model.xml file existed the corresponding Schema for the DB provider that was throwing the error.  And sure enough apps created in Prod had Schema Version 2.4.  This made sense but I had installed the latest DAC version on both environments?  What I found out is that installing the newest DAC framework version 13.0.2993.2 does not upgrade the previous version.  I then remove everything I can find for older DAC versions and give it a shot.

image

Another error? Come on!  At least I got a correlation ID.

————–

DacManager.ExecuteDacOperation: DAC operation failed with exception {Microsoft.Office.Access.Services.Design.TemplateException: Sorry, an app package could not be created.  

————–

Well that didn’t appear too helpful.  The next shot I took was a guess that the version I installed was too new.  So I installed the Data-Tier Application Framework August 2013 version 11.1.2902.0 and completely uninstalled version 13.0.2993.2.

image

SUCCESS!  I was then able to successfully add an app that was created in the Dev environment to Prod.  Mission completed, let’s load this app and move on…..(queue up loading music)……Error…..dang……

image

————–

We weren’t able to load the app’s home page. Please let your administrator know about this.

————–

Not helpful, onto ULS logs and actually some helpful info.

————–

The application’s system schema version is newer than what the current version of Access Services understands. AppVersion: 15-0-61-0. ServerVersion 15-0-59-0.

An error occurred reading application properties while opening a command scope. Microsoft.Office.Access.Services.VersioningException: Sorry, the server doesn’t recognize the version of the app you are trying to open.

————–

Alright so more discrepancies between farms.  What I found on dev was a hotfix applied KB2956180.  I knew this had been there as this was the first place I looked when we had server mismatches at the beginning.  I didn’t believe that this was vital as the overall version of Access Services was identical between farms and the KB was a lower build number.   Reviewing the KB though had a lot of direct ties into Access Services.  This was also a large hotfix (154MB) and required PSConfig to be ran.

image

I went ahead an installed this hotfix on all SP servers and ran PSconfig on all servers.  I then opened the app that had threw an error earlier and the sky’s opened up with a wonderful ray of light and I saw my Access app.

image

I certainly hope this post will save someone some time as it was quite an adventure.  The final breakdown and advice of this post:

  1. Ensure the same version of DACFramework.msi is installed across your farms
  2. Ensure you only have 1 version across your farms
  3. Do not use version 13.0.2993.2 of DACFramework.msi
  4. Ensure hotfixes (even if lower build numbers) are matched between farms

Happy SharePoint troubleshooting!

SharePoint 2013 PowerPivot Install – Error Deploying Farm Solutions

I ran into an issue recently that I thought would be good to pass along. I was installing the BI features for SharePoint 2013 on a new farm and ran into an issue when configuring PowerPivot. This environment is a 3 tier environment as depicted in the image below.

2015-07-15 13_24_45-Document1 - Word

These are the BI steps that I had already completed. (along with some helpful links)

Here is the download link for SQL Server 2014. The installer does not deploy or configure Power Pivot features in SharePoint. The following components install by default:

  • Power Pivot for SharePoint 2013. This component includes:
    1. PowerShell scripts (.ps1 files)
    2. SharePoint solution packages (.wsp)
    3. Power Pivot for SharePoint 2013 configuration tool to deploy Power Pivot in a SharePoint 2013 farm
  • Microsoft OLE DB Provider for Analysis Services (MSOLAP).
  • ADOMD.NET data provider.
  • SQL Server Analysis Management Objects.

A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the Power Pivot for SharePoint 2013 configuration tool. When you install spPowerPivot.msi you can customize the installation by excluding individual components.

The next step is to configure the PowerPivot for SharePoint Add-in

This is the area in which I ran into an issue.  Here is the technet article about the configuration.

  1. I launched the configuration toolimage<
  2. I set the default account information, DB Server and PowerPivot infoimage
  3. I set the parameters for the task “Create PowerPivot Service Application” to use my naming format.  (Remove the GUID from the DB)image
  4. I set the web application deployment location for the task “Deploy Web Application Solution”image
  5. I set the unattended account info for the task “Create Unattended Account for Data Refresh”image
  6. I then clicked Validate and Run

The configuration failed on the task “Deploy Farm Solution” with the error:

Running PS Script failed. Reason: Solution failed to deployed, reason: SPAPP01 :
Error: Cannot add the specified assembly to the global assembly cache: Microsoft.AnalysisServices.SPAddin.dll.

The solution DID deploy to SPWEB01 and SPAPP02.

These were the items I tried to debug first all of which failed:

  1. IIS Reset on all servers
  2. Reset Timer Service on all servers
  3. Ran Configuration tool as Administrator
  4. Set default account to different account names with different permissions (farm/install/service/etc…)

I then stumbled across this blog – Troubleshooting PowerPivot for SharePoint Solution Deployments – which lead me into the correct direction based on this statement:

“…the Timer service depends on the Administration service to carry out the deployment work because the Timer service usually runs under a low-privileged service account that might not have the required permissions to deploy assemblies in the Global Assembly Cache and so forth, while the Administration service uses the high-privileged Local System account with full access to all local resources.”

So I head out to SPAPP01 and check out what account is running the Administration service and sure enough it was set to the fully qualified farm domain account!

I reset the log on to be Local System and reran the configuration.

image

Success!

image

Hopefully this helps someone else moving forward!