SQLChicken.com

SQL Server DBA Tips & Tricks

By

PowerShell I Heart You

PowerShell = Giggity Giggity Goo

Ok so in the last 48 hours I think I’ve fallen madly in love with PowerShell. My time is limited so I’ll make this short. Last night I wrote a script to copy files from one file share to another and log the process. Luckily PowerShell community guru Laerte Junior (Blog | Twitter) and Ron Dameron (Blog | Twitter) were online and willing to help me out (as always). I aksed him some general questions but was determined to write script on my own and just have him review it and point me in right direction. So after much Boogling I got some code example and spat this PowerShell turd out: 

[sourcecode language="powershell"]
try {
$a = (Get-Date -uformat "%Y%m%d").ToString()
 Get-ChildItem \servernameextracts_foldertestdata*.001 | ForEach-Object -Process{Move-Item -PassThru -Force $_.FullName -Destination \targetservernametest | Format-Table -AutoSize >> ("\servernameextracts_foldertestlogstest_extracts_copy" + $a + ".log")  }
}
 catch {
  "Error occurred on $_" >> \servernameextracts_foldertestlogstest_copyerrors.txt
}
 
try {
 Get-ChildItem \servernameextracts_foldertestdata*.IDX | ForEach-Object -Process{Move-Item -PassThru -Force $_.FullName -Destination \targetservernametest | Format-Table -AutoSize >> ("\servernameextracts_foldertestlogstest_extracts_copy" + $a + ".log")  }
}
 catch {
  "Error occurred on $_" >> \servernameextracts_foldertestlogstest_copyerrors.txt
}

[/sourcecode]

Basically it moves the files from one server to another and writes basic information of each file moved to a log file. The log file it writes to is dynamically named based on the date the script is ran. If the script bombs then it writes the errors to another log file the administrator can refer to for troubleshooting. 

Next up this morning I saw an article on SQLServerCentral.com ‘s newsletter on how to Make a 100+ Server Inventory in 30 minutes. 30 minutes? Hell, with PowerShell I think I could knock that out even quicker! And so I have, try 5. First create a text file with a list of all your server names. I’m sure you could get that using PowerShell too but Boogle it. Next try this code out: 

[sourcecode language="powershell"]

Get-Content ‘c:serverlist.txt’ | ForEach-Object {systeminfo.exe /s $_ } | Out-File c:testserver_reports.txt

[/sourcecode]

ONE line of code and you have a full report of everything on your server. Giggity giggity goo! ALRIGHT!

Share
  • http://benchmarkitconsulting.com Colin

    Now if you wanted to be really sexy you could do a /FO TABLE at the end of that an UPSERT it into a database to keep an up to date queryable inventory of your environment. :D

    • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

      Haha damn you sir, your ideas have just tacked on another few hours of hacking because now I’ll do that plus build the SSRS reports to view everything.

  • Pingback: Tweets that mention PowerShell I Heart You - The SQL UPDATE Statement -- Topsy.com

  • http://benchmarkitconsulting.com Colin

    Sounds great… then you could write a book on Powershell cause you’ll be the guru or awesomeness!!! :)

    GO CHICKEN GO!!

    • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

      Lol are you volunteering for another co-authoring gig? You me and Ken could become the new book-writing Muskateers!

  • Meredith

    If you have your servers in their own OU adding this bit will create your input string.

    $strCategory = “computer”
    $objDomain = New-Object System.DirectoryServices.DirectoryEntry(“LDAP://OU=yourou,dc=domain,dc=com”)
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher($objDomain,”(objectCategory=$strCategory)”,@(‘name’))
    $objSearcher.FindAll() | %{$_.properties.name} | out-File C:testserverlist.txt

  • http://scarydba.wordpress.com/ Grant Fritchey

    Or you could rewrite it to use invoke-command and run it against all your servers in parallel, rather than serially, and I’ll bet it would be down to less than minute. Just mentioned this to Don Jones and he said “Any time you’re using foreach, you’re trying too hard.”

    • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

      Huh, very cool, hadn’t thought of that approach. I’m probably going to re-write this script with all of your inputs and repost (maybe update this one or post whole new entry for it). Thanks for your replies guys!

  • ob

    I am gettin an error. Get-Content : Cannot find path ‘C:serverlist.txt’ because it does not exist.
    At line:1 char:12
    + Get-Content <<<< 'c:serverlist.txt' | ForEach-Object {systeminfo.exe /s $_ } | Out-File
    + CategoryInfo : ObjectNotFound: (C:serverlist.txt:String) [Get-Content], Ite
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

    • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

      Did you create a text file with all your server names in it called serverlist.txt and place it on the root of your c: drive? If not, my code won’t work but you can create the text file anywhere you want (and name it anything you want for that matter) and simply modify the script to point to the correct location.