Thursday, August 30, 2007

Formatting through the -f parameter

Formatting in PowerShell is typically achieved through the use of .NET's format strings. You might have seen cryptic statements like:

"{0,-8:P1}" -f 1.75

The above format can be explained as:

"{argument index[,alignment][:formatString zeros]}"

Note that both the alignment and the formatString are optional.

argument index is used when formatting more than one argument.

##### Argument Index
"{0,8}{1,10}" -f "John", "Doe"        # " John Doe"
"{0,8}{1,10}" -f "Albert", "Smith"    # " Albert Smith"

alignment indicates the minimal length of the string.
Positive valus are right justified and padded with spaces on the left if the string size is shorter than the value.

Negative values are left justified and padded with spaces on the right if the string size is shorter than the value.

##### Alignment

"{0,10}" -f "orange"     # " orange"
"{0,-10}" -f "orange"    # "orange "

format string is used mainly for numeric formatting.

##### C - Currency, the number represents the number of trailing zeroes
"{0:C3}" -f 25        # "$25.000"
"{0:C3}" -f -25        # "($25.000)"

##### D - Decimal, the number represents the number of leading zeroes
"{0:D5}" -f 25        # "00025"
"{0:D5}" -f -25        # "-00025"

##### E - Exponential, the number represents the number of trailing zeroes
"{0:E5}" -f 25        # "2.50000E+E001"
"{0:E5}" -f -25        # "-2.50000E+E001"

##### N - Number, the number represents the number of trailing zeroes
"{0:N5}" -f 25        # "25.00000"
"{0:N5}" -f -25        # "-25.00000"

##### P - Percentage, the number represents the number of leading zeroes
"{0:P3}" -f 25        # "2,500.000%"
"{0:P3}" -f -25        # "-2,500.000%"

##### X - Hexadecimal, the number represents the number of leading zeroes
"{0:X5}" -f 25        # "00019"
"{0:X5}" -f -25        # "FFFFFFE7"


Here are a few examples. Try them yourself.

"{0,9:P2}{1,20}" -f 0.09,"Standard loan"
"{0,9:P2}{1,20}" -f 0.25,"High interest loan"
"{0,9:P2}{1,20}" -f 3.5,"Mafia loan"

"{0,10:N3}{1,10:N3}{2,10:N3}{3,10:N3}" -f 0.2, 0.3, 0.45, 0.91
"{0,10:N3}{1,10:N3}{2,10:N3}{3,10:N3}" -f 1.1, 3, 2.5, 91.1
"{0,10:N3}{1,10:N3}{2,10:N3}{3,10:N3}" -f 25, 300, 211.5, 450

Wednesday, August 29, 2007

Is my drive fragmented?

Marco Shaw commented on my post ragarding fragmentation of files. Unfortunately WMI doesn't expose the fragmentation of specific files (at least I can't find it). But Marco's approach is useful in many situations.

Here is a small example on how to retrieve and display the fragmentation staus from a series of servers:

$allservers = "server1.", "server2"

Write-Host -foregroundcolor Yellow "Server         Drive Label         ClusterSize AverageFragmentsPerFile"
Write-Host -foregroundcolor Yellow "-------------- ----- ------------ ----------- -----------------------"

foreach ($server in $allservers) {
    $allvolumes = Get-Wmiobject -class Win32_Volume -Computername $server
    foreach ($volume in $allvolumes) {
        if ($volume.drivetype -eq 3) {
            $output = "{0,-15} {1,-6} {2,-15} {3,8} {4,15}" -f $server, $volume.DriveLetter, $volume.Label, $volume.DefragAnalysis().DefragAnalysis.ClusterSize, $volume.DefragAnalysis().DefragAnalysis.AverageFragmentsPerFile
            Write-Host -foregroundcolor White $output
        }
    }
}

Wednesday, August 22, 2007

A few nice variables

Obtaining the name of the current script: $myInvocation.MyCommand.Name
Path to Powershell executable: $PSHome
Get environment variables: $env:username or $env:logonserver
List all available environment variables: dir env: (note that no $ is included)
Show last commandline entered: $line
Current Path: $pwd

Thursday, August 09, 2007

Reading directly from SQL Server

A small example of reading a value from an SQL Server. In this case the maximum memory allowed for the SQL Server Instance.

$adOpenStatic = 3

$adLockOptimistic = 3
$timeout = 3

function GetMaxMemory {
    ## returns single string
    ## returns -1 if memory is unlimited
    ## returns -2 if no connection to the desired DB can be obtained
    ## arg0 = datasource (eg. server1\instance01)
    $objConnection = New-Object -comobject ADODB.Connection
    $objRecordset = New-Object -comobject ADODB.Recordset
    $query = "SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544"
    $objConnection.Set_ConnectionString("Provider=SQLOLEDB; Data Source=" + $args[0] + "; Initial Catalog=master; Integrated Security=SSPI")
    $objConnection.Set_ConnectionTimeout($timeout)
    $objConnection.Open()
    if ($objConnection.State -eq "1") {
        $objRecordset.Open($query, $objConnection, $adOpenStatic, $adLockOptimistic)
            $objRecordset.MoveFirst()
            $temp0 = ($objRecordset.Fields.Item("value").Value)
            if ($temp0 -eq 2147483647) {
                $temp0 = -1
            } else {
                $temp0 = $temp0 * 1mb
            }
        [long]$temp1 = $temp0
        Write-Output $temp1
        $objRecordset.Close()
        $objConnection.Close()
    } else {
        Write-output -2
    }
}

Usage:

GetMaxMemory servername\isntancename