How to use Python in SQL Server 2017 to obtain advanced data analytics

On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.

After the conference, there were a few questions some of us had, though. Is Microsoft SQL Server 2017 emerging as an enterprise solution for data science? Does it provide the required capabilities—is the engine capable of handling huge data? It seems the answer is “Yes”, as starting with the CTP 2.0 release of SQL Server 2017, Microsoft has brought Python-based intelligence to data in SQL Server.

Python has gathered a lot of interest recently as a language of choice for data analysis. This language has the right set of libraries for data analysis and predictive modeling, not to mention a simpler learning curve.

The growing trends of data science and modeling predict a massive growth in data in the upcoming years. The propulsion towards innovation and adaptation to leading trends in the data technology might intrigue us enough to make us take a look at the current release of SQL Server 2017.

Data science is a combination of Data Mining, Machine Learning, Analytics and Big Data. The integration of SQL 2016 with data science language, R, into the database the engine provides an interface that can efficiently run models and generate predictions using SQL R services. Python builds on the foundation laid for R Services in SQL Server 2016, and extends that mechanism to include Python support for in-database analytics and machine learning.

further reading…….

https://www.sqlshack.com/how-to-use-python-in-sql-server-2017-to-obtain-advanced-data-analytics/

Happy Learning!!

 

 

Posted in Python, SQL Server 2017 | Leave a comment

Top string functions in SQL Server 2017

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

This article outlines the basic concepts of how to use the new string function in SQL Server 2017 on a Linux machine.

The SQL Server 2017 CTP 1.1 contains various string function out of the box, a few of them being:

  • CONCAT_WS, TRANSLATE, and TRIM
  • Support for the WITHIN GROUP clause for the STRING_AGG function.

A collection of new string function in SQL Server 2017, such as TRANSLATE, TRIM, CONCAT_WS, STRING_AGG are almost similar to the string functions of other relational databases engines.

As developers, we try to achieve results in simple and smart ways. The string functions available in SQL Server 2017 make the life of a developer much simpler.

So, let us get started, and see the usage of the available string functions!

TRIM

Removes the space character char(32) or other specified characters from the start or end of a string.

As a SQL developer, you must have often come across a scenario where you had to remove the empty spaces at the beginning and the end of strings. To achieve that, you may have used the string functions, RTRIM and
LTRIM—you had to use both because SQL Server does not have a function which can trim leading or trailing spaces of a given string with a single function, even though TRIM() is a very popular function in many languages. The release of SQL Server 2017 comes with this new string function “TRIM”, which you can use to get rid of the leading and trailing spaces around any given string.

SQL Server 2017 SQL Server
Syntax TRIM ([characters FROM] string)
Characters could be a literal, variable, or a function call of any non-LOB character type (nvarcharvarcharnchar, or char) containing characters to be removed. nvarchar(max) and varchar(max) types are not allowed.
A string is an expression of any character type (nvarcharvarcharnchar, or char) from which characters should be removed.
CREATE FUNCTION dbo.TRIM(@str VAR-CHAR(MAX))
RETURNS VAR-CHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@str))
END
or CLR

Let us consider an example string, from which we have to remove the leading and trailing spaces:

further reading

https://www.sqlshack.com/top-string-functions-in-sql-server-2017/

Happy Learning!!

 

 

Posted in SQL Server 2017, Uncategorized | Tagged , , , | Leave a comment

Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol

In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux.

In the case of moving a database to Linux, SQL Server’s Backup and Restore feature is the recommended mode of migration; we would migrate from SQL Server to Windows to SQL Server vNext CTP 1.4 on Linux.

This topic provides step-by-step instructions for this procedure. In this tutorial, you will:

  • Create a database, SQLShack
  • Backup SQLShack on a Windows machine
  • Transfer the backup to your Linux machine using a File Sharing Protocol, Samba. Use Samba* to setup an SMB network share between the Windows and the Linux machines.
  • Restore the database using the SSMS console (sqlcmd) or GUI

Demonstration

Let’s create a database, SQLShack. I’m going to switch to the SQLShack database to create a sample table to hold dummy data which is needed to demonstrate the restoration process.

Database Backup on Windows Machine

Backup the database, SQLShack, on a Windows Machine.

