45

I keep reading everywhere that PowerShell is the way of the future. When it was first released I did a whole bunch of virtual labs, but since then I still haven't used it in a production environment. I know the day will come when I'm dealing with OS's where it's already installed, so I want to be ready.

I want to know:

  1. Do you use it?
  2. What has your 'bootstrapping' process been for using PowerShell?
  3. What kind of system administration tasks have you scripted with it?
  4. I'm an SQL Server database administrator. What are some cool things to do with it?

It seems that everyone agrees that Microsoft is pushing this hard, but no one is actually using it yet. I want to hear from system administrators out there that are using it to do every day tasks and share some code samples.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47

22 Answers22

34

Microsoft is doing all it can to make PowerShell the choice of power-users and automation writers everywhere. Gone are the days of compiling code in .NET in order to do the same thing, now you just need notepad.exe and google. We're big fans of it in the office, especially since Exchange 2007's Management Console does NOT include everything that you can do in PowerShell. Microsoft deliberately failed to implement things that only get done once in a great while, easier to develop that way, which downright forces its use if you have anything resembling a complex environment.

Managing Microsoft's newer generation of products (Win7, Windows Server 2008, Exchange 2007/2010, SQL Server 2008) all have very rich PowerShell hooks. Once Remote Powershell (PowerShell 2.0 IIRC) gets deployed with Server 2008 R2, it'll become even MORE useful for automation writers.

What we've done with it:

  • Create a web-page to delegate certain admin tasks to helpdesk users. The web-page fires off commands that get executed in PowerShell. Things it does:
    • Create and delete user accounts, including provisioning Exchange 2007 mailboxes and home directories
    • Unlocks locked out accounts
    • Create/delete groups
    • Add/remove users from groups
    • Move users between mail-stores
    • Set passwords
  • Take extracts from the ERP system and push global-address-book data into Active Directory nightly.
  • Solve the LegacyExchangeDN problem that cropped up with our Exchange 2003 to Exchange 2007 migration. Had to add an X500 address to everyone that used to be on Exchange 2003. A fairly short PowerShell script fixed it.
  • Scripted creation of "group mailboxes" (shared mailboxes in Exchange where multiple users have access to the mailbox), an otherwise manual process due to the nature of the data we need before kicking it off. It greatly standardized the setup of these mailboxes.
  • Created a script that walked through all domained machines resetting a specific registry key and restarting a service. It took 18 hours to complete, but it got the job done.

So yes, PowerShell is going to be with us for quite some time.

EDIT: Adding a code-sample, since it was requested

$list=import-csv("groupusers.csv")
$lastseengroup=$list[0].group
$ADGroupPrefix="grp.netware."
$ADGroupSuffix="{redacted -- in the format of ,ou=groups,dc=domain,dc=domain,dc=domain}"
Clear-Variable memberlist
Clear-Variable unknownusers
foreach ($entry in $list) {
    if ($($entry.group) -ne $lastseengroup) {
        echo "stumbled across new group $($entry.group), committing changes to $lastseengroup"
        $newgroup=$ADgroupPrefix+$lastseengroup
        $newgroupdn='"'+"cn=$newgroup$ADGroupSuffix"+'"'
        echo "getting DN for $newgroup"
        $existinggroup=dsquery group domainroot -name $newgroup
        if (($existinggroup -ne $null)) {
            dsmod group $newgroupdn -chmbr $memberlist
        } else {
            dsadd group $newgroupdn -scope u -secgrp yes -members $memberlist -desc "Group imported from eDirectory"
        }
        Clear-Variable memberlist
    }
    $User=get-user $($entry.member) -ErrorAction SilentlyContinue
    if ($User.isvalid) {
        $UserDN=$User.distinguishedname
        $memberlist=$memberlist+'"'+"$UserDN"+'" '
    } else {
        $unknownusers=$unknownusers+$($entry.member)
    }
    $lastseengroup=$($entry.group)

}
dsadd group "cn=$ADGroupPrefix$lastseengroup$ADGroupSuffix" -scope u -secgrp yes -members $memberlist

