Archive

Posts Tagged ‘SharePoint’

Delete all list Item and file versions from site and sub sites using CSOM and PowerShell

April 22, 2017 Leave a comment

The versions property is not available from client object model on the ListItem class as with server object model.  I landed on the article SharePoint – Get List Item Versions Using Client Object Model that describes how to get a list item versions property using the method GetFileByServerRelativeUrl by passing the FileUrl property. The trick is to build the list item url as “sites/test/Lists/MyList/30_.000” where 30 is the item id for which the version history needs to be retrieved. Using that information I created a PowerShell to loop through all lists in the site collection and sub sites to delete all version history from lists.

The script below targets a SharePoint tenant environment.

Please note that I have used script Load-CSOMProperties.ps1 from blog post Loading Specific Values Using Lambda Expressions and the SharePoint CSOM API with Windows PowerShell to help with querying object properties like Lambda expressions in C#. The  lines below demonstrate use of the  Load-CSOMProperties.ps1  file which I copied in the same directory where the script DeleteAllVersionsFromListItems.ps1 is.

         Set-Location $PSScriptRoot

        $pLoadCSOMProperties=(get-location).ToString()+”\Load-CSOMProperties.ps1″
       . $pLoadCSOMProperties

  •  Retrieve ServerRelativeURL property from file object

 Load-CSOMProperties -object $file -propertyNames @(“ServerRelativeUrl”); 

You can download the script from technet.

http://bit.ly/2oztKnR

from reshmeeauckloo http://bit.ly/2ozAHFb

Reshmee Auckloo
Reshmee Auckloo – Reshmee is a certified Microsoft professional and has been involved in delivering solutions across a wide variety of industry sectors in a range of assignments from SSRS to Microsoft SharePoint, Project Server development, CRM Dynamics and .Net including business requirements gathering and software quality assurance.

This article has been cross posted from reshmeeauckloo.wordpress.com (original article)

Dynamically selecting one or more approvers as participants in a “Start a task process” action in Nintex workflows for Office 365

April 20, 2017 Leave a comment

How can I dynamically select the participants/approvers in a parallel task approval process in Nintex Workflows for Office 365?

I recently created a solution where our customer wanted a task approval process to be sent to multiple users based on a user’s selection of an area of responsibility.  For example if selecting the “Finance” department the approval task may be sent to Jon Smith and Jane Doe.  However, if the “HR” department was selected then the approvals may go out to Fred Young and Joe Mandoza.  The departments and their approvers exist in a SharePoint list.  In this post I’ll describe how I solved this problem.    I hope you find it useful and that it saves you much heart ache.

Creating the Department Approvers list.

First, let’s create a SharePoint list that contains a column for the department (single line of text) and one for the approver (person).

I filled in some data so that if I select the Finance department then two approvers ,Clements, Chris and Chris Clements will be selected and added as participants in the task process.

Creating the primary list.

Next, let’s create a list to which we will attach our Nintex workflow.  This list will have one lookup column that will be linked to our Department Approvers list.  Let’s call our primary list, Quarterly Reports.

Here is what the new item form looks like.  Note the look up column displaying the available departments from Department Approvers.

Creating the “Start Approval Task Process” workflow.

Start by creating a new list workflow on the Quarterly Reports list.  Call the workflow Get Approvals.

Step 1: Query the list to get the approvers based on the selected department.

Use the query list action with the following configuration:

We are going to select the approver column from this Department Approvers list where the department column matches the selected department on the quarterly reports list. Configure the action as shown below.

Note that we are selecting the “User Name” property of the approver column.  In Office 365 this will be the person’s E-mail address.  Also notice that I created a workflow variable of the collection type, colDepartmentApprovers to store the results in.

Step 2: Transform the colDepartmentApprovers variable into a dictionary

If you were to run the workflow now and observe the contents of the colDepartmentApprovers variable you would see that its format is that of a JSON object called “Approver” which contains an array of “UserName” key value pairs.