The following simple command creates a backup of SQLShack to a given file location

Transfer backup file to the Linux machine

You can use Samba to create a shared folder on the Linux system and transfer files from the Windows computer to the Linux (CentOS) computer—or vice versa. This allows the files to be shared on the network through the SMB protocol. This will also allow Windows hosts to get remote access to the files using the file explorer.

Let us go through a step-by-step installation and configuration process of Samba so that we can easily share the files between computers running different operating systems.

further reading…

https://www.sqlshack.com/migrate-an-sql-database-from-windows-to-linux-using-backup-and-restore-and-smb-protocol/

Happy Learning!!!

 

Posted in SQL, SQL 2016, SQL Server vNext | Tagged , , , | Leave a comment

Installation of SQL Server vNext CTP on Linux Distribution CentOS 7

It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.

Introduction

The close integration of various data sources under one umbrella called Hadoop – Big Data enabled DBAs to handle data and its data structures on the available platforms. It’s a great opportunity for the SQL community members to try MS SQL on the non-windows platform.

SQL Server on Linux can potentially provide customers with even more flexibility in their data solution. SQL Server’s proven enterprise experience and capabilities is a valuable asset to Enterprise Linux customers around the world. It’s expanding the database market to meet the needs and be on par with the changing trends in technology.

This article is an effort to detail the instructions for the installation of SQL Server vNext on Linux. The article also includes several basic Linux commands, thereby being helpful in understanding the process of installation and configuration.

This article is a comprehensive guide for the installation and configuration of MSSQL; it includes:

  • Introduction and understanding of how SQL Server run on Linux
  • Challenges and complications in building and managing SQL Server on Linux
  • Curating the MSSQL Server installation using Linux commands
  • Detailed installation procedure

Pre-requisites

  • Basic understanding of Linux
  • Access to CentOS/RHEL Software Libraries
  • At least 4 GB RAM
  • At least 8 GB of hard disk space

How Does SQL Server Run on Linux?

Microsoft has provided great insight on how they were able to port SQL Server to run natively on Linux introducing what is known as Platform Abstraction Layer (“PAL”). The Microsoft Research Team set out to bring full functionality, performance, and scale value of the SQL Server RDBMS to Linux. The Microsoft Research Drawbridge acts as an abstraction layer between the OS and the application layers. Drawbridge explored a new approach to process virtualization and isolation. It’s a form of virtualization, specifically for application sandboxing. In fact, it’s very hard to provide a capable SQL version outside of windows within the stipulated time, hence MSR (Microsoft Research) team decides to integrate SQL Server’s existing platform layer SOS (SQL Server Operating System) with Drawbridge to create what we call as the SQLPAL. The SOS provides robust memory management, thread scheduling, and IO services. Creating SQLPAL enabled the full functional SQL version to run on Linux. Please read the reference section for more information.

Further reading….

https://www.sqlshack.com/installation-of-sql-server-vnext-ctp-on-linux-distribution-centos-7/

Thanks for reading my space. Happpy Learning!!

 

Posted in SQL Server vNext | Tagged , , , | Leave a comment

Understanding Database Backup Encryption in SQL Server

Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.

While this is less of a challenge in an all-on-premises environment, database backups stored off-site or on the cloud require some more precaution. The data literally resides on someone else’s infrastructure. Data security has to be thought of from a different perspective now. If someone gets unauthorized access to the site, they could simply restore a copy of your database from a backup, onto their own hardware. What good, then, is it to lock the doors of your own equipment, when the soul has already departed?

Security best-practices are in place in order to secure the metaphorical soul. They need to be implemented not just in the production environment, but within our backup solution as well.

Earlier versions of SQL Server had a limitation on this security feature; we had to use third-party solutions to encrypt and compress the backup files. Microsoft, with SQL Server 2014, has begun introducing database backup encryption within the native backup capability—now, SQL Server has the ability to encrypt the data while creating a backup using various encryption algorithms!

Continue reading…..

www.sqlshack.com/understanding-database-backup-encryption-sql-server/

Happy Learning!!

 

Posted in SQL, SQL 2012, SQL 2016 | Tagged , , , , , , , | Leave a comment

SQL Server 2016 enhancements – Truncate Table and Table Partitioning

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the truncate command partitioning enhancements in SQL 2016

