SharePoint Tenant Creation with Change Request tracking in System Center Service Manager

One of the more troublesome issues in SharePoint is creating new site collections when not logged into Central Administration. Because this task always requires someone with farm admin access it tends to not get high priority in a normal administrators daily role. Additionally, Governance teams should validate when site collections are being created and monitored correctly versus creating a sub site. By combining the ease of submitting a request in a SharePoint list and the Best Practice of tracking changes in Systems Center Service Manager with Opalis there is a smooth, expedient, tracked manor to provision site collections. By combining SharePoint and SCSM a decision can be made as to where the approval workflow reside. For this demonstration the site collection will be automatically provisioned and a Change Request will automatically be created inside Systems Center Service Manager. Then the SharePoint list item that originally kicked off the process will be updated to show the request has been completed. Finally the SCSM ticket will be closed automatically so there is a record of the change.

Requirements

· Custom SharePoint List that includes

§ Title column to store the type of request. The default value is defined as Site Collection Request.

§ Site Collection URL column to the preferred url of the site.

§ Primary Site Collection Administrator column.

§ A Request column that is a calculated field that combines the Title and the Site Collection URL to create a unique name for reporting in SharePoint and in SCSM.

§ A status column that is a dropdown with the values of New & Complete available.

§ A customized All Items view that filters out all entries that do not have a status of New.

· Systems Center Service Manager Integration Pack for Opalis

· Systems Center Service Manager configured

· Execute PowerShell Script Integration Pack

· Global Variables

Start by configuring the SharePoint Integration Pack in Opalis. Under Options Select Microsoft SharePoint (see Figure 9-50). Then add in an a configuration to point to the new SharePoint list defined in the requirements (see Figure 9-51).

Figure 9-50 Navigating to the Microsoft Configuration section in Opalis.

Figure 9-51 Defining another configuration point.

Then add a configuration for the installed System Center Service Manager. Start by selecting Options and then select System Center Service Manager (see Figure 9-52). Then create entries to connect to the System Center instance. Finally, validate the settings by clicking the Test Connection (see Figure 9-52).

Figure 9-52 Navigating to the System Center Service Manager Configuration section in Opalis.

Figure 9-53 A defined configuration to connect to System Center.

Then expand Global Settings and right-click Variables select New then select Variable (see Figure 9-54). Rather than pass the variables from SharePoint the Global Variable functionality in Opalis will be used in case these values are required again. Create two variables (see Figure 9-55).

Figure 9-54 Creating a new Global Variable.

Figure 9-55 Defined Global Variables

Then create a New Policy inside the Opalis Client and add-in the following objects

· Microsoft SharePoint

o Monitor List Item

o Update List Item

· Execute PowerShell Script

o Execute PS Script

· System Center Service Manager

o Create Change with Template

o Get Activity

o Update Activity

The end result should end up looking like Figure 9-56.

Figure 9-56 A Policy setup in Opalis.

Configuration of the Policy objects

Monitor List Item

Under Configuration select the name by choosing the name of the configured SharePoint list for creating a site collection request (see Figure 9-57).

Figure 9-57 Monitor List Item object properties.

Create Change with Template

Under the Properties section set the Connection to the name of the System Center instance defined earlier. Then for Class choose Change Request and for Template choose Standard Change Request. The Fields need to be added to so click on Select optional Fields and choose Status, Impact, Title and Actual start date. After hitting OK individually set the values in these items (see Figure 9-58).

· Status = New

· Impact = Minor

Figure 9-58 Create Change with Template properties.

To set the Title field by right-clicking in the Title field, select Subscribe and select Published Data (see Figure 9-59). The only available option (after connecting the two objects) is Monitor list Item. Scroll through the columns and select the Request column from SharePoint. Actual start date is defined the same way but the value is from Created.

Figure 9-59 Setting the Title field by subscribing to Published Data.

Execute PS Script

Under the Input Properties right-click the PS Script 01 and select Expand. There are five variables in the PowerShell script and just a simple concatenation of the variables. Finally the script creates a new site collection with the url and primary site collection administrator passed in as dynamic variables (see Figure 9-60).

