Troubleshoot Missing Of MSI or MSP files using FixMissingMSI tool

If you’ve ever had troubles with missing cached MSI/MSP files during SQL server setup, you might be familiar with the following article:
How to fix difficulties that occur during a SQL Server update by restoring the missing Windows Installer cache files.

https://docs.microsoft.com/en-US/troubleshoot/sql/install/restore-missing-windows-installer-cache-files

The above link has a VB script that will assist you in locating lost MSI/MSP files in the Windows installer folder, as well as recovering these files if the setup source is restored. The VB script works fine, however if there are a lot of missing MSI/MSP files, copying them back one by one can be tedious.

FixMissingMSI Tool :

The FixMissingMSI software will be used in this approach to locate MSI and MSP files that are missing from the Windows Installer cache. You can also point the tool to the original media locations and have it recache the missing files as an extreme measure.

The FixMissingMSI software is available for Free download from the below link :

https://github.com/suyouquan/SQLSetupTools/releases/

I extracted the file from the latest version 2.2.1 software that I downloaded from the URL above.

One of the prerequisites for using this tool is that you have.NET Framework 4.5 installed.

After you’ve extracted the downloaded software, navigate to the Manual folder to learn how to use this tool.

When I was installing SQL 2014 Service Pack 3 and Cumulative Update today, I ran into an issue with MSI and MSP files missing. I tried this FixMissingMSI software to see if I had several files missing, and we were able to fix them using this tool.

When I double-clicked FixMissingMSI.exe, a wizard appeared, asking if I wanted to set up the scan menu.
If you select “Product name contains” and enter “SQL,” the program will search for all products whose product names contain the key word and also you need to provide Media + SP + CU location path

If you specify this setup source folder, this program will search all of the *.MSI and *.MSP files inside to see if any missing or mismatched MSI/MSPs can be identified.
If you don’t specify this folder, the tool may be unable to locate the MSI/MSP for those missing/mismatched files, leaving you unable to repair them. Even if you don’t specify this path, this program may still be able to locate the missing MSI/MSP in the installed product’s LastUsedSource folder.
For example, for the SQL product, the best practice is to place the SQL server RTM setup media and all subsequent service packs, CUs in the same folder, for example:

Please remember to extract the package using the ‘X‘ switch to the folder if you download service packs or cumulative updates from the Microsoft site. For instance, the command below extracts SP1 to the SP1 subfolder:


SQLServer2014SP3-KB4022619-x64-ENU.exe /X


Even though the package is in the same folder, it is useless if you do not extract it.

I’ve created a media path that includes RTM, SP, and CU. Clicked SCAN NOW button

If you have any missing files, the scanning results will show them like below :

You will see all files in the All option, which signifies the status of Missing, Mismatched, and OK.
Only those will be listed if you pick Missing or Mismatched only.

When I selected Fix It, a pop-up window appeared, instructing me to click OK to copy the missing file.

It was fixed by copying the file from Install Source to Installer Folder after pressing the Fix It button, and that line was marked as GREEN.

We can fix the missing msi files separately, as shown above, however fixing the number of multiple files individually is a time-consuming operation. So, we can accomplish it by going to the Fix TAB and clicking on the Fix All button.

As you can see, I have 60 missing files that need to be restored to the Installer folder on Drive C. When I clicked the Fix All option, a pop window appeared asking me to click OK to immediately fix all missing files. I pressed the OK button.

It repaired 30 missing files and attempted to install SQL 2014 SP3 and CU, which it did successfully.

There are still 30 files that need to be repaired. Here’s what I noticed from the tool, This FixmissingMSI tool has a column called ‘LastUsedSource,’ which is nothing but the location where we installed the most recent SP or CU. If the setup folder exists on the server, this tool will automatically gather information from that LastUsedSource, which is nothing more than the Last Installed Source location of the media, service pack, or cumulative update.

It will not do anything if the recently installed SP or CU folders do not exist on the server, and the column ‘Fixcommand’ will not be generated with any commands to repair it.

I‘m not sure if my assumption is correct.

As a report, we can also export the data from the missing files to a CSV and text file.

When you are exporting the data out from File TAB and clicking on Export just provide the Filename and click ok
Two Files with format .csv and .txt are automatically created on the physical location where you have given path to store with Filename which you have given .

Note :

The SQL Media\Service Pack \ Security Update \ Cumulative Update We must manually extract any patch or ISO file to a specific directory or folder before running the installation by double-clicking the “SETUP.exe” file, which is Best Practice for Installation.
If we launch the package without first manually extracting it, the software will extract it to an unknown location and begin the setup process. When the server is REBOOTED, that UN-known location may no longer exist on the server, and the next time we run any New SP\CU\SU, it will check for previously extracted locations, but if that place is not present on the server, an error will be thrown, and the installation will fail.

SQL Agent jobs Status Report Using PowerShell

As a SQL DBA we need to know if SQL Agent jobs are running successfully on all the Servers which we are supporting, for this we have SCOM monitoring in place and which is linked to SNOW ticketing tool means If any SQL Agent Fails it triggers a SNOW ticket to our DBA Queue and we work on it.

But recently there was a SCOM upgrade happened and after that we our DBA team are not getting any sort of alerts for some servers which we are supporting this includes not getting SNOW tickets for SQL Agent Job Failures , this becomes a problematic for us to Login each and every server to check the jobs status (Servers which are Missed by SCOM to generate a ticket ).

Manual checking is creating a lot of work for us So, thought of Automating it to get the SQL Agent Jobs Status Report generated for every day ( in each Shift at least once ). In my career starting there is no monitoring tool setup in my environment to check the status of jobs ,databases e.tc., At that time our Team Leader had done some good automation to reduce our DBA’s work in that one of the Automation is to get Status Report of SQL Agent Jobs for all the Servers which are provided in TEXT file and get the report stored in an Excel file .

I have that script with me and saved in my Scripts repository , today that script is reQuired for me in my current organization So, I have did some testing in my Personal Laptop and it works well.

This Automation was done using a PowerShell where we need to provide the list of servers in a text file to check for SQL Agent jobs Status .Also we need an Excel already installed on your Remote VDI or Citrix where you are running this script and the Login account which you are using for running the PowerShell script needs to have Admin permission to all the SQL Instances mentioned in the text file.

I have created a text file with name ‘ Servers ‘ and provided list of Instances in the text file that you want to check .

I have opened the PowerShell ISE with run As Administrator and copied below script to it :

#############################################################################################
#
# NAME: SQL Agent Job Status Report to Excel.ps1
# Iterates through the sqlservers.txt file to populate
# Excel File with color coded status
## ————————————————————————

# Get List of sql servers to check
$sqlservers = Get-Content 'C:\temp\Servers.txt'; # Change the Path here where you stored the servers text file

# Create a .com object for Excel
$xl = new-object -comobject excel.application
$xl.Visible = $true # Set this to False when you run in production

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

$date = Get-Date -format f
$Filename = ( get-date ).ToString('ddMMMyyyHHmm')

$cells = $ws.Cells

# Create a description

$cells.item(1, 3).font.bold = $True
$cells.item(1, 3).font.size = 18
$cells.item(1, 3) = "Jobs Status Report $date"
$cells.item(5, 9) = "Last Job Run Older than 1 Day"
$cells.item(5, 8).Interior.ColorIndex = 43
$cells.item(4, 9) = "Last Job Run Older than 7 Days"
$cells.item(4, 8).Interior.ColorIndex = 53
$cells.item(7, 9) = "Successful Job"
$cells.item(7, 8).Interior.ColorIndex = 4
$cells.item(8, 9) = "Failed Job"
$cells.item(8, 8).Interior.ColorIndex = 3
$cells.item(9, 9) = "Job Status Unknown"
$cells.item(9, 8).Interior.ColorIndex = 15


#define some variables to control navigation
$row = 3
$col = 2

#insert column headings

$cells.item($row, $col) = "Server Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$col++
$cells.item($row, $col) = "Job Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 40
$col++
$cells.item($row, $col) = "Enabled?"
$cells.item($row, $col).font.size = 16    
$Cells.item($row, $col).Columnwidth = 15
$col++    
$cells.item($row, $col) = "Outcome"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 12
$col++
$cells.item($row, $col) = "Last Run Time"
$cells.item($row, $col).font.size = 16    
$Cells.item($row, $col).Columnwidth = 15
$col++

   
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;


# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
    # Create an SMO Server object
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
 
    # For each jobs on the server
    foreach ($job in $srv.JobServer.Jobs) {

        $jobName = $job.Name;
        $jobEnabled = $job.IsEnabled;
        $jobLastRunOutcome = $job.LastRunOutcome;
        $Time = $job.LastRunDate ;

        # Set Fill Colour for Job Enabled
        if ($jobEnabled -eq "FALSE")
        { $colourenabled = "2"}
        else {$colourenabled = "48" }         

        # Set  Fill Colour for Failed jobs
        if ($jobLastRunOutcome -eq "Failed") {
            $colour = "3" # RED
        }
            
        # Set Fill Colour for Uknown jobs
        Elseif ($jobLastRunOutcome -eq "Unknown")
        { $colour = "15"}       #GREY        

        else {$Colour = "4"}   # Success is Green    
        $row++
        $col = 2
        $cells.item($Row, $col) = $sqlserver
        $col++
        $cells.item($Row, $col) = $jobName
        $col++
        $cells.item($Row, $col) = $jobEnabled    
        #Set colour of cells for Disabled Jobs to Grey
    
        $cells.item($Row, $col).Interior.ColorIndex = $colourEnabled
        if ($colourenabled -eq "48") { 
            $cells.item($Row , 1 ).Interior.ColorIndex = 48
            $cells.item($Row , 2 ).Interior.ColorIndex = 48
            $cells.item($Row , 3 ).Interior.ColorIndex = 48
            $cells.item($Row , 4 ).Interior.ColorIndex = 48
            $cells.item($Row , 5 ).Interior.ColorIndex = 48
            $cells.item($Row , 6 ).Interior.ColorIndex = 48
            $cells.item($Row , 7 ).Interior.ColorIndex = 48
        } 
        $col++

        $cells.item($Row, $col) = "$jobLastRunOutcome"
        $cells.item($Row, $col).Interior.ColorIndex = $colour

        #Reset Disabled Jobs Fill Colour
        if ($colourenabled -eq "48") 
        {$cells.item($Row, $col).Interior.ColorIndex = 48}

        $col++

        $cells.item($Row, $col) = $Time 
    
        #Set teh Fill Colour for Time Cells

        If ($Time -lt ($(Get-Date).AddDays(-1)))
        { $cells.item($Row, $col).Interior.ColorIndex = 43}
        If ($Time -lt ($(Get-Date).AddDays(-7)))
        { $cells.item($Row, $col).Interior.ColorIndex = 53} 
              
    }
    $row++
    $row++

    # Add two Yellow Rows
    $ws.rows.item($Row).Interior.ColorIndex = 6
    $row++
    $ws.rows.item($Row).Interior.ColorIndex = 6
    $row++
}


$wb.Saveas("C:\temp\SQLAgentJobstatusReport$filename.xlsx") #Change the Path where ever you want the Excel Report to Store
$xl.quit()
Stop-Process -Name EXCEL

I have executed the script , immediately an Excel is opened at the backend where the jobs information is getting populated in it , you can find that in below screenshot :

Once the Report collection is finished the excel file will get closed automatically and saved the populated jobs information in it, you can open the excel file from where you have given the path to Save it in above PowerShell Script.

After opening the Excel file you can see all the servers Jobs Information with Color coded for easy identification like below :

Also after each server it will Separate with Yellow Color coding like below :

From the Servers list I provided one of my SQL Instance is in Stopped State for that server While pulling the information of jobs this script does not get any information but it left the Space for us to know that one of the server information is not pulled by script also the script didn’t stop and it continue to next server to get the needed information.

I have Implemented this in my Current organization and it reduces our manually efforts .

Note : Before Implementing please do some testing from your end and get the confident to Implement in your Environment.

Export Multiple Blob Files Data From Sql Table to Physical Location Using PowerShell

BLOB Data can be in a Audio file, pdf file, doc file, jpg/ png/ any image file of data types which can be loaded to a Sql table and saved in a Sql Database.

I have Imported or loaded some jpg files and pdf files into a Table in Sql Database to save the data. Generally this data loading to Sql table will be done by application team , if they need the data to be exported from a Sql table application will contact us .

I have exported this Blob data using PowerShell Script which is simple to use , for this approach used Ado.Net to query the data and used binary writer to write the files on a drive

I have Created a Database ‘BLOB‘ and a table ‘dbo.BlobData‘ , inserted some jpg and pdf files into the table .

From above screenshot you can see both PDF and JPG files in a Table with 27 rows. Now I am going to export those all files to a physical Location in one go.

I want to export the files to below shown location, currently we don’t see any files:

Below is the PowerShell Script I used to export the data , you need to change Parameters like InstanceName, DatabaseName, Destination Path and SELECT Statement .


## You can export LARGE Blob to file            
## with GetBytes-Stream.         
# In the below Code you need to change some parameters which are needed to get successfull export of blob files from a Table to disk.
            