One of the biggest challenges for a DBA is to identify the right candidate for table partitioning, as it requires expertise in design and implementation.

The following are the various truncating options available in SQL 2016

  • Truncate individual partitions
  • Truncate multiple individual partitions
  • Truncate a Range of partitions
  • Truncate a Range with multiple individual partitions

Article Highlights

  • Define the importance and the implication of SQL table partitioning
  • Identify the right candidate for table partitioning
  • Provide inline comparison of features available in the different editions of SQL 2016
  • Discuss the truncate partition enhancements in SQL 2016
  • Demonstrate the truncate table partition use cases

SQL Partition

Let’s understand the objective of SQL partitioning, why we need partitioning and the factors that are vital to deciding on a Table Partitioning Strategy.

Partitions are a logical mapping of the physical data. A well-designed partition gives us an option to scale out the data. It optimizes the performance and simplifies the management of data by partitioning each table into multiple separate partitions. Although, not all tables are good candidates for partitioning. If the answer is ‘yes’ to all or most of the following questions, table partitioning may be a viable database design strategy; if the answer is ‘no’ to most of the following questions, table partitioning may not be the right solution for that table.

Continue reading……..

https://www.sqlshack.com/sql-server-2016-enhancements-truncate-table-table-partitioning/

 

Happy Learning!!!

 

Posted in SQL 2016, T-SQL | Tagged , , , , | Leave a comment

SQL Server Database Recovery Process Internals – database STARTUP Command

A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on a lot of scenarios. The desire to improve recovery results has resulted in various procedures but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.

Introduction

Let’s discuss a scenario where we have a SAN disk issue and latency is really high and think many jobs on the data warehouse systems started running indefinitely without a success.

The owner of the server felt this might be a server issue and decided to restart the server but from that point, he’s invited trouble. Everything comes back but he’s now not able to query the database because of a block generated by the system process on the user sessions during the recovery process. This leads to an involvement of senior DBA to troubleshoot the problem.

Further reading…

https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

 

Happy Learning!!

 

Posted in SQL | Tagged , , | Leave a comment

Custom keyboard shortcuts in SQL Server Management Studio (SSMS)

People love taking shortcuts because it’s the easiest way to do things faster; computers are no exception. Shortcuts, particularly ones performed by keyboard, can save you hours of time once applied properly. Learning keyboard shortcut saves you a lot of time; you’ll definitely notice a boost the productivity because you’re not unnecessarily reaching for a mouse all the time.

I’ve rounded up some of the most common DBA customized keyboard shortcuts in SSMS. The same set of rules can be applied to define one which you feel is most important in your day to day activities. Stay on board and you’ll be mastering these tricks in no time!
https://www.sqlshack.com/custom-keyboard-shortcuts-in-sql-server-management-studio-ssms/

Happy Learning!!

Posted in SQL, SQL 2012, SQL 2016 | Tagged , , | Leave a comment

Illustration of an example availability monitoring service Using PowerShell and SQL

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 HTML

Article highlights

  • Defines the objective of JSON
  • Explains the PoSH JSON cmdlets 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 data

Continued…..

http://www.sqlshack.com/illustration-of-an-example-availability-monitoring-service-using-powershell-and-sql/

Happy Reading!!

 

Posted in JSON, SQL | Tagged , , , | Leave a comment

PowerShell: Managing Service(s) Using WMI, CIM and Get-Service Cmdlet

Abstract

A common task of any administrator is to manage and maintain the application availability by checking the various services status, accomplish configuring the services and restarting the service on a local/remote system. There are several components are available to accomplish this day to day tasks.This can be done using WMI (Win32_Services/Get-CIMinstance(PowerShell 3.0 onwards)) and use Get-Service along with Stop/Start/Restart-Service cmdlets.There are many instances where we opt for one over the other available GUI tools or commands.Let’s consider a requirement to check only those services where StartMode set to Auto or services that are stopped or services that are disabled. In this case, the Get-Service cmdlet which does not bind any such information hence querying Win32_Service or Get-CIMInstance are appropriate and serves the purpose. But fortunately, we have Windows Management Instrumentation (WMI). This class has several properties such as StartMode, State, start name etc:-
Introduction
This article talk about how to services are administrated using available components in our day to day activities.  This also includes how to monitor service(s) on local/remote systems over a group of servers using credentials and without using credentials.This script will allow you to read a list of servers from the input file and use credentials to connect remote/DMZ servers to pull service status information for a set of specific services. The various output forms are included with this article are excel, CSV, JSON and PowerShell console output.
First, we have to collect all services available on the computer. You can use three methods for this purpose:
  • Get-WMIObject
  • Get-CimInstance:(Supported in PowerShell 3.0 onwards)
  • Get-Service