· #webUrl -This is subscribed to the Global Variable SharePoint Web Application URL

· $managedPath - This is subscribed to the Global Variable SharePoint Managed Path

· $scUrl - This is subscribed to from the Monitor List Item object.

· $ownerAlias – This is subscribed to from the Monitor List Item object

· $completeUrl – This concatenates $webUrl, $managedPath, and $scUrl

Figure 9-60 PS Script 1 properties to create a dynamic site collection

Update List Item

The objective of the Update List Item object in this scenario is to demonstrate automated completion of the operation without the complexity of creating a SharePoint workflow. This object takes in the ID of the list item from Monitor List Item and updates the Status column to Complete (see Figure 9-61).

Open the Configuration section of this object and select the SharePoint configuration that points the Site Collection Request list. There are two properties that are setup in this object.

· ID – This is subscribed to from the Monitor List Item

· Status – This is set to the value of Complete

Figure 9-61 Update List Item Properties.

Get Activity

The Get Activity object needs the Connection to point to the configured Systems Center in Opalis. The Activity Class is set to Manual Activity and Source Object Guid is subscribed to from the Create Change with Template object (see Figure 9-62).

Figure 9-62 Get Activity properties.

Update Activity

The final object in the policy updates SCSM and changes the status of the ticket to Completed. The Properties Connection needs to be set to the configured System Center in Opalis. The Activity Class is set to Manual Activity and the Object Guid is subscribed to from Get Activity. The only field utilized is the Status field and it is set to Completed (see Figure 9-63).

Figure 9-63 Update Activity properties connected to the System Center connection, set to a Manual Activity and receiving the Object Guid from Get Activity.

Testing

Place the Policy in Policy Test Console and click the Run button. The Monitor List Item object will be waiting for a new SharePoint list item to be created (see Figure 9-64). After the new item is detected a ticket is created (see Figure 9-65).

Figure 9-64 Site Collection provisioning Policy in debug mode.

Figure 9-65 Site collection ticket in System Center Service Manager showing a status of completed.

Solving real world SharePoint problems with Opalis

Monitoring Content DB Size and provisioning a new Content DB

By taking the previous example a little farther it would be possible to monitor the content databases of a SharePoint environment to prevent content databases from becoming too large. This is one of the main advantages of Opalis is that it assists in maintaining Best Practices in the enterprise (see Figure 9-49).

Requirements

· Initial Global scheduler to analyze the environment on a daily basis

· PowerShell script that finds all the web application url’s, then finds the content databases that make up the site collections

· Database query that passes in the content database name and returns the size

· Logic that defines the maximum size of the content database

· Logic that calculates 90% and 70% and provides alerts

· PowerShell script that automatically provisions a new content database to the appropriate web application when the database size hits 70%. The content database should initially be offline to prevent use initially.

· At 90% of the defined Best Practice size use a PowerShell script that automatically put the initial content database in offline mode and puts the new database in Ready mode.

Figure 9-49 Policy to monitor content databases in SharePoint.

SharePoint Integration Kit

Opalis has built-in Integration Packs and the ability to utilized custom Integration Packs. An Integration Pack has been created for SharePoint and is available at http://opalis.codeplex.com. After installing the Integration Pack using the steps provided previously it is necessary to configure the SharePoint Integration Pack. Start by selection Options from the top menu and select Microsoft SharePoint (see Figure 9-40). After selecting that option the Configuration Window opens (see Figure 9-41). Click Add to enter the specifics about what SharePoint environment that this configuration should connect to. Every configuration can be different logins, different lists or document libraries, or different web url’s (see Figure 9-42).

Figure 9-40 Microsoft SharePoint configuration selection for the Microsoft SharePoint Integration Pack.

Figure 9-41 Microsoft SharePoint configurations defined in Opalis.

Figure 9-42 An individual Microsoft SharePoint configuration for Opalis.

Database sizes into a SharePoint List

