Tag Archives: MimboloveSharePoint

SharePointSDK PowerShell Module Updated to Version 2.1.0

Written by Tao Yang

OK, this blog has been very quiet recently. Due to some work related requirements, I had to pass few Microsoft exams. so I have spent most of my time over the last couple of months on study. Firstly, I passed the MCSE Private Cloud Re-Certification exam, then I passed the 2 Azure exams: 70-532 Developing Microsoft Azure Solutions and 70-533 Implementing Microsoft Azure Infrastructure Solutions. Other than studying and taking exams, I have also been working on a new version of the SharePointSDK PowerShell module during my spare time. I have finished everything on my to-do list for this release last night, and I’ve just published version 2.1.0 on PowerShell Gallery and GitHub:

This new release includes the following updates:

01. Fixed the “format-default : The collection has not been initialized.” error when retrieving various SharePoint objects.

i.e. When retrieving the SharePoint list in previous versions using Get-SPList function, you will get this error:

image

This error is fixed in version 2.1.0. now you will get a default view defined in the module:

image

02. SharePoint client SDK DLLs are now automatically loaded with the module.

I have configured the module manifest to load the SharePoint Client SDK DLLs that are included in the module folder. As the result of this change, the Import-SPClientSDK function is no longer required and has been removed from the module completely.

In the past, the Import-SPClientSDK function will firstly try to load required DLLs from the Global Assembly Cache (GAC) and will only fall back to the DLLs located in the module folder if they don’t exist in GAC. Since the Import-SPClientSDK function has been removed, this behaviour is changed in this release. Starting from this release, the module will not try to load the DLLs from GAC, but ALWAYS use the copies in the module folder.

03. New-SPListLookupField function now supports adding additional lookup columns.

When adding a lookup field in a SharePoint list, you can specify including one or more additional columns. i.e.:

image

The previous versions of this module did not support adding additional columns when creating a lookup field. In this version, you are able to add additional columns using the “-AdditionalSourceFields” parameter to achieve this goal.

04. Various minor bug fixes

Other than above mentioned updates, this version also included various minor bug fixes.

Special Thanks

I’d like to thank my friend and fellow CDM MVP Jakob Gottlieb Svendsen (@JakobGSvendsen) for his feedback. Most of the items updated in this release were results of Jakob’s feedbacks.

A Major Update for the SharePointSDK PS Module

Written by Tao Yang

Sharepoint-2013-LogoIntroduction

This blog has been a bit quiet over the last few weeks. This is because I have been really really busy. I have spent a lot of time working on an updated version of the SharePointSDK PS module. Just in case you have not played with this module, here’s some background info:

Just over a year ago, I posted a PowerShell / SMA / Azure Automation module on this blog called SharePointSDK. Few months ago, I have also published this module on Github and PowerShell Gallery. This module was designed to help automate operations around SharePoint lists (i.e. CRUD operations for SharePoint list items). Coupling SharePoint (both On-prem version or SharePoint Online) with Azure Automation (or even SMA) is becoming more and more common in the community when designing automation solutions. This module provides ways for your automation runbooks to interact with SharePoint list items.

However, I believe the original 1.0 release was really basic, and there are still a lot I’d like to cover in this module. Now I’m pleased to announce the new major release (version 2.0.1) is now available on both Github and PowerShell Gallery.

What’s New?

I’ve included the following updates in version 2.0.1:

  • 26 additional functions!
  • Updated the SharePoint CSOM (Client Component SDK) DLLs to the latest version in the module.
  • Created a separate help file for the module. Get-Help is now fully working
  • Various bug fixes

The table below lists all the functions that are shipped in the current release (version 2.0.1):