Querying WMI

WMI is the Microsoft implementation of Web-Based Enterprise Management (WBEM), with some enhancements in the initial version of it, WBEM is an  industry initiative to develop a standard technology for accessing management information in an enterprise environment that covers not only Windows but also many other types of devices like routers, switches, storage arrays …etc. WMI uses the Common Information Model (CIM) industry standard to represent systems, applications, networks, devices, and other managed components. CIM is developed and maintained by the Distributed Management Task Force (DMTF).
All of that sounds pretty but when Microsoft developed the first versions of WMI they use DCOM (Distributed Component Object Model) wish if a proprietary  Microsoft Technology, so the standards and cross compatibility just took a back seat at the time, on more recent versions of the operating system with Windows Management Framework 2.0 and above MS started to include more and more of the standards and shifted to using WS-Management SOAP-based protocol thru their WinRM (Windows Remote Management) protocol.
We can look at WMI as a collection of objects that provide access to different parts of the operating system, just like with PowerShell objects we have properties, methods, and events for each. Each of these objects is defined by what is called MOF  (Manage Object Format) files that are saved in %windir%\System32\wbem with the extension of .mof. The MOF files get loaded by what is called a Provider when the Provider is registered he loads the definitions of the objects into the current WMI Namespace. The Namespace can be seen a file system structure that organizes the objects on function, inside of each namespace the objects are just like in PowerShell in what is called Class Instances and each of this is populated with the OS and Application information as the system runs so we always have the latest information in this classes.
Namespaces are organized in a hierarchical way where \root is the top level for all other namespaces. The default namespace where most of the other namespaces and classes are located is root\CIMv2 on Windows Kernel 6.x on Kernel 5.x it is Default\CIMv2. Some are installed by default and others are only available when specific applications are installed.
In summary, each Namespace contains Classes, these have:
Methods Actions that can be taken.
  • Properties Information that can be retrieved.
  • Instances of the class objects (services, Processes, Disks) each instance with Methods and Properties.
  • Events are actions that WMI can monitor for and take action when they happen.
  • Caveats of WMI
Now WMI is great, do not get me wrong, but sadly it does have some caveats the main ones being:
Not all classes are available on all versions of Windows. Check
Some applications even from MS have Providers in one version and not in another (Office) and in some cases they even change the properties and methods.
In other words, if you plan on running WMI queries against a series of hosts that may be of different versions of Windows or a specific application do make sure you test and validate your results. This goes the same for Architecture if you are testing x64 or x86.
Display all service names matching a string pattern.  The below example lists all the services starts with character “A”
get-service -ComputerName hqdbsp18|  Where-Object {$_.Name -like 'A*'} | select -expand name 
get-service -ComputerName hqdbsp18|  Where-Object {$_.Name -match '^M' -or $_.Name -match '^SQ' } | select -expand name
[] – for range matching. For example, Name LIKE ‘[M-S]%’ will list all services starting with any letter from a to f.
Get-WMIObject Win32_Service -Filter "Name LIKE '[M-S]%'" -ComputerName hqdbsp18| Select *|format-table -AutoSize
^ – not. For example, Name LIKE ‘[^M-S]%’ will list services that do not start with any letter from a to f.
Get-WMIObject Win32_Service -Filter "Name LIKE '[^M-S]%'" -ComputerName hqdbsp18| Select *|format-table -AutoSize
_ – matches one letter. For example, Name LIKE ‘_QL%’ will list services that start with S and followed by any letter.
Get-WMIObject Win32_Service -Filter "Name LIKE '_Q_S%'" -ComputerName hqdbsp18| Select *|format-table -AutoSize
Group the services based on its startup service account
Get-wmiobject win32_service -ComputerName HQDBSP18 | group startname |select * |Format-Table -AutoSize

