Illustration of an example availability monitoring service Using PowerShell and SQL
Illustration of an example availability monitoring service Using PowerShell and SQL
Author Recent Posts Prashanth JayaramI’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
SQLShack
SQL Server training EspañolIllustration of an example availability monitoring service Using PowerShell and SQL
March 23, 2017 by Prashanth Jayaram This article discusses a simple solution of how to monitor SQL service availability across multiple servers and reporting. To build this I’ll use SQL Server with simple PowerShell script and cmdlets which generate JSON data and displays results in HTMLArticle highlights
Defines the objective of JSON Explains the PoSH JSON cmdllets internals and other details on its usage Proactive Monitoring of Service(s) over multiple servers – Covers fundamentals of how to Monitor SQL service(s). This sample code can be implemented to monitor any services on a Windows environment Automated Process – Easy to implement If you don’t have a monitoring tool Easier customization of services list and input servers list Seamless Integration of PowerShell and SQL using SQL 2016 JSON constructs HTML Report – shows how to do effective reporting using XML parsing and Database Mail Summary of dataTechnologies used
SQL 2016 JSON Constructs – OPENROWSET and OPENJSON for JSON data manipulation and transformation into relational data PowerShell Cmdlets – ConvertTo-JSON for Data export, ConvertFrom-JSON for Data import XML Parsing for Prepare HTML tags SQL Database Mail for the Email exchange An integration of JSON in the relational world provides a robust platform for various automations using PowerShell automation framework. There are several JSON constructs available in SQL 2016 to manipulate and transform JSON data into relation data. The relational architecture in support of JSON provides several benefits in the non-relational world from the point of Migration and deployment. It is flexible because of simple syntax and lesser overhead to maintain and manage the JSON data. Parsing of relational data using XML gives the flexibility to manipulate huge data which will ease out the functioning of reportingTechnical overview
This article will provide an overview of the following technical implementations Various methods and techniques implemented to find the windows service Details about the purpose and benefits of using JSON Pre-requisites and installation of related Powershelll components cmdlets under the respective library are listed The convertTo-JSON and convertFROM-JSON will explained with an example along with JSON data representation in various tabular formats Finally, the constructs used to manipulate the JSON string will be illustrated with an example Transformation of JSON data using SQL constructs Let’s discuss the internals of JSON and cmdlets which are used to build the PowerShell script for generating the JSON dataJSON JavaScript Object Notation
JSON is an Open standard, lightweight data exchange based on a subset of the JavaScript Programming Language. It is easy for humans to parse and generate value out of each attribute. XML used to be a preferred choice for data exchange over the internet and it enjoyed the top spot for a long time but the gradual tendency of people to shift towards JSON due to its simple structure.JSON in PowerShell
Microsoft provides a framework and useful cmdlets to work with JSON. The ConvertTo-Json and ConvertFrom-Json cmdlets play a vital role in data transformation. These cmdlets allow working with APIs or classes and returns or accept JSON as an input.ConvertTo-Json
The ConvertTo-Json cmdlet converts any object to a string in JavaScript Object Notation (JSON) format. The properties are converted to field names, the field values are converted to property values, and the methods are removed. You can then use the ConvertFrom-Json cmdlet to convert a JSON-formatted string to a JSON object, which is easily managed in Windows PowerShell.ConvertFrom-JSON
The ConvertFrom-Json cmdlet converts a JavaScript Object Notation (JSON) formatted string to a custom PSCustomObjectobject that has a property for each field in the JSON string. JSON is commonly used by websites to provide a textual representation of objects. Pre-requisite Minimum shell version -> PowerShell 3.0 SQL 2016 for Data transformation Powershell 3.0 is integrated with Windows 8 released on 9/12/2011. This bundle in houses two new functions ConvertFrom-Json and ConvertTo-Json to support JSON.The ConvertFrom-Json is included in the module Microsoft.PowerShell.Utility, make sure that the module is loaded by doing Import-Module Microsoft.PowerShell.Utility before you use ConvertTo-Json. These are the default modules will be automatically loaded with your profile.How to List and verify the Modules monitor
The next question would be how do I retrieve the available commands from a module?. There are instances that modules are not loaded due to the profile problem. The below commands ensures that the cmdlets are loaded and it’s available for use. 12345 PS P:\> Get-Module -ListAvailable PS P:\>Get-Module Microsoft.PowerShell.Utility -ListAvailable % { $_.ExportedCommands.Values } The below sample code retrieves the details of listed services from the remote server(s). 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596 <# .SYNOPSIS Name : Service Report (Get-ServiceJSON.ps1) Description : Get disk space usage information from remote server(s) with WMI and ouput JSON file Author : Prashanth Jayaram * Select list of servers from a text file * Get remote Servers information with WMI and Powershell * Service (Servername,Name,startmode,state,serviceaccount,displayname + JSON Output) .INPUT Input Server text file Service list .OUTPUTS JSON output, console .NOTES Version: 1.0 Author: Prashanth Jayaram Creation Date: 2017-03-08 Purpose/Change: Initial script development Use the Get-Content cmdlet with the Raw parameter to read JSON file .EXAMPLE .\Get-ServiceJSON.ps1 -ServerList "\\hq6021\c$\server.txt" -includeService "VM","Dhcp","SQL" -JSONoutputFile "e:\CU2\ServericeReport.JSON" #> ######################################################################################### param ( [Parameter(Mandatory=$true)][string]$ServerList, [Parameter(Mandatory=$true)][string[]]$includeService, [Parameter(Mandatory=$true)][string]$JSONoutputFile ) # Check if the output file CSV exist, if exists then delete it. if (test-path $JSONoutputFile ) { rm $JSONoutputFile } #Custom object to maintain the order of the output columns $props=@() Foreach($ServerName in (Get-Content $ServerList)) { $service = Get-WmiObject Win32_Service -ComputerName $servername if ($Service -ne $NULL) { foreach ($item in $service) { #$item.DisplayName Foreach($include in $includeService) { #write-host $inlcude if(($item.name).Contains($include) -eq $TRUE) { $props += [pscustomobject]@{ Servername = $servername name = $item.name Status = $item.Status startmode = $item.startmode state = $item.state serviceaccount=$item.startname DisplayName =$item.displayname} } } } } } #$props Format-Table Servername,Name,startmode,state,serviceaccount,displayname -AutoSize #convert the output to JSON $json=$props Select-Object Servername,Name,startmode,state,serviceaccount,displayname ConvertTo-Json #write the output to file $json Out-File $JSONoutputFile #invoke to process to open JSON file invoke-item $JSONoutputFile Reading the content of a file in PowerShell is very easy. Use the Get-Content cmdlet with the raw parameter to read the content of JSON file. 1234 $json=Get-Content -Raw -Path E:\cu2\ServericeReport.JSON ConvertFrom-Json$json select-object @{Name="servername";Expression={$_.servername.value}},name,startmode,state,serviceaccount,displayname ft -AutoSizeRelational data presentation for JSON
Let’s discuss the JSON data representation in a tabular format by importing the JSON file in SQL Server. The OPENROWSET(BULK) is Table Valued function to read data from JSON file. The SQL 2016 contains various JSON constructs for data manipulation and relational data transformation. OPENJSON(BULK) – The TVF(Table Valued Function) reads and content of JSON file and return the value via BulkColumn. The return value is either stored in a variable or table. This section details the use of OPENROWSET(BULK) and OPENJSON(Bulkcolumn).The OPENROWSET read text value from a file and return it as Bulkcolumn. The Bulkcolumn is then passed to OPENJSON TVF. The function will iterate over the array values to return required values of each attribute. Let’s execute the SQL to import the JSON data. The JSON file is saved in Unicode format hence have used SINGLE_NCLOB in the SQL. You can also make use of other bulk loading options such SINGLE_CLOB, SINGLE_BLOB depending on the type of the data stored in a file. The below SQL is an effort to define the use of inline JSON data processing. 123456789101112131415 SELECT t2.value Servername,t.name,t.startmode,t.state, t.serviceaccount,t.DisplayName FROMOPENROWSET(BULK N'\\hqsqrt05\e$\cu2\Serverice.JSON', SINGLE_NCLOB) AS JSON CROSS APPLY OPENJSON(BulkColumn) WITH( name nvarchar(40), startmode NVARCHAR(20), state NVARCHAR(20), serviceaccount NVARCHAR(60), DisplayName NVARCHAR(60), Servername NVARCHAR(MAX) as JSON ) AS t CROSS APPLY OPENJSON(Servername) WITH(value nvarchar(100)) t2In-Built JSON constructs for data processing
Processing is made much easier by using the available JSON constructs on referring JSON paths. In this case, the dollar sign ($) references entire JSON object in the input text for processing. The OPENJSON is a table-value function that parses JSON text and returns objects and properties in JSON as rows and columns. The OPENJSON function and with clause lets to define an explicit schema. This is another way of processing the JSON text using JSON path references. 12345678910111213 SELECT t.* FROMOPENROWSET(BULK N'\\hqsqrt05\e$\cu2\Serverice.JSON', SINGLE_NCLOB) AS JSON CROSS APPLY OPENJSON(BulkColumn) WITH( Servername NVARCHAR(20) '$.Servername.value' , name nvarchar(40), startmode NVARCHAR(20), state NVARCHAR(20), serviceaccount NVARCHAR(60), DisplayName NVARCHAR(60) ) AS tReporting
Using the FOR XML PATH the casting is done to the string in the result set but not on each column attributes this itself is a great improvement when you deal with huge data. The use of RAW clause which adds the new table row string at the beginning of each record and the ELEMENTS and the [td] column name help to set the cells tags in the HTML table otherwise, each tag would contain the column name. As you can see, using FOR XML PATH can improve the way we write our reports.Conclusion
One of the important system parameters to be measured to increase the availability of the application is discussed in this article. The process, described in this article can be implemented by anyone who monitors large and complex environments across multiple servers as the script has the provision of making customization to the services list and server list.References
SQL Server 2016: JSON integration FOR XML (SQL Server) OPENJSON (Transact-SQL) Configure SQL Server Agent Mail to Use Database MailAuthor Recent Posts Prashanth JayaramI’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021