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

  • 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

Comments

Popular posts from this blog

Hacking the Sonoff Wifi Switch - Part 2

Creating Alexa Skills for IoT and Nodemcu - Part 2 AWS IoT

Hacking the Sonoff Wifi Switch - Part 3 Alexa Smart Home