This takes a CSV file created with a perl script and updates a set of groups. If the group already exists, it replaces the membership with that specified in the file. If the group does not exist, it creates it. This is a one-way sync. Also, not quite in production yet, but close.

sysadmin1138
  • 131,083
  • 18
  • 173
  • 296
  • 6
    remote powershell!! you mean like ssh!?! about time! – Nick Kavadias May 28 '09 at 07:46
  • 2
    How do you run powershell scripts from a website, do you use powershell scripts as web programming language? are you using powershellASP? are you running your scripts as IIS user? – quentin Nov 05 '09 at 10:19
17

Given that Microsoft's server products are going to be PowerShell-enabled from the outset (I believe the next version of Exchange has all config available through PowerShell), and that books like PowerShell in Practice describe some great ways to automate otherwise monotonous tasks, I think it's reasonable to suggest that PowerShell will be a prevalent technology in Windows serverland for a while yet.

moobaa
  • 417
  • 1
  • 3
  • 8
  • that book is still in beta/ being written – Nick Kavadias May 28 '09 at 07:25
  • Yes, it is - but you can still purchase it now & get early access to the contents. And there's tons of server-maintenance stuff in there :) – moobaa May 28 '09 at 12:34
  • 4
    Exchange has been all PowerShell since Exchange 2007. OCS will be fully PowerShell-capable in its next release; most of the System Center products either support it fully or will in their next releases. PowerShell isn't going anywhere. – paulr May 28 '09 at 13:39
12

I'd recommend Scott Hanselman's Podcast #162. It seems like all the Microsoft server teams are "forced" to provide PowerShell cmdlets, and also to follow a single, consistent syntax.

Also, third parties like VMWare are adopting it

In short, I believe that PowerShell starting with 2.0 is seriously in the business of replacing batch files for all but the most trivial tasks.

Michael Stum
  • 4,010
  • 4
  • 35
  • 48
10

Ten PowerShell scripts I use as an SQL Server database administrator (all are described/posted on my blog):

  1. Check disk space on all SQL Servers and load data to an SQL table
  2. Run permissions reports on all production SQL Servers and load data to an SQL database
  3. Discover all Windows Server 2003 clusters, nodes, and virtuals on network and load to SQL tables
  4. Discover all databases on all SQL Servers as defined in SCCM and load to an SQL table
  5. Create an SQL Server backup scorecard by loading backup information across all SQL Servers to an SQL table
  6. Verify that TCP Offload Engine is disabled in Windows Server 2003 SP2 (this is a performance killer for many SQL Servers)
  7. Verify Disk Partition alignments (also a performance killer if disks are aligned improperly)
  8. Copy SQL tables from one server to another
  9. Recursivley copy all SSIS packages using an MSDB Storage from one server to another, including folder structure
  10. Create my own graphical object dependency viewer
Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Chad Miller
  • 1,091
  • 8
  • 11
8

What kind of admin tasks have you scripted with it?

application/service monitoring: get key performance numbers out of (remote) EventLog and Stored Procedures and display them from a single batch file

I'm a SQL Server DBA, show me some cool things to do with it?

automssqlbackup: daily backup for MSSQL with rotation (daily=incremental, weekly+monthly=full; keep 5 daily, 5 weekly backups), zipping, email notification

devio
  • 201
  • 1
  • 4
  • +1 for automating the whole MSSQL backup process. – Andrei Rînea Nov 20 '09 at 11:27
  • Yep... Powershell is pure gold for automating complex backups in SQL Server. We use it to zip and encrypt hourly backups and then send the files to Amazon S3 for offsite storage: http://codeblog.theg2.net/2010/02/powershell-7-zip-amazon-s3-upload.html – Greg Bray Mar 15 '10 at 04:22
7