Querying Get-CimInstance

PowerShell 3.0 shipping with Windows server 2012 and Windows 8 brings a new set of Cmdlets to manage any server or device that complies with CIM and WS-Man standards defined by DMTF. In this blog post we will explore these new Cmdlets and how can they help IT Pros in managing a data center.The WMI is derived from CIM and it provides and query interface called WMI Query Language (WQL) for accessing CIM object data.

The Get-CimInstance cmdlet available in PowerShell V3 can be used to retrieve WMI information from a remote computer using the WSMAN protocol instead of the legacy WMI service that uses DCOM and RPC. However, the remote computers must be running PowerShell 3 and WSMAN protocol version 3. When querying a remote computer,  Get-CIMInstance setups a temporary CIMSession. However, if the remote computer is running PowerShell 2.0 this will fail. You have to manually create a CIMSession with a CIMSessionOption to use the DCOM protocol. This Script does it for you and creates a CimSession depending on the remote Computer capabilities.

Get-cimInstance win32_service -computer hqdbsp18 -filter "startname like '%localsystem%' and name like '%App%'"| Select Name,startmode,state,startname,systemname |Format-Table -AutoSize
Get-wmiobject win32_service -computer hqdbsp18 -filter "startname like '%localsystem%' and name like '%App%'"| Select Name,startmode,state,startname,systemname |Format-Table -AutoSize

Get-Service

The Get-Service cmdlet retrieves information about all services running on the local/remote system. These cmdlets are.NET objects (of the type System.ServiceProcess.ServiceController) and Its to apply advanced filters and operations to administrate the services
First, Let’s list the available cmdlet to manage the services
Get-Command | findstr Service

Querying Get-Service

The Get-Service cmdlet retrieves information about all services running on the local/remote system. These cmdlets are .NET objects (of the type System.ServiceProcess.ServiceController) and Its to apply advanced filters and operations to administrate the services
The Get-Service cmdlet doesn’t support for supplying credentials, this is one of the main limitation of Get-Service cmdlet when it comes for non-domain or cross-domain scenarios.The workaround for the problem is to make use of Invoke-Command

Credentials

Most of the time it’s not good practice to hardcode the credentials. In that case, you might need to encrypt the password and use the secured code in the script. You need to run the below Powershell script to pull the encrypted password to c:\SecurePassword.txt and copy the code in the script.
$password = read-host -prompt "Enter your Password"
$secure = ConvertTo-SecureString $password -force -asPlainText 
ConvertFrom-SecureString $secure |Out-File c:\SecurePassword.txt
In the below example, Test@2013##) password is encrypted and its content is shown below
There are many instances where it’s ok to store the password in the script itself. In this case, the encrypted password is used in the script which serves the purpose.
The Get-Service and Get-CIMinstance cmdlet doesn’t accept the credential parameter. The workaround for the problem is to use the invoke-command cmdlet to create a session on the remote server.
Invoke-Command { Get-Service "*SQL*" } -ComputerName HQDBSP18 -Credential $Credentials |select Name,displayname,Status |Format-Table -AutoSize
This is the advantage of using WMI object where it accepts the credential parameter.

# Hard code the the Credentials details
$User = "CDW\DDOV810"
$Pass = ConvertTo-SecureString "thanVitha@2016" -AsPlainText -Force
#contain the username and password in a variable
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
Get-WMIObject Win32_Service -computer hqdbsp18 -credential $Credentials |where-object {$_.name -like "*SQL*"} |select Name,startmode,state,startname,systemname |Format-Table -AutoSize


The below screen shot gives more insight on the credentials parameter

The CIM Cmdlets do not have a -Credential parameter. The only way to specify alternate credentials is to manually build a new CIM session object, and pass that into the -CimSession parameter on the other cmdlets. The details are given below

$User =
"hadt\sdsdfs"
$Pass = ConvertTo-SecureString "sdfsdf@2016" -AsPlainText -Force
#contain the username and password in a variable
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
$CimSession = New-CimSession -ComputerName abcd -Credential $Credentials
$service = Get-CimInstance -ClassName Win32_service -CimSession $CimSession
$service
Remove-CimSession -CimSession $CimSession