Over the years I have received many requests from different levels of management and normally the more difficult ones have the biggest priority. I recall an issue with another group’s database that was consistently causing issues because they were getting too large and were not being monitored correctly. So the question came up as to how do we get all the databases and their sizes provided to management on a daily basis? Normally this requires some manual intervention in Excel or providing access to the administration tools in the datacenter or the DBA’s tools. Providing ODBC access in Excel or Access has the potential to expose the username and password. What if on a daily basis, or even hourly, Opalis queried all the databases and populated a SharePoint list that could be provided to anyone? This would remove any security concerns and alleviates another request.

Start by creating a new Policy and drag a Query Database object from the utilities section. Then enter a simple query to get the top 100 databases from the master databases and get the size into Megabytes (see Figure 9-42). Finally, define the connection and security credentials.

/** Select the databases from the master database**/

SELECT TOP (100) name, (size*8)/1024 SizeMB

FROM sys.master_files

Figure 9-42 Query Database object properties displaying the SQL statement.

Now drag a Create List Item object from the Microsoft SharePoint section of objects and drag a link from the Query Database object to the Create List Item object (see Figure 9-43).

Figure 9-43 Connecting objects in Opalis.

Double-click the Create List Item object to open the properties dialog. Under Configuration set the name to SharePoint Database List. This queries SharePoint and provides the Title field. Click Optional Properties to add-in the Size MB field and select to move to the Selected column (see Figure 9-44).

Figure 9-44 Adding properties available to a SharePoint Create List Item.

To configure the field mapping it takes a little more configuring so since it known that the SQL Query is going to return two fields separated by a semicolon it is possible to use the following function (see Figure 9-45).

Figure 9-45 Data manipulation function inside Opalis.

The function passes in the familiar Full line as a string with fields separated by ‘;’ and passes in a delimiter and requests the first value from the pair. The field for the Size MB is similar but requests the second value. The final result looks like Figure 9-46.

Figure 9-46 Create List Item properties showing two fields populated.

From this point it is possible to run a Test on this policy and populate the database. Figure 9-47 shows the Policy running. Figure 9-48 shows the SharePoint list being populated.

Figure 9-47 Policy populating a SharePoint list from the Policy Testing Console.

Figure 9-48 SharePoint list populated with values from Opalis.

Creating a basic Policy

Figure 9-28 The Opalis Integration Server Interface.

By opening the Opalis Integration Server and right-clicking on the Policies tree select Policy to create a new Policy (see Figure 9-29). This will create a policy in the main section of the interface (see Figure 9-28 B). For the scenario with the new Policy we need a process to run on a schedule. Expand the section called Global Settings and right-click Schedule and select New Schedule (See Figure 9-30). For this SharePoint specific example the Schedule name is Content Databases (see Figure 9-31). By updating the Detail section this will check on the size of the content databases in a SharePoint environment once a day (see Figure 9-32).

Figure 9-29 Creating a new Policy in Opalis.

Figure 9-30 Creating a new Schedule in Opalis.

Figure 9-31 General Scheduling information in Opalis

Figure 9-32 Setting a Schedule in Opalis.

Testing

Opalis has the ability to run tests on the Policies created. The following example has a Custom Start object that runs a Database Query (see Figure 9-33). In the Task bar select Test to perform a test on the Policy. This brings up a new application window that allows the user Run, set Breakpoints and debug objects (see Figure 9-34).

Figure 9-33 Simple Policy with two connected objects.

Figure 9-34 Policy Testing Console

In the Testing Console if an individual object is selected the properties are displayed in the Design Time Properties window (see Figure 9-35). There is a simple SQL statement that runs a built in stored procedure that returns all the databases on the SQL Database that the Query Database object is setup to connect to. By clicking the Run button the two objects will execute and provide a log of their actions, properties, and return values (if available) (see Figure 9-36).

Figure 9-35 Design Time Properties for the Query Database object.

Figure 9-36 Policy Testing Console displaying the populated logs after running a policy.

The Query Database stores all its returned values in the Full lines as a string with fields separated by a ‘;’. By highlighting that line in the logs the interface adds a ellipsis button. But clicking on the ellipsis button a small modal window provides the data returned but the query (see Figure 9-37). While the results are interesting the name of the modal is very important. The data is designated as Published Data. So when objects are connected in the Policy and there is a desire to interact with the data, this is the actual data that is published from the Query Database object that was defined.