Having used unix shell scripts in a previous career, then moving to windows server administration, I can say that I really, really appreciate powershell--I don't have time nor ability to learn too much arcane syntax and was pleasantly surprised that a lot of powershell scripts can be written with very straightforward language...take that what its worth as I was used to ksh!!

I predict it will be around for a long time.

WiredNut
  • 79
  • 1
7

I thought this was cool: with SQL Server 2008 you get new PowerShell extensions that allow you to navigate SQL databases just like your local file system or registry.

After installing the SQL Server Client tools you can open any database in PowerShell (via the right click context menu) and play about with it in an object oriented way:

PS SQLSERVER:\SQL\MyServer\MyInstance\Databases\ExampleDB\>function prompt {"PS >"}
PS >cd tables
PS >dir

SCHEMA          NAME         CREATED
------          ----         -------
dbo             MyTable      25/06/2009 17:02

PS > dir | format-table name, columns, rowcount, parent

NAME            COLUMNS      ROWCOUNT      PARENT
----            -------      --------      ------
MyTable         {Id, Name}   0             {ExampleDB}

PS >$myTable = dir | where {$_.Name -eq "MyTable"}
PS >$myTable.Script()

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[MyTable](
[Id] [int] IDENTITY(1,1) NOT NULL
[Name] [nText] NOT NULL
) ON [PRIMARY]

PS >set-location \SQL\AnotherServer\Default
PS >cd databases\Northwind\tables
PS >$myTables = dir | where {$_.Rowcount -gt 100 -and $_.HasIndex -eq 1}
PS >$myTables.Count

8

PS >foreach ($table in $tables) {$table.RebuildIndexes(90)}

Note the first line simply changes the prompt so that it's not so long.

Hopefully apart from that it's self evident what is going on here :)

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Lex
  • 296
  • 1
  • 5
5

SQL Server 2008 now has PowerShell context options in Management Studio, so more than Exchange is embracing it. Also, think of it as the replacement for DOS, as that is pretty much what MS was thinking as they just can't improve the security of DOS for backwards compatibility issues, so they just created something new and wammo, problem solved!

SQLGuyChuck
  • 114
  • 5
4

I too am a DBA, and long time scripter, going back to DOS batch files, V3.3! Moved on to VB Script but PS is very powerful. Take a look at - Sorry, I can't post the link, as I'm a new user. Got this from Otto Helweg's Blog. It is a cheap and cheerful software inventory tool.

  • 1- Pulls the list of computers to query from a database table
  • 2- Adds the current data and time to the result
  • 3- Records the audit results into a database

Example PowerShell script:

# Open the database connection
$dbConn = new-object System.Data.SqlClient.SqlConnection "server=kcdb;database=Inventory;Integrated Security=sspi"
$dbConn.Open()
$sqlQuery = $dbConn.CreateCommand()

# Get all known computers
$sqlQuery.CommandText = "select * from Inventory..Computers"
$reader = $sqlQuery.ExecuteReader()
$computersToQuery = @()
while ($reader.Read()) {
   $computersToQuery += $reader["Computer"]
}

# Close the database connection
$dbConn.Close()

