Windows PowerShell: Doing Databases with PowerShell

You can use some Windows PowerShell code to configure your databases to store exactly what you need.

Don Jones

Databases are the undisputed king of data storage. So why not leverage those databases like SQL Server to store some of the administrative information that could make your life easier? You could store server names, Windows versions, service pack levels, last admin to log on, that kind of thing.

It’s all pretty easy with Windows PowerShell, because you can use it to natively access the underlying database capabilities of the Microsoft .NET Framework. Now this is going to look less like a Windows PowerShell script and a lot more like a C# program. The code you’ll need, however, is pretty easy to template. You can take what I’ll show you here and with just a few minor tweaks adapt it to almost any situation.

Scripts for SQL

Let’s suppose you have a SQL Server 2008 database named SYSINFO. The database has a table named SERVERS. That table has five columns: ServerName, UserName, LastLogon, Reason and SPVersion. This can be mostly simple text columns of the varchar type.

However, make LastLogon a datetime column. Set it to use the SQL Server NOW function as its default value. That way, whenever you add a new row, it will contain the current date and time, without needing you to specify it manually. Make the Reason column a varchar(MAX), so it can contain lots of text, if necessary.

Create a logon script in a Group Policy object (GPO), linking the GPO to the organizational unit (OU) where your servers live. Each time someone logs on to a server, the script will run. Use a Windows PowerShell script, something you can do on any computer where Windows PowerShell 2.0 is installed. You’ll need to ensure you have the GPO templates from Windows Server 2008 R2 (or the Windows 7 Remote Server Administration Toolkit, or RSAT) in order to have the Windows PowerShell-based logon script option when editing the GPO.

Here’s the script you’ll need. Note that I’m using ↵ to indicate where you should hit Enter. Only hit Enter when you come to that symbol if you want this to work properly (you can replicate the symbol in HTML using the ↵ entity):

$reason = Read-Host 'Reason are you logging on'↵
$reason.replace("'","''")↵

Those two lines prompt someone for the reason they’re logging onto the server—that’s something that’s fairly important to track. The Replace function will replace any single quotes with two single quotes, ensuring that the SQL statement we construct later will work properly. This isn’t designed to guard against deliberate SQL injection attacks. After all, we’re talking about trusted administrators, right?

This line will retrieve OS information:

$os = Get-WmiInfo Win32_OperatingSystem↵

The important data here is the server’s name and its current service pack version. You might also want the BuildNumber property, which tells you the version of Windows with which you’re dealing.

This line loads the part of the .NET Framework that’s responsible for working with databases:

[assembly.reflection]::loadwithpartialname('System.Data')↵

These lines create a new database connection:

$conn = New-Object System.Data.SqlClient.SqlConnection↵
$conn.ConnectionString = "Data Source=SQLSERVER;Initial Catalog=SYSINFO;Integrated Security=SSPI;"↵

If you aren’t using SQL Server 2008, the connection string might be a bit different (visit ConnectionStrings.com to look up connection string examples for a wide variety of databases):

$conn.open()↵

Now the connection is open and ready to use.

These two lines create a SQL command, which is what I’ll use to send my query to SQL Server. I’ve set its connection property to “opened,” so that Windows PowerShell has a way to actually send the command:

$cmd = New-Object System.Data.SqlClient.SqlCommand↵
$cmd.connection = $conn↵

This does all the hard work. It creates a SQL “INSERT” query to insert a new row into the database table. Note that I’m using the –f formatting operator to insert four pieces of information into the query. The information is inserted into the {x} placeholders, in the same order that it’s provided in the comma-separated list that follows the operator:

$cmd.commandtext = "INSERT INTO servers (servername,username,spversion,reason) VALUES('{0}','{1}','{2}','{3}')" -f
$os.__SERVER,$env.username,$os.servicepackmajorversion,$reason↵

Now I’ll execute the query and close the database connection:

$cmd.executenonquery()↵
$conn.close()↵

Make sure you close that connection or you’ll have a database administrator smacking you in the back of the head. As you’ll see on ConnectionStrings.com, you can access a large number of databases.

If you’re using something besides SQL Server, you’ll have to change the “SqlClient” part of the object names to “OleDb” instead. Also, it’s not recommended to use file-based databases like Access. For one, to get the driver, you’ll have to install Access on your servers, which would be an awful idea. For another, the performance of those databases isn’t up to the level you’ll need for this to work well.

If you don’t have a SQL Server instance that can host your database, get a copy of the Express edition installed someplace. That will suffice for the lower level of traffic this technique will likely generate.

You can obviously modify this technique to do quite a bit. You can add columns to the database and have your script collect additional information. One thing you’ll need to do is become skilled in the SQL language itself. You don’t need to be a guru, but you’ll want to be able to write basic queries.

If you need a primer on the SQL language (which at this level works almost identically on every major database platform), check out this video series I created that provides a complete tutorial on the industry-standard SQL language. It also covers the major differences between platforms like SQL Server, Oracle, MySQL and so on.

Here’s the entire script:

$reason = Read-Host 'Reason are you logging on'↵
$reason.replace("'","''")↵
$os = Get-WmiInfo Win32_OperatingSystem↵
[assembly.reflection]::loadwithpartialname('System.Data')↵
$conn = New-Object System.Data.SqlClient.SqlConnection↵
$conn.ConnectionString = "Data Source=SQLSERVER;Initial Catalog=SYSINFO;Integrated Security=SSPI;"↵
$conn.open()↵
$cmd = New-Object System.Data.SqlClient.SqlCommand↵
$cmd.connection = $conn↵
$cmd.commandtext = "INSERT INTO servers (servername,username,spversion,reason) VALUES('{0}','{1}','{2}','{3}')" -f $os.__SERVER,$env.username,$os.servicepackmajorversion,$reason↵
$cmd.executenonquery()↵
$conn.close()↵

Don Jones

Don Jones is a popular Windows PowerShell author, trainer and speaker. His most recent book is “Learn Windows PowerShell in a Month of Lunches” (Manning, 2011); visit MoreLunches.com for info and free companion content. He’s also available for on-site training classes (visit ConcentratedTech.com/training for more information).

Related Content