Archive

Archive for the ‘Windows 7’ Category

Mashing CSVs around using PowerShell

February 15, 2012 2 comments

Since I spend most of my day in the console, PowerShell also serves as my ‘Excel’. So, continuing my recent trend of PowerShell related posts, let’s dig into a quick and easy way to parse up CSV files (or most any type of file) by creating objects!

We, of course, need a few rows of example data. Let’s use this pseudo student roster.

Example data:

Student,Code,Product,IUID,TSSOC,Date
123456,e11234,Reading,jsmith,0:18,1/4/2012
123456,e11234,Reading,jsmith,1:04,1/4/2012
123456,e11234,Reading,jsmith,0:27,1/5/2012
123456,e11234,Reading,jsmith,0:19,1/7/2012
123456,e11235,Math,jsmith,0:14,1/7/2012

Now, for reporting, I want my ‘Minutes’ to be a calculation of the TSSOC column (hours:minutes). Easy, we have PowerShell–it can split AND multiple!

The code:

Begin by creating an empty array to hold our output, importing our data into the ‘pipe’, and opening up an iteration (for each) function. The final $out is our return value–calling our array so we can see our results.

$out = @()
import-csv data_example.csv |
   % {

   }
$out

Next, let’s add in our logic to split out the hours and minutes. We have full access to the .NET string methods in PowerShell, which includes .Split(). .Split() returns an array, so since we have HH:MM, our first number is our hours and our second number is our minutes. Hours then need to be multiplied by 60 to return the “minutes per hour.”

You’ll also notice the [int] casting–this ensures we can properly multiply–give it a whirl without and you’ll get 60 0’s or 1’s back (it multiples the string).

$out = @()
import-csv data_example.csv |
   % {
	$hours = [int]$_.TSSOC.Split(':')[0] * 60
	$minutes = [int]$_.TSSOC.Split(':')[1]
   }
$out

The next step is to create a new object to contain our return values. We can use the new PowerShell v2.0 syntax to create a quick hashtable of our properties and values. Once we have our item, add it to our $out array.

$out = @()
import-csv data_example.csv |
   % {
	$hours = [int]$_.TSSOC.Split(':')[0] * 60
	$minutes = [int]$_.TSSOC.Split(':')[1]
        $item = new-object PSObject -Property @{
			Date=$_.Date;
			Minutes=($hours + $minutes);
			UserId=$_.IUID;
			StudentId=$_.Student;
			Code=$_.Code;
			Course=$_.Product
		}
	$out = $out + $item
   }

With that, we’re done, we can pipe it to an orderby for a bit of sorting, grouping, table formatting, or even export it BACK out as another CSV.

$out = @()
import-csv data_example.csv |
   % {
	$hours = [int]$_.TSSOC.Split(':')[0] * 60
	$minutes = [int]$_.TSSOC.Split(':')[1]
        $item = new-object PSObject -Property @{
			Date=$_.Date;
			Minutes=($hours + $minutes);
			UserId=$_.IUID;
			StudentId=$_.Student;
			Code=$_.Code;
			Course=$_.Product
		}
	$out = $out + $item
   } | sortby Date, Code
$out | ft -a

Quick and dirty CSV manipulation–all without opening anything but the command prompt!

UPDATE: Matt has an excellent point in the comments below. PowerShell isn’t the ‘golden hammer’ for every task and finding the right tool for the job. We’re a mixed environment (Windows, Solaris, RHEL, Ubuntu), so PowerShell only applies to our Windows boxes. However, as a .net developer, I spend 80-90% of my time on those Windows boxes. So let’s say it’s a silver hammer. 🙂

Now, the code in this post looks pretty long–and hopping back and forth between notepad, the CLI, and your CSV is tiresome. I bounce back and forth between the CLI and notepad2 with the ‘ed’ and ‘ex’ functions (these commands are ‘borrowed’ from Oracle PL/SQL). More information here.

So how would I type this if my boss ran into my cube with a CSV and needed a count of Minutes?

$out=@();Import-Csv data_example.csv | % { $out += (new-object psobject -prop @{ Date=$_.Date;Minutes=[int]$_.TSSOC.Split(':')[1]+([int]$_.TSSOC.Split(':')[0]*60);UserId=$_.IUID;StudentId=$_.Student;Code=$_.Code;Course=$_.Product }) }; $out | ft -a

Now, that’s quicker to type, but a LOT harder to explain. 😉 I’m sure this can be simplified down–any suggestions? If you could do automatic/implied property names, that’d REALLY cut it down.

Posting to Campfire using PowerShell (and curl)

January 16, 2012 Comments off

