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.