Tuesday, January 24, 2012

PowerShell - Let SQL sort it out

I came across a piece of code yesterday that provided a learning opportunity. The code was a simple SQL query that returned a list of computers from a database. The code I saw, had PowerShell handling the sort after the computers were retrieved from the SQL database. While this works, it is not a best practise. In fact, it something that Don Jones has often mentioned - Filter Left, Format Right. Look below to see the performance difference of letting SQL Server handle the sort.
"Filter Left: {0} seconds" -f (Measure-Command -Expression {           
$qry = "select name from vcomputer where [IsManaged] ='1' order by name"
$Altiris = Invoke-Sqlcmd -ServerInstance SQL01 -Database Altiris -Query $qry

"Filter Right: {0} seconds" -f (Measure-Command -Expression {
$qry = "select name from vcomputer where [IsManaged] ='1'"
$Altiris = Invoke-Sqlcmd -ServerInstance SQL01 -Database Altiris -Query $qry | sort

As you can see, this is a significant difference!
Like Active Directory, let the server that is good at filtering or sorting handle the work for you.


Monday, January 23, 2012

Creating an LDIF file with PowerShell

Occasionally, I am asked to create a large batch of users for our eDirectory environment. Following is an example on how to create 500 test users (gotta love Here-Strings).
$path = "c:\temp\LDIF$(get-date -Format yyyyMMdd).txt"          
New-Item -Path $path -ItemType File -Force
Add-Content -Value "version: 1" -Path $path

100..600 | Foreach {
$value = @"

dn: cn=PSFTTest$_,ou=users,o=OSUMC
changetype: add
userPassword: P@ssw0rd
uid: PSFTTest$_
givenName: First$_
fullName: First$_ Last$_
sn: Last$_
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: Person
objectClass: Top
cn: PSFTTest$_

Add-Content -Value $value -Path $path


PowerShell - Project Euler Problem 102

I am always pleased when I can use PowerShell to solve a Project Euler problem. This one was straightforward. You are supplied with a text file containing 1000 random triangular coordinates and you need to determine how many of the triangles contain the origin (0,0). There are multiple ways to attack this, I went for he easier approach: if the area of the supplied triangle is equal to the 3 triangles created using the origin, then we know that the triangle contains the origin. I used Heron's Formula to calculate the area.

Following is the code used to find the answer.

<# Get side lengths 
$LengthAB = Length of AB = SquareRoot of (Ax - Bx)^2 + (Ay - By)^2
$LengthAC = Length of AC = SquareRoot of (Ax - Cx)^2 + (Ay - Cy)^2
$LengthBC = Length of BC = SquareRoot of (Bx - Cx)^2 + (By - Cy)^2

$s = .5($LengthAB +$LengthAC +$LengthAC)
$Area = SQRT($s($s-$LengthAB)($s-$LengthAC)($s-$LengthBC) #>
function Get-LengthOfSide {     

return [Math]::sqrt( [Math]::pow(($X[0]-$Y[0]),2) + [Math]::pow(($X[1]-$Y[1]),2))

function Get-AreaOfTriangle {
param([Array]$X,[Array]$Y, [Array]$Z)

$LengthAB = Get-LengthOfSide -X $X -Y $Y
$LengthAC = Get-LengthOfSide -X $X -Y $Z
$LengthBC = Get-LengthOfSide -X $Y -Y $Z
$s = .5*($LengthAB+$LengthAC+$LengthBC)
$Area = [Math]::sqrt( $s*($s-$LengthAB)*($s-$LengthAC)*($s-$LengthBC) )
return $Area

$path = 'C:\Users\stah06\Documents\triangles.txt'
$uri = 'http://projecteuler.net/project/triangles.txt'

# Using Invoke-WebRequest (PowerShell V3)
#Invoke-WebRequest -Uri $uri -OutFile $path

# Using System.Net.WebClient (PowerShell V2)
$web = New-Object System.Net.WebClient
$web.DownloadFile($uri, $path)

$match = 0
Get-Content $path |
foreach {
$A = $_.split(",")[0],$_.split(",")[1]
$B = $_.split(",")[2],$_.split(",")[3]
$C = $_.split(",")[4],$_.split(",")[5]
$D = 0,0

$TriangleABC = Get-AreaOfTriangle -X $A -Y $B -Z $C
$TriangleABD = Get-AreaOfTriangle -X $A -Y $B -Z $D
$TriangleACD = Get-AreaOfTriangle -X $A -Y $C -Z $D
$TriangleBCD = Get-AreaOfTriangle -X $B -Y $C -Z $D

$SumofTriangles = $TriangleABD +$TriangleACD + $TriangleBCD

if ( [math]::abs($TriangleABC -$SumofTriangles) -lt .5) {
#"{0} {1}" -f $TriangleABC, $SumofTriangles



Thursday, January 19, 2012

Will Rogers Phenomenon

Following is a example of the Will Rogers phenomenon. This discussion was a tangent from a water-cooler discussion of the Monty Hall problem (much more interesting).

The Will Rogers phenomenon occurs when the averages
of 2 groups are raised by moving one item from one
to the other.Note: Data may not be truly
representative of actual figures. #›

# Sample IQs
$Ohio = 110,105,115,120
$StateUpNorth = 90,95,85,90

# Initial State (pun intended...)
"Average Ohio IQ before move is: {0}" -f ($Ohio |
Measure-Object -Average).Average

"Average StateUpNorth IQ before move is: {0}`n" -f ($StateUpNorth |
Measure-Object -Average).Average

# Rumoured to be in the Toledo area...
$LowestOhioIQ = ($Ohio sort)[0]

# Remove from Ohio
$Ohio = @($ohio where {$_ -ne $LowestOhioIQ})

# Add to State up North (Ann Arbor area)
$StateUpNorth+= $LowestOhioIQ

# Final State
"Average Ohio IQ after move is: {0}" -f ($Ohio |
Measure-Object -Average).Average

"Average StateUpNorth IQ after move is: {0}" -f ($StateUpNorth |
Measure-Object -Average).Average


Wednesday, January 18, 2012

More Training Questions: Connect to different domain

At a recent internal PowerShell training session, I was asked how to connect to a different domain. Following are a couple ways to accomplish this (using Quest cmdlets or the ActiveDirectory Module):

# Quest cmdlets
Add-PSSnapin Quest.ActiveRoles.ADManagement
$cred = Get-Credential 'ExtDomain.Local\FatBeard'
Connect-QADService -Service ExtDomain.Local -Cred $cred


# Active Directory Module
Import-Module ActiveDirectory
New-PSDrive –Name ExtDomain
–PSProvider ActiveDirectory
–Server ExtDomain.Local
–credential (Get-Credential ‘ExtDomain.Local\FatBeard’)
–root ‘//RootDSE/’

Get-ADUser -filter *


Training Questions: Date and Numeric Formatting with PowerShell

At a recent internal training session, I was asked how to format dates in PowerShell. A little later I was asked how to format numbers, and later still I was asked if it was possible to right justify strings. Following is the example I used to demonstrate the formating capabilities:

Get-ADUser SamAccountName -Properties WhenCreated | 
Select SamAccountName,WhenCreated,
@{Name="ShortDate"; Expression={ "{0:d}" -f $_.WhenCreated} },
@{N="LongDate";E={ "{0:D}" -f $_.WhenCreated} },
@{L="FullDateShortTime";E={ "{0:f}" -f $_.WhenCreated} },
@{N="FullDateLongTime"; E={ "{0:F}" -f $_.WhenCreated} },
@{N="GeneralDateShortTime";E={ "{0:g}" -f $_.WhenCreated} },
@{N="GeneralDateLongTime"; E={ "{0:G}" -f $_.WhenCreated} },
@{N="Month";E={ "{0:M MM MMM MMMM}" -f $_.WhenCreated} },
@{N="Day"; E={ "{0:d dd ddd dddd}" -f $_.WhenCreated} },
@{N="Year"; E={ "{0:y yy yyy yyyy}" -f $_.WhenCreated} },
@{N="Hour"; E={ "{0:h hh H HH}" -f $_.WhenCreated} },
@{N="Minute"; E={ "{0:m mm}" -f $_.WhenCreated} },
@{N="Second"; E={ "{0:s ss}" -f $_.WhenCreated} },
@{N="AM/PM"; E={ "{0:t tt}" -f $_.WhenCreated} },
@{N="CustomDateTime1"; E={ "{0:M/d/yy h:m:s tt}" -f $_.WhenCreated} },
@{N="CustomDateTime2"; E={ "{0:dddd, MMMM yyyy - HH:mm:ss}" -f $_.WhenCreated} }

Get-WmiObject -Class win32_logicalDisk -Filter "DeviceID='C:'" |
Select DeviceID, FreeSpace,
@{N="FreeSpaceDecimal"; E={ "{0:d}" -f $_.FreeSpace} },
@{N="FreeSpaceScientific1"; E={ "{0:E}" -f $_.FreeSpace} },
@{N="FreeSpaceScientific2"; E={ "{0:E1}" -f $_.FreeSpace} },
@{N="FreeSpaceFixed1"; E={ "{0:F1}" -f $_.FreeSpace} },
@{N="FreeSpaceFixed2"; E={ "{0:F5}" -f $_.FreeSpace} },
@{N="FreeSpaceGeneral"; E={ "{0:G3}" -f $_.FreeSpace} },
@{N="FreeSpaceNumber"; E={ "{0:N3}" -f $_.FreeSpace} },
@{N="FreeSpacePercent"; E={ "{0:P}" -f ($_.FreeSpace/$_.Size)} },
@{N="FreeSpaceGB"; E={ "{0:N0} GB" -f ($_.FreeSpace/1GB)} }

$myString = "Yuengling"
"{0,10}" -f $myString
"{0,15}" -f $myString
"{0,20}" -f $myString