Archive for August, 2009
Exporting Data to Excel using PowerShell
Thursday, August 20th, 2009I had to get a quick audit of systems and i needed to know OS Version, SP version, Memory and Disk Details.
One problem is that if you don’t know what disks you have exporting this to excel is difficult. I chose to dynamically create the headers for the excel file based on the drive letter names. This approach is by no means perfect as you rely on the fact that people have built the systems with sequential drive letters. Any drive letter out of sequence will disturb the excel header names.
Example: If you have 3 servers and two have letters C: and D: and the third has C: and E: this approach will not work for you. Luckily I know my servers have sequential drive letters.
Code:
$excel = New-Object -comobject Excel.Application $excel.visible = $True $wbook = $excel.Workbooks.Add() $wsheet = $wbook.Worksheets.Item(1) $wsheet.Cells.Item(1,1) = "Date" $wsheet.Cells.Item(1,2) = "Server" $wsheet.Cells.Item(1,3) = "Memory" $wsheet.Cells.Item(1,4) = "OSName" $wsheet.Cells.Item(1,5) = "SPVersion" $iRow = 2 $InputFile = "C:\Server.txt" #Input File Containing Servers to be Scanned $Servers = Get-Content $InputFile ForEach($Server in $Servers) { $wsheet.Cells.Item($iRow,1) = Get-Date $wsheet.Cells.Item($iRow,2) =$Server $AllCS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Server | Select-Object TotalPhysicalMemory ForEach($CS In $AllCS){$wsheet.Cells.Item($iRow,3) = $CS.TotalPhysicalMemory/1GB} $AllOS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Server ForEach($OS In $AllOS){$OSVersion = $OS.Caption;$SPVersion = $OS.CSDVersion} $wsheet.Cells.Item($iRow,4) = $OSVersion $wsheet.Cells.Item($iRow,5) = $SPVersion $logicalDisk = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $Server -Filter "DriveType=3" | Select-Object Caption,Size $j = 6 For($i=0;$i -le $LogicalDisk.Length - 1; $i++){ $wsheet.Cells.Item(1,$j) = $LogicalDisk[$i].Caption $wsheet.Cells.Item($iRow,$j) = $LogicalDisk[$i].Size/1GB $j++} $iRow++ } $range = $wsheet.UsedRange $range.Interior.ColorIndex = 19 $range.Font.ColorIndex = 11 $range.Font.Bold = $True $range.EntireColumn.AutoFilter() $range.EntireColumn.AutoFit() $excel.ActiveWorkbook.SaveAs("C:\Server_Data.xls") $excel.ActiveWorkbook.Close $excel.Application.Quit If (ps excel) { kill -name excel}
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Disconnected Mailboxes in Exchange 2003
Wednesday, August 19th, 2009I was looking into finding all disconnected/orphaned mailboxes in an Exchange2003 org. Normally I would use either an old VBScript I have or the Exchange 2003 ESM. I thought it would be good to experiment with PowerShell and try to simplify the VBScript that I have.
Below is a PowerShell script that utilizes ADSI to gather all Exchange 2003 servers in your configuration container. Then for each server it utilizes WMI to connect to the Root/MicrosoftExchangeV2 ‘NameSpace’ and the Exchange_Mailbox provider. From there we filter on only mailboxes that the “DateDiscoveredAbscentInDS” property is NOT NULL. That data is then passed through the pipeline to a Select-Object where a custom Hash table is built to display the Server Name of the filtered mailboxes. The filtered mailboxes and the selected data is then passed through the pipeline and exported to a .csv file on the root of C:\ named with the format C:\DisconnectedMailboxes_%Servername%.csv.
Code:
[String]$StrFilter = "(objectCategory=msExchExchangeServer)" $objRootDSE = [ADSI]"LDAP://RootDSE" [String]$strContainer = $objRootDSE.configurationNamingContext $objSearcher = New-Object System.DirectoryServices.DirectorySearcher $objSearcher.SearchRoot = New-object System.DirectoryServices.DirectoryEntry("LDAP://$strContainer") $objSearcher.PageSize = 1000 $objSearcher.Filter = $strFilter $objSearcher.SearchScope = "Subtree" $colResults = $objSearcher.FindAll() ForEach ($objResult in $colResults) { [String]$ServerDN = $objResult.Properties.distinguishedname $objServer = [ADSI]"LDAP://$ServerDN" [String]$Server = $objServer.cn Get-WmiObject -NameSpace Root\MicrosoftExchangeV2 -Class Exchange_Mailbox -Computer $Server | ` Where { $_.DateDiscoveredAbsentInDS -ne $null } | ` Select-Object @{N="Server";E={$_.__Server}},` StorageGroupName,StoreName,MailboxDisplayName,Size,DateDiscoveredAbsentInDS | ` Export-Csv "C:\DisconnectedMailboxes_$Server.csv" -NoTypeInformation }#End ForEach
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Scheduling PowerShell Scripts
Sunday, August 16th, 2009Just a quick Post…
Yesterday I had to schedule PowerShell scripts on a Windows 2003 Server, this following technique worked for me but I have not tested it on other OS platform.
Similar to a VBScript in the AT scheduled job you point to a .bat or .cmd file for the Program to run. In this file you have the below code to initialize the script in its shell.
In the .bat or .cmd file you will need the following code.
Powershell -NoLogo -Command "&{C:\Scripts\Posh\Script1.ps1}"
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
Exchange 2007 Mailbox Access Times
Friday, August 14th, 2009Here is a quick one liner to gain the “Logon” and “LogOff” times for all mailboxes hosted on a Exchange 2007 mailbox server. All mailboxes and their access times will be exported into a .csv file for reference.
Note: Replace %ServerName% with a valid Exchange 2007 Mailbox Server in your Org.
Get-Mailbox | Where{$_.ServerName -eq '%ServerName%'} | Get-MailboxStatistics | Select-Object ServerName,StorageGroupName,DatabaseName,DisplayName,LastLogonTime,LastLogOffTime,LastLoggedOnUserAccount | Export-CSV "C:\MailboxAccessTimes.csv" -NoTypeInformation
Hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.
