Removing Header/Footer lines from CSV files

I was recently tasked with scheduling a script to read in a TSM.out file and create a usable CSV file from it. The TSM *.out file has at least 9 header lines that are not relevant to the end file as well as some extra footer lines. I also noticed that the column headers are not descriptive either. So, to automate the file transformation, we look to PowerShell!

function Create-CSV {
  param(  [int]$HeaderLines,
          [int]$FooterLines,
          [string]$SourceFilePath,
          [string]$DestinationFilePath,
          [string]$NewColumnNames    )  

  if(Test-Path -literalPath $SourceFilePath) {
      $a = Get-Content -path $SourceFilePath
      if ($NewColumnNames -ne $null) {
          Add-Content -path $DestinationFilePath `
                      -value $NewColumnNames
      }
      # Grab only the lines we need 
      $a[$HeaderLines..($a.count - $FooterLines)] | `
      foreach{Add-Content -path $DestinationFilePath -value $_}
  }
  else {
      Write-Error "$SourceFilePath does not exist."
  }
} # End of function

Create-CSV -HeaderLines 9 `
          -FooterLines 4 `
          -SourceFilePath 'c:\temp\test.out' `
          -DestinationFilePath 'c:\temp\test.csv' `
          -NewColumnNames "Nodename,hostname,tcpipaddress"

Enjoy!

Central Ohio PowerShell Users Group

Please join us for our first Central Ohio PowerShell Users Group on Thursday, July 30th, 2009 at the Ohio State University Medical Center. Jeffery Hicks, Microsoft MVP will be joining us! For more information, visit the Central Ohio PowerShell Users Group site.

Searching for files remotely

We are looking to possibly centralize the management of our OPT files. Thought maybe a quick script in PowerShell would do the trick. Ran the following line:

Get-childitem \\server1-vp01\c$ -Include *.opt -Recurse

and waited, waited, waited...... 6 minutes later the console displayed my results.

Knowing a little bit about WMI, I decided to approach this from a different angle:

Get-WmiObject `
-class CIM_DATAFile `
-computername 'server-vp01' `
-filter "extension='opt' and drive='c:'"

This gave me back the results in 2.73 seconds!

Comparing the times generated the following:

measure-command {`
get-childitem \\server-vp01\c$ `
-Include *.opt `
-Recurse}

Days              : 0
Hours             : 0
Minutes           : 6
Seconds           : 3
Milliseconds      : 476
Ticks             : 3634762131
TotalDays         : 0.00420690061458333
TotalHours        : 0.10096561475
TotalMinutes      : 6.057936885
TotalSeconds      : 363.4762131
TotalMilliseconds : 363476.2131

measure-command {`
Get-WmiObject `
-class CIM_DATAFile `
-computername 'server-vp01' `
-filter "extension='opt' and drive='c:'"}

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 2
Milliseconds      : 739
Ticks             : 27390035
TotalDays         : 3.17014293981481E-05
TotalHours        : 0.000760834305555556
TotalMinutes      : 0.0456500583333333
TotalSeconds      : 2.7390035
TotalMilliseconds : 2739.0035

Pretty obvious which method to use.
Once again, PowerShell and WMI save the day.

PowerShell Formatting Error

I was looking throught the PowerGUI forum yesterday when I saw a post similar to this:

$processes = Get-Process -name m*
$drives = Get-Wmiobject `
-class win32_logicaldisk `
-Filter "DriveType=3"

$processes
$driveinfo | Format-Table -autosize

When you run this code in an editor, you are likely to get the following error:

out-lineoutput : Object of type "Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" is not legal or not in the correct sequence. This is likely caused by a user-specified "format-tabl e" command which is conflicting with the default formatting.

After a few minutes of research, I see that this has been registered on Microsoft Connect as a bug. A quick, easy fix is to pass the $processes object down the pipline to Out-Default. ( $processes | Out-Default )

Hope this helps!

TechED 2009 (Pics)

Had a great time at TechED. I met a lot of great folks including quite a few from the PowerScript community. Following are a few pics from TechED - specifically the Birds of a Feather Session that Hal and Steven hosted (Practical PowerShell: Best practices from the field - Check out the recorded podcast of the session!).


Hal Rottenberg, PowerShell MVP & Steven Murawski hosting the BOF.


Hal fielding some questions.