Return to Top


Simple way to get the services Details in a CSV file

The simplest and quickest way to retrieve the services details is by exporting the data to CSV.The two Windows PowerShell cmdlets that work with comma-separated values are ConvertTo-CSV and Export-CSV. The two cmdlets are basically the same; the difference is that Export-CSV will save to a text file, and ConvertTo-CSV does not.
# The output CSV file
 $CSVOutputfile="c:\Services.csv"
#Input file server details location
$input ="c:\server.txt"
# query win32_service class
Get-WmiObject Win32_Service -ComputerName (Get-Content $input )|Select-Object -Property __Server,Name,startmode,state,serviceaccount,displayname |Export-Csv -Path $CSVOutputfile
#The Invoke-Item cmdlet provides a way to open a file  from within Windows PowerShell.
invoke-item $CSVOutputfile

Service details in Excel

PowerShell is made available to use Excel component by invoking excel.application COM object that will allow us to work with excel to add data and format that data.  The New-object -ComObject  creates a new excel object using COM components and it made visible for troubleshooting using visible property set to true.  Once everything is defined, we can call the respective methods and properties to create workbook by adding sheet as its item

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
#you can see the Excel sheet and helpful in troubleshooting
$Excel.visible = $True
#After instantiating the excel objects, It's a time use its respective methods and properties
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

Workbook number and details

FileFormat numbers in Mac Excel. These are the main file formats in Windows Excel 2007-2016:

51 = xlOpenXMLWorkbook (without macro’s in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
$xlOpenXMLWorkbook=[int]51

<#  
.SYNOPSIS     
    Name :  Disk Space Utilization excel Report (Get-ServiceExcelReports.ps1)
    Description : Get disk space usage informations from remote server(s) with WMI and output CSV file
    
    Author : Prashanth Jayaram
       
    * Select list of servers from a text file
    * Get remote Servers informations with WMI and Powershell
    * service (Servername,Name,startmode,state,serviceaccount,displayname + display a Excel output)
      
       
.INPUT
    Input Server text file
    Service list
   
.OUTPUTS
    Excel output
   
.NOTES
    Version:        1.0
    Author:         Prashanth Jayaram
    Creation Date:  2017-02-02
    Purpose/Change: Initial script development
     
.EXAMPLE
    .\Get-ServiceExcelReports.ps1 -ServerList C:\server.txt -includeService "VM","Dhcp","SQL"
#>
  
  
#########################################################################################
  
 
  
param (
    [Parameter(Mandatory=$true)][string]$ServerList,
    [Parameter(Mandatory=$true)][string[]]$includeService
 )
  
 
$excel = New-Object -ComObject Excel.Application
 
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.Worksheets.Item(1)
 
$worksheet.Cells.Item(1,1) = "Server Name"
$worksheet.Cells.Item(1,2) = "Service Name"
$worksheet.Cells.Item(1,3) = "State"
$worksheet.Cells.Item(1,4) = "StartUp Type"
$worksheet.Cells.Item(1,5) = "Service Account"
$worksheet.Cells.Item(1,6) = "DisplayName"
 
  
$range = $worksheet.UsedRange
$range.Interior.ColorIndex = 19
$range.Font.ColorIndex = 11
$range.Font.Bold = $true
  
$row = 2
  
$IncludeArray=@()
  
#List of programs to exclude
#$IncludeArray = $includeService
  
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 $include                                    
 if(($item.name).Contains($include) -eq $TRUE)
    {
        $worksheet.Cells.Item($row, 1) = $servername
        $worksheet.Cells.Item($row, 2) = $item.name
        $worksheet.Cells.Item($row, 3) = $item.Status
        $worksheet.Cells.Item($row, 4) = $item.startmode
        $worksheet.Cells.Item($row, 5) = $item.startname
        $worksheet.Cells.Item($row, 6) = $item.displayname          
        $row++
        
     }
    }
 }
}
}
   
$range.EntireColumn.AutoFit()
$excel.Visible = $True

Excel Output

Return to Top


Service Details in CSV file