I have a few tasks that kick off nightly that I wanted to post status updates into our team’s Campfire room. Thankfully, 37signals Campfire has an amazing API.  With that knowledge, time to create a quick PowerShell function!

NOTE: I use curl for this. The Linux/Unix folks likely know curl, however, I’m sure the Windows folks have funny looks on their faces. You can grab the latest curl here for Windows (the Win32 or Win64 generic versions are fine).

The full code for this function is available via gist.

I pass two parameters: the room number (though this could be tweaked to be optional if you only have one room) and the message to post.

param (
 [string]$RoomNumber = (Read-Host "The room to post to (default: 123456) "),
 [string]$Message = (Read-Host "The message to send ")
)
$defaultRoom = "123456"
if ($RoomNumber -eq "") {
 $RoomNumber = $defaultRoom
}

There are two baked-in variables, the authentication token for your posting user (we created a ‘robot’ account that we use) and the YOURDOMAIN prefix for Campfire.

$authToken = "YOUR AUTH TOKEN"
$postUrl = "https://YOURDOMAIN.campfirenow.com/room/{0}/speak.json" -f $RoomNumber

The rest is simply using curl to HTTP POST a bit of JSON back up to the web service. If you’re not familiar with the JSON data format, give this a quick read. The best way I can sum up JSON is that it’s XML objects for the web with less wrist-cutting. 🙂

$data = "`"{'message':{'body':'$message'}}`""

$command = "curl -i --user {0}:X -H 'Content-Type: application/json' --data {1} {2}" 
     -f $authToken, $data, $postUrl

$result = Invoke-Expression ($command)

if ($result[0].EndsWith("Created") -ne $true) {
	Write-Host "Error!" -foregroundcolor red
	$result
}
else {
	Write-Host "Success!" -foregroundcolor green
}
Running SendTo-CampFire

Running SendTo-Campfire with Feedback

Indeed, there be success!

Success!

Success!

It’s important to remember that PowerShell IS extremely powerful, but can become even more powerful coupled with other available tools–even the web itself!

Changing Default Printers by Network Subnet

January 13, 2012 Comments off

Windows 7 includes a pretty handy feature for mobile devices called location-aware printing. The feature itself is pretty cool and great if you’re moving between two distinct networks (home and work, for example). However, if you’re moving within the SAME network–and the SAME wireless SSID, it doesn’t register a difference. LAP doesn’t pay attention to your IP address, just the SSID you’re connected to.

In our organization, and most large corporations, wireless access points have the same name/credentials so that users can move seamlessly through the enterprise. How can we address location-based printing then?

One of my peers recently moved into a position where they are constantly between two buildings multiple times per day and frequently forgetting to reset their default printer.

Here’s how I helped her out using a bit of PowerShell.

For the full code, check out this gist.

Set-PrinterByLocation in action!

Set-PrinterByLocation in action!

To begin, we need to specify our IP subnets and the printers associated to them. As this gets bigger (say 4-5 sites), it’d be easier to toss this into a separate file as a key-value pair and import it.

$homeNet = "10.1.4.*", "OfficePrinter"
$remoteNet = "10.1.6.*", "W382_HP_Printer"

Next, let’s grab all of the IP addresses currently active on our computer. Since we could have both wireless and wired plugged in, this returns an array.

$ipAddress = @()
$ipAddress = gwmi win32_NetworkAdapterConfiguration |
	? { $_.IPEnabled -eq $true } |
	% { $_.IPAddress } |
	% { [IPAddress]$_ } |
	? { $_.AddressFamily -eq 'internetwork'  } |
	% { $_.IPAddressToString }

Write-Host -fore cyan "Your current network is $ipAddress."

Our last step is to switch (using the awesome -wildcard flag since we’re using wildcards ‘*’ in our subnets) based on the returned IPs. The Set-DefaultPrinter function is a tweaked version of this code from The Scripting Guy.

function Set-DefaultPrinter([string]$printerPath) {
	$printers = gwmi -class Win32_Printer -computer .
	Write-Host -fore cyan "Default Printer: $printerPath"
	$dp = $printers | ? { $_.deviceID -match $printerPath }
	$dp.SetDefaultPrinter() | Out-Null
}

switch -wildcard ($ipAddress) {
	$homeNet[0] { Set-DefaultPrinter $homeNet[1] }
	$remoteNet[0] { Set-DefaultPrinter $remoteNet[1] }
	default { Set-DefaultPrinter $homeNet[1] }

The full source code (and a constantly updated version available from gist).

$homeNet = "10.1.4.*", "OfficePrinter"
$remoteNet = "10.1.6.*", "W382_HP_Printer"

function Set-DefaultPrinter([string]$printerPath) {
	$printers = gwmi -class Win32_Printer -computer .
	Write-Host -fore cyan "Default Printer: $printerPath"
	$dp = $printers | ? { $_.deviceID -match $printerPath }
	$dp.SetDefaultPrinter() | Out-Null
}

$ipAddress = @()
$ipAddress = gwmi win32_NetworkAdapterConfiguration |
	? { $_.IPEnabled -eq $true } |
	% { $_.IPAddress } |
	% { [IPAddress]$_ } |
	? { $_.AddressFamily -eq 'internetwork'  } |
	% { $_.IPAddressToString }

Write-Host -fore cyan "Your current network is $ipAddress."

switch -wildcard ($ipAddress) {
	$homeNet[0] { Set-DefaultPrinter $homeNet[1] }
	$remoteNet[0] { Set-DefaultPrinter $remoteNet[1] }
	default { Set-DefaultPrinter $homeNet[1] }
}

Setting up a NuGet PowerShell Profile

November 15, 2010 Comments off

While NuGet alone is a pretty spectacular package management system, one of the hottest features involves the underlying PowerShell Package Console (we’ll call it PM from here out).  It may look fancy and have a funky PM> prompt, but it’s still PowerShell.

Considering I use psake and live in PowerShell most of the day, I wanted to do a bit of customizing.  Out of the box… urr… package, PM is barebones PowerShell.

Screenshot - 11_15_2010 , 8_31_25 PM

Well, that’s pretty boring!

The question is, how would I customize things?

The answer? Setting up a profile!

Step 1: Finding the NuGet Profile

Just like a normal PowerShell installation, the $profile variable exists in PM.

image

Okay, that was easy.  If you try to edit the file, however, it’s empty. You can either use the touch command to create an empty file, then edit it with Notepad, or simply run Notepad with the $profile path–it’ll ask you to create the file. 🙂

For an example, we’ll just pipe a bit of text into our profile and see what happens.

image

Now, close Visual Studio (PM only seems to load the profile when Visual Studio first starts) and relaunch it.  PM should now welcome you!

image

 

Step 2: Customize, customize, customize!

Now we’re ready to add variables, setup custom paths and scripts, add some git-tastic support, and add modules (like psake).  There are quite a few posts on the blog about customizing PowerShell’s environment, click here to check them out.

Remember: Since we’re using a separate PowerShell profile, be frugal with your commands and keep them "development centric".  For example, I don’t load HyperV modules, Active Directory management commands, and other "non-Solution" things into the PM. Visual Studio is slow enough–don’t bog it down. :) 

This is also a great opportunity to trim your profile down and break it into modular pieces (whether that be scripts or modules).  Keep those profiles as DRY as possible.

 

A few caveats…

There do seem to be a few caveats while in the PM environment:

1. Execution, whether it’s an actual executable, a script, or piping something to more, seems to be tossed into another process, executed, and then the results returned to the PM console window. Not terrible, but something to be aware of if it seems like it’s not doing anything.

2. I can’t find a good way to manipulate the boring PM> prompt. It seems that $Host.UI is pretty much locked down. I’m hopeful that will change with further releases because not KNOWING where I am in the directory structure is PAINFUL.

PowerShell: Recreating SQL*Plus ‘ed’ Command

November 9, 2009 Comments off

Aside from PowerShell, I spend a bit of time day-to-day in Oracle’s SQL*Plus.  I’m slowly replacing my dependency on SQL*Plus with PowerShell scripts, functions, and such, but there are some core things that it’s just easier to bring up a console and hack out.

One thing I really love about SQL*Plus is the ‘ed’ command.  The ‘ed’ command dumps your last statement into a temp file, then opens it in Notepad.  Make your changes in Notepad, save, and rerun (in SQL*Plus, it’s a single slash and then Enter), and the frustration of lengthy command line editing is solved.

So, this is a work in progress—my attempt to simulate the same process in PowerShell.

Example Situation

Let’s say, for example, we have a long command we have been hacking away at, but are getting tired of arrowing back and forth on:

$count = 0; sq mydb “select id from reports” | % { $count++ }; $count

(Yes, yes, it’s not ‘very long’, but that’s why this is called an example!)

So, a simple row counter.

The ‘ed’ Command

To recreate the ‘ed’ experience, I need two things:

  • Access to the command line history via get-history.
  • A temporary file.

touch c:\temp.tmp

get-history | ?{ $_.commandline -ne “ex” } |

? { $_.commandline -ne “ed” } |

select commandline -last 1 |

%{ $_.CommandLine} > c:\temp.tmp

np c:\temp.tmp

Stepping through the code:

  1. Create a temporary file.  I used c:\temp.tmp, but you could get a bit fancier and use the systems $TEMP directory or something.
  2. Get-History.  This returns an ascending list of your command history in PowerShell by ID and CommandLine.  We want to ignore any calls to our ‘ed’ or ‘ex’ functions and grab the “last 1”—then insert it into our temp file.
  3. Finally, open up the temp file in NotePad (np is an alias on my PowerShell profile for NotePad2).

So now that I have my ed command, let’s try it out after I’ve already ran my simple row counter.

Last Command in NotePad2

Cool.  We can now make changes to this and SAVE the temp file.  The next step would be to jump back to PowerShell and execute the updated command.

The ‘ex’ Command

Since the slash has special meaning in a PowerShell window, I simply named this script ‘ex’ instead.  Ex… execute… surely I can’t forget that, right?

get-content c:\temp.tmp |

% {

$_

invoke-expression $_

}

The ‘ex’ command is pretty simple.

  1. Get the content of our temporary file from get-content.
  2. For each line (since get-content returns an array of lines), print out the line as text and then invoke (run) it using invoke-expression.

Invoke-Expression takes a string as a command (from our text file) and returns the results.  For more information on Invoke-Expression, check out MSDN.

Wrapping It Up

Now that we have our two commands—ed and ex—we’re ready for those “oh, this is short and easy” commands that turn into multi-page monsters—without fighting tabs and arrow keys.

As I said, this is a work-in-progress.  I’ll update the post as new/better ideas come up. 🙂

PowerShell : TGIF Reminder

November 6, 2009 1 comment

Jeff Hicks’ TGIF post yesterday was a fun way to learn a bit of PowerShell (casting, working with dates, etc) and also add something valuable to your professional toolbelt—knowing when to go home. 🙂

I tweaked the date output a bit to be more human readable, but also moved it from just a function to part of my UI.  I mean, I should ALWAYS know how close I am to quittin’ time, right? Especially as we joke around our office during our ‘payless weeks’.

# Determines if today is the end of friday! (fun function)
function get-tgif {
 $now=Get-Date
 # If it’s Saturday or Sunday then Stop! It’s the weekend!
 if ([int]$now.DayOfWeek -eq 6 -or [int]$now.DayOfWeek -eq 7)
 {
  “Weekend!”
 }
 else {
  # Determine when the next quittin’ time is…
  [datetime]$TGIF=”{0:MM/dd/yyyy} 4:30:00 PM” -f `
   (($now.AddDays( 5 – [int]$now.DayOfWeek)) )
  
  # Must be Friday, but after quittin’ time, GO HOME!
  if ((get-date) -ge $TGIF) {
   “TGIF has started without you!”
  }
  else {
   # Awww, still at work–how long until
   # it’s time to go to the pub?
   $diff = $($tgif – (get-date))
   “TGIF: $($diff.Days)d $($diff.Hours)h $($diff.Minutes)m”
  }
 }
}

NOTE: My “end time” is set to 4:30PM, not 5:00PM—since that’s when I escape.  Change as necessary. 😉

The code comments explain most of it.  As you can see, I added in one more check—let me know when it’s simply the weekend.  I also removed the Write-Host calls, since I simply want to return a String from the function.  I could use the function, as necessary, with formatting, and add it to other scripts and such.  For example:

Write-Host $(get-tgif) -fore red

The next step was tapping into the $Host variable.  Since I use Console2, my PowerShell window is a tab rather than the whole window.  Console2 is aware of PowerShell’s $Host.UI methods and adheres to the changes.

To add get-tgif to my prompt’s window title:

$windowTitle = “(” + $(get-tgif) + “) “
$host.ui.rawui.WindowTitle = $windowTitle

Easy enough.  Now my window title looks like (ignore the path in there for now):

TGIF countdown in WindowTitle

But that only sets it when you log in… and I want to update it (and keep that path updated as I traverse through directories).  To do that add a function called ‘prompt’ to your PowerShell Profile.  Prompt is processed every time the “PS>” is generated and allows you a great deal of customization.  See the post here for further details on how I’ve customized my prompt to handle Git repositories.

So, move those two lines into our prompt function, and our TGIF timer now updates every time our prompt changes… keeping it pretty close to real time as you work.

function prompt {
 $windowTitle = “(” + $(get-tgif) + “) ” + $(get-location).ToString()
 $host.ui.rawui.WindowTitle = $windowTitle

[…]

}

This could be tweaked to any type of countdown.  I’m sure a few of those around the office would have fun adding retirement countdowns, etc. 😉

Happy TGIF!

Updated: PowerShell “Code-Cat” With Colors and Line Breaks

October 13, 2009 Comments off

Motivated by Jeffery Hicks’ fun with my original “code-cat” version, I decided to fill in a few of the original updates I had floating in my head.

The full code is available here via CodePaste.Net.

First off, I loved the idea of colorizing the comments in a file; however, I wanted to keep my line numbers out of the “coloring” and simply color the comments.  To do this, I had to break apart the line numbers from the actual output.

Coloring Code Cat Comments

Second, I really wanted a cleaner way to wrap lines in my code and to have those lines still lineup with everything else. 

Line Wraps

The full code is available via CodePaste.Net (click here), but let’s step through it and see how things work.

SplitString Function

You’ll notice at the top, I’ve included a custom helper function that takes a string and splits it into an array.  I use this to break up “long” strings into window-sized strings.

function splitString([string]$string, [int]$length)
{
 $lines = @();
 $stringLength = $string.Length;
 $position = 0;

 while ($position -lt $stringLength)
 {
  if (($position + $length) -lt $stringLength)
  {
   $lines += $string.substring($position, $length);
  }
  else
  {
   $lines += $string.substring($position);
  }
  $position += $length;
 }

 return $lines;
}

This method is actually in my PowerShell profile for use in numerous parsing activities.

Colors and Window Size

Unlike the original method, I’ve pulled out the colors into variables so they are easily configured.  I am also using the built-in PowerShell $Host object to determine the width of my console window.  I’ll use this later for the line breaks.

$codeColor = “Gray”;                
$counterColor = “White”;
$commentColor = “Green”;
$newLineColor = “Yellow”;

# Window size, minus the counter, spacing, and a bit extra padding.
$maxLineSize = $Host.UI.RawUI.WindowSize.Width – 12;

Parsing The Code File

There are a few important points in the $content body, here we go!

Replacing Tabs With Spaces

I found that the Substring and Length functions got really quirkly (is a tab 1 character or five, etc) when they came across several tabs (which, tabbing is pretty common in a code file, especially some SQL files).  To remedy this, I replace tabs with five spaces.  This also allows me to control the tabs a bit more, so if I wanted to ease my code output to the left, I could use three or four spaces.

# remove tabs, they’re counted as one character, not five.
$currentLine = $_.Replace(“`t”, ”    “);

Setting the Color of Comments

Using Jeffery’s example, I’m also setting my comments (for PowerShell, C#, and TSQL) to a bright color.

# Color-code comments green (PowerShell, C#, SQL)
if ($currentLine.Trim().StartsWith(“#”) -or `
    $currentLine.Trim().StartsWith(“//”) -or `
    $currentLine.Trim().StartsWith(“–“))
{
     $foregroundColor = $commentColor;
}

Separating Line Numbers From Content

I didn’t want my line numbers to show up green for comments and I wanted to treat them with a different color than the rest of the code body, so I’m taking advantage of the “-nonewline” feature of write-host. This returns the content without forcing a new line break into the result.  Very handy.

write-host (“{0:d4} | ” -f $counter) -nonewline -foregroundColor $counterColor;

Checking for Line Breaks and Handling Them

The next bit is in “beta” for the time being.  The gist is that it looks at the length of the current line vs. the maximum line size we specified in the variables. 

If the current line is longer, it breaks the line into an array of “max line size” lines.  The first line is output using our normal methods—we want our counter to show up, right?  The ‘overflow’ lines are output using a fancy arrow “\—>” and do not include a counter—simply because they are not ‘new lines’.

Of course, if our line is shorter than our window size, it is simply output.

if ($currentLine.Length -ge $maxLineSize)
{
 $splitLines = splitString $currentLine $maxLineSize;
    $countOfLines = $splitLines.Length;
 for ($i = 0; $i -lt $countOfLines; $i++)
    {
  if ($i -eq 0)
  {
   # our first line doesn’t look any different.
   write-host (“{0}” -f $splitLines[$i]) -foregroundColor $foregroundColor;
  }
  else
  {
   # next lines are using the fancy arrow.
   write-host (“\–> |   “) -nonewline -foregroundColor $newLineColor;
   write-host (“{0}`n” -f $splitLines[$i]) -nonewline -foregroundColor $foregroundColor;
  }
 }
}
else
{
 write-host (“{0}” -f $_) -foregroundColor $foregroundColor;
}

Again, the full code is available here via CodePaste.Net.

Like all things, a work in progress!