$Server = "DESKTOP-02JIB76";         # Change to your Instance Name.            
$Database = "Blob";                  # Change to your Database Name
$Dest = "C:\temp\Blob\Export\";             # Change Path here.            
$bufferSize = 8192;               # Stream buffer size in bytes.            
# Select-Statement for name & blob            
# with filter.            
$Sql = "SELECT [PictureName]
              ,[PictureData]
        FROM dbo.BlobData";            
             
# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=True;" +            
                        "Initial Catalog=$Database";            
$con.Open();            
             
# New Command and Reader            
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;            
$rd = $cmd.ExecuteReader();            
             
# Create a byte array for the stream.            
$out = [array]::CreateInstance('Byte', $bufferSize)            
             
# Looping through records            
While ($rd.Read())            
{            
    Write-Output ("Exporting: {0}" -f $rd.GetString(0));                    
    # New BinaryWriter            
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write;            
    $bw = New-Object System.IO.BinaryWriter $fs;            
                
    $start = 0;            
    # Read first byte stream            
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    While ($received -gt 0)            
    {            
       $bw.Write($out, 0, $received);            
       $bw.Flush();            
       $start += $received;            
       # Read next byte stream            
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    }            
             
    $bw.Close();            
    $fs.Close();            
}            
             
# Closing & Disposing all objects            
$fs.Dispose();            
$rd.Close();            
$cmd.Dispose();            
$con.Close();            
             
Write-Output ("Finished");

I have executed above script in a PowerShell and all 27 files are exported to our destination location :

In below screenshot you can see all 27 files are successfully exported from a table :

We can also get this Blob data out by some other methods like below :

. Export Data using BCP T-Sql from SSMS but this required xp_cmdshell needs to be ENABLED

. Export Data using BCP Command from Command Prompt.

. Export Data using OLE Processes which needs Ole Automation Procedures to be ENABLED

Sql Services Information with dbatools

Today I found sql services information with dbatools script where we need to execute a function to get it created , once function is created we can get needed information by calling that function and passing some parameters like computer names, Type of sql service, e.t.c.,

To run this you need to open PowerShell with Run As Administrator, also the Login account from which we are executing needs to have local admin Privileges on target \ destination server. Output of this function can be displayed in a Table Format or in a Grid View . This function I got from powershellgallery site

You can get sql Services information for Single or Multiple Servers , go through the Synopsis.