$softwareInventory = @{}
foreach ($computer in $computersToQuery) {
   $psinfoOutput = ./psinfo.exe -s Applications \\$computer

   $foundSoftwareInventory = 0
   $computerName = ""
   foreach ($item in $psinfoOutput) {
      if ($foundSoftwareInventory -eq 1) {
         # Force the results to a string
         # Remove any single quotes which interfere with T-SQL statements
         # Load the result into a hash whereby removing any duplicates
         [string]$softwareInventory[$computerName][$item.Replace("'","")] = ""
      }

      if ($item -like "System information for *") {
         $computerName = $item.Split("\")[2].TrimEnd(":")
      } elseif ($item -eq "Applications:") {
         $foundSoftwareInventory = 1
         $softwareInventory[$computerName] = @{}
      }
   }
}

$dbConn = new-object System.Data.SqlClient.SqlConnection "server=kcdb;database=Inventory;Integrated Security=sspi"
$dbConn.Open()
$sqlQuery = $dbConn.CreateCommand()

foreach ($computer in $softwareInventory.Keys) {
   foreach ($softwareItem in $softwareInventory[$computer].Keys) {
      "Loading-" + $computer + ":" + $softwareItem

      # Try an Update then an Insert 
      trap {
         $sqlQuery.CommandText = "update Inventory..SoftwareInventory set AuditDate = getdate() where  Computer = '" + $computer + "' and Software = '" + $softwareItem + "'"
         $result = $sqlQuery.ExecuteNonQuery()
         continue
      }

      $sqlQuery.CommandText = "insert into Inventory..SoftwareInventory (Computer,Software,AuditDate) values ('" + $computer + "','" + $softwareItem + "',getdate())"
      $result = $sqlQuery.ExecuteNonQuery()
   }
}

$dbConn.Close()

Dave J

3

The following tasks are done by my team on a very regular basis with PowerShell

  • list the available memory on all the nodes of our Hyper-V clusters.
  • Manage a Netapp filer
  • Provision new Hyper-V Virtual Machines
  • Write to and Read from SharePoint lists programmatically

Day to day, I have a PS prompt up all the time and use it for any one off tasks that I used to use CMD for.

Here are some code samples for reading Sharepoint Lists. I use web services from Sharepoint and the new "New-WebServiceProxy" cmdlet in PowerShell V2.0

$url = "http://server/sites/site/_vti_bin/lists.asmx"
$listWebService = new-webServiceProxy -uri $url -UseDefaultCredential
$listName = "MyList"
$viewGuid = $null
$query= $null
$viewFields = $null
$numRowsReturned = 20000
$queryOptions = $null
$result = $listWebService.GetListItems($listName,$ViewGuid,$query,$viewFields,$numRowsReturned,$queryOptions,$null)

Oisin Grehan has a great blog post on using Sharepoint Web Services that I used as a base to create all my functions. He evens has an awesome new-webservice.ps1 script that you can use if you are still using PowerShell V1.

Also, here is the base function I used to get the available memory on nodes in my cluster

Function Get-AvailableMemory {
param
(
[string[]]
$servers
)
BEGIN {
$query = "select AvailableBytes,__SERVER from Win32_PerfRawData_PerfOS_Memory"

$availableMemory = @{
                    Name="AvailableMemory";
                    Expression = {[math]::round($_.AvailableBytes/1gb,2)}
                    }
$serverName = @{  
                Name ="Server";
                Expression = {$_."__SERVER"}                                      
               }
}
PROCESS {
foreach ($server in $servers){
    get-wmiobject -query $query -computer $server |
    select-object $availableMemory, $serverName
    }
}

END {}    

}
Andy Schneider
  • 1,533
  • 5
  • 19
  • 28
  • Got any sample code you're willing to share? I'm particular interested in reading and writing SharePoint lists. – Brian Jun 06 '09 at 16:55
2

I tried PowerShell for a small project at work - a daily migration of a subset of ~600 users and ~200 groups from the corporate AD to an old UNIX system using CSV-like files. I was offered a 500-line VBScript script (!) that pulled out a single group and its users as a starting point.

It turned out a ~20-line PowerShell script could do the entire thing...

As a *NIX user (server and client side), I've been very pleasantly surprised with PowerShell. Even if you just know a small subset of commands, you can do really powerful work with it. I just need SSH(-like) remote access...

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Morten Siebuhr
  • 639
  • 1
  • 6
  • 16
1

How can I not use it? All Microsoft server applications coming out/being upgraded are now managed using Powershell. Some more or less annoying UI/Management Console are then provided built on-top of this core which often is dog slow and as someone pointed out, lacks features that are available... so I can't say we have much choice actually ^^

Oskar Duveborn
  • 10,740
  • 3
  • 32
  • 48
  • Check out this little powershell script to ngen your assemblies and speed up all your powershell based consoles: http://msexchangeteam.com/archive/2008/08/01/449426.aspx – Jesse Weigert Jun 06 '09 at 04:57
1

Powershell isn't perfect but if you've used it you know it's pretty cool. Having access to the entire .net library plus built-in cmdlets (and make your own cmdlets using PS in 2.0), that's pretty cool.

The only thing I think is missing is a good IDE for it which I'm sure will come from MS some day. Right now I use Idera's powershell plus and adminscripteditor which allow debugging and are pretty cool.

I was able to wean myself off of VB pretty well.

My biggest project to date with posh is to use "powershellASP" to make a basic web page that runs powershell commands and pulls vmware host and VM information from my more than a dozen VMWare ESXi (free) hosts and displays them in HTML tables. I did this because with the free VMWare ESXi and no virtual center, there's no other way to get real-time overview of all of my VMs in the company. PowershellASP is a free 3rd party parser written for IIS and not supported by MS. So it can do some limited things right now and the documentation is pretty sparse.

  • There is PowerGUI, and in powergui use ScriptEditor.exe, I can't imagine how I can live without that... – Nicolas Dorier Jun 20 '09 at 08:15
  • Powershell 2 includes a great Powershell ISE (Integrated Script Editor). The CTP 3 is available, RTM is around now, final release in October 2009. – Precipitous Jul 31 '09 at 06:49
1

I have a whole bunch of management/inventory scripts that search through AD for differnt things, and query them over WMI for vital statistics such as shares on the server, disk space, used disk space, etc.

General Scripts:

  1. Get Free/used disk space, RAM, etc.
  2. Query WMI for BIOS information, server make model, etc.
  3. Check AV version, and make sure certain registry entries are there
  4. Audit Shares on Windows Servers

User Scripts:

  1. Enumerate AD grabbing all users, and what groups they belong to then write to Excel, or CSV
  2. Find currently logged on users Via TS and on the console

I have one that enumerates all VMWare guest machines on my ESX servers

I have a script that grabs files after x days and securely deletes them.

Basically I can do what took about 200 lines of VBScript and do it in 10 of PowerShell. You can also do stuff you just can't do or do well in VBScript - like send emails with attachments - which I do regularly to send me the results of my scripts. Also you can write meaningful and unique entries into the event log with custom service names and Event IDs.

That's what I can think of off the top of my head. My tool of choice is PowerShell now, it just makes life easier.

Basically it is just about as useful to a system admininistrator as Bash scripting is to a Unix administrator.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Zypher
  • 36,995
  • 5
  • 52
  • 95
0

I was looking at poweshell to replace our client batch scripts - running programs, logon scripts etc.

But found that it added at least 10 seconds to logon time - thats with all the "tweaks" in place. So ended up staying with what we were using.

Tubs
  • 1,194
  • 3
  • 11
  • 19
  • It's not the fastest thing in the world, but it can do just about everything. If all you need to do is map a few drives, then batch scripts are way faster. – sysadmin1138 May 28 '09 at 14:33
0

We use it for quite a few admin tasks, being a mostly Microsoft shop. Some scripts are simple, like cleaning up old files or defragging machines and recording the results. One script audits computers using mostly WMI and records the results in a database. It gets things like hardware (CPU, RAM, drives, etc.), network information, local users, installed software, and profile information. There's one that calls a web service to send email. I think in the future it will replace the rest of the VBScript scripts that are still being used. We're not using it for Exchange or SQL Server tasks yet.

I'm looking forward to the next version, which will have support for background jobs, transactions, better exception handling, a new ISE (Integrated Scripting Environment), better debugging, etc.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Bratch
  • 432
  • 4
  • 12
0

Automating certain things with server checkups where I work (gathering basic info from WMI and gathering errors and warnings from the event logs).

It's handling of Event Logs is awesome.

I recently wrote a script to disable the auto-update popup that Foxit Reader has enabled by default across a domain that the script is run on. It pulls a list of computers from AD then tests to see if its up, then it changes a 1 to a 0.

One thing I forgot!

You pretty much have full access to the .NET libraries. It's a bit of a pain to do really complicated stuff with it, but if you have the need, it's there for you.

0

Not being a .NET developer, I have limited use for all the .NET-related goodies that PS lets you use. However, being an Exchange admin (amongst other things), the PowerShell integration for Exchange 2007 is AWESOME. I finally have a reliable scripting engine for my mail system; so far, I've got it sending me monthly stats on everyone's mailboxes, adjusting who various email addresses are assigned to depending on the time of day, automatically creating and assigning permissions on public folders and load-balancing users between our two mail mailbox DBs. The other cool thing from the Ex07 point of view is that after you do anything in the GUI, it presents you with the relevant PowerShell commands for what you just did. Copy those, and you'll eventually have a set of PS scripts that can rebuld your Exchange environment from scratch.

RainyRat
  • 3,700
  • 1
  • 23
  • 29
0

My Windows administrators do not use PowerShell yet. They have all heard of it, but they haven't taken the time to get familiar with it.

I decided to take a look at it myself. Coming from a Unix shell background I thought that Windows must finally have a real shell programming environment. I think that Microsoft did some things well with PowerShell and somethings not so well.

On the good side is their use of objects as the intermediary between cmdlets in PowerShell. This brings a level of power to PowerShell that text based scripting has to hack around to get to work.

On the negative side they really don't leverage much of the Unix shell commands. So the learning curve is unnecessarily steep and doesn't help people move easily from Unix to PowerShell or vice versa. The most they do is define aliases for some of the PowerShell cmdlets such as ls aliasing Get-ChildItem with all the command-line switches being different.

Just my two cents...

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
user7286
  • 135
  • 3
  • 7
0

This is more of a developer answer, but it is real world and we will start testing this at some point in the next few months. I'm beginning to learn PowerShell in order to write scripts to bring SQL Server tasks into our continuous integration environment. The continuous integration scripts will be run primarily by NAnt and MSBuild via JetBrains TeamCity.

In addition to that, I'm looking at switching over to PowerShell as my primary Windows command shell for general purpose and especially for SQL Server database administration tasks.

Sorry, I don't have any code samples yet to share, because I'm still learning! I will be happy to post some when I do, though :)

