Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: PowerShell

Windows PowerShell

I like calling it POWAH shell. The reason is simple. It’s (almost) everything I miss from my good old Linux-days. When I first started managing windows servers, I couldn’t find anything. Same thing goes for Vista. When I first started using it, I couldn’t find any settings, because Microsoft has made it so user friendly. That’s great, till you’re for example trying to use wildcard-mapping in IIS7. THAT’S NOT EASY IN UI! It’s 5 lines of code in web.config, but it’s very hard to find in the UI.

So, what I’ve been missing is a real good console configuration tool, where you can do everything you want from console. Now I found it. After playing with it for a couple of hours, I’m convinced! This is POWAH shell!

A very cool thing about POWAH shell is that you’re working with .NET objects, and not "stupid" strings like in Linux. Of course, this works just fine, but here you can do basically the same things as you would have done in a .NET application. Let’s make an example. We want to know how much free space all disks on all our servers have. Without powershell, I have two ways of doing this. Either make an application for it, or log on remote desktop. With powershell you just do this:

foreach ($server in Get-Content "C:\powershell\servers.txt")
{
    $server;
    Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $server |
        Where {$_.Name -ne "_Total"} |
        Select Name, PercentFreeSpace, FreeMegabytes |
        Format-Table
}

In c:\powershell\servers.txt I have added one line per server. This is 3 lines of code! Since I’m managing a total of about 15-20 servers, this saves me ALOT of work!

The result I get is:

MyServer

Name PercentFreeSpace FreeMegabytes
C: 83 116757
D: 96 270806
E: 99 139761

If I change the last “Format-Table” to “Format-List” I get this:

MyServer

Name C:
PercentFreeSpace 83
FreeMegabytes 116757
   
Name D:
PercentFreeSpace 96
FreeMegabytes 270806
   
Name E:
PercentFreeSpace 99
FreeMegabytes 139761

I’m using the Win32 API to connect to the server, filtering where the name of the disk is Not Equal (-ne) to _Total (which is all disks combined) and then selecting the fields I want to output.

Another good example would be to check that our SQL servers are running the SQL services, and NO IIS. Same goes to our web servers. No SQL Services, but IIS should run. To do this, you can execute:

foreach ($server in Get-Content "C:\powershell\servers.txt")
{
    $server;
    Get-WmiObject win32_Service -ComputerName $server |
        Where {$_.name -eq "MSSQLSERVER"
            -or $_.Name -eq "SQLSERVERAGENT"
            -or $_.Name -eq "SQLBROWSER"
            -or $_.Name -eq "IISADMIN"} |
        Format-Table
}

This will return a table for each server, with the the services I’ve filtered on (MSSQLSERVER, SQLSERVERAGENT etc), the state and the status. If we want, we can add a pipe (|) and a Select so it only returns the columns we want.

The third script I’ve made is to check all SQL Servers Jobs, if any jobs failed last time they were executed:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

foreach ($srv in get-content "C:\powershell\servers.txt")
{
    $srv;
    $server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$srv"
    $server.JobServer.Jobs |
        Where {$_.LastRunOutcome -eq "Failed"
            -and $_.IsEnabled -eq $TRUE} |
        Format-Table Name, LastRunOutcome, LastRunDate -autosize
}

Notice, we’re “loading” the Microsoft.SqlServer.Smo assembly. This is a .NET assembly which we’re using later on. For each server we’re making a new object $server, and accessing the JobServer.Jobs, filtering where LastRunOutcome equals “Failed” and that it is Enabled.

You can also open a SQL connection, run SQL statements and handle the results in POWAH shell if you want. There’s almost no limits! There are still some things I’m missing, but I’m sure they’ll add it in the next versions. After all, this is only version 1! Good job Microsoft!

Advertisements