From the MVPs: PowerShell and SQL Stored Procedure
This is the 45th in our series of guest posts by Microsoft Most Valued Professionals (MVPs). You can click the “MVPs” tag in the right column of our blog to see all the articles.
Since the early 1990s, Microsoft has recognized technology champions around the world with the MVP Award . MVPs freely share their knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology. Of the millions of individuals who participate in technology communities, around 4,000 are recognized as Microsoft MVPs. You can read more original MVP-authored content on the Microsoft MVP Award Program Blog .
This post is by Lync MVP Desmond Lee . Thanks, Desmond!
PowerShell and SQL Stored Procedure
PowerShell is the window to managing many aspects of a Lync Server 201x environment. Coupled with the deployment of the Monitoring Server service (or separate role in 2010), extensive data can be collected to support operational and troubleshooting demands. By installing the optional Monitoring Reports on selected SQL Server Reporting Services (SSRS) instances, common types of reports are easily available at your disposal.
As Lync deployments become ever more popular and widespread, it became evident that the Lync Management Reports shipped with the product do not address certain reporting requirements needed in the field. Since persistent and dynamic Lync data are stored in various SQL databases in the backend as well as on each Front-End Server, the act of firing up SQL Management Studio, connecting to the right server and executing a SQL query will enable you to put together a user-defined report. You can find many excellent blog posts that walk you through the intricacies of constructing the often complicated looking SQL statements.
Microsoft discourages building SQL queries to pull information directly from the underlying database tables for a good reason. Besides the inherent complexity and tediousness involved, such as the use of multiple table JOINs, the database schema, table relationships and naming conventions may change in future updates and product versions. Hence the risk of breaking customized SQL queries is very real indeed.
In this compact article, I shall show you how to utilize the built-in SQL stored procedures associated with the Lync databases. Think of a stored procedure as a function which comprises of one or more PowerShell statements, and is capable to accept input parameters and return one or more values. Stored procedures are found in the rtc / Programmability / Stored Procedures node of the rctlocal instance.
One popular yet missing report (from Lync Management Reports) is to list the various Lync client versions deployed in the environment. We shall be using the common skeleton code framework to build and run SQL queries in PowerShell (see code listing for the complete script).
To begin, instead of constructing a SQL query similar to the following:
SELECT rtc.dbo.Resource.UserAtHost as 'SIP Address',
CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100)) as 'Client Version'
FROM rtcdyn.dbo.RegistrarEndpoint
INNER JOIN rtc.dbo.Resource
ON rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtc.dbo.Resource.ResourceId
WHERE IsServerSource = 0
You simply specify the name of the stored procedure and assign it to CommandText:
$SqlCmd.CommandText = “DiagShowEndpointsByClientApp”
The result, stored in the first table of the dataset, is a summary of the range of Lync client versions, all the way from desktop to mobile apps, which are currently in use in the environment.
If the stored procedure expects one or parameters, you can supply them as well. Here, we are interested to confine our list to users connecting using Lync Mobile for Android devices:
$clientVersion = "Android"
$SqlCmd.Parameters.AddWithValue("@_Contains",[string]$clientVersion)
The second table (index 1) of the dataset contains the results matching the given criteria.
To discover if parameters are optional or not, go ahead and inspect the stored procedure with the help of SQL Management Studio.
That is all to it in using stored procedures with PowerShell really. Be aware that this technique can be employed anywhere stored procedures are available in place of writing your own SQL queries.
Using readily available SQL stored procedures makes it easy to harness the power and work already done by Microsoft to generate reports absent in the out-of-box Lync Monitoring Reports, while future-proofing your PowerShell scripts against any underlying database changes less the agony of building your own complex SQL queries. I hope this article will help you put to real good use of the vast potential of PowerShell and stored procedures.
# PowerShell and SQL Stored Procedure
# Author: Desmond Lee, MVP Lync
# www.leedesmond.com 10 Oct 2014
# setup the ‘framework’ to use PowerShell with SQL
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
# Lync 201x FE local SQL
$SqlConnection.ConnectionString =
"Server=fe01\RtcLocal;Database=rtc;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
# indicate that we are working with stored procedure
$SqlCmd.CommandType=[System.Data.CommandType]'StoredProcedure'
# supply the name of the stored procedure
$SqlCmd.CommandText = "DiagShowEndpointsByClientApp"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
# retrieve first set of results
$DataSet.Tables[0]
$DataSet.Tables.count
#1
#------------- specify optional parameters to stored procedure
$clientVersion = "Android"
$SqlCmd.Parameters.AddWithValue("@_Contains",[string]$clientVersion)
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)
$DataSet.Tables.count
#2
$DataSet.Tables[0]
# results as above
# retrieve second set of results matching criteria
$DataSet.Tables[1]