The main part of the code takes input details from a text file and output columns are ordered and customized as per the requirement. The five column output are listed in the csv output file and they are Servername, Name,start mode, state, serviceaccount and displayname. The three input parameters input text file, service list used to compare against the listed services and csv output file used to capture all five column outputs.
<#
 
.SYNOPSIS      
    Name :  Disk Space Utilization csv Report (Get-ServiceExcelReports.ps1)
    Description : Get disk space usage informations from remote server(s) with WMI and output CSV file
   
    Author : Prashanth Jayaram
      
    * Select list of servers from a text file
    * Get remote Servers informations with WMI and Powershell
    * service (Servername,Name,startmode,state,serviceaccount,displayname + display a csv output)
     
      
.INPUT
    Input Server text file
    Service list
  
.OUTPUTS
    CSV output, console and Grid output
  
.NOTES
    Version:        1.0
    Author:         Prashanth Jayaram
    Creation Date:  2017-02-02
    Purpose/Change: Initial script development
    
.EXAMPLE
    .\Get-Servicecsv.ps1 -ServerList C:\server.txt -includeService "VM","Dhcp","SQL"
#>
 
 
#########################################################################################
 
 
param (
    [Parameter(Mandatory=$true)][string]$ServerList,
    [Parameter(Mandatory=$true)][string[]]$includeService,
    [Parameter(Mandatory=$true)][string]$CSVoutputFile
 )
 
# Check if the output file CSV exist, if exists then delete it.
 
if (test-path $CSVoutputFile ) { rm $CSVoutputFile } 
#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 $include                                     
         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
$props | Select-Object Servername,Name,startmode,state,serviceaccount,displayname  |Export-Csv -Path $CSVoutputFile
Invoke-item $CSVoutputFile

Service Details in HTML file

The below section outlines the use of the convert-to-HTML cmdlet.without digging much into the detail, the <table> element, the <th> (table heading) element, and <td> (table cell) element. These styles enable us to put a nice border around our table and around each of the cells in that table: The See Also section has several in-depth information about formatting.
<#
 
.SYNOPSIS      
    Name :  Disk Space Utilization excel Report (Get-ServiceHTML.ps1)
    Description : Get disk space usage informations from remote server(s) with WMI and output HTML file
   
    Author : Prashanth Jayaram
      
    * Select list of servers from a text file
    * Get remote Servers informations with WMI and Powershell
    * Disk (Disk type, letter, capacity in GB, free space in GB, % free , Status + display a HTML output)
     
      
.INPUT
    Input Server text file
    Service list
    Output HTML file
  
.OUTPUTS
    HTML output
    Console Output
  
.NOTES
    Version:        1.0
    Author:         Prashanth Jayaram
    Creation Date:  2017-02-02
    Purpose/Change: Initial script development
    
.EXAMPLE
   
   Get-ServiceHTMLReport.ps1 -ServerList "\\hq6021\c$\server.txt" -includeService "VM","Dhcp","SQL" -OutputHTML "e:\CU2\ServericeReport.htm"
#>
 
 
#########################################################################################
param (
    [Parameter(Mandatory=$true)][string]$ServerList,
    [Parameter(Mandatory=$true)][String[]]$includeService,
    [Parameter(Mandatory=$true)][string]$OutputHTML
 )
<#
[String]$ServerList="\\hq6021\c$\server.txt"
[String[]]$includeService= "VM","Dhcp","SQL"
#>
$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 $include                                     
         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
$a = "<style>"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "</style>"
$props |Select-Object Servername,Name,startmode,state,serviceaccount,displayname| ConvertTo-HTML -head $a |  Out-File $OutputHTML
invoke-item $OutputHTML

HTML Output

Conclusion

There are plenty of tools that are available to find the windows service. The article illustrated how to access and query the Windows Service on a local/remote machine using available tools and technique of PowerShell i.e. is WMI, CIM and Get-Service cmdlet also explains the various ways to identify the service status.  The service(s) detail output is reported using CSV, Console, HTML and Excel. The references links also state the difference protocol usage and the use of CIM are have better control over the WIM class libraries in terms of design and performance.  Even though windows remoting is disabled still able to use the CIM cmdlets to query the remote computer.The CIM can also be used to access non-windows systems.

References

Posted in PowerShell | Tagged , , , , , , | Leave a comment
%d bloggers like this: