Powershell - Collecting and Reporting on SQL Instances

Generate a list of every SQL Instance on your Servers

To be successful as a Systems Administrator, you need to be able to collect information about your environment. This little guide will show you a simple and effective way to collect and report on every SQL Instance that is installed on a server in your domain.

Overview

I like to write functions and automated collection scripts, so I can have up to date reports. Sometimes it makes more sense to take someone else's code if for no reason other than saving time and effort. 

I have been using Boe Prox's useful Get-SQLInstance for several years and thought I would share how I use it. Get-SQLInstance supports SQL versions 2000 - 2016.

Prereqs

• You need an account with Administrative permissions on every server you want to run the script against
• The account must have the ability to query AD for server names
• Download Get-SQLInstance from https://gallery.technet.microsoft.com/scriptcenter/Get-SQLInstance-9a3245a0

• Create a script folder and a report folder
• Examples would be c:\scripts and c:\reports
• Place the Get-SQLInstance in the scripts folder
• It can be a great strategy to keep your scripts on a file share, so they can be accessed from any machine you wish.
• Open Powershell or Powershell ISE

AD Domain

• Using Powershell ISE, create a new .ps1 named Report-SqlInstances.ps1 and place it in your scripts folder
• Paste the following code into the editor and save

My apologies for the interesting color coding.

# Load the Get-SQLInstance cmdlet
. C:\scripts\Get-SQLInstance.ps1

# Create a list of all server names on the AD domain
$servers = Get-ADComputer -filter 'OperatingSystem -like "*server*"'

# Count the total number of servers to scan for SQL
$count = $servers.count
$i = 1

# Create an object to hold all our SQL data that will be returned
$output = @()

foreach ($server in $servers){

    # Output the server name and the count
    Write-Host "[$i : $count] $($server.Name)"

    # Ping the server once prior to attempting to connect
    # This speeds up the script a bit
    if ((ping $server.name -n 1) -match 'reply'){
        
        $data = Get-SQLInstance -Computername $server.Name
        
        # If any SQL data was found, add it to the output object
        if ($data){
            $output += $data
        }
    }

    else {
        Write-Host "   No Response" -ForegroundColor Yellow
    }

    # Increment the counter
    $i++
}

# Export the list to a CSV file
$output | Export-CSV -Path c:\reports\sqllist.csv -NoTypeInformation

Stand Alone Servers

• To report on a single server, run this from the console

Get-SQLInstance -ComputerName "YOUR SERVERNAME" | Export-CSV -Path c:\reports\sqlinfo.csv -NoTypeInformation

Potential Improvements

There are some things I typically like to add to any report script. I am giving a list here to give you an idea of what you might like to add. 

Performance

• Job concurrency using Start-Job

Usability

• Use an input file such as a .TXT or .CSV to gather server names to process
• Automatic creation of Excel file
• Email report in HTML format
• Writing data to a reporting DB such as Microsoft SQL Server
• Logging to Window Events or a log file
• This can include information, warning, and errors