Figure 9-37 Published Data Browser displaying the results of the Query Database object.

By exiting the Test client and returning to the Integration Server Client we can show how it is possible process this Published Data some more. By selecting and adding a Run .Net Script object to the Policy the capacity to run C#, Jscript, PowerShell or VB.Net code is available (see Figure 9-38).

Figure 9-38 The Run .Net Script Object displaying the types of script it can run.

After selecting PowerShell as the script type it is necessary to right-click in the Script section to open a context menu. This allows the ability to select the Published Data from the Query Database object (or from a Global Variable). Select Published Data and then the field Full line as string with fields separated by ‘;’ the script section will display a hyperlinked section of code that can be assigned to a variable in PowerShell. By adding a few more lines of PowerShell for processing and putting the same Policy in the Test Console again it is possible to see the first value passed in from the Query Database object into the PowerShell (when set as a breakpoint) script (see Figure 9-39).

Figure 9-39 Policy Testing Console in debug mode with a breakpoint hit at the Run .Net Script object.

Automating SharePoint in a Private Cloud using Opalis

• Define what Opalis can provide in an organization
• How to install Opalis
• Opalis Integration Packs
• Creating a basic Policy
• Testing Opalis Policies
• Opalis SharePoint Integration Kit
• Solving real world SharePoint problems with Opalis

 Now let us focus on a specific solution that offers a unique toolset to the current mindset of datacenter administration.
Opalis is an automation platform that improves the reliability of IT processes by orchestrating and integrating IT tools to control the cost of datacenter operations. By enabling an organization to automate best practices through workflow processes that coordinate’s System Center & management/monitoring tools to automate incident response, change & compliance, and service-lifecycle management processes.
Opalis offers a designer for custom workflow processes, through Integration Packs, and automatically shares data and initiates tasks in System Center Operations Manager, System Center Configuration Manager, System Center Service Manager, Virtual Machine Manager, Active Directory and many other 3^rd party toolsets. I have seen some impressive implementations by organizations where they can script out a remediation’s but consistently the one missing piece is the ability to track that the incident, what did it take to resolve it, and close the ticket without delayed manual intervention. Opalis offers the ability to not only configure the scripting portion but also try multiple variations of scripted solutions till a remedy is found, offer escalations through email or System Center, track/monitor incident and change requests and much, much more. Opalis offers the “Last Mile” piece many IT organizations have been trying to accomplish. By combining System Center and Opalis organizations open the doors to increased integration, efficiency and business alignment of the datacenter IT services by:

• Automating cross-silo processes and enforcing best practices for incident, change and service lifecycle management.
• Reduce unanticipated errors and service delivery time by automating tasks cross vendor and organization silos.
• Integrating System Center with non-Microsoft tools to enable interoperability across the datacenter.
• Orchestrating tasks systems for consistent, documented, compliant activity.

Many organizations have successfully jumped on the PowerShell bandwagon and accomplished many automation techniques. There are now many books that have sections dedicated to PowerShell automation within the Microsoft solution space. But normally this requires multiple systems that are running timer jobs across the organization. This tends to get so overwhelming for organizations that they are unable to define where all the jobs are running or when they are running because they become so disparate and have no ability to roll up an accomplished task to a central repository (e.g. System Center) the jobs end up being lost in the organization. Consider another scenario where management starts seeing random issues with a particular job and the only fallback an administrator can provide is to attempt to find logs for a particular script and hope the logs actually caught the issue.
Opalis Components
Opalis has the following main components
Integration Server Client This is the application that creates, modifies, deploys, tests and manages policies.
Action Server Policies are executed on the Action Servers and more than one Action Server can be created. It is possible to dedicate Action Servers for policies requiring a lot of disk space.
Policy Testing Console - Application that allows for testing of policies configured in the Integration Server Client before being deployed to the Action Server. It also has the ability to debug and step through policies.
Operator Console Web-Based application that allows real-time information about metrics such as policy state, duration, and capacity utilization.
Management Server Server that provides connections from the Integration Server Clients to the Opalis Datastore for building and testing policies.
Opalis Datastore A Oracle or Microsoft SQL Server database where configuration information, policies and logs are stored.
Deployment Server Tool that offers a view of the entire Opalis Integration Server infrastructure and deploy Action Servers, Integration Clients, Integration Packs and hotfixes from one place.