Here's a single line code sample that I just answered another question of yours with :) But hey, for the wiki...

This will list installed hotfixes:

Get-ChildItem -Path “HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\HotFix”
Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
squillman
  • 37,618
  • 10
  • 90
  • 145
0

While it isn't the most easy script language to pick up (solid experience with pipes presumed) and has its own leverage of oddities (give me a simple sed-like thingy!!), I find PowerShell quite powerful. Not up to the level of Bash or other Unix shells, but still.

I use it to supervise some database exports and imports with email warnings in case things go bad, and do some routine batch operations.

It certainly already is my primary Windows shell, although it isn't really the "write less do more" kind of stuff. PowerShell has its verbosity (and .NET inheritance) playing against it.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Berzemus
  • 1,162
  • 3
  • 11
  • 19
0

PowerShell version 1.0 was sorely limited in a lot of aspects, but version 2.0 is coming along nicely. Compared to batch scripts, it is much more powerful. I don't really use it as a shell, only for scripts, so my experience is colored accordingly. I don't really like the syntax (-eq vs ==, -gt vs >, etc.), but I appreciate being able to drop into .NET if I need to do something weird. I think that by version 3.0 it will be great. Right now I would put it firmly in the category of "gets the job done".

I use it mainly for automating the deployment of code. This is an area where it shines over creating something in C#. The shell-related operations like recursive directory copy and other things Bash scripters have been taking for granted for years make things so much easier.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Trent
  • 81
  • 2