Kirk Munro, PowerShell MVP (http://poshoholic.com/)


From left to right, Steven Murawski, the back of Ed Wilson (1/2 of the Scripting Guys) and John Merrill (IT content evangelist and publishing manager in the Windows Server and Solutions Division User Assistance group).


Steven Murawski getting ready to work the PowerShell booth.


Ed Wilson evangelizing the merits of PowerShell.


Had a great time at the TechED, especially at the BOF and at the PowerShell Dinner.

File Migration with PowerShell

Was recently tasked with assisting our file migration project. Until the following script (assumes you are using Quest AD Commandlets), this was a manual process.

$Users = get-content -path c:\users.txt
foreach ($User in $Users) {
    $SourceFolder = "\\HumanResources\vol10\Users\$User"
    $NewFolderName = "$SourceFolder-Migrated"

    $homeDir = "\\personal-p01\users$\" + $User.substring(0,1) + "\$User"
    Copy-Item $SourceFolder -Destination $homeDir -Recurse

    Set-QADUser $User -ObjectAttributes @{'HomeDirectory'=$homeDir; 'HomeDrive'= 'P:'}
    $rule=new-object System.Security.AccessControl.FileSystemAccessRule("OSUMC\$User","FullControl","Allow")

    foreach ($file in $(Get-ChildItem $homeDir -recurse)) {
        $acl=get-acl $file.FullName
        $acl.SetAccessRule($rule)
        set-acl $File.Fullname $acl
    }
    # set the acl on the root folder
    set-acl $homeDir $acl
   
    # Rename-item doesn't work, so copy and delete
    Copy-item $SourceFolder -Destination "$SourceFolder-migrated" -Recurse
    Remove-Item $SourceFolder -Recurse -Force
}

Enjoy!

Using PowerShell, LogParser and PowerGadgets to get Exchange 2003 storage information – Part 1

Was asked this week to write up a script that would graphically show how much free space was available in our Exchange Databases. My twitter colleague Alan Renouf made me aware of Jonathan Medds's post about Exchange free space. This gave me a good starting point. I now knew to look for EventID 1221 in the Exchange server(s) application event log. Being a big fan of LogParser, I decided to query the event logs using it instead of Get-WMIObject Win32_NTLogEvent.  My reasoning for this is two-fold, I have a lot of LogParser scripts that I am able to reuse in PowerShell.  Additionally, for many log parsing tasks, LogParser still is faster then using Select-String or other parsing methods. 

I highly suggest that if you are entertaining the idea of using call LogParser within PowerShell, you should copy David Muegge’s LogParser library into your code base.  Check out his posting for an explanation on how his library works (it is slick).

function Get-ExchangeEventLogRecords {

# Include our LogParser library
# Check out David Muegge's Blog
# http://muegge.com/blog/?p=65
# for more info on his library
    .\LogParser_Lib.ps1

# This is the query we will be passing
# to LogParser
# Let's hear it for Here-Strings!
$query = @"
SELECT ComputerName, timewritten, Message
FROM \\Exchange01\application
WHERE eventID = 1221 and
SourceName = 'MSExchangeIS Mailbox Store' and
to_date(timewritten) = System_Date()
"@
$inputformat = Get-LPInputFormat "evt"
$records = Get-LPRecordSet $query $inputformat
return $records
}

The function Get-ExchangeEventLogRecords returns all the records that I care about (in this example I have included only one Exchange Server). In order to get the exact fields I want, I need to parse the Message field from the event log.  The field looks something like this:

The database "MSXC01SG4\MSXC01SG4DB4" has 4626 megabytes of free space after online defragmentation has terminated…

The next function takes care of the parsing and creates a custom object for me.

function Format-ExchangeEventLogObject {
    Begin {
        $regex = [regex] '(?:MSXC\w+\\MSXC\w+)'
        $regex1 = [regex] '(?:\s\d+)' }
 
    Process {
        $Store = $regex.Match($_.message) | select Value
        $Mem = $regex1.Match($_.message) | select Value
        $Mem = $Mem.value.ToString().Trim()
        $obj = New-Object psObject
        $obj | Add-Member NoteProperty TimeWritten $_.TimeWritten
        $obj | Add-Member NoteProperty ExchangeServer $_.ComputerName
        $obj | Add-Member NoteProperty Store $Store.Value
        $obj | Add-Member NoteProperty AvailableMem $Mem
        Write-Output $obj }
}

One thing to note, so far, we have only looked at the available space within the *.EDB file.  In the next post, I will take this information and gather a few more relevant fields (total size of the EDB file, size of STM file, estimated availability in the STM file) and show a graphical representation of the data.

Enjoy!