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           
}).TotalSeconds           
          
"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           
}).TotalSeconds

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.

Enjoy!

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          
}          

Enjoy!

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 {     
param([Array]$X,[Array]$Y)     
  
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     
 $match++     
}     
}     

$match      

Enjoy!

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

Enjoy!

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

Get-QADUser

# 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 *

Enjoy!

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

Results:


Enjoy!