Function Description Released on Version
Import-SPClientSDK Load SharePoint Client SDK DLLs 1.0
New-SPCredential Create a SharePoint credential that can be used authenticating to a SharePoint (online or On-Premise) site. 1.0
Get-SPServerVersion Get SharePoint server version. 1.0
Get-SPListFields Get all fields from a list on a SharePoint site. 1.0
Add-SPListItem Add a list item to the SharePoint site. 1.0
Get-SPListItem Get all items from a list on a SharePoint site or a specific item by specifying the List Item ID. 1.0
Remove-SPListItem Delete a list item to the SharePoint site. 1.0
Update-SPListItem Update a list item to the SharePoint site. 1.0
Get-SPListItemAttachments Download all attachments from a SharePoint list item. 1.0
Add-SPListItemAttachment Upload a file as a SharePoint list item attachment. 1.0
Remove-SPListItemAttachment Remove a SharePoint list item attachment. 1.0
New-SPList Create a new list on the SharePoint site. 2.1
Remove-SPList Remove a list from the SharePoint site. 2.1
Get-SPList Get a list from the SharePoint site. 2.1
New-SPListLookupField Create a new lookup Field for a SharePoint list. 2.1
New-SPListCheckboxField Create a new checkbox Field for a SharePoint list. 2.1
New-SPListSingleLineTextField Create a new single line text Field for a SharePoint list. 2.1
New-SPListMultiLineTextField Create a new Multi-line text Field for a SharePoint list. 2.1
New-SPListNumberField Create a new number Field for a SharePoint list. 2.1
New-SPListChoiceField Create a new choice Field for a SharePoint list. 2.1
New-SPListDateTimeField Create a new date time Field for a SharePoint list. 2.1
New-SPListHyperLinkField Create a new Hyperlink or Picture Field for a SharePoint list. 2.1
New-SPListPersonField Create a new Person or Group Field for a SharePoint list. 2.1
Remove-SPListField Remove a Field from a SharePoint list. 2.1
Update-SPListField Update a SharePoint list field. 2.1
Set-SPListFieldVisibility Set the visibility of a SharePoint list field. 2.1
Get-SPGroup Get a single group or all groups from the SharePoint site. 2.1
New-SPGroup Create a new SharePoint group. 2.1
New-SPGroupMember Add an user to a SharePoint group. 2.1
Remove-SPGroupMember Remove an user from a SharePoint group. 2.1
Clear-SPSiteRecycleBin Empty the SharePoint site recycle bin. 2.1
Get-SPSiteTemplate Get avaialble Site Template(s) from the SharePoint site. 2.1
New-SPSubSite Create a new SharePoint sub site. 2.1
Get-SPSubSite Get all SharePoint sub sites from a root site. 2.1
Remove-SPSubSite Delete a SharePoint sub site. 2.1
Add-SPListFieldToDefaultView Add a SharePoint list field to the list default view. 2.1
Remove-SPListFieldFromDefaultView Remove a SharePoint list field to the list default view. 2.1

As you can see, the previous version has shipped 11 functions, and 26 additional functions have been added to the current release (2.0.1). With this release, other than the SharePoint list items, we are also able to manage SharePoint lists, list fields, groups, group members, and even subsites. I have included functions to create what I believe the most common list fields (as highlighted below):

image

Future Plans

At this stage, there are still few things I’d like to cover in this module but I simply do not have time. Since I think I have reached another milestone at this stage, I have decided to release this version now and roll other ideas into the future release.

In the second week of March, I will be presenting at SCU APAC (Kuala Lumpur, Malaysia) and Australia (Melbourne).  I am presenting 2 identical sessions at both locations:

  • Be a hero and save the day with OMS and Power BI (Co-present with CDM MVP Alex Verkinderen)
  • Automation for IT Ops with OMS and Azure Automation (Co-present with CDM MVP Pete Zerger)

As part of the demos I have prepared for the Azure Automation session with Pete, I will cover how I’m using this module as part of my automation solutions.

After SCU, I am planning to write another blog post for my Automating OpsMgr series which will cover one of the our SCU demos (I know, it has been a long time since my last post for that series). I will also cover this module in more details in this upcoming blog post.

Download the Module

So for now, if you’d like to give this module a try, you can find it from both GitHub and PowerShell Gallery. All functions are fully documented in the help file. You can access the help document as well as code examples using Get-Help with –Full switch.

Lastly, if you have any feedback, or suggestions for future releases, please feel free to drop me an email.

This is all I have to share for today, until next time, happy automating Smile.

A SMA Integration Module For SharePoint List Operations

Written by Tao Yang

Background

Many Microsoft System Center Orchestrator and Service Management Automation (SMA) users may agree with me, that these two automation platform does not have feature rich end user portals natively. Although System Center Service Manager can be used as a user portal for triggering SCORCH/SMA runbooks, Microsoft SharePoint is also a very good candidate for this purpose.

Integrating SharePoint with Orchestrator and SMA is not something new, many people have done this already. i.e.

System Center Universe America 2014 – Orchestrating Daily Tasks Like a Pro (by Pete Zerger and Anders Bengtsson)

Service Management Automation and SharePoint (by Christian Booth and Ryan Andorfer)

In my opinion, SharePoint (especially SharePoint lists) provides a quick and easy way to setup a web based end user portal for orchestration runbooks. I have also blogged my experiences in the past:

My Experience Manipulating MDT Database Using SMA, SCORCH and SharePoint

SMA Runbook: Update A SharePoint 2013 List Item

To me, not only I am using SharePoint 2013 in my lab; SharePoint Online from my Office 365 subscription, I also have no choice but using SharePoint 2010 in real life.

In my opinion, it is complicated to write SMA runbooks to interact with SharePoint (Using SharePoint web based APIs), not to mention the different versions of SharePoint also dictates how the runbook should be written. It is easier to use Orchestrator as a middle man in between SMA and SharePoint so we can use Orchestrator’s SharePoint integration pack.

Earlier this month, I was developing solutions to use SMA and Azure Automation to create OpsMgr Management Packs catalog on SharePoint 2013 / SharePoint Online sites. I have blogged the 2 solutions here:

On-Premise Solution (SMA + SharePoint 2013)

