Connect PowerShell scripts to native SharePoint lists for reporting, notifications and workflows - Why?

 

The Layer2 Cloud Connector for Microsoft Office 365 and SharePoint connects almost any on-premise data source, e.g. ODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), File Shares, SQL databases like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists and libraries in the Microsoft Cloud - or any other Microsoft SharePoint 2010 / 2013 installation - in just minutes without any programming. This FAQ is about connecting the output of PowerShell scripts with SharePoint lists to enable notifications and workflows, e.g. on specific data changes.

 

PowerShell-SharePoint-Connection.png

 

 

Windows PowerShell is Microsoft's task automation and configuration management framework, consisting of a command-line shell and associated scripting language built on .NET Framework. It is often used for administration and reporting, not only for the Windows operating system but also for applications platforms like Microsoft Exchange, SQL Server or SharePoint. You can use the Layer2 Cloud Connector (for SharePoint Online) or the Layer2 Business Data List Connector (for SharePoint on-premise) to connect the output of such scripts as reports to synchronize with a native SharePoint list. This opens up endless possibilities:

 

  • Reporting using native SharePoint lists: All list features available for the PowerShell reporting data, e.g. views, filter, grouping, calculated fields, search etc.
  • Management Dashboards: Can be connected to the SharePoint lists.
  • Mobile Access: PowerShell results are cached in native SharePoint lists for mobile access, everywhere with any device.
  • Notifications on data change: The script is executed scheduled on a regular base (e.g. every hour). You will be notified in case of data changed by list subscriptions
  • Workflows on data change: You can check for specific values, e.g. thresholds and start your own business logic a workflow, if required. Think about library quotas or any other reporting issue. Tasks can be created if required.
  • Versioning: You have a history of changes for your script output / report.
  • File Share Integration: You can list file shares with PowerShell and have the output available in lists to link back to files (e.g. using calculated fields).

Any other ideas? For any questions please use the FAQ Web Form or contact sales@layer2solutions.com directly.

Connect PowerShell scripts to native SharePoint lists for notifications and workflows - How?

 

​Installation requirements

If not installed, please install the Layer2 Cloud Connector. To connect Layer2 tools to a data source you need a data provider (driver). For Windows PowerShell you can install and use the provider offered here by RSSBus.

Please select the data provider in the Cloud Connector data entity settings as shown below.

 

 

SharePoint-List-to-PowerShell-Connection-1.png

 Fig.: Data Source configuration to execute a PowerShell script and connect to results via ADO.NET.

 

Setup a PowerShell sample skript

 

As a sample we have used a script service.ps1 that simply lists the services running on the current computer. Please note the "param" part with data types, field names and default values that is required for using SQL with the result set.

 

param(
 [String]$DisplayName='*',
 [String]$Name='',
 [String]$Status='',
 [String]$ServiceName='',
 [String]$ServiceType='',
 [Boolean]$CanPauseAndContinue=0,
 [Boolean]$CanShutdown=0,
 [Boolean]$CanStop=0
)
 
Get-Service  |Sort-Object DisplayName | Select-Object DisplayName, Name, Status, ServiceName, ServiceType, CanPauseAndContinue, CanShutdown, CanStop

 

The PowerShell script has the following output when executed:

 

SharePoint-List-to-PowerShell-Connection-3.png

 

Fig.: Sample PowerShell Script output that simply lists the local services. 

 

Connection String

 

As a next step you have ro provide a connection string. The most important attribute is the script location The directory that contains your PowerShell scripts), e.g.:

 

script location=C:\myScripts

 

You will find many more options, e.g. for logging, authentication, caching etc. here.

 

SQL data query

 

You can query the PowerShell result set as usual with SQL. Please use your script name as table or view name, e.g.:

 

Select * from MyScript

 

You will find more options to use SQL, e.g. to make use of aliases, where conditions or to limit the result set here.

 

Set a primary key

 

To carefully update the SharePoint list (or any other target data entity) you need one or more unique columns that acts as a primary key. 

 

Verify your settings using the data preview

 

SharePoint-List-to-PowerShell-Connection-2.png

Fig.: Data preview of the script result shown in the Layer2 Cloud Connector.

Connect PowerShell scripts to native SharePoint lists for notifications and workflows - Setup Data Destination

 

​Before you setup the data destination, you have to create. We are using a SharePoint Online list in this sample. Generally you can use any data destination, e.g. SQL tables. You have to create manually with the appropriate structure / fields. After your data destination is created, you can connect:

 

 

SharePoint-List-to-PowerShell-Connection-4.png

Fig.: Setup a SharePoint Online list as data destination for the PowerShell output.

 

 

You don't have to provide a primary key here, SharePoint Item-ID is used by default. Please verify the connection and data preview. As a next step you have to setup the field / column mapping between data source and data destination.

 

SharePoint-List-to-PowerShell-Connection-5.png

 

Fig.: Setup field / column mapping between data source and destination.

 

Please note that we don't use the auto-mapping feature in this sample. That gives us the option to map the ServiceName field (from PowerShell) to the Title column in SharePoint (as often required for lists).

 

Now you can run the first synchronization.

SharePoint-List-to-PowerShell-Connection-6.pngFig. First synchronization of the PowerShell script results to the SharePoint list.

 

Please note that with next updates existing SharePoint list items are updated via the primary key (Name). Only changes in data source cause changes in data destinations. In this way notifications and workflows can be used to take actions. In case everythings fits, you can enable scheduling and set an appropiate interval.


Connect PowerShell scripts to native SharePoint lists for notifications and workflows - Results

 

As a result we have a native SharePoint list connected to the output of the PowerShell script available with all the great SharePoint list options like views, calculated fields, search, notifications, workflows etc.

 

 

SharePoint-List-to-PowerShell-Connection-7.png

Layer2 Cloud Connector - Next Steps

 

​You can download the Layer2 Cloud Connector and the RSSBus ADO.NET Data Provider for PowerShell as trial after registration. Please contact sales@layer2solutions.com if you need help with your specific requirements.

 Ready to go next steps?

Register for free download.

Keep your Sharepoint in sync. Download and try today.

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.