Function Get-DbaSqlService
{
<#
    .SYNOPSIS
    Gets the SQL Server related services on a computer.
 
    .DESCRIPTION
    Gets the SQL Server related services on one or more computers.
 
    Requires Local Admin rights on destination computer(s).
 
    .PARAMETER ComputerName
    The SQL Server (or server in general) that you're connecting to. This command handles named instances.
 
    .PARAMETER Credential
    Credential object used to connect to the computer as a different user.
 
    .PARAMETER Type
    Use -Type to collect only services of the desired SqlServiceType.
    Can be one of the following: "Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS"
 
    .PARAMETER Silent
    Use this switch to disable any kind of verbose messages
 
    .NOTES
    Author: Klaas Vandenberghe ( @PowerDBAKlaas )
 
    dbatools PowerShell module (https://dbatools.io)
    Copyright (C) 2016 Chrissy LeMaire
    This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
    You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.
 
    .LINK
    https://dbatools.io/Get-DbaSqlService
 
    .EXAMPLE
    Get-DbaSqlService -ComputerName sqlserver2014a
 
    Gets the SQL Server related services on computer sqlserver2014a.
 
    .EXAMPLE
    'sql1','sql2','sql3' | Get-DbaSqlService
 
    Gets the SQL Server related services on computers sql1, sql2 and sql3.
 
    .EXAMPLE
    Get-DbaSqlService -ComputerName sql1,sql2 | Out-Gridview
 
    Gets the SQL Server related services on computers sql1 and sql2, and shows them in a grid view.
 
    .EXAMPLE
    Get-DbaSqlService -ComputerName $MyServers -Type SSRS
 
    Gets the SQL Server related services of type "SSRS" (Reporting Services) on computers in the variable MyServers.
 
#>
[CmdletBinding()]
Param (
  [parameter(ValueFromPipeline = $true)]
  [Alias("cn","host","Server")]
  [string[]]$ComputerName = $env:COMPUTERNAME,
  [PSCredential] $Credential,
  [ValidateSet("Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS")][string]$Type,
  [switch]$Silent
)

BEGIN
  {
  $ComputerName = $ComputerName | ForEach-Object {$_.split("\")[0]} | Select-Object -Unique
  $TypeClause = switch($Type){ "Agent" {" = 2"} "Browser" {" = 7"} "Engine" {" = 1"} "FulText" {"= 3 OR SQLServiceType = 9"} "SSAS" {" = 5"} "SSIS" {" = 4"} "SSRS" {" = 6"} default {"> 0"} }
  }
PROCESS
  {
        foreach ( $Computer in $ComputerName )
        {
            $Server = Resolve-DbaNetworkName -ComputerName $Computer -Credential $credential
            if ( $Server.FullComputerName )
            {
                $Computer = $server.FullComputerName
                Write-Message -Level Verbose -Message "Getting SQL Server namespace on $Computer via CIM (WSMan)"
                $namespace = Get-CimInstance -ComputerName $Computer -NameSpace root\Microsoft\SQLServer -ClassName "__NAMESPACE" -Filter "Name Like 'ComputerManagement%'" -ErrorAction SilentlyContinue |
                            Where-Object {(Get-CimInstance -ComputerName $Computer -Namespace $("root\Microsoft\SQLServer\" + $_.Name) -Query "SELECT * FROM SqlService" -ErrorAction SilentlyContinue).count -gt 0} |
                            Sort-Object Name -Descending | Select-Object -First 1
                if ( $namespace.Name )
                {
                    Write-Message -Level Verbose -Message "Getting Cim class SqlService in Namespace $($namespace.Name) on $Computer via CIM (WSMan)"
                    try
                    {
                        Get-CimInstance -ComputerName $Computer -Namespace $("root\Microsoft\SQLServer\" + $namespace.Name) -Query "SELECT * FROM SqlService WHERE SQLServiceType $TypeClause" -ErrorAction SilentlyContinue |
                        ForEach-Object {
                            [PSCustomObject]@{
                                ComputerName = $_.HostName
                                ServiceName = $_.ServiceName
                                DisplayName = $_.DisplayName
                                StartName = $_.StartName
                                ServiceType = switch($_.SQLServiceType){1 {'Database Engine'} 2 {'SQL Agent'} 3 {'Full Text Search'} 4 {'SSIS'} 5 {'SSAS'} 6 {'SSRS'} 7 {'SQL Browser'} 8 {'Unknown'} 9 {'FullTextFilter Daemon Launcher'}}
                                State = switch($_.State){ 1 {'Stopped'} 2 {'Start Pending'}  3 {'Stop Pending' } 4 {'Running'}}
                                StartMode = switch($_.StartMode){ 1 {'Unknown'} 2 {'Automatic'}  3 {'Manual' } 4 {'Disabled'}}
                                }
                            }
                     }
                     catch
                     {
                        Write-Message -Level Warning -Message "No Sql Services found on $Computer via CIM (WSMan)"
                     }
                }
                else
                {
                  Write-Message -Level Verbose -Message "Getting computer information from $Computer via CIMsession (DCOM)"
                  $sessionoption = New-CimSessionOption -Protocol DCOM
                  $CIMsession = New-CimSession -ComputerName $Computer -SessionOption $sessionoption -ErrorAction SilentlyContinue -Credential $Credential
                  if ( $CIMSession )
                  {
                    Write-Message -Level Verbose -Message "Get ComputerManagement Namespace in CIMsession on $Computer with protocol DCom."
                    $namespace = Get-CimInstance -CimSession $CIMsession -NameSpace root\Microsoft\SQLServer -ClassName "__NAMESPACE" -Filter "Name Like 'ComputerManagement%'" -ErrorAction SilentlyContinue |
                    Where-Object {(Get-CimInstance -CimSession $CIMsession -Namespace $("root\Microsoft\SQLServer\" + $_.Name) -Query "SELECT * FROM SqlService" -ErrorAction SilentlyContinue).count -gt 0} |
                    Sort-Object Name -Descending | Select-Object -First 1
                  }
                  else
                  {
                    Write-Message -Level Warning -Message "can't create CIMsession via DCom on $Computer"
                    continue
                  }
                  if ( $namespace.Name )
                  {
                      Write-Message -Level Verbose -Message "Getting Cim class SqlService in Namespace $($namespace.Name) on $Computer via CIM (DCOM)"
                      try
                      {
                          Get-CimInstance -CimSession $CIMsession -Namespace $("root\Microsoft\SQLServer\" + $namespace.Name) -Query "SELECT * FROM SqlService WHERE SQLServiceType $TypeClause" -ErrorAction SilentlyContinue |
                          ForEach-Object {
                              [PSCustomObject]@{
                                  ComputerName = $_.HostName
                                  ServiceName = $_.ServiceName
                                  DisplayName = $_.DisplayName
                                  StartName = $_.StartName
                                  ServiceType = switch($_.SQLServiceType){1 {'Database Engine'} 2 {'SQL Agent'} 3 {'Full Text Search'} 4 {'SSIS'} 5 {'SSAS'} 6 {'SSRS'} 7 {'SQL Browser'} 8 {'Unknown'} 9 {'FullTextFilter Daemon Launcher'}}
                                  State = switch($_.State){ 1 {'Stopped'} 2 {'Start Pending'}  3 {'Stop Pending' } 4 {'Running'}}
                                  StartMode = switch($_.StartMode){ 1 {'Unknown'} 2 {'Automatic'}  3 {'Manual' } 4 {'Disabled'}}
                                  }
                           }
                        }
                        catch
                        {
                          Write-Message -Level Warning -Message "No Sql Services found on $Computer via CIM (DCOM)"
                        }
                    if ( $CIMsession ) { Remove-CimSession $CIMsession }
                  }
                  else
                  {
                  Write-Message -Level Warning -Message "No ComputerManagement Namespace on $Computer. Please note that this function is available from SQL 2005 up."
                  }
                }
            }
            else
            {
                Write-Message -Level Warning -Message "Failed to connect to $Computer"
            }
        }
    }
}

Output of above PowerShell Function looks like below with Format-Table :

Output of above PowerShell Function looks like below with Out-Gridview :

Sql Services State Report with Colour Coding in Html file

I tried to look out in google regarding Sql Services State Report with Colour Coding where if the instance State is Running then it should indicate GREEN and if the instance state is Stopped then it should indicate with RED .

Today I have got the script by looking it into multiple sites and combined those scripts into one and finally got the output which is needed and it helps me to identify easily which instance is Running or Stopped.

Thanks for the original script writers for the script lines in PowerShell which makes DBA’s life easier , Here using CSS code for colour coding ,CIM Cmdlets to get the all Sql Services State then converting to HTML code in a table and it is stored in a Variable. The reports will be gathered and combined into a Single HTML Report ,finally the output of the report is generated to an HTML file which will be stored in physical Location from the Directory where we are executing.

Find the Script below :

#CSS code
$header = @"
<style>

    h1 {

        font-family: Arial, Helvetica, sans-serif;
        color: #e68a00;
        font-size: 28px;

    }

    
    h2 {

        font-family: Arial, Helvetica, sans-serif;
        color: #000099;
        font-size: 16px;

    }

    
    
   table {
		font-size: 12px;
		border: 0px; 
		font-family: Arial, Helvetica, sans-serif;
	} 
	
    td {
		padding: 4px;
		margin: 0px;
		border: 0;
	}
	
    th {
        background: #395870;
        background: linear-gradient(#49708f, #293f50);
        color: #fff;
        font-size: 11px;
        text-transform: uppercase;
        padding: 10px 15px;
        vertical-align: middle;
	}

    tbody tr:nth-child(even) {
        background: #f0f0f2;
    }
    


    #CreationDate {

        font-family: Arial, Helvetica, sans-serif;
        color: #ff3300;
        font-size: 12px;

    }



    .StopStatus {

        color: #ff0000;
    }
    
  
    .RunningStatus {

        color: #008000;
    }




</style>
"@

#The command below will get the name of the computer
$ComputerName = "<h1>Computer name: $env:computername</h1>"

#The command below will get SQL services information, convert the result to HTML code as table and store it to a variable
$ServicesInfo = Get-CimInstance -ComputerName DESKTOP-02JIB76 -ClassName Win32_Service -Filter "Name like '%sql%'"  |ConvertTo-Html -Property Name,DisplayName,State -Fragment -PreContent "<h2>Services Information</h2>"
$ServicesInfo = $ServicesInfo -replace '<td>Running</td>','<td class="RunningStatus">Running</td>'
$ServicesInfo = $ServicesInfo -replace '<td>Stopped</td>','<td class="StopStatus">Stopped</td>'

  
#The command below will combine all the information gathered into a single HTML report
$Report = ConvertTo-HTML -Body "$ComputerName $ServicesInfo" -Head $header -Title "Computer Information Report" -PostContent "<p id='CreationDate'>Creation Date: $(Get-Date)</p>"

#The command below will generate the report to an HTML file
$Report | Out-File .\sql-Services-Status-Report.html