PowerShell, WMI and SQL Server

I was recently asked "how can we determine the specific SKU of an installed SQL Server - without being able to execute T-SQL?". My answer:

The following one-liner lists various advanced properties of a SQL installation:

Get-WmiObject sqlserviceadvancedproperty -namespace "root\Microsoft\SqlServer\ComputerManagement" -computername | Select-Object -Property PropertyName, PropertyNumValue, PropertyStrValue

Google Chart API & PowerShell

Have you heard of Google's "Chart API"?
No?
Well, the Google Chart API lets you dynamically generate charts. Using only a browser. But why use a browser when you can use PowerShell instead? :-)
Click here to learn more about Google Chart API.

I had been playing around with Google Chart API for some time, when I decided to create a small PowerShell script to obtain a pie chart - sort of a poor mans replacement for Microsoft Excel or PowerGadgets:

Lets start by writing a small function to download and display an image. This will be a very basic function, so the only input in the function will be an URL.

function DownloadAndShowImage ($url) {
    $localfilename = ".\chart.png"
    $webClient = new-object System.Net.WebClient
    $webClient.Headers.Add("user-agent", "PowerShell Badass Script v666")
    $Webclient.DownloadFile($url, $localfilename)
    Invoke-Item $localfilename
}

Next, we need a function to create a valid Google Chart API url. Input parameters will be two arrays (data values and text), the desired size of the chart and an option to create a 3D pie chart insted of a plain 2D one:

function simpleEncoding ($valueArray, $labelArray, $size, [switch] $chart3D) {
    $simpleEncoding = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
    if ($chart3D) {$chartType = "p3"} else {$chartType ="p"}
    $total = 0
    foreach ($value in $valueArray) {
        $total = $total + $value
    }
    for ($i = 0;$i -lt $valueArray.length;$i++) {
        $relativeValue = ($valueArray[$i] / $total)*62
        $relativeValue = [math]::round($relativeValue)
        $encodingValue = $simpleEncoding[$relativeValue]
        $chartData = $chartData + "" + $encodingValue
    }    
    $chartLabel = [string]::join("|",$labelArray)
    Write-Output "http://chart.apis.google.com/chart?cht=$chartType&chd=s:$chartdata&chs=$size&chl=$chartLabel"
}

That's it! We are now ready to create a small Chart. Let's try it out:

$values = 100,11,40,9
$text = "Hans","Jane","Rose","Simon"
$url = simpleEncoding $values $text "320x150" -Chart3D
DownloadAndShowImage $url

The above should result in your local .png viewer showing something like this:

How about something a little more advanced?
Lets try it - but first we need a small support function:

function GetProcessArray() {
    $ListOfProcs = Get-Process | Sort-Object CPU -desc | Select-Object CPU, ProcessName -First 15
    for ($i = 0;$i -lt $ListOfProcs.length ;$i++) {
        $ProcName = $ProcName + "," + $ListOfProcs[$i].ProcessName
        $ProcUsage = $ProcUsage + "," + $ListOfProcs[$i].CPU
    }
    Write-Output (($ProcName.trimStart(",")).split(","), ($ProcUsage.trimStart(",")).split(","))
}

Now we are ready to create a more detailed graph, this time of the top 15 processes counted by CPU seconds consumed.

$data = GetProcessArray
$url = simpleEncoding $data[1] $data[0] "700x350"
DownloadAndShowImage $url

The result should look like this:

DBCC check through ADO.NET/PS

Here is a small script I use to run DBCC against various databases in my environment:

$ScriptName = $myInvocation.MyCommand.Name
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
$ConnString = "Server=Servername\Instance;Integrated Security=SSPI;Database=DatabaseName;Application Name=$ScriptName"
$MasterConn = new-object ('System.Data.SqlClient.SqlConnection') $ConnString
$MasterCmd = new-object System.Data.SqlClient.SqlCommand
$MasterCmd.Connection = $MasterConn
$SqlDBCC = "DBCC CHECKDB(master) WITH TABLERESULTS"
$MasterCmd.CommandText = $SqlDBCC
$MasterConn.Open()
$Rset = $MasterCmd.ExecuteReader()
If ($Rset.HasRows -eq $true) {
    While ($Rset.Read()) {
        $line = $Rset["MessageText"]
        If ($Rset["Level"] -gt 10) {
            Write-Host $line -backgroundcolor Yellow -foregroundcolor Red
        } else {
            Write-Host $line
        }
    }
    $Rset.Close()
}
$MasterConn.Close()