Opalis Installation

Pre-requisites

· Windows Server 2003, 2008 or 2008 R2

· Service account (a domain user) that is a Local Administrator

· Microsoft SQL Server 2005, 2008, or 2008 R2. The service account must be added to the logins in SQL Server.

Installation begins by unzipping the supplied files to retrieve the setup file (See Figure 9-1). Then begin by double-clicking Setup.exe to begin the installation.

Figure 9-1 Unzipping the Opalis files.

After double-clicking on setup the first dialog offers the option to Install Opalis Integration Server. Select that option to begin the installation (Figure 9-2).

Figure 9-2 Initial installation dialog screen.

Click on the Install the Management Server to begin the installation. It is a simple wizard that requires the service account information to complete (see Figure 9-3). Management server install is a basic installation that only requires a domain account for running Opalis.

Figure 9-3 Selecting the only available installation Install the Management Server option.

Configure the Datastore

After completing the installation of the Management Server the next option to configure the Datastore is available. By selecting this option another installation wizard is started. Currently you can select between Microsoft SQL Server and Oracle Server (see Figure 9-4)

Figure 9-4 Selecting the Database type in the Datastore install sequence.

Import a License

After completing the Datastore wizard the next step is available so select Import a License (see Figure 9-5). Opalis is extremely license centric and requires a license to run different components. The trial version comes with 180 day licenses which will be covered. Not only does Opalis require license files but it also requires the corresponding key (see Figure 9-6)

Figure 9-5 Opalis License Manager

Figure 9-6 Opalis Key and License dialog.

The 180 day trial comes with a variety of license files and key’s (see Figure 9-7)

Figure 9-7 Sample of the licenses available in the 180 day trial download of Opalis

The trial download also includes a Microsoft Word document that has the corresponding keys for each of the licenses (see Figure 9-8)

Figure 9-8 Sample of the keys provided in the 180 trial download of Opalis

Copy the key into the dialog box (include the brackets) and click the button with the ellipsis to point to a local folder that contains the license (.lic file) (see Figure 9-9)

Figure 9-9 An Opalis key and license file designated.

After hitting OK ensure that the License Manager component is set to correct component that is going to be licensed. This examples is setting the license for Opalis Integration Server –v6 (see Figure 9-10)

Figure 9-10 Selecting the correct component to associate the license to.

The final link is now available in the Opalis installation steps after importing the Opalis License. Now select Install the Client (see Figure 9-11).

Figure 9-11 Start of the Client Installation wizard.

Upgrading from 6.2.2 to 6.3

In order for Opalis to be configured on a Windows Server 2008 instance it requires upgrading to version 6.3. The 6.3 release also includes new integration packs for System Center Configuration Manager, System Center Data Protection Manager, System Center Service Manager, and System Center Virtual Machine Manager. The integration pack for System Center Operations Manager has also been updated to support the 2008 platform.

The upgrade to 6.3 is included in the 180 trial but is not intuitive or linear. The first step is to replace an .msi file from the initial installation with a newer one from version 6.3. Navigate to the current installed folder, by default is located in the system drive Program Files (x86)\Opalis Software\Opalis Integration Server. Then open the Components folder and finally the Objects folder. Then open another explorer window to find the new file. In the 6.3 upgrade folder navigate to the Opalis Integration Server folder. Copy and Replace the existing OpalisIntegrationServer_FoundationObjects.msi (see Figure 9-12). After copying the file run the OpalisIntegrationServer_ManagementService_630_Patch.msp from the 6.3 upgrade location. It is a simple wizard that does not have any customization or parameters to install.