[{"Approver":[{"UserName":"Chris.Clements@mydomain.com"},{"UserName":"cleme_c@mydomain.com"}]}]

But look closely at the leading and trailing brackets [].  This means that this is an array of JSON approver objects each with its own array of key value pairs.

We need to crack open this string to get at the E-mail addresses inside but it is going to take several steps to get there so grab a beer and chill.

Let’s add the “Get Item from Collection” action.

Configure the action as shown below:

Notice that I hard-coded the 0 index counter.  This means that I will always fetch the first item in the collection.  I realize that if a user were to create a second record for the “finance” department in the department approvers list that this workflow would not pull in the approvers listed in that row.  To handle that case we would need to loop across the colDepartmentApprovers variable. However, to keep this post concise I will only handle one row of approvers per department.

In the output section of this action I created a workflow variable called “dicDepartmentApprovers“.  This variable will hold whatever was in the first position (the 0 index) of the colDepartmentApprovers variable.

Let’s run the workflow again and check out the contents of dicDepartmentApprovers.

{"Approver":[{"UserName":"Chris.Clements@mydomain.com"},{"UserName":"cleme_c@mydomain.com"}]}

We were successful at pulling the first and only “Approver” row from the collection. The leading and trailing brackets “[]” are gone! Now we need to get at the array of key value pairs containing the E-mail addresses.

Step 3: Parse the Approver object into a dictionary

Let’s add the “Get an Item from Dictionary” action.

Configure the action as show below:

In this step I am selecting the “Approver” path from the dictionary called dicDepartmentApprovers however, I insert the resulting data back into the collection variable.  What’s going on here?  Let’s run the workflow again and see what the data in the colDepartmentApprovers variable looks like.

[{"UserName":"Chris.Clements@mydomain.com"},{"UserName":"cleme_c@mydomain.com"}]

Hopefully you see that we are again dealing with an array/collection of data.  Selecting the “Approver” value from the dictionary in yields a collection of “UserName” values.  This time we are going to loop across this collection and crack out the individual E-mails.

Step 5: Looping across the colDepartmentApprovers collection

Add the “For Each” action.

Configure the action as shown below:

I specified our collection variable, colDepartmentApprovers, as the input collection across which we are going to loop.  For the output variable I created a new workflow variable called “dicUserName“.  This variable holds the currently enumerated item in the collection.  In other words, it is going to hold a single instance of the “Username” key value pair each time through the loop.  Its value will look something like this on the first trip through the loop: {"UserName":"Chris.Clements@mydomain.com"}  and then it will look like this: {"UserName":"cleme_c@mydomain.com"} during it’s second loop.

Given that the value of the dicUserName is dictionary we can use the “Get an Item from Dictionary” and specify the “UserName” path to arrive at the E-mail address.  Let’s take a look.

Add the “Get and Item from Dictionary” action. Be sure to place this action inside of the for each looping structure.

Configure the action as shown below:

Again we use the Item name or Path setting to retrieve the value.  In this case we set “UserName” and save the results into a new workflow variable called txtApproverEmail.  Its contents look like this:

Chris.Clements@mydomain.com

VICTORY!  Well, almost. Remember we want to our approval task to go out to both E-mail addresses at once.  We are going to need to concatenate our E-mail addresses into a format that is acceptable to the “Start a task process” action.

Let’s add the “Build String” action.

Configure the Action as shown below:

In this action I am stringing together our E-mail address with a new, empty workflow variable called txtCombinedApproverEmail.  I take the result of the concatenation and run it back in the txtCombinedApproverEmail variable.  It works like this:

First time through the loop where txtApproverEmail = “chris.clements@mydomain.com” and txtCombinedApproverEmail is empty.

chris.clements@mydomain.com;

The second time into the loop where txtApproverEmail = “cleme_c@mydomain.com” and txtCombinedApproverEmail = “chris.clements@mydomain.com;”

cleme_c@mydomain.com;chris.clements@mydomain.com;

PRO TIP: Notice that there is NO space between the E-mail addresses and the semicolon.  You are warned.

Step 6: Beginning the Approval Task

Finally, we are at the last step. That is to create the “Start a Task Process” action and assign our txtCombinedApproverEmail in the Participants field.  Remember to create this action outside of the for each loop.

And the configuration:

All that I am really concerned with here is setting the participants field to my workflow variable txtCombinedApproverEmail.  Let’s run the workflow and see if we get our task assigned to two approvers at once based on our selected “Finance” department.

Ah, and there you have it:

This is the entire workflow I created in this post:

Closing Thoughts

I have to be honest.  This feels like it shouldn’t be this hard to do in a tool such as Nintex for Office 365.  Seven steps?  Really?  If I were a “citizen developer” there is no way I could have pulled off a task like this. Knowledge of JSON encoding is essential.

Chris

 

 

 

 

 

Chris Clements
I am a senior software developer and development team lead in Houston Texas. I am passionate about the “art” of software development. I am particularly interested in software design patterns and the principles of SOLID object-oriented code. I am an evangelist for test driven development. I love to think and write about my day-to-day experiences in the trenches of enterprise IT. I relish the opportunity to share my experiences with others.

From the wire to the presentation, I am holistic solutions guy. I have broad experience in client side technologies such as Javascript, Ajax, AngularJS, Knockout, and Bootstrap. I have extensive experience with MVC, MVVM, and ASP.NET Web Forms. I am strong in SQL Databases, performance tuning, and optimization. I also have a background in network engineering, wide-area and inter-networking.

This article has been cross posted from jcclements.wordpress.com/ (original article)

Rebuild and Reorganize indexes on all tables in MS database

March 14, 2017 Leave a comment

As part of database maintenance, indexes on databases have to be rebuilt or reorganised depending on how fragmented the indexes are. From the article Reorganize and Rebuild Indexes, the advice is to reorganise index if avg_fragmentation_in_percent value is between 5 and 30 and to rebuild index if it is more than 30%.

The script below queries all fragmented indexes more than 5 percent and using a cursor a loop is performed on the results to rebuild or reorganise  indexes depending on the percentage of fragmentation using dynamic SQL, i.e.

The script can be downloaded from technet gallery  , i.e. if avg_fragmentation_in_percent value is between 5 and 30 then reorganise else rebuild.


declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)


declare FragmentedTableList cursor for
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC


OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment


WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList

from reshmeeauckloo http://bit.ly/2mHUNQb

Reshmee Auckloo
Reshmee Auckloo – Reshmee is a certified Microsoft professional and has been involved in delivering solutions across a wide variety of industry sectors in a range of assignments from SSRS to Microsoft SharePoint, Project Server development, CRM Dynamics and .Net including business requirements gathering and software quality assurance.

This article has been cross posted from reshmeeauckloo.wordpress.com (original article)

SQL script batch execution using sqlcmd in PowerShell

There is often a mismatch between needs of the development team (multiple discreet T-SQL files for separate concerns) and the release team (the requirement  for one step automated deployment) . The script bridges the requirement by using sqlcmd.exe to run a batch of SQL scripts.

A text file is used listing all sql files that need to run in a particular order to avoid errors which may occur if there are dependencies between the scripts. Instead of using a text file a number can be prefixed to the scripts based on the order they need to run.

The script expects two parameters –

  • Path of folder containing the set of T-SQL files (and the manifest file, see below)
  • Connection string

The script can be downloaded from technet gallery.

## Provide the path name of the SQL scripts folder and connnection string
##.\SQLBatchProcessing.ps1 -SQLScriptsFolderPath "C:\Sql Batch Processing\SQLScripts" -ConnectionString "DEV-DB-01"
Param(
[Parameter(Mandatory=$true)][String]$ConnectionString ,
[Parameter(Mandatory=$true)][String]$SQLScriptsFolderPath
)
Set-ExecutionPolicy -ExecutionPolicy:Bypass -Force -Confirm:$false -Scope CurrentUser
Clear-Host
#check whether the SQL Script Path exists
$SQLScriptsPath = Resolve-Path $SQLScriptsFolderPath -ErrorAction Stop
#a manifest file will exisit in the SQL scripts folder detailing the order the scripts need to run.
$SQLScriptsManifestPath = $SQLScriptsFolderPath + "\Manifest.txt"
#Find out whether the manifest file exists in the the SQL Scripts folder
$SQLScriptsManifestPath = Resolve-Path $SQLScriptsManifestPath -ErrorAction Stop


#if manifest file found iterate through each line , validate if corresponding SQL script exists in file before running each of them
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
Resolve-Path $SQLScriptsPath -ErrorAction Stop
}
$SQLScriptsLogPath = $SQLScriptsFolderPath + "\" + "SQLLog.txt"
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "Started processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
$text = "Running script " + $_.ToString();
Add-Content -Path $SQLScriptsLogPath -Value $text
sqlcmd -S "DEV-DB-01" -i $SQLScriptsPath | Out-File -Append -filepath $SQLScriptsLogPath
}
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "End processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""

from reshmeeauckloo http://bit.ly/2mntBWD

Reshmee Auckloo
Reshmee Auckloo – Reshmee is a certified Microsoft professional and has been involved in delivering solutions across a wide variety of industry sectors in a range of assignments from SSRS to Microsoft SharePoint, Project Server development, CRM Dynamics and .Net including business requirements gathering and software quality assurance.

This article has been cross posted from reshmeeauckloo.wordpress.com (original article)

Create DevTest Labs in Azure

January 11, 2017 Leave a comment

Azure DevTest Labs is available in UK South and UK West as from December 2016, in addition to the other 21 regions it has supported.

The steps to create the DevTest lab are

  • Login to Azure portal as administrator
  • Click the green + New menu

createdevtestlab

  • Type DevTest Labs into the search box
  • Select DevTestLabs from the results page
  • Click on Create from the Description page.

The advantages using DevTest Labs as mentioned from the Description page are

DevTest Labs helps developers and testers to quickly create virtual machines in Azure to deploy and test their applications. You can easily provision Windows and Linux machines using reusable templates while minimizing waste and controlling cost.

  • Quickly provision development and test virtual machines
  • Minimize waste with quotas and policies
  • Set automated shutdowns to minimize costs
  • Create a VM in a few clicks with reusable templates
  • Get going quickly using VMs from pre-created pools
  • Build Windows and Linux virtual machines

 

  • Enter the lab name, select the subscription, select location North Europe, tick the Pin to Dashboard tick box and alternatively update the Auto-shutdown schedule.

createdevtestlab_details

  • Click on Create.
  • The dashboard is displayed with a new tile showing that the DevTest Lab is being deployed.deployingdevtest-labs_inprogress
  • The DevTest Lab page is displayed once deployment of the DevTest Lab is completed.

devtest-labs_completed

 

Instead of using the Portal, PowerShell can be used to create Azure DevTest Lab. The GitHub repository http://bit.ly/2jhNQ4t an example how it can be achieved.

The repository has a readme file, a deployment template with a corresponding parameters file and a PowerShell script to execute the deployment.

The Readme file provides a description of the resources created.

About the resources created in the Demo Lab:

The ARM template creates a demo lab with the following things:

* It sets up all the policies and a private artifact repo.

* It creates 3 custom VM images/templates.

* It creates 4 VMs, and 3 of them are created with the new custom VM images/templates.

To run the PowerShell script the subscriptionId is required. This can be obtained from the cmdlet Login-AzureRmAccount.

login-azurermaccount

The PowerShell is run as below

.\ProvisionDemoLab.ps1 -SubscriptionId 41111111-1111-1111-1111-111111111111 
-ResourceGroupLocation northeurope -ResourceGroupName RTestLab

provisiondemolab

The script produces the following results.

ProvisionDemoLab_SuccededMessage.PNG

From the portal , the result shows the 4 vms.provisiondemolab_portal

The repositories have been created as well.provisiondemolab_repositories

Custom images of the running machines have been created as well.

ProvisionDemoLab_CustomImages.PNG

There are artifacts ready to be used though none are applied yet to the virtual machines.

ProvisionDemoLab_Artifacts.PNG

You can create your own templates/parameters files in the Portal by creating a new resource and exporting  instead of executing the configuration in the GitHub repository.

from reshmeeauckloo http://bit.ly/2j4Ys94

Reshmee Auckloo
Reshmee Auckloo – Reshmee is a certified Microsoft professional and has been involved in delivering solutions across a wide variety of industry sectors in a range of assignments from SSRS to Microsoft SharePoint, Project Server development, CRM Dynamics and .Net including business requirements gathering and software quality assurance.

This article has been cross posted from reshmeeauckloo.wordpress.com (original article)

Unable to update User Profile Property due to Policy Settings set to Disabled

December 23, 2016 Leave a comment

The web service  userprofileservice  <site url>/_vti_bin/userprofileservice.asmx has to be used to update user profile properties for other users in SharePoint 2013 and SharePoint Online.

Even though the HTTP status code response returned was 200 which means successful, the user profile property was blank when the user properties was queried using the REST api method sp.userprofiles.peoplemanager/getpropertiesfor.

<site url>/_api/sp.userprofiles.peoplemanager/getpropertiesfor(@v)?@v=’i%3A0%23.f%7Cmembership%7CfirstName.LastName%40arteliauk.onmicrosoft.com’

The user profile property updated was PictureUrl , however it was showing as null

<d:PictureUrl m:null="true" />

After spending a couple of hours trying to figure out why the use profile property was not showing,  I decided to review the user profiles properties for Picture property and how it is different from the other user profile properties, e.g. Job Title which was showing the updated value.

The Policy Settings on Picture property was disabled for some reason.

pictureurl-policysettingsettodisabled-1

Disabled from Manage user profile policies in SharePoint admin center meant

The property or feature is visible only to the User Profile Service administrator. It does not appear in personalized sites or Web Parts, and it cannot be shared.

Also it meant the property was not visible when queried by REST API.

After updating the policy settings to Optional, the pictureURL property for the user is being returned by the REST API query and showing up in SharePoint sites as well.

pictureurl-policysettingsettooptional

<d:PictureUrl>siteurl/StaffDetail/js.jpg?t=63579640559</d:PictureUrl>

 

 

 

from reshmeeauckloo http://bit.ly/2hPZfdw

Reshmee Auckloo
Reshmee Auckloo – Reshmee is a certified Microsoft professional and has been involved in delivering solutions across a wide variety of industry sectors in a range of assignments from SSRS to Microsoft SharePoint, Project Server development, CRM Dynamics and .Net including business requirements gathering and software quality assurance.

This article has been cross posted from reshmeeauckloo.wordpress.com (original article)

Unable to change Content Type of document in Library

December 23, 2016 Leave a comment

I was trying to remove a content type from a document library which was associated with multiple content types. As a first action, I identified all documents tagged with the content type to be removed and tried to update them with another content type. However some documents were still showing the old content type despite saving it with the new content type by updating the [Content Type] field.

contenttypecolumn

I tried removing the document and adding the document back to the document library, unfortunately it was still referencing the old content type.

The solution that worked for me was to open the document in desktop office and open the “Advanced Properties”.

advancedproperties

Find and select the property ContentTypeId and click on Delete.

contenttypeiddelete

After deleting the ContentTypeId property I was able to update the content type property of the document and eventually remove the old content type from the document library.

from reshmeeauckloo http://bit.ly/2hZNhvL

Reshmee Auckloo
Reshmee Auckloo – Reshmee is a certified Microsoft professional and has been involved in delivering solutions across a wide variety of industry sectors in a range of assignments from SSRS to Microsoft SharePoint, Project Server development, CRM Dynamics and .Net including business requirements gathering and software quality assurance.

This article has been cross posted from reshmeeauckloo.wordpress.com (original article)

%d bloggers like this: