Checking Error table with Powershell

One of our main web applications writes audit and error messages to the SQL database.  I was looking for a faster way to get a quick overview of the last few messages written to the table.  I could save a sql script, but seriously, where's the fun in that?  And besides, it would require me opening up SQL Query Analyzer. However, I do keep a Powershell window open at all times. 

Speaking of Query Analyzer, I had a significant breakthrough to a new level of keyboard zen with it last week.  More on that later. 

Onto the script:

   1:  $count = 50

   2:  if ($args.length -gt 0) { $count = $args[0] }

   3:   

   4:  $conn = new-Object System.Data.SqlClient.SqlConnection

   5:  $conn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MySqlServer"

   6:  $conn.Open()

   7:   

   8:  $cmd = new-Object System.Data.SqlClient.SqlCommand

   9:  $cmd.CommandText = "SELECT TOP $count * FROM tblError ORDER BY ErrorID DESC"

  10:  $cmd.Connection = $conn

  11:   

  12:  $da = new-Object System.Data.SqlClient.SqlDataAdapter($cmd)

  13:  $ds = new-Object System.Data.DataSet "Errors"

  14:  $da.Fill($ds) | out-Null

  15:  $conn.Close()

  16:   

  17:  $ds.Tables[0].Rows | Format-Table -property ErrorID, errorTime, ErrorMessage -wrap -autosize

  18:   

  19:  rv count, conn, cmd, da

The first thing to do is to create a count variable that will be used to determine how many records to pull from the db.  It defaults to 50, but that can be overwritten in line 2 if you pass in a parameter to the script.  Lines 4-15, should look familiar.  They basically use ADO.NET objects to get the records from the db and place them in a DataSet.  Line 17 is responsible for displaying the results in the console.  I use the Format-Table cmdlet to filter out some of the fields and to make them look pretty by autsizing them and causing the cells to wrap their text.

In line 19, a bunch of variables are removed.  Normally, you wouldn't have to worry with this step as the variables will go out of scope as soon as the script finishes.  I did this, incase I ran the script in global scope.  You can accomplish this by inserting a ". " at the beginning of the command line.  In which case all of the variables would remain even after the script is complete.  In my case, I'm removing all the variables except the dataset.  That way, I can continue to manipulate the object and if I want to refresh it, I just re-run the script.

Wargames Influenced Powershell Hack

As a child, I love the movie WarGames.  Looking back though, it does seem kind of corny and maybe even improbable.  No matter, I still think its a great movie.  The movie introduces concepts that we still deal with today.  Things like system security, artificial intelligence and the concept of futility.  You prabably know that I love lists, so here are my favorite things about the movie.

The five greatest things about WarGames the movie:

5. That scene where Matthew Broderick phreaked a pay phone by sticking a pull tab from a soda can in the receiver.  Through the eyes of a kid, this was awesome.

4. The extensive use of the acoustically coupled modem to hack the Government's most secure network.

3. The 8 inch floppy drive.  Which at it's prime could hold 1200 kb.

2. Barry Corbin.

1. The cool terminal that would talk to you, a.k.a. Joshua.

Its the terminal that inspired my personallized use of this hack.  Below is an excerpt from my Powershell profile.  You may recognize some of the code from a previous post.

function say($script)
{
    $v = New-Object -ComObject "SAPI.spvoice"
    $r = $v.Speak($script)
    rv v
}

#Say Hello
$Greeting = "Hello " + $env:Username + ". Shall we play a game?"
write $Greeting
say $Greeting
rv Greeting

The first thing I do is create a function named "say".  Inside the function I create a instance of the SAPI.spvoice COM object.  SAPI is the Text to Speech API that is installed with Windows XP by default.  You can administer it by going into your control panel and opening the Speech option.  The spvoice object allows you to pass in some text and it will convert it into audible speech and send it out to your speakers.  So the "say" function takes some text and speaks it.  Note that I took the time to use "rv" to remove the variable.  Don't need to hold on to that COM object longer than I need to.

Next you see where I get the user name of the person logged on and print out a greeting right before I speak it.  I've seen some articles on using this technology in ASP.NET applications.  Think of the fun you could have.