Figure 9-12 Copying the OpalisIntegrationServer_FoundationObjects.msi file from the upgrade folder to the existing installation folder.

After installing the Management Service Pack it is time to deploy action servers and clients. Open the Opalis Deployment Manager and right-click on Action Servers and select Deploy new Action Server (see Figure 9-13).

Figure 9-13 Selecting to Deploy a new Action Server inside the Opalis Integration Server environment.

Enter the machine or IP address of the machine that is desired to make an Action Server and the service account initially created (see Figure 9-14).

Figure 9-14 Specifying the Action Server to be added.

One of the unique aspects of Opalis is that it allows the designation of Integration Packs to Action Servers. The next step in the wizard allows the ability to specify what integration packs will be allowed to run on that particular action server. This is important if you have a policy that is resource intensive. During the initial install there will be no Integration packs to choose from on this screen) (see Figure 9-15).

Figure 9-15 Selecting the Integration Packs allowed on a specific Action Server.

After completing the wizard Opalis begins deploying to the defined Action Server (see Figure 9-16).

Figure 9-16 Opalis deploying to the Action Server.

Setting up the client

Open the Opalis Deployment Manager and right-click Clients and select Deploy new Client (see Figure 9-17).

Figure 9-17 Selecting Deploy new Client for the 6.3 upgrade.

Add in the computer that should be added as an Opalis Client (see Figure 9-18)

Figure 9-18 Adding a computer to be defined as a client for Opalis.

Opalis also allows the ability to define which Integration packs are visible to clients. The next step in the wizard allows the selection of which Integration Packs will be allowed to build Policies on the client (see Figure 9-19).

After completing the wizard Opalis will deploy the client to the selected machine(s) (see Figure 9-19).

Figure 9-19 Opalis deploying to the client machines.

Client update for 6.3

In the upgrade folder it is now possible to run the OpalisIntegrationServer_Client_630_Patch.msp. It is a simple wizard that doesn’t allow for any customization. This is the final step in completing the upgrade to version 6.3.

Integration Packs

Integration Packs allow for integration into solutions using Web Services, SQL, WMI, and CLI. These can be Microsoft products or 3^rd party systems. Opalis has an existing set of Integration Packs available to connect to System Center solution and there are new ones from providers like

· BMC

· CA

· EMC

· FTP

· HP

· IBM

· Microsoft

· Symantec

· VMWare

Opalis also has an SDK and a Quick Integration Kit (QIK) to create custom Integration Packs.

Start by defining the Integration Packs that are necessary for the environment. This is done by opening the Deployment Manager and expanding Management Server then right-clicking Integration Packs and selecting Register IP with the Management Server (see Figure 9-20). It allows you to select the Integration Packs or HotFixes for Opalis.

Figure 9-20 Context menu to Register an Integration Pack with the Management Server.

This brings up the Integration Pack Registration Wizard (see Figure 9-21) and by clicking on Add it is possible to find the Integration Pack that is desired to install (see Figure 9-22).

Figure 9-21 Integration Pack Registration Wizard.

Figure 9-22 Selecting the Integration Pack for Microsoft SharePoint 1.3

After selecting the Integration Pack and completing the wizard Opalis registers the Integration Pack (see Figure 9-23)

Figure 9-23 Registering an Integration Pack in Opalis

At this point the Integration Pack is only registered and has yet to have an Action Server attached to it. To deploy the Integration Pack to an Action Server select the Integration Pack(s) you wish to allow on the Action Server and right-click it to get a context menu. Select Deploy IP to Action Server or Client (see Figure 9-24).

Figure 9-24 Selecting an Integration Pack to be deployed.

This action starts the Integration Pack Deployment Wizard. From here select the Integration Pack that needs to be deployed (see Figure 9-25) and then select the computer that the Integration Pack will be deployed to (see Figure 9-26). Finally, define an installation time. This allows for deployment of Integration Packs during non-peak times (see Figure 9-27).

Figure 9-25 Selecting the Integration Pack.

Figure 9-26 Selecting the computer to deploy the Integration Pack to.

Figure 9-27 Scheduling the deployment time.