Book Review: Windows Phone 7 Plain & Simple

Overall, Windows Phone 7 Plain & Simple is a good book for people new to Windows Phone 7 and especially for people new to Smart Phones. Michael does a great job illustrating how to use the phone and get up to speed quickly. He walks quickly through the general operation, setting up the phone, and entering text in the first couple chapters. The next several chapters discuss using the most popular features of WP7: calling, e-mail, text messages, calendar, browsing the web, and using the maps/navigation. Next we learn more about music, video, taking pictures and videos, and using the Marketplace. We end with a quick session on using WP7's Office Hub for documents, spreadsheets, and OneNote. This is followed by a discussion on using the Zune software to synchronize files between your phone and your PC. (Michael doesn't mention the Mac software to do this. It's capable, but more limited.)

Having followed the progress of WP7, I found that I knew a lot of the information in this book. There were still a couple interesting bits of information that were news to me, but not many. Michael didn’t write this for people like me who have followed WP7 for some time. He wrote for people who aren’t really familiar with smartphones and especially with the changes that WP7 brings. For those people, this will be a useful read and a quick reference while they get used to their device. I plan to show this to my family members who have WP7 devices and aren't quite sure what to do with them.

The positives:

• The information is very well presented, easy to follow, and broken out into logical sections.
• Michael's casual writing is well-suited for this book.
• Readers will not feel intimidated by their new phone and are encouraged to try things out.
• Michael presents some information that you may not easily find by reading articles online.

The negatives:

• I don't see this as a book that will be a long-lasting reference book. Once the reader has used the phone for a month or so, the lessons from this book should be second-nature.
• WP7 already has two planned updates at the time of this review. A minor update in January 2011 that will reportedly enable Copy/Paste functionality and a larger one in February 2011. The errata for this book will need to be updated to take these changes into account as some information may be outdated.

Conclusion:
If you can find Windows Phone 7 Plain & Simple for a reasonable price and are new to the platform, pick it up. It's a short, but informative, read. If you've followed WP7 for a while, this is not likely the book for you as you'll know most of the information here already. You may still want to pick up a copy to share with family or friends who are new to the phone. It could save you "support" calls.

Please visit the Windows Phone 7 Plain & Simple page at O’Reilly for more information about this book.

Disclaimer: I received a free copy of this book in electronic format in return for providing an honest review. I was not compensated in any other way.

Book Review: Cooking For Geeks

(I know this isn’t SQL-related, but I think this may be a great read for people with similar interests.)

This is not a cookbook. If you’re looking for a new collection of recipes, this is not the book for you. If you like shows like Good Eats or enjoy seeing how food is prepared and served, you’ll almost certainly love Cooking for Geeks. If you like experimenting in the kitchen and knowing why food turns out the way it does, pick up this book!

As a geek, I loved Jeff’s analogy: Recipes are code. Follow a recipe as written and you generally get good results. Forget the where clause and you could have unrecoverable errors. Introduce your own changes and you could get something great or you could get something horrible that requires a lot of cleanup. Recipes may have bugs or need corrections. Perhaps there’s more than one way to the same result. Oh, and don’t forget to comment your recipe. Otherwise you might not be able to recreate something fantastic.

Each chapter of Cooking for Geeks deals with different concepts, each with their own scientific background. Common utensils, ingredients, time/temperature, baking, additives (chemicals), and even some geeky fun with hardware or unusual cooking techniques – all are included in a way that not only gives some neat recipes, but the science behind the recipes.

To me, the most interesting parts were on baking and the chemical reactions that take place as heat is applied. It was great reading exactly why food turns out with all of its various nuances. That science got me thinking about ways to tweak the outcome of various recipes I follow and was just fun to read.

You can get your own copy of Cooking for Geeks direct from O’Reilly.

Disclaimer: I received an electronic review copy of this book, though I’d likely have wanted to read and review this anyway.

SSIS, OLEDB Data Sources, and Table Variables

I ran across an interesting problem when trying to tune some SSIS data source performance issues the other day. I had noticed that we were doing a pretty large select out of a table filtered on a handful of date-related columns. We then pulled in most of the data from the table. Well, SQL Server saw this query and apparently figured that since we needed most of the columns, a table scan would be the most efficient way to get that data.  Since in real life, we only need a very small percentage of those rows, I decided to rewrite the select to pull in the PK values first, then pull in the data based on that subset.

My first attempt was to use a Temp Table. Sadly, using a temp table does not work in SSIS Data Sources, even if you put that inside a stored procedure. Disappointed, but not deterred, I decided to use a Table Variable. I quickly made the changes and tested them with some values I knew would returned quite a few results. Great – no worries.  I was still using a stored procedure at the time and used some of our standard code to start it off so had no issues.

Ultimately, I didn’t want to write a bunch of stored procedures that I would need to maintain outside of SSIS just for this particular load.  We had the code already inside of the OLE DB Sources, just not optimized as much as I’d like. I started pulling out the old queries, adding a table variable, populating it with the necessary PK values, the using that to filter out the main data set.  Save, deploy, run, see major speed increase.

Then, I looked at the actual insert/update counts. Yes, it ran much faster, but in the process, it didn’t pick up any results.  I was pretty confused because the step was successful, the code ran as expected through SSMS, there were no errors or warnings. I re-verified that running that same code got results in SSMS; it did.  I started doing some searching to see what might be happening and found this post by Dustin Ryan.

In short, it seems that if you have row counts being returned (default behavior), you return a number of rows affected when you insert the table variable, which results in a “Done in Proc” type message, which in turn tells SSIS that the data flow is done and moves on to the next step.  The workaround?  Add SET NOCOUNT ON; to the beginning of your OLEDB Source query (or make sure this is in your stored proc – we tend to put that in our procs by default).

Once again, thanks to Dustin Ryan for his post on this topic. There’s a good possibility I’d still be struggling with the root cause of this without that assistance.

Review: Windows PowerShell 2.0 Best Practices - Introduction

 

 

A while back I received a review copy of Windows PowerShell 2.0 Best Practices by Ed Wilson (blog | twitter) and the Windows PowerShell Teams at Microsoft. Having finally found the time to sit down and really digest the information within, I thought I’d do a multi-part review of the book.  Ed Wilson has compiled an excellent resource not only to show off what PowerShell 2.0 can do, but also to provide practical examples and several useful tips for best practices not only in using PowerShell, but in writing reusable and efficient code.

This book is broken down into 5 major sections:

• Introduction
• Planning
• Designing
• Testing and Deploying
• Optimizing

I want to give a quick overview of what’s contained in each chapter, though I’m not going to include the code from the book. You may be able to get it from the main page for the book, but ideally I think this book is well worth buying. You get a PDF file of the book and all of the code included in the book as well. If you plan to do a lot of PowerShell scripting, this book is well worth the money.  I will say that this book is not designed to teach PowerShell.  Ed Wilson really does expect that readers have a working knowledge of PowerShell. He builds on that knowledge to show how to write PowerShell scripts with these best practices in mind.

Chapter 1 contains an overview of PowerShell 2.0. Ed explains some of the differences between PowerShell 1.0 and 2.0, focusing on the new remoting capabilities, WMI enhancements, changes to the way some cmdlets work, and why they have changed the original behavior. He touches on some of the differences between VBScript and PowerShell and why PowerShell might be a better choice than VBScript, despite people’s familiarity with the latter. One of the key advantages of PowerShell over VBScript is that PowerShell was designed to be run from a prompt or as a script. VBScript must be run through something that calls the script.  This command-line interaction allows people to work with the script in a much more interactive way and even use native Windows commands with which people are already familiar.  Ed closes the chapter by reminding readers of the minimum requirements for PowerShell 2.0, informing readers from where they may obtain the latest version of PowerShell and suggesting where PowerShell 2.0 should be installed (pretty much everywhere).

Chapter 2 is where readers become more familiar with PowerShell 2.0 capabilities. We’ve already read about the promise of PowerShell 2.0, but now we can start to see it in action. Ed starts by introducing readers to the PowerShell 2.0 interactive command line. The first command shows users running processes inside the PowerShell environment.  Ed then shows some of the easiest cmdlets to run and remember, followed by the “most important” cmdlets: Get-Help, Get-Command, and Get-Member.  I agree that these three cmdlets are key. You can always find out more about what commands are available, how to run them, and what properties are available.  Ed then lists several useful cmdlets to enhance filtering, grouping, sorting, and such.  Chapter 2 ends with a good introduction to WMI, Remoting, Services, and the Event Log from a PowerShell perspective.

Chapter 3 concentrates on Active Directory. Ed reminds us that what we typically call “AD” is really a legacy term now and is actually “Active Directory Service Interfaces” or ADSI. We tend to think of AD in terms of users and domains, but it contains much more now than it used to. Ed jumps right in by showing readers how to reference ADSI Users, Groups, and Computers with some quick examples of how to reference each within PowerShell.  There’s then a quick “Inside Track” by James Turner showing how PowerShell can be used to automate some common administrative tasks around user accounts. James tells us to use the right tool for the job as well. While the LDAP functionality allows more authentication methods, the WinNT functionality allows more object types and more familiar syntax.  The bulk of Chapter 3 really concentrates on building a functional script to create and update Users within ADSI. The reader is guided from the more simple scripts to query data through reading a file to automate the creation and updating of user accounts. We also get to see several ways to accomplish this task to introduce the reader to reading from a connection to Excel.

Chapter 4 builds on what we’ve already learned from Chapter 3 to work within ADSI.  Ed starts by helping us become familiar with the AD schema through PowerShell. As always, Ed actually puts to use the “Best Practices” that he wants his readers to learn. His first script includes a full Help example and several functions, followed by a breakdown of each function. I found it helpful to look through the original script while Ed explains the details so I could keep track of how each function fit into the bigger picture.

Ed then demonstrates how to query AD for information, going beyond merely listing the results – this is PowerShell after all and the results can be full objects. He gives a pretty good list of ADSI-related properties, shows how to display results that list results, then moves on to true queries – ADO and OLEDB queries. Of course, his overall goal is to really use PowerShell effectively. ADO and OLEDB are useful, but we also want to be efficient. Therefore, the reader is introduced to the DirectorySearcher class and PowerShell 2.0’s [ADSISearcher] to simplify calling DirectorySearcher. Along the way, we see the “FindOne()” method to limit the results to a single row. That is useful to see what data we can expect.  Some familiarity with ADSI can really help when going through these chapters.

We’re led from these queries to actually managing user accounts. First, the reader needs to understand ADSI User Account Control values. He then shows using the Bitwise AND operation to compare the various bits set and determine properties of the various user accounts. Ed shows how to find and display disabled user accounts in a short script using bitwise AND and color coding. Readers are then shown how to move objects within AD without too much code. The final example details how to find missing values within AD. These are the values that are expected, but not always completed.  Once again, I found it very useful to see the whole script while Ed explains each step of the script.  Be sure to include the backtick if you want to type these scripts yourself and try them.

 

Conclusion

The Introductory section of Windows PowerShell 2.0 Best Practices is well-written and serves its purpose of getting the readers ready to move through the rest of the book. If I have any complaints about this section it’s that it uses ADSI pretty heavily. For people who don’t work directly with ADSI on a regular basis, a lot of the examples are harder to follow. While still possible to see the best practices for PowerShell, it’s easy to get lost in trying to figure out what Ed is doing with ADSI.  The Planning section in chapters 5-8 seem to be less focused on a specific set of functionality and much more on how to plan and use PowerShell effectively.  I plan to post a review of those chapters in the near future.

 

I’d appreciate feedback on this review. Too lengthy? Too short? Let me know what’s good and what’s not good. I’d like to write a useful and honest review for those considering this book.

Powershell for Database Developers

I recently presented for the SQLPASS AppDev Virtual Chapter on "Powershell for Database Developers".  I promised that I'd get the files used in the demos up to my blog shortly so wanted to make good on that promise. First, some of these depend on having either SQLPSX or the SQL 2008 Snap-in loaded in your Powershell environment. Some of those demos won't work unless that's set up. I think I've got some posts on that already, but if you need help with it, leave me a comment or shoot me a message.

I know that the SQL ISE portions of my demo require SQLPSX. Once again, I highly recommend that you download and install these modules for your use. You don't necessarily need them in your startup profile, but they are useful if you work with SQL Server.

I'd also mentioned Red-Gate in my presentation. Here's one link for more details on Doing Schema Compares.

You can access all of my files used for this presentation on Skydrive.

Powershell: Writing Text Files

I was recently trying to generate a bunch of data that I needed to pass through one of our in-house DLLs in order to use some custom encryption algorithms. Thanks to the wonderful Powershell community, I found several examples on using custom DLL methods. However, I ultimately wanted this data to end up in SQL Server. My first attempt used an “Invoke-SQLCmd” Cmdlet from the SQLPS snap-in. However, generating several million rows took a while to insert. I knew that I didn’t want to queue all of this data in memory, so was inserting one row at a time. I stopped this process after a while because it was too time-consuming.

My next attempt was to push these values to a text file using a variable pointing to a file and then using add-content to write each line to the file.  This took longer than doing the SQL inserts one at a time.

I remembered coming across an article by Linchi Shea titled Add-Content and Out-File are not for performance. After finding the link to that article, I found that he recommends using System.IO.StreamWriter to write out files in the most performant manner. I definitely recommend that you check out his article for his summary and examples. For my part, I can say that it took me the better part of 6 hours to generate and populate 10 million rows. I just generated 20 million into a file in < 15 minutes!

I’m including a mockup of my code below in case anyone is interested. I’m definitely not a Powershell expert so as always, be careful if you copy any of this code.  My first attempt at directly inserting into SQL Server worked, but threw an error at about 3.5 million rows. As the time was already pretty lengthy by then, I stopped the process and moved on to writing the text file, resuming where I’d left off. That ran for hours until I finally killed it and looked for a better way.  The uncommented code is what finally ended up working in a timely manner.

I started by loading my DLL. I called it’s “EncryptValue” function (against just the integer – that’s a longer story) and populated both out to a table. There are several reasons for doing this, but this gave me a good working set for a test project and definitely lays some groundwork for the future.  If anyone has any ideas on how I can speed this up, feel free to leave me some comments. If I find some, I’ll post an update.

 

[Reflection.Assembly]::LoadFile("C:\MyDLL.dll")

# First attempt – insert directly into SQL Server.
#foreach ($i IN 1..10000000) { invoke-sqlcmd –query '
#("insert dbo.MyTable VALUES(" + "$i" + ",'" + [MyDLLNamespace]::EncryptValue("$i") + "')") -serverinstance "localhost" }

#Attempt #2 – Write to file with add-content
#$file = New-Item -type File "C:\Values.txt"
#foreach ($i IN 1..10000000) { add-content $file ("$i" + "," + [MyDLLNamespace]::EncryptValue("$i") )  }

$file = New-Object System.IO.StreamWriter "E:\Values.txt";

foreach ($i IN 10000001..30000000) { $file.Writeline("$i" + "," + [MyDLLNamespace]::EncryptValue("$i") )  }

$file.close();
            

Powershell Links – 2010-03-06

Here are some of the more interesting Powershell links I came across recently, along with some thoughts about them.

Powershell, String Encryption, and GPG – SQLServerCentral.com brought this one to my attention. Chad Miller discusses working with Oracle through Powershell, and the subject of encrypting the connection strings came up. There are times that we need to connect using something other than Windows authentication. Storing an encrypted connection string makes a lot of sense in those cases. I don’t normally work with Oracle, but the idea of passing and saving encrypted connection strings makes a lot of sense. I do a lot of work against SQL Servers that require SQL Logins so plan to investigate this more thoroughly in the near future.

Powershell Cheatsheet – If you don’t follow the #Powershell hashtag on Twitter, you may have missed out on this useful set of Powershell tips and tricks, including a way to set your current location to a UNC path. This is cool because it’s something you cannot do with a standard command prompt without mapping a drive. There are other tips here that make this well worth bookmarking if you are getting started with Powershell.

The T-SQL Hammer – This is taken from Chad Miller’s blog, but is a great reminder that just because your typical DBA knows T-SQL and thinks in T-SQL does not make that the best solution for everything. If you’re wondering why a DBA might want to learn Powershell, give this a read.

Powershell Help Brower – This script uses Primal Forms and Powershell to build a Treeview Help browser for Powershell. I tested this out and the only entry that seemed to give me fits was trying to get help for Get-ChildItem. That constantly threw an error for me. However, the rest was very nice – an easy way to browse the available commands and see the help for them.

As with anything else you find on the Internet, don’t just copy these scripts and run them. Check them out carefully and be sure you know what’s going to happen. (Someday I’m just gonna copy Buck Woody’s disclaimer for scripts and give attribution. :)  )

DDL Schema Change Auditing on SQL Server 2005 / 2008

I’ve run across this before so wanted to write up a solution to keeping track of schema changes we’ve used. We trust our team to make DDL changes, additions, drops in order to get their job done. We also trust them to save those scripts into source control as they are run so we can use them when we release software. However, there are times that people make changes and forget to save the script or just think that it’s a temporary change to test and forget about it. With that in mind, we decided to take advantage of SQL Server 2005’s DDL triggers.
First, we created a database called [Audit] on our development server.
Next, we created a table to store the logs.

USE Audit

GO

CREATE TABLE [dbo].[DDL_Audit](

[DDL_Audit_ID] [int] IDENTITY(1,1) NOT NULL,

[Event_Type] [varchar](100) NULL,

[Database_Name] [varchar](100) NULL,

[SchemaName] [varchar](100) NULL,

[ObjectName] [varchar](100) NULL,

[ObjectType] [varchar](100) NULL,

[EventDate] [datetime] NULL,

[SystemUser] [varchar](100) NULL,

[CurrentUser] [varchar](100) NULL,

[OriginalUser] [varchar](100) NULL,

[EventDataText] [varchar](max) NULL

) ON [PRIMARY]


GO

GRANT INSERT ON DDL_Audit TO public

GO

Note in this example, I granted INSERT permissions to public to avoid needing to give our team any other access to that database. I didn’t want them to read/write rows in that table if I could avoid it. After that, I ran a script in SSMS using Text output to step through all of our databases and generate the trigger create code.

sp_msforeachdb 'SELECT ''use ?

GO

SET ANSI_PADDING ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE TRIGGER trg_DDL_Monitor_Change
ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

SET NOCOUNT ON

SET ANSI_PADDING ON


declare @EventType varchar(100)

declare @SchemaName varchar(100)

declare @DatabaseName varchar(100)

declare @ObjectName varchar(100)

declare @ObjectType varchar(100)

DECLARE @EventDataText VARCHAR(MAX)


SELECT 

@EventType = EVENTDATA().value(''''(/EVENT_INSTANCE/EventType)[1]'''',''''nvarchar(max)'''')  

,@DatabaseName = EVENTDATA().value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''',''''nvarchar(max)'''')  

,@SchemaName = EVENTDATA().value(''''(/EVENT_INSTANCE/SchemaName)[1]'''',''''nvarchar(max)'''')  

,@ObjectName = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectName)[1]'''',''''nvarchar(max)'''')

,@ObjectType = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectType)[1]'''',''''nvarchar(max)'''')   

,@EventDataText = EVENTDATA().value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''',''''nvarchar(max)'''')


insert into Audit.dbo.DDL_Audit (Event_Type, Database_Name, SchemaName, ObjectName, ObjectType

, EventDate, SystemUser, CurrentUser, OriginalUser, EventDataText)

select @EventType, @DatabaseName, @SchemaName, @ObjectName, @ObjectType

, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()

, @EventDataText

GO

'''

We took the results of that script and removed any databases that did not matter for purposes of tracking changes. We ran that and created the appropriate triggers on all of the databases. That left us with the notification messages. We handled this through a SQL Server Agent job using Database Mail.

IF EXISTS (SELECT 'x' FROM Audit.dbo.DDL_Audit

WHERE Event_Type NOT LIKE '%statist%'

AND SystemUser NOT IN (service account list here)

AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101))

BEGIN

DECLARE @email_from nvarchar(100)

, @email_address nvarchar(200) 

, @TheSubject nvarchar(255)


SELECT @Email_Address = 'myemail@myemail.com'

SET @email_from = 'DevServer@server.com'
select @email_address as 'To:' ,  @email_from as 'From:'


set @TheSubject = 'Recent Schema changes on ' + RTRIM(@@SERVERNAME)

DECLARE @tableHTML  NVARCHAR(MAX) ;


SET @tableHTML =

N'<H1>DevDB Schema Change</H1>' +

N'<table border="1">' +

N'<tr><th>Database_Name</th><th>SchemaName</th>' +

N'<th>ObjectName</th><th>Event_Type</th><th>ObjectType</th>' +

N'<th>EventDate</th><th>SystemUser</th><th>CurrentUser</th><th>OriginalUser</th><th>EventDataText</th></tr>' +

CAST ( ( SELECT td = Database_Name,       '',

td = SchemaName, '',

td = ObjectName, '',

td = Event_Type, '',

td = ObjectType, '',

td = EventDate, '',

td = SystemUser, '',

td = CurrentUser, '',

td = OriginalUser, '',

td = EventDataText

FROM Audit.dbo.DDL_Audit

WHERE Event_Type NOT LIKE '%statist%'

AND SystemUser NOT IN (serviceaccount)

AND EventDataText not like '%ALTER%INDEX%REBUILD%'

AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101)

ORDER BY Database_Name, ObjectType, ObjectName, EventDate, Event_Type

FOR XML PATH('tr'), TYPE 

) AS NVARCHAR(MAX) ) +

N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default' ,

@recipients=@email_address,

@subject = @TheSubject,

@body = @tableHTML,

@body_format = 'HTML' ;


END

We scheduled this code in a job step that runs daily at 10am. Any changes in that time frame were then sent to a team of people on a daily basis. If no changes were made, no email was sent. Hopefully this will give someone some ideas on one way to audit changes to their DBs. I make no claims that this is the best way, but it worked well for us.

You can download the script file containing all of this code using the link below.

Powershell Community Extensions

While with the time and knowledge we can write a lot of the things we’ll use in Powershell, it’s often not worth it to re-invent the wheel. There’s a group of Powershell coders who have put together the Powershell Community Extensions – a set of Powershell aliases, functions, and other useful code to make working with Powershell a little easier.

For example, there’s a built-in function to split a string. Functions and DLLs are included to read and write ZIP files. There are even some Cmdlets written to allow easy reading and writing from the clipboard or MSMQ. While not all of this will be immediately useful, it’s definitely worth having around.

Installation should be pretty straightforward. Make sure all instances of Powershell are closed. The recommended install is performed using the MSI file. If you want the latest version, you’ll have to unzip the file into your Documents\Windows Powershell\Modules folder.  After that, you can import the module using “Import-Module PSCX”.  There is supposed to be a way to customize what you actually import as well so you don’t load items you’ll never use. The details should be on the Powershell Community Extensions site.

 

While I’m at it, I’d like to give props to John D. Cook who has written a short pamphlet called Day 1 With Powershell which lists a lot of little things that he wished he’d known before starting. There is some good, basic information in it, including configuration, some basics behind Powershell decisions (like = vs –eq), and some pointers to other Powershell sites. I’d recommend it if you’re just getting started with Powershell as it could give you some pointers on how to proceed past the basics.

Powershell – Comparison Operators

I’m writing this one so I remember these operators. Powershell doesn’t use standard operators such as =, <, <>, !=, etc. These operators are used in the following manner.

Operator	Conventional Operator
-eq	=
-ne	<> or !=
-gt	>
-ge	>=
-lt	<
-le	<=
-contains	e.g., 1,2,3 –contains 1 is true
-notcontains	e.g., 1,2,3 –notcontains 1 is false
-not !	e.g., –not ($a –eq $b) Logical Not operator
-and	Logical AND e.g., ($age –ge 21) –and ($gender –eq “M”)
-or	Logical OR e.g., ($age –le 21) –or ($InSchool –eq $true)

 

I think this is one of the areas that is going to trip me up quite a bit. In Powershell, “=” is always used to assign.  “<” and “>” are used for redirection. The names make sense to me in a way, but I can tell I’ll be spending some time with these before I’m completely comfortable with this concept.

 

The above are the base comparisons.  If you prefix them with “i” the operator becomes case-insensitive.  If you prefix an operator with “c”, the operator will be case-sensitive.  Thus we would get the following:

  "johnson" -eq "Johnson" #True

  "johnson" -ieq "Johnson" #True

  "johnson" -ceq "Johnson" #False

Where-Object

This is a useful Cmdlet to filter out results from the pipeline, but this should also be used with care. The initial object will still contain everything with which it started. The results will then be filtered. If you have a way to filter these results prior to passing them through the pipeline, you’ll often have better performance. The following will find all instances of Firefox running on the machine:

  Get-Process | where-object {$_.name -eq 'firefox'}

We could then pass this through the pipeline to operate on that specific object. Where-Object can be abbreviated with a “?”.

 

If, ElseIf, Else

This is a pretty basic statement for Programming and seems to make sense in the way it was implemented in Powershell.

If (condition) {#do something}

ElseIf (next condition) {#do something else}


Else (final condition) {#final action}

As in most uses of If, the process will work through until it finds a condition that evaluates to $true. Once it hits that, it will execute the command(s) inside the corresponding curly braces and exit out of the If.

 

Switch

Sometimes it doesn’t make sense to write out a bunch of IfElse lines to keep checking conditions. Switch can help shorten that. A simple example would be something like this.

$Value = 15
Switch($Value)
{
  1 {"Value is 1"}
  2 {"Value is 2"}
  15 {"Value is 15"}
  default {"Value is $_"}
}

Powershell interprets these in order and will evaluate each one in turn. The default line tells Powershell what to do if no match is found.  If you want Powershell to stop evaluating when it finds a match, add a ;break inside the {} following the matching condition. Otherwise, it could hit the same matching condition and evaluate each one it finds. This may be desirable at some times when you want to act on all matching statements. Other times you may want to short-circuit the process.

Another advantage of the Switch statement is that you can evaluate expressions instead of just the values.  In the example above, the behavior behind the scenes is performing {$_ –eq 1} and {$_ –eq 2}, etc for the lines in the switch. You can substitute your own expressions in here easily to change the evaluations.

Switch supports character comparisons (defaulting to case-insensitive equals), but can do other types by default with a parameter on the Switch statement (e.g., –regex, –case, –wildcard, etc.)

The biggest drawback for Switch is that it would be considered a “blocking” component in the pipeline. It needs to wait for all results before it operates. If you just want to do some filtering on the results, you would be better off using Where-Object or filtering up front.

Powershell – Quite note on Objects

I’m sure this comes as no surprise to anyone who’s dabbled in Powershell, but just about everything in Powershell has an Object underneath. The results we see on screen come from those objects and it’s only when those results are formatted, output, captured, etc that they cease to be an object. This drives much of the power in Powershell. You can set a variable to the contents of an object or part of an object and use that later.

Typically objects will have properties and methods. Properties define what an object “is” – e.g., Red, Small, Named, etc. Methods define what an object can do – e.g., Write, Read, Slice, Mix, etc. There are quite a few internal methods and properties that are common across the internal Powershell objects. You can find these by piping the object to the “Get-Member” cmdlet.

  $host | Get-Member

You can add your own properties and methods to objects easily by calling the Add-Member Cmdlet. The most direct way to do this seems to be by piping the object to the Add-Member Cmdlet.

  $host | Add-Member –Member NoteProperty –Name PropertyName –Value PropertyValue

As Powershell is based on MS technologies, it makes sense that you can use the .NET Framework to work with objects. I will admit that I’m not as familiar with the wide variety of .NET objects so will not try to go into detail.  For one example, here’s a way to call the .NET framework to look up a DNS name by IP address.

  [system.Net.Dns]::GetHostByAddress("207.46.19.190")

 

Perhaps a more useful class would be System.Environment, first by querying its Static members:

  [System.Environment] | Get-Member -Static

You can see that there are a lot of members that are useful. If we choose one and don’t enter valid parameter data, we can even get useful error messages at times.  For example, the following will return an error with valid parameters in the message:

  [system.environment]::GetFolderPath("z")

We can tell by the error message, that some valid parameters would be Desktop, Programs, MyDocuments, Home, etc. Entering a valid parameter in this case will then return the path stored in the Environment for that variable.

 

You can even define your own objects using the New-Object Cmdlet. With no parameters, this creates an empty object. You can create objects of various types by passing the type as the first parameter to the New-Object cmdlet. If you set a variable to an object, you need to be careful about type casting.  Setting a variable to a string containing a date will not necessarily cause that variable to be an object of type System.DateTime. It will most likely be a System.String.  You may need to explicitly cast the object as the type you desire or force the value to be of the type you desire in order to cast the object as the correct/desired type.

 

You can call on COM objects, .NET objects, Powershell objects, Assemblies, and probably more than I’ve listed here as long as you know the name or way to call those objects. I’m still learning this are of Powershell, but hope to expand on this in future posts.

Powershell – Piping

One of the most often-used features I’ve seen so far in Powershell has been the concept of piping the results of commands into other commands to ultimately return something formatted, limited, or otherwise morphed into the desired output. The easiest examples might be something like:

dir | more
dir | sort-object length –desc | format-table -autosize

The first command returns a listing of the files in the current folder one screen at a time, pausing for you to read each screen and tell the command when to advance.  The second is a string of commands to get a listing of files, sort by the file size from largest to smallest, then auto-size each column for a best fit based on the data. Because each result set is passed as an object through the pipeline, you don’t have to handle the format of the text or other conversions. Powershell handles the objects and consumes them until you reach the end. By default Powershell appends a hidden “| Out-Default” command to display the results on screen. If you want to see more options for output, you can run:

Get-Command Out*
Get-Command Export*

Obviously there are a lot of other commands that can be used in the pipeline, but these seem to be especially useful if you want to output the results to something other than the screen.  Also note that if you convert your results along the way using Format-Table or Out-String, you will change the results from an object into an array of text.  (Out-String is the only output command that you can insert into a pipeline and not stop the pipeline. All other "Out-" commands will terminate the pipeline.)


Blocking vs. Non-Blocking

As with other languages, some Cmdlets may be “blocking” other parts of the pipeline. For example, a sort cannot pass its results on until the entire result set has been sorted. The “more” Cmdlet in Powershell is also blocking, but the equivalent “Out-Host –paging” will do the same thing without blocking. When to use various commands is an important consideration if you pipe multiple Cmdlets together, especially if you will be dealing with large objects or result sets.


Filtering

Filtering is accomplished by piping your resultset to Cmdlets such as Where-Object, Select-Object, ForEach-Object, or Get-Unique.

• Where-Object allows you to examine all objects and return just those matching your criteria. This is very similar to a WHERE clause in SQL Server.
• Select-Object acts in a manner similar to the SELECT clause of a SQL Statement and allows you to pick which properties are displayed. It can also handle things such as “TOP 5” or “Distinct” (not exact commands), but can also do some interesting handling of values in the array. For example, you can choose to display every other line of the result set or the first, last, and middle rows by examining the array.  (Note that this will create a new object in a lot of cases because you're filtering the columns.)
• ForEach-Object operates on each result in the resultset to run commands against them.
• Get-Unique eliminates duplicates in the resultset.

Filtering would be considered a Blocking operation in many cases and can sometimes be done more efficiently through the native commands or Cmdlets. For example, if you need to pull back just the files of type “.txt”, you may be better off filtering that out in your dir command rather than passing the entire resultset of all files to the Where-Object Cmdlet.


Tee-Object
It’s worth mentioning this Cmdlet because it could be really useful in troubleshooting. A simple example would be:

dir | Tee-Object -variable t1 | Select-Object Name,Length | Tee-Object -variable t2 | Sort-Object Length –descending

This would pipe the output of the “dir” command into a variable called $t1. It would then pass the output to Select-Object to limit the results down to Name and Length, then pass that resultset to a variable called $t2. Finally, it would output your results ordered by length descending.  You would now have two variables to examine results as you stepped through the pipeline. If you are getting unexpected results, this would be very valuable in troubleshooting.  You can also use “-filepath” instead of “-variable” to store results to a file instead of a variable.

Conclusion
There is a lot of information available for piping commands because this is one of the true strengths of Powershell. I'm not going to go into a lot of details at this point. There will be more examples in future posts and there are lots of other posts detailing the process.  Piping takes a set of results and passes that to the next command, and to the next, and to the next, until it gets to the end of the pipeline. At that point, the results are returned, stored, or discarded (if you chose to output to Out-Null). You can store these results along the way for comparison or even store them in a file for later analysis.

I plan to spend a pretty significant amount of time familiarizing myself with the various uses for the pipeline. Because it's used so heavily, this is going to be a key component to understanding and getting the most benefit from Powershell.

Powershell – Arrays & Hash Tables

I don’t have too much to say about Arrays at this point, but as I’m partly blogging this for my own education, I’ll put down what I’m learning.  At this point, I’m expecting the arrays to be useful when trying to build my example project. One piece will be to step through existing files to look for certain flags or traits to tell me to do further processing.

Arrays

First of all, any variable that stores the results of a command with multi-line output will result in an array. This includes commands such as dir and ipconfig. Arrays start with position [0]. You can create an array by using comma-separated values such as $Variable = 1,2,3,4 or by using $Variable = @(1).

The first cool thing I saw was that using negative positions in an array steps through the array in reverse.  That means if I have an array of $Variable = @(0,1,2,3,4), that $Variable[1] will be 1.  $Variable[-1] will be 4.  I can see this being very useful when you need to step backwards through the results. This is very intuitive to me and I wish this were easier to implement in other scenarios. However, stepping through the array backwards could be tricky because it could result in creating a copy of the array. For small arrays this wouldn’t necessarily be a problem, but with large arrays, I could see performance becoming an issue. If you need to reverse the entire array, you can simply use [array]::Reverse($Variable) to reverse the array elements in place. (This actually changes the array stored in the variable.)

You can easily specify to return multiple elements from the array using $Variable[0,4,-12] which would return the 1st element, 4th element, and the element 12th from the end.

Adding an element is pretty easy. You can type:
  @Variable += @(NewElement)
However, this will create a new copy of the array in order to add another element because arrays in Powershell cannot be resized. This may be something to consider when working with large arrays.

Hash Tables

Hash tables seem to be pretty much what you’d expect. They store key/value pairs such as “Name”, “SQL Server 2008 Enterprise Edition” stored together. You define these in a manner very similar to that of arrays:
  $Variable = @{Name=”SQL Server”; Version=”10.0"; Edition=”Enterprise”}
That will define a new hash table containing 3 keypairs. Name, Version, and Edition.  Each will have a Name and Value property that will display when you display the variable. You can display just the value of a particular keypair with $Variable.Name or $Variable.Version.

Adding a new keypair is easy as well.  Use:
  $Variable.NewName = “New Value”

Similarly, you can remove a keypair with:
  $Variable.Remove(“Name”)

Hash Tables are used with the Format-Table command as well. You can customize the columns returned by manipulating their respective hash table values: Expression, Width, Label, and Alignment. Use get-help Format-Table for more information.

 

By default, copies of an array or hash table are actually copies of a pointer to the values. That means that if you just set the variable with the normal assign of $Var1 = $Var2, you’ll end up with pointers. Modifying either of those variables will affect the other(s). Modifying the array/hash table or using the .Clone() command on either will make a new copy.

Finally, if you need to strongly type an array, you can do so by putting the datatype before the array definition:
  [int[]]$MyArray = @(0,1,2,3)

This will ensure that all values stored in the array are of that datatype. Any attempts to add/remove values to the array that are not of that datatype will raise an error.

 

There are a lot of uses for arrays and hash tables. This just scratches the surface of what can be done, but it should be enough to get you started.