Cloud Based Solution (Azure Automation + SharePoint Online)

As I mentioned in the previous posts, I had to write a separate SMA module to be used in Azure Automation to interact with SharePoint Online because SharePoint Online sites require a different type of credential (SharePointOnlineCredential) that the PowerShell cmdlet Invoke-RESTMethod does not support. I called that module SharePointOnline back in the previous post and it utilises assemblies from the SharePoint Client Component SDK. I think the SharePoint people also refer to this SDK as Client-Side Object Model (CSOM)

After the MP catalogs posts were published, I have decided to spend a bit more time on the SharePoint Client Component SDK and see if it can help me simplify the activities between SMA and SharePoint. I was really happy to find out, the SharePoint Client Component SDK works for SharePoint 2013, SharePoint Online and SharePoint 2010 (limited). So I have decided to update and extend the original module, making it a generic module for all 3 flavours of SharePoint.

After couple of weeks of coding and testing, I’m pleased to announce the new module is now ready to be released. I have renamed this module to SharePointSDK (Sorry I’m not really creative with names Smile with tongue out).

 

SharePointSDK Module Introduction

The SharePointSDK module contains the following functions:

image

CRUD Operations for SharePoint List items:

Function Description Compatible SharePoint Version
Add-SPListItem Add an item to a SharePoint list 2010, 2013 and SP Online
Get-SPListFields Get all fields of a SharePoint list 2010, 2013 and SP Online
Get-SPListItem Get all list items of a SharePoint list or a specific item by specifying the List Item ID 2010, 2013 and SP Online
Remove-SPListItem Delete an item from a SharePoint list 2010, 2013 and SP Online
Update-SPListItem Update one or more field values of a SharePoint list item 2010, 2013 and SP Online

The functions listed above are the core functionalities this module provides. it provides simplified ways to manipulate SharePoint list items (Create, Read, Update, Delete).

Miscellaneous Functions

Function Description Compatible SharePoint Version
Import-SPClientSDK Load SharePoint Client Component SDK DLLs 2010, 2013 and SP Online
New-SPCredential Based on the type of SharePoint site (On-Prem vs SP Online), create an appropriate credential object to authenticate to the Sharepoint site. 2010, 2013 and SP Online
Get-SPServerVersion Get SharePoint server version 2010, 2013 and SP Online

These functions are called by other functions in the modules. It is unlikely that runbook authors will need to use them directly.

SharePoint List Attachments Operations

Function Description Compatible SharePoint Version
Add-SPListItemAttachment Add an attachment to a SharePoint list item 2013 and SP Online
Get-SPListItemAttachments Download all attached files from a SharePoint list item 2013 and SP Online
Remove-SPListItemAttachment Delete an attached file (based on file name) from a SharePoint list item 2013 and SP Online

As the names suggest, these functions can be used to manage attachments for SharePoint list items.

I’d like to point out  that the Add-SPListItemAttachment function not only support uploading an existing file to the SharePoint list item. it can also be used to create an attachment file directly using a byte array. This function can be used in 3 scenarios:

  • Uploading an existing file from the file system
  • Directly creating a text based file with some contents as a list item attachment.
  • Read the content of an existing binary (or text)  file, save it as a attachment with a different name

 

Configuration Requirements

Download and Prepare the module

The module zip file should consist the following 5 files:

image

  • Microsoft.SharePoint.Client.dll – One of required DLLs from the SDK
  • Microsoft.SharePoint.Client.Runtime.dll – One of required DLLs from the SDK
  • SharePointSDK.psd1 – Module Manifest file
  • SharePointSDK.psm1 – PowerShell module file
  • SharePointSDK-Automation.json – SMA Integration Module Meta File (where the connection asset is defined).

Download SharePointSDK Module

Note:

The zip file you’ve downloaded from the link above DOES NOT contain the 2 DLL files. I am not sure if Microsoft is OK with me distributing their software / intellectual properties. So, just to cover myself, you will need to download the SDK (64-bit version) from Microsoft directly (https://www.microsoft.com/en-us/download/details.aspx?id=35585), install it on a 64-bit computer, and copy above mentioned 2 DLLs into the SharePointOnline module folder.

Once the SDK is installed, you can find these 2 files in “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\” folder.

Once the DLLs are placed into the folder, zip the SharePointSDK folder to SharePointSDK.zip file again, and the integration module is ready.

image

Import Module

Once the DLLs are zipped into the module zip file, import the module into SMA by using the Import Module button under Assets tab

image

Create a Connection object to the SharePoint site

After the module has been successfully, a connection to SharePoint Site must be created. The Connection type is “SharePointSDK”

image

The following fields must be filled out:

  • Name: Name of the connection.
  • SharePointSiteURL: URL to your sharepoint site
  • UserName : a User who should be part of the site members role (members group have contribute access).
    • If the site is a SharePoint Onine site, this username MUST be in the email address format. (i.e. yourname@yourcompany.com). I believe this account must be an account created in the Office 365 subscription. I have tried using an outlook.com account (added as a SharePoint site member), it didn’t work.
    • When connecting to a On-Prem SharePoint site, you can use the Domain\UserName format (As shown in the screenshot below)
  • Password: Password for the username you’ve specified.
  • IsSharePointOnlineSite: Boolean field (TRUE or FALSE), specify if it is a SharePoint Online site.

i.e. the connection to a SharePoint site in my lab:

image

Sample Runbooks

In order to better demonstrate this module, I have also created 10 sample runbooks:

image

Download Sample runbooks

I’ll now go through each sample runbook.

Runbook: Sample-SPNewUserRequestList

This sample runbook creates a brand new dummy new users requests list on your SharePoint site. The list created by this runbook will then be used by other sample runbooks (for demonstration purposes).

This runbook is expecting 2 input parameters:

  • ListName: The Display Name that you’d like to name the new users requests list (i.e. New Users OnBoarding Requests).
  • SPConnection: The name of the SharePointSDK connection that you’ve created previously (i.e. Based on the connection I’ve created in my lab as shown previously, it is “RequestsSPSite”

image

This runbook creates a list with the following fields:

image

Runbook: Sample-SPGetListFields

This runbook demonstrates how to retrieve all the fields of a particular list.

image

Runbook: Sample-SPAddListItem

This runbook adds an item to the New Users Requests list the previous runbook created. It also demonstrates how to create a text file attachment directly to the list item (without having the need for an existing file on the file system).

It is expecting the following inputs:

  • Title (New users title, i.e. Mr. Dr. Ms, etc)
  • FirstName (New user’s first name)
  • LastName (New user’s last name)
  • Gender (New user’s Gender: Male / Female)
  • UserName (New user’s user vname)
  • AttachmentFileName (file name of the text based attachment)
  • TextAttachmentContent (content of the text file attachment)
  • NewUserListName (display name of the new users requests list. i.e. New Users OnBoarding Requests)
  • SPConnection (The name of the SharePointSDK connection that you’ve created previously (i.e. Based on the connection I’ve created in my lab as shown previously, it is “RequestsSPSite”)

i.e.

image

The list item is created on SharePoint:

SNAGHTML1d5a6df8

Attachment content:

image

Runbook: Sample-SPUpdateListItem

This runbook can be used to update fields of an existing list item on the New Users Requests list.

Runbook: Sample-SPGetAllListItems

This runbook can be used to retrieve ALL items from a list. Each list item are presented as a hash table.

image

Runbook: Sample-SPGetListItem

This runbook can be used to retrieve a single item from a list.

image

Runbook: Sample-SPDeleteListItem

This runbook deletes a single list item by specifying the List Item ID.

Runbook: Sample-SPAddListItemAttachment

This runbook demonstrates 2 scenarios:

  • Directly attaching a file to a list item
  • attach and rename a file to a list item

image

image

Runbook: Sample-SPDeleteListItemAttachments

This runbook demonstrates how to delete an attachment from a list item (by specifying the file name).

Runbook: Sample-SPDownloadListItemAttachments

This runbook demonstrates how to download all files attached to a list item:

image

Files downloaded to the destination folder:

image

Benefit of Using the SharePointSDK Module

Using as a Regular PowerShell Module

As we all know, SMA modules are simply PowerShell modules (sometimes with optional SMA module meta file .json for creating connections). Although this module is primarily written for SMA, it can also be used in other environments such as a regular PowerShell module or in Azure Automation. When using it as a normal PowerShell module, instead of passing the SMA connection name into the functions inside the module, you may provide each individual value separately (Username, password, SharePoint Site URL, IsSharePointOnlineSite).

Simplified scripts to interact with SharePoint

When using this module, most of the operations around the list item only takes very few lines of code.

i.e. Retrieving a list item:

Using PowerShell:

Using PowerShell Workflow (in SMA):

If you use SharePoint 2013’s REST API, the script will be much longer than what I’ve shown above.

Same Code for Different SharePoint Versions

The SharePoint REST API has been updated in SharePoint 2013. Therefore, if we are to use the REST API, the code for Share Point 2013 would look different than SharePoint 2010. Additionally, when throwing SharePoint Online into the mix, as I mentioned previously, it requires different type of credential for authentication, it further complicates the situation if we are to use the REST API. This makes our scripts and runbooks less generic.

By using this SharePointSDK module, I am able to use the same runbooks on SharePoint 2010, 2013 and SharePoint Online sites.

Limitations

During testing, I noticed the 3 attachments related functions in the SharePointSDK module would not work on SharePoint 2010 sites. These functions are:

  • Add-SPListItemAttachment
  • Remove-SPListItemAttachment
  • Get-SPListItemAttachments

After a bit of research, looks like it is a known issue. I didn’t think it too much a big deal because all the core functions (CRUD operations for the list items) work with SharePoint 2010. Therefore, in these 3 functions, I’ve coded a validation step to exit if the SharePoint Server version is below version 15 (SharePoint 2013):

image

Conclusion

If you are using SMA and SharePoint together, I strongly recommend you to download this module and the sample runbooks and give it a try. If you have a look at the sample runbooks, I’m sure you will realise how easy it is to write PowerShell code interacting with SharePoint.

In case you didn’t see the download links, you can download them here:

Download SharePointSDK Module

Download Sample Runbooks

Lastly, I’m not a SharePoint specialist. If you believe I’ve made any mistakes in my code, or there is room for improvement, I’d like to hear from you. Please feel free to drop me an email Smile.

Using Azure Automation to Build Your Own Microsoft OpsMgr MP Catalog on SharePoint Online

Written by Tao Yang

Background

Previously, I have posted how to build your own Microsoft OpsMgr MP Catalog on SharePoint 2013 using SMA. It is a solution that you can implement on-prem in your own environment if you have existing SharePoint 2013 and SMA infrastructure in place. As I mentioned at the end of the previous post, I have also developed a solution to populate this MP catalog on a Office 365 SharePoint Online site using Azure Automation – a 100% cloud based solution. Because of the differences in APIs between on-prem SharePoint 2013 and SharePoint online, one of the runbooks is completely different than the on-prem version. In this post, I will go through how I’ve setup the MP catalog on SharePoint Online using Azure Automation.

01. Create a List on the SharePoint Online site

The list creation and customization process is exactly the same as the On-Prem SharePoint 2013 version. I won’t go through this again. please refer to Step 1 and the customizing SharePoint List sections in my previous post.

02.  Create a Runbook to Retrieve Microsoft MP info

Again, this runbook is unchanged from the On-Prem version. Simply import it into your Azure Automation account.

Download Get-MSMPCatalog

SNAGHTML803890ed

03. Prepare the SMA Integration Module SharePointOnline

In order to authenticate to SharePoint Online sites, We must use a SharePointOnlineCredentials instance in the script. In my previously post, I wrote a runbook called Populate-OnPremMPCatalog. That runbook utilize Invoke-RestMethod PowerShell cmdlet to interact with SharePoint 2013’s REST API. Unfortunately, we cannot pass a SharePointOnlineCredentials object to this Cmdlet, therefore it cannot be used in this scenario.

Additionally, the SharePointOnlineCredentials class comes from the SharePoint Client Component SDK. In order to create a SharePointOnlineCredentials object in PowerShell scripts, the script need to firstly load the assemblies from 2 DLLs that are part of the SDK. Because I can’t install this SDK in the Azure Automation runbook servers, I needed to figure out a way to be able to load these DLLs in my runbook.

As I have previously written SMA Integration Modules with DLLs embedded in. This time, I figured I can do the same thing – Creating a PowerShell / SMA Integration module that includes the required DLLs. Therefore, I’ve created a customised module in order to load the assemblies. But since the SDK also consists of other goodies, I have written few other functions to perform CRUD (Create, Read, Update, Delete) operations on SharePoint list items. These functions have made the runbook much simpler.

I called this module SharePointOnline, it consists of 5 files:

  • Microsoft.SharePoint.Client.dll – One of required DLLs from the SDK
  • Microsoft.SharePoint.Client.Runtime.dll – One of required DLLs from the SDK
  • SharePointOnline.psd1 – Module Manifest file
  • SharePointOnline.psm1 – PowerShell module file
  • SharePointOnline-Automation.json – SMA Integration Module Meta File (where the connection asset is defined).

image

Download SharePointOnline Module

Note:

The zip file you’ve downloaded from the link above DOES NOT contain the 2 DLL files. I am not sure if Microsoft is OK with 3rd party distributing their software / intellectual properties. So, just to cover myself, you will need to download the SDK (64-bit version) from Microsoft directly (https://www.microsoft.com/en-us/download/details.aspx?id=35585), install it on a 64-bit computer, and copy above mentioned 2 DLLs into the SharePointOnline module folder.

Once the SDK is installed, you can find these 2 files in “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\” folder.

Once the DLLs are placed into the folder, zip the SharePointOnline folder to SharePointOnline.zip file again, and the integration module is ready.

image

I’d like to also briefly go through this SharePointOnline module. This module contains the following functions:

  • Import-SharePointClientSDK: Load the Assemblies from the 2 DLLs included in the module
  • New-SPOCredential: Create a new SharePointOnlineCredentials object from the username and password provided.
  • Get-SPOListFields: Get all fields from a SharePoint Online list (return an array object)
  • Add-SPOListItem: Add an item to the SharePoint Online list (by passing in a hash table containing the value for each field)
  • Get-SPOListItems: Get all items from a SharePoint Online list (return an array object)
  • Remove-SPOListItem: Remove a list item from a SharePoint Online list (by providing the ID of the item)
  • Update-SPOListItem: Update a list item (by providing the list Item ID and a hash table containing updated values)

This module is made to be re-used for SharePoint Online operations that involves list items. I will write a separate post to go through this in details. But for now, all we need to do is to import it into Azure Automation.

 

04. Import SharePointOnline Module into Azure Automation and Create SharePoint Online Connection

Now that the integration module is ready, it needs to be imported into your Azure Automation account. This is done via the Import Module button under Assets tab.

Once the module is imported, a connection object must also be created.

SNAGHTML806ea00a

image

You must provide the following information when creating the SharePointOnline connection object:

  1. SharePointSiteURL – The URL to your SharePoint Online site (i.e. https//yourcompany.sharepoint.com)
  2. UserName – a User how should be part of the site members role (members group have contribute access). This username MUST be in the email address format. (i.e. yourname@yourcompany.com). I believe this account must be an account created in the Office 365 subscription. I have tried using an outlook.com account (added as a SharePoint site member), it didn’t work.
  3. Password – Password for the username you’ve specified.

i.e.

SNAGHTML80731611

 

05. Create a Runbook to Populate SharePoint List

This is equivalent to the previous runbook Populate-OnPremMPCatalog. I have named it Populate-SPOnlineMPCatalog.

Download Populate-SPOnlineMPCatalog Runbook

SNAGHTML8093a11f

This runbook is expecting 4 parameters:

  • SPOConnection: The name of the SharePointOnline connection that you’ve created earlier.
  • ListName: The list name of your MP catalog list.
  • NotifyByEmail: Specify if you’d like an email notification when new MPs have been added to the catalog.
  • ContactName: If NotifyByEmail is set to “true”, specify the SMAAddressBook connection name for the email notification recipient.

Note: If you’d like to receive email notifications, you also need to import and configure the SendEmail and SendPushNotification modules from my blog. Once the SMTP server connection and the Address book connection are created, please modify line 111 of the runbook with the name of your SMTP server connection:

image

Note: I have previously blogged the issues I have experienced using the SendEmail module in Azure Automation. You may find this post useful: Using the SendEmail SMA Integration Module in Azure Automation.

 

06. Executing Runbook Populate-SPOnlineMPCatalog

When executing the runbook, you need to fill out the parameters listed above:

image

image

Result:

image

Same as the On-Prem version using SMA, you can create a schedule to run this on a regular basis to keep the catalog in sync with Microsoft. I won’t go through the schedule creation again.

Azure Automation Fairshare

Currently, Azure Automation has a “Fairshare” feature, where the maximum allowed execution time for a job is 30 minutes. Fortunately, based on my multiple test runs against multiple Office 365 SharePoint online sites, the first executions of this runbook always complete JUST under 30 minutes. However, if you found your job in Azure Automation is terminated after 30 minutes, you should be able to just run it again to top up the list. But any subsequent runs should only take few minutes.

Conclusion

To me, this post completes the circle. I’m happy that I am able to provide solutions for people who wants to host the catalog On-Premise (by using SharePoint 2013 and SMA), as well as who’d like to hosted in on the cloud (Office 365 and Azure Automation).

The 2 different runbooks (and the additional integration module) are 100% based on what SharePoint are you going to use. There is also a 3rd possible combination: Using SMA to populate SharePoint Online list. In this scenario, the steps are the same as what I described in this post. I have also tested in my lab. it is working as expected.

Additionally, I am also working with my fellow System Center MVP Dan Kregor to make this MP Catalog publicly available for everyone on Sparq Consulting’s public SharePoint Online site. We will make a separate announcement once it is ready. – So even if you can’t setup one up on-prem or on the cloud, we’ve got you covered Smile.

Credit

Thanks for all the System Center MVPs who have provided feedback and input into this solution. Smile

My Experience Manipulating MDT Database Using SMA, SCORCH and SharePoint

Written by Tao Yang

Background

At work, there is an implementation team who’s responsible for building Windows 8 tablets in a centralised location (we call it integration centre) then ship these tablets to remote locations around the country. We use SCCM 2012 R2 and MDT 2013 to build these devices using a MDT enabled task sequence in SCCM. The task sequence use MDT locations to apply site specific settings (I’m not a OSD expert, I’m not even going to try to explain exactly what these locations entries do in the task sequence).

SNAGHTML4d5244b

In order to build these tablets for any remote sites, before kicking off the OSD build, the integration centre’s default gateway IP address must be added to the location entry for this specific site, and removed from any other locations.

SNAGHTML4dbe1c0

Because our SCCM people didn’t want to give the implementation team access to MDT Deployment Workbench, my team has been manually updating the MDT locations whenever the implementation team wants to build tablets.

I wasn’t aware of this arrangement until someone in my team went on leave and asked me to take care of this when he’s not around. Soon I got really annoyed because I had to do this few times a day! Therefore I decided to automate this process using SMA, SCORCH and SharePoint so they can update the location themselves without giving them access to MDT.

The high level workflow is shown in the diagram below:

MDT Automation

Design

01. SharePoint List

Firstly, I created a list on one of our SharePoint sites, and this list only contains one item:

SNAGHTML52b1a8c

02. Orchestrator Runbook

I firstly deployed the SharePoint integration pack to the Orchestrator management servers and all the runbook servers. Then I setup a connection to the SharePoint site using a service account

SNAGHTML532bed6

The runbook only has 2 activities:

image

Monitor List Items:

SNAGHTML53742a6

Link:

SNAGHTML536dbb9

The link filters the list ID. ID must equal to 1 (first item in the list). This is to prevent users adding additional item to the list. They must always edit the first (and only) item on the list.

Start SMA Runbook called “Update-MDTLocation”:

image

image

This activity runs a simple PowerShell script to start the SMA runbook. The SMA connection details (user name, password, SMA web service server and web service endpoint) are all saved in Orchestrator as variables.

SNAGHTML53aaec0

03. SMA Runbook

SNAGHTML5413f3c

Firstly, I created few variables, credentials and connections to be used in the runbook:

Connections:

Credential:

  • Windows Credential that has access to the MDT database (we have MDT DB located on the SCCM SQL server, so it only accepts Windows authentication). I named the credential “ProdMDTDB”

Variables:

  • MDT Database SQL Server address. I named it “CM12SQLServer”
  • Gateway IP address. I named it “GatewayIP”

 

Here’s the code for the SMA runbook:

Putting Everything Together

As demonstrated in the diagram in the beginning of this post, here’s how the whole workflow works:

  1. User login to the SharePoint site and update the only item in the list. He / She enters  the new location in the “New Gateway IP Location” field.
  2. The Orchestrator runbook checks updated items in this SharePoint list every 15 seconds.
  3. if the Orchestrator runbook detects the first (and only) item has been updated, it takes the new location value, start the SMA runbook and pass the new value to the SMA runbook.
  4. SMA runbook runs a PowerShell script to update the gateway location directly from the MDT database.
  5. SMA runbook sends email to a nominated email address when the MDT database is updated.

The email looks like this:

SNAGHTML197a0f95

The Orchestrator runbook and the SMA runbook execution history can also be viewed in Orchestrator and WAP admin portal:

image

image

Room for Improvement

I created this automation process in a quick and easy way to get them off my back. I know in this process, there are a lot of areas can be improved. i.e.

  • Using a SMA runbook to monitor SharePoint list direct so Orchestrator is no longer required (i.e. using the script from this article. – Credit to Christian Booth and Ryan Andorfer).
  • User input validation
  • Look up AD to retrieve user’s email address instead of hardcoding it in a variable.

Maybe in the future when I have spare time, I’ll go back and make it better , but for now, the implementers are happy, my team mates are happier because it is one less thing off our plate Smile.

Conclusion

I hope you find my experience in this piece of work useful. I am still very new in SMA (and I know nothing about MDT). So, if you have any suggestions or critics, please feel free to drop me an email.

SMA Runbook: Update A SharePoint 2013 List Item

Written by Tao Yang

Background

This blog hasn’t been too active lately. I’ve been spending a lot of time learning the new member in System Center family: Service Management Automation.

Yesterday, I needed a SMA runbook to update SharePoint 2013 list items, I found a sample from a blog post by Christian Booth, which contains a SMA runbook written by Ryan Andorfer, a System Center Cloud and Datacenter MVP.  Looks Ryan’s code was written for SharePoint 2010, which does not work for SharePoint 2013 because the SharePoint REST API has been updated. So I have spent some time, learned a bit more about SharePoint 2013’s REST API, and developed a new runbook for SharePoint 2013 based on Ryan’s code.

PowerShell Code

Here’s the finished work:

Unlike Ryan’s code, which also monitors the SP list, my runbook ONLY updates a specific list item.

Pre-Requisite and Parameters

Prior to using this runbook, you will need to save a credential in SMA which has  access to the SharePoint site

SNAGHTML7ebadf0

The runbook is expecting the following parameters:

SharePointSiteURL: The URL to the sharepoint site. i.e. http://SharepointServer/Sites/DemoSite

SavedCredentialName: name of the saved credential to connect to SharePoint site

ListName: Name of the list. i.e. “Test List”

ListItemID: the ID for the list item that the runbook is going to update

PropertyName: the field / property of the item that is going to be updated.

PropertyValue: the new value that is going to be set to the list item property.

Note: The list Item ID is the reference number for the item within the list. If you point the mouse cursor to the item, you will find the list item ID in the URL.

SNAGHTML90254b7

Putting it into Test:

To test, I’ve created a new list as shown in the above screenshot, I have kicked off the runbook with the the following parameters:

image

image

Here’s the result:

SNAGHTML8fd5d4a

SNAGHTML8fed515

Using It Together With Orchestrator SharePoint IP

Since this SMA runbook requires the List Item ID to locate the specific list item, when you design your solution, you will need to find a way to retrieve this parameter prior to calling this runbook.

If you are also using SC Orchestrator and have deployed the SharePoint IP, you can use the “Monitor List Items” activity, and the List Item ID is published by this activity:

image

Conclusion

Although I’m still a newbie when comes to SMA, it got me really excited. Before its time, when I design Orchestrator runbooks, I often ended up just write the entire solution in PowerShell and then chopped up my PowerShell scripts into many “Run .Net Script” activities. I thought, wouldn’t it be nice if there is an automation engine that only uses PowerShell? Well, looks like SMA is the solution. I wish I have started using it sooner.

If you are like me and want to learn more about this product, i highly recommend you to read the Service Management Automation Whitepaper (currently version 1.0.4) from my fellow SCCDM MVP Michael Rueefli. I have read it page by page like a bible!

Setting up OpsMgr Knowledge Base on SharePoint 2010 Wiki

Written by Tao Yang

Previously, I blogged my opinion on using external knowledge base for OpsMgr. In that article, I mentioned a SharePoint 2013 Wiki solution developed by Stefan Koell. As I mentioned, I successfully set it up in my home lab without issues. However, I had issues setting it up at work, which is still using SharePoint 2010.

After going through what I’ve done with the SharePoint engineer in my company, I was told the issue is with WikiRedirect.aspx. I was told this page doesn’t exist on my SharePoint 2010 Enterprise Wiki site. So to work around the issue, I had to update Stefan’s JavaScript and made it work in SharePoint 2010. The UI looks a bit different than what WikiRedirect.aspx does, but essentially the same. The script redirects to a page if it already exists, or prompt to create a new page if it doesn’t exist.

Here’s what it looks like:

Redirects to an existing page:

image

Prompt to create a new page:

image

And here’s the script (I named it RedirectJs.txt):

<script type="text/JavaScript" unselectable="on">

// the path to the Enterprise Wiki Site in sharepoint
var SiteUrl = 'http://Sharepoint/Sites/SCOMKB/';

// make sure we only execute when a name is provided and when we are not in design/edit mode
var executeMain = false;
if (querystring('DisplayMode') == 'Design' || querystring('ControlMode') == 'Edit')
    executeMain = false;
else if (querystring('Name') == '' || querystring('Name') == null || querystring('Name') == undefined)
    executeMain = false;
else
    executeMain = true;

if (executeMain)
    main();

function querystring(key) {
    // helper function to access query strings
    var re=new RegExp('(?:\\?|&)'+key+'=(.*?)(?=&|$)','gi');
    var r=[], m;
    while ((m=re.exec(document.location.search)) != null) r.push(m[1]);
    return r;
}

function UrlExists(url)
{
    var http = new XMLHttpRequest();
    http.open('HEAD', url, false);
    http.send();
    return http.status!=404;
}
function load_url(externallink)
{
    //window.open(externallink,target='_blank');
	window.location = externallink;
}

function main()
{
	// strip " # % & * : < > ? \ / { } ~ | from name
	var name = querystring('name');
	name = unescape(name);
	name = name.replace("\"", '-');
	name = name.replace('#', '-');
	name = name.replace('%', '-');
	name = name.replace('&', '-');
	name = name.replace('*', '-');
	name = name.replace(':', '-');
	name = name.replace('<', '-');
	name = name.replace('>', '-');
	name = name.replace('?', '-');
	name = name.replace('/', '-');
	name = name.replace("\\", '-');
	name = name.replace('{', '-');
	name = name.replace('}', '-');
	name = name.replace('~', '-');
	name = name.replace('|', '-');
	// page url
	var pageName = name + '.aspx';
	var pageUrl = SiteUrl + '/Pages/' + pageName;
	var CreatePageUrl = SiteUrl + '/_layouts/CreatePage.aspx?IsDlg=1&Folder=RootFolder&Name=' + name;
	var pageExists = UrlExists(pageUrl);
	if (pageExists) {
		//open page
		load_url(pageUrl);
	} else {
		//Create New page
		load_url(CreatePageUrl);
	}
}
</script>

When you use this script, please update the SiteUrl variable to represent the URL of your SharePoint wiki site.

SNAGHTMLb695c2

Please note adding scripts to a web part is different in SharePoint 2010 than 2013. The script needs to be uploaded to the site as a separate file then link the the web part. here’s a good article on how to do it.

Note: I have also tested this script on my SharePoint 2013 wiki site. Unfortunately, it does NOT work.

Disclaimer:

My knowledge on SharePoint and JavaScript is next to none. Therefore, when I was told wikiredirect.aspx was the problem, I had to take his word for it. And I did lots of search online and came up with this JavaScript that worked for me. I won’t get offended if someone criticise me if my statement is wrong about SharePoint and if you think the script can be improved. Smile Lastly, please test it and make sure it works in your environment. I don’t have another SharePoint 2010 site I can test on, the only place I made it work is on my work’s production SharePoint site.

The script can also be downloaded HERE.