PowerShell and MongoDB

I was exploring MongoDB last weekend and was a bit skeptical at first. The relational model (I used to teach it) is so ingrained into my way of thinking. Like the florescent bulbs in my garage during the Winter, the light slowly started to brighten. I can now see a lot of uses for a schema free database (especially as a tool for prototyping). After playing around with the JavaScript interface, I decided to see what PowerShell could do with it.

Luckily, there is a driver available for download.

Assuming you have MongoDB, the C# driver and PowerShell installed, you can play around with the following code:

# Add a reference to our dll        
Add-Type -Path 'C:\Program Files (x86)\MongoDB\CSharpDriver 1.3.1\MongoDB.Driver.dll'        
    
# Name our test db (not actually created until we insert)        
$db = [MongoDB.Driver.MongoDatabase]::Create('mongodb://localhost/PowerShellMongoTest');        
    
# Name or test collection        
$coll = $database["Stuff"]        
    
# Define a couple list         
$languages = @("C#","Haskell","PowerShell","Python")        
$beers = @("Honkers Ale","Stella","Summer Shandy","Yuengling")        
    
# Define our document        
$doc = @{FirstName="Wes"; LastName="Stahler"; Languages=$languages; Beers=$beers}        
$collection.Insert($doc)        
$info = $collection.FindAll()        
$info | Format-Table -AutoSize        
    
#$collection.RemoveAll()

This yields:

Name       Value
----       -----
_id        4f42b6798359da1e7ce51bfb
Beers      {Honkers Ale, Stella, Summer Shandy, Yuengling}
LastName   Stahler
Languages  {C#, Haskell, PowerShell, Python}
FirstName  Wes

I will be experimenting more with MongoDB.
Share your insights with me if you decide to explore it as well.

Enjoy!

Calling vbScript via PowerShell

Following is an example of how to call a vbScript from PowerShell. I recently had to do something similar to this for a Postini SafeSender list conversion to Exchange 2007/AD.

First the vbScript:

Saved as CallFromPowerShell.vbs

Option Explicit

Dim strComputer, objWMI, OS

strComputer = WSH.Arguments(0)

On Error Resume Next

Set objWMI=GetObject("winmgmts://" & strComputer).InstancesOf("win32_operatingsystem")

If objWMI is nothing Then

WScript.Echo "Unable to connect to " & strComputer

Else

For Each OS In objWMI

wscript.Echo OS.Caption

Next

end If

To call this from PowerShell:

$computers = 'fatbeard-vp01','fatbeard-vp02','fatbeard-vp03'          
$computers |          
foreach {"{0,20}`t{1}" -f $_,$(cscript.exe //nologo c:\temp\callfrompowershell.vbs $_) }

Yields...

fatbeard-vp01 Microsoft Windows 7 Enterprise
fatbeard-vp02   Microsoft(R) Windows(R) Server 2003, Enterprise Edition
fatbeard-vp03   Unable to connect to fatbeard-vp03

Project Euler #89

This one was fairly easy to do with PowerShell.
Problem 89 - " Develop a method to express Roman numerals in minimal form. "

$pre = $post = 0      
foreach ($line in Get-Content C:\temp\roman.txt) {      
$pre += $line.Length      
$line = $line -replace("DCCCC","CM")      
$line = $line -replace("LXXXX","XC")      
$line = $line -replace("VIIII","IX")      
$line = $line -replace("IIII","IV")      
$line = $line -replace("XXXX","XL")      
$line = $line -replace("CCCC","CD")      
$post += $line.Length      
}      
$pre-$post

Enjoy!

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!