Converting Excel to CSV using PowerShell

February 14th, 2010

Quick Script: Converting Excel[.xls] to Comma Separated Value[.csv]

Many times my data is in .xls format and I really need it in .csv to leverage the accessibility of the Import-CSV cmdlet.

I found this code on the ‘PowerShellCommunity.org’ web forum: Link

Code:

$xlCSV=6
$Excelfilename = "file.xls"
$CSVfilename = "file.csv"
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $False
$Excel.displayalerts=$False
$Workbook = $Excel.Workbooks.Open($ExcelFileName)
$Workbook.SaveAs($CSVfilename,$xlCSV)
$Excel.Quit()
If(ps excel){kill -name excel}

I have only tested this with Office 2003 Sp2. I will try with 2007/2010 and post my findings.

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , ,
Posted in PowerShell | No Comments »

VMWare Fusion 3.X – TCP\IP and DHCP Configuration

February 9th, 2010

How To: Modify VMWare Fusion 3.x Network Configurations and DHCP Configuration.

I am in the process of building a Microsoft Windows lab environment within VMWare Fusion 3 on my MAC.

I want to run an isolated “Host Based” network environment utilizing the DHCP services within VMWare Fusion.

Tasks:

  1. Change VMNET Interface IP Addresses
  2. Change the DHCP settings for the ‘host network only’ interface VMNET1 to enable correct DHCP based options and static leases for networking in Windows.

I found these articles and they helped a great deal;

There are obviously several methods for this and I have tried to summarize the configuration steps below that I implemented for a successful Windows Network.

- Step 1)
Modify VMNET interface IP Addresses using ‘VMWare 3.x Virtual Network Configuration Instructions.pdf’

Open a Terminal window in OSX.

Change to the VMWare Fusion directory.

  • cd /Library/Application\ Support/VMware\ Fusion/

Stop VMWare interfaces.

  • sudo ./vmnet-apps.sh –stop

Verify VMNET interface are no longer available.

  • ifconfig -a

Edit the ‘locations’ file to change the IP address values

  • sudo nano locations

Example:

remove_answer VNET_1_HOSTONLY_HOSTADDR
remove_answer VNET_1_HOSTONLY_NETMASK
answer VNET_1_HOSTONLY_HOSTADDR 10.0.1.1
answer VNET_1_HOSTONLY_NETMASK 255.255.255.0
remove_answer VNET_1_DHCP
answer VNET_1_DHCP yes
remove_answer VNET_8_HOSTONLY_HOSTADDR
remove_answer VNET_8_HOSTONLY_NETMASK
answer VNET_8_HOSTONLY_HOSTADDR 10.0.8.1
answer VNET_8_HOSTONLY_NETMASK 255.255.255.0
remove_answer VNET_8_NAT
answer VNET_8_NAT yes
remove_answer VNET_8_DHCP
answer VNET_8_DHCP yes

Once edited successfully, type Ctrl-O and then Enter to save. Ctrl-X to exit.

Start VMWare Interfaces.

  • sudo ./vmnet-apps.sh –start

Verify VMNET interfaces are now available with the new IP addresses.

  • ifconfig -a

- Step 2)
Editing the DHCP configuration for VMNET1(host only) to issue the correct domain-name, domain-name-server options and static leases.

  • cd /library/application\ support/vmware\ fusion/vmnet1

Backup your dhcpd.conf file.

  • cp dhcpd.conf dhcpd.conf.backup

Edit your dhcpd.conf file.

  • sudo nano dhcpd.conf

To allow name resolution in Active Directory you want to set the DHCP option domain-name-servers and domain-name for the DHCP scope and the for the VMNET1 adapter.
Options to set for the DHCP scope and VMNET1 adapter. (This would probably be your 1st DC in the domain.).

option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";

Adding a reservation for a VM Guest. You will need to know the systems Host Name, MAC and TCP/IP Address.

Add the below text for each server after the “####### VMNET DHCP Configuration. End of “DO NOT MODIFY SECTION” #######” statement. When assigning a TCP/IP address to a guest ensure that it is outside of the DHCP scope.

Example: (reservation for Server01)

host Server01 {
hardware ethernet 00:00:00:00:00:00;
fixed-address 10.1.0.1;
option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";
}

Example: (completed dhcpd.conf file)

# Configuration file for ISC 2.0 vmnet-dhcpd operating on vmnet1.
#
# This file was automatically generated by the VMware configuration program.
# See Instructions below if you want to modify it.
#
# We set domain-name-servers to make some DHCP clients happy
# (dhclient as configured in SuSE, TurboLinux, etc.).
# We also supply a domain name to make pump (Red Hat 6.x) happy.
#
###### VMNET DHCP Configuration. Start of "DO NOT MODIFY SECTION" #####
# Modification Instructions: This section of the configuration file contains
# information generated by the configuration program. Do not modify this
# section.
# You are free to modify everything else. Also, this section must start
# on a new line
# This file will get backed up with a different name in the same directory
# if this section is edited and you try to configure DHCP again.
# Written at: 01/11/2010 00:31:51
allow unknown-clients;
default-lease-time 1800;                # default is 30 minutes
max-lease-time 7200;                    # default is 2 hours

subnet 10.1.0.0 netmask 255.255.255.0 {
range 10.1.0.128 10.1.0.254;
option broadcast-address 10.1.0.255;
option domain-name-servers 10.1.0.1;
option domain-name "addomaincom";
default-lease-time 1800;                # default is 30 minutes
max-lease-time 7200;                    # default is 2 hours
}
host vmnet1 {
hardware ethernet 00:50:56:C0:00:01;
fixed-address 10.1.0.1;
option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";
}
####### VMNET DHCP Configuration. End of "DO NOT MODIFY SECTION" #######
host Server01 {
hardware ethernet 00:00:00:00:00:00;
fixed-address 10.1.0.1;
option domain-name-servers 10.1.0.1;
option domain-name "addomain.com";
}

Once edited successfully, type Ctrl-O and then Enter to save. Ctrl-X to exit.

Restart VMWare daemon’s.

  • sudo /library/application\ support/vmware\ fusion/boot.sh –restart

Once restarted you should restart your Virtual Machines that you have assigned the reserved leases to.

If you have problems with failures when restarting the VMWare daemon’s you may have a badly formatted dhcpd.conf file, restore the file from your backup and try again.

If you have issues getting the reserved IP addresses, try renewing their leases.

  • C:\> IPConfig /release
  • C:\> IPConfig /renew

The above process worked for me but if you have issues either post a comment and I will try to help or i recomend the VMWare community web site mentioned above.

Note: Please backup all files before editing them!

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , , ,
Posted in VMWare | No Comments »

Quest AD Management Shell – Moving AD User Objects

January 28th, 2010

Code Snippet: Moving disable user objects in Active Directory using PowerShell and Quest Management cmdlets.Task:

Task: Moving all disabled users in the ‘/Users’ OU to the /Users/Disabled OU.

Code:

Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue
$Users = Get-QADUser -SearchRoot 'ad.domain.com/Users/' -Disabled
Write-Host "Moving $Users.Count Users."
ForEach($User In $Users){
Move-QADObject $User.DN -NewParentContainer 'ad.domain.com/Users/Disabled'}

Hope this helps

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , ,
Posted in AD, PowerShell, Quest | No Comments »

Exchange 2010 on a Windows 2008 R2 Domain Controller

January 27th, 2010

Issue: “Service ‘MSExchangeTransport’ failed to reach  status ‘Running’ on this server.”

I am building my virtual lab using VMWare Fusion at home, It consists of a Windows 2008 R2 server with AD Domain Services installed and Exchange 2010.

Please Note:

I am running AD (Global Catalog) and Exchange 2010 on the same server and this is not a recommended configuration by Microsoft.

Possible Issues

After the installation of Windows 2008 R2 and AD Domain Services, I have installed all prerequisites for Exchange 2010 and performed the Exchange 2010 install using the install wizard. I chose to install Exchange Management Tools, Hub, CAS and Mailbox to have a nice contained environment.

However during the install of the ‘Hub Transport Role’ I got this failure;

The execution of: “$error.Clear(); if ($RoleStartTransportService) { start-SetupService -ServiceName MSExchangeTransport }”, generated the following error: “Service ‘MSExchangeTransport’ failed to reach  status ‘Running’ on this server.”

And this error appeared in the Application Event log.

Source: MSExchange ADAccess
Event ID: 2114
Task Category: Topology
Level: Error
Description:
Process MSEXCHANGEADTOPOLOGYSERVICE.EXE (PID=1784). Topology discovery failed, error 0x80040a02 (DSC_E_NO_SUITABLE_CDC). Look up the Lightweight Directory Access Protocol (LDAP) error code specified in the event description. To do this, use Microsoft Knowledge Base article 218185, “Microsoft LDAP Error Codes.” Use the information in that article to learn more about the cause and resolution to this error. Use the Ping or PathPing command-line tools to test network connectivity to local domain controllers.

After finding several mentions of security changes and group membership alterations including domain policy issues I found this article from ‘Rui Silver’ in his blog. He experienced the very same issue while installing Exchange 2007 on Windows 2008 and it was a result of Exchange requiring IPv6. I had disabled it on the interface properties while building my Windows 2008 R2 server believing I would not need it.

After I re-enabled IPv6 on the interface and left it to obtain an IPv6 address automatically by default, rebooted the system and like magic the exchange services could start and I could complete the installation of all Exchange 2010 roles.

You can disable IPv6 permanently before installing Exchange and there are several articles out there. (I have not tested either of these.)

  1. Windows Server Forums
  2. Exchange Server Share

Resources:

  1. Windows 2008 R2 Server
  2. Active Directory Domain Services
  3. Exchange 2010 Prerequisites

Please note that this is a basic overview of my server and it is not intended as a recommendation or standard to be used by anyone else.

Hope this helps

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , , ,
Posted in AD, Exchange 2010, Windows 2008 R2 | 1 Comment »

AD and Kerberos Token Bloat – Analyzing Group Nesting

January 25th, 2010

How To: Analyze Nested AD Groups.

I am working on a Kerberos Token size issue (Token Bloat) and need to accurately depict a groups nested members for token size analysis and remediation.

Brief Token Bloat Explanation:

Token Bloat is where due to several factors including deep group nesting the Kerberos token utilized in AD for resource authentication bloats towards the set limit or even beyond. This can cause larger logon times to resources and even issues in applications like Exchange 2003 that can only exist on a 32Bit OS.

I came across this article on the “Microsoft Active Directory Group PowerShell” blog, it has a great script for analysing nested group memberships. Unfortunately to use the PowerShell script you need to be running Windows 2008 servers for the Active Directory cmdlets to be available. The environment that I required the script for was not W2008 so I converted the script to use the Quest cmdlets available with the Quest Active Roles Management PSSnapIn.

You will need to have these installed before using this script.

The script originally written by the MS AD PowerShell team, provides a visual tree view of the groups and their nested levels, a ‘MaxNestingLevel’ value and a ‘NestedGroupMembershipCount’.

Example:

PS C:\> Get-QADGroupNesting.ps1 group-name

Or

PS C:\> Get-QADGroupNesting.ps1 group-name -ShowTree

The above example not only displays group statistics it also plots the structure of the group in a tree format.

Download Script at PoshCode.org

Code:

##########Copy the below script into a new file called Get-ADGroupNesting.ps1
 
Param (
    [Parameter(Mandatory=$true,
        Position=0,
        ValueFromPipeline=$true,
        HelpMessage="DN or ObjectGUID of the AD Group."
    )]
    [string]$groupIdentity,
    [switch]$showTree
    )
#Validate Quest PSSnapin is loaded
Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue
$global:numberOfRecursiveGroupMemberships = 0
$lastGroupAtALevelFlags = @() 
 
function Get-GroupNesting ([string] $identity, [int] $level, [hashtable] $groupsVisitedBeforeThisOne, [bool] $lastGroupOfTheLevel)
{
    $group = $null
    $group = Get-QADGroup -Identity $identity -SizeLimit 0
    if($lastGroupAtALevelFlags.Count -le $level)
    {
        $lastGroupAtALevelFlags = $lastGroupAtALevelFlags + 0
    }
    if($group -ne $null)
    {
        if($showTree)
        {
            for($i = 0; $i -lt $level - 1; $i++)
            {
                if($lastGroupAtALevelFlags[$i] -ne 0)
                {
                    Write-Host -ForegroundColor Blue -NoNewline "  "
                }
                else
                {
                    Write-Host -ForegroundColor Blue -NoNewline "│ "
                }
            }
            if($level -ne 0)
            {
                if($lastGroupOfTheLevel)
                {
                    Write-Host -ForegroundColor Blue -NoNewline "└─"
                }
                else
                {
                    Write-Host -ForegroundColor Blue -NoNewline "├─"
                }
            }
            Write-Host -ForegroundColor Blue $group.Name
        }
        $groupsVisitedBeforeThisOne.Add($group.DN,$null)
        $global:numberOfRecursiveGroupMemberships ++
        $groupMemberShipCount = $group.memberOf.Count
        if ($groupMemberShipCount -gt 0)
        {
            $maxMemberGroupLevel = 0
            $count = 0
            foreach($groupDN in $group.memberOf)
            {
                $count++
                $lastGroupOfThisLevel = $false
                if($count -eq $groupMemberShipCount){$lastGroupOfThisLevel = $true; $lastGroupAtALevelFlags[$level] = 1}
                if(-not $groupsVisitedBeforeThisOne.Contains($groupDN)) #prevent cyclic dependancies
                {
                    $memberGroupLevel = Get-GroupNesting -Identity $groupDN -Level $($level+1) -GroupsVisitedBeforeThisOne $groupsVisitedBeforeThisOne -lastGroupOfTheLevel $lastGroupOfThisLevel
                    if ($memberGroupLevel -gt $maxMemberGroupLevel){$maxMemberGroupLevel = $memberGroupLevel}
                }
            }
            $level = $maxMemberGroupLevel
        }
        else #we've reached the top level group, return it's height
        {
            return $level
        }
        return $level
    }
}
$global:numberOfRecursiveGroupMemberships = 0
$groupObj = Get-QADGroup -Identity $groupIdentity -SizeLimit 0
if($groupObj)
{
    [int]$maxNestingLevel = Get-GroupNesting -Identity $groupIdentity -Level 0 -GroupsVisitedBeforeThisOne @{} -lastGroupOfTheLevel $false
 Add-Member -InputObject $groupObj -MemberType NoteProperty  -Name MaxNestingLevel -Value $maxNestingLevel -Force
 Add-Member -InputObject $groupObj -MemberType NoteProperty  -Name NestedGroupMembershipCount -Value $($global:numberOfRecursiveGroupMemberships - 1) -Force
 $groupObj | Select-Object Name,DN,MaxNestingLevel,NestedGroupMembershipCount | Format-List
}

Hope this helps
All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , , , , , ,
Posted in AD, Exchange 2003, PowerShell, Quest | 1 Comment »

Scanning Active Directory for SMTP Addresses

January 19th, 2010

How To: Scanning Active Directory for SMTP Addresses.

When an object in AD is mail enabled it is assigned an SMTP address. Sometimes these addresses are assigned incorrectly to objects or a requested address is already in use on a object and you need to find the object that it is assigned to. I come across this situation sometimes and the simplest way is to query AD for the SMTP address. The SMTP addresses are stored in an AD attribute called “proxyAddresses” this holds all addresses including SMTP. Using the QUEST PowerShell PSSnapIn, you can query AD for this attribute using an LDAP filter.

Querying AD using Quest cmdlets and LDAP query.

Get-QADObject -LdapFilter "(proxyAddresses=*$Address*)" -IncludedProperties "proxyAddresses"

Using the above command with perform the filter, however you will only pull back the default cached set of attributes for the object so I add the ‘-includeallproperties’ switch to pull all attributes available for the object back into the cache.
I also set the ‘-SizeLimit 0′ so the result set isn’t limited to the standard 1000 returned objects.

The below code turns the basic shell command into a ‘Function’ called ‘Get-ProxyAddress.ps1′.
Instructions:

  1. Copy the below code into a file called ‘Get-ProxyAddress.ps1′
  2. Open the PowerShell shell and traverse to the directory that hosts the above file
  3. Run the script using this syntax, “./Get-ProxyAddress.ps1 address@domain.com”
  4. address@domain.com being the SMTP address you are searching for.

The output details either that the SMTP address is not found in AD or the below attributes are listed for the object that own the SMTP address.

  • Name
  • DisplayName
  • ObjectClass
  • Email
  • AccountisDisabled
  • AccountisLockedOut
  • MailNickName
  • LegacyExchangeDN

Download Script from PoshCode.org

Code:

Param (
    [Parameter(Mandatory=$true,
        Position=0,
        ValueFromPipeline=$true,
        HelpMessage="Enter SMTP address to search for in Active-Directory."
    )]
    [string]$objSMTP
	)
Function Get-ProxyAddresses ([string]$Address){
$objAD = $null
$objAD = Get-QADObject -LdapFilter "(proxyAddresses=*$Address*)" -IncludeAllProperties -SizeLimit 0 -ErrorAction SilentlyContinue
Write-Output $objAD
}#Close Function
#Validate Quest PSSnapin is loaded
Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue
#Run Function to search AD for SMTP address
$Results = $null
$Results = Get-ProxyAddresses -Address $objSMTP | Select-Object Name,DisplayName,ObjectClass,Email,AccountisDisabled,AccountisLockedOut,MailNickName,LegacyExchangeDN -ErrorAction SilentlyContinue
IF($Results -eq $null){
Write-Host ""
Write-Host "No Object Found with .attribute[proxyAddress] containing $objSMTP."}
Else{$Results | Format-List *}
#End

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , , , , ,
Posted in Exchange 2003, PowerShell, Quest | No Comments »

Getting Virtual Machine CPU, Disk and Memory

October 22nd, 2009

I was asked to pull disk, memory and cpu statistics from VMware guests managed by multiple Virtual Center servers. Working with the VMWare Senior Administrator, we came up with this.

$CsvFile = "C:\VM_Hosts_Info.csv"
$CsvData = @()
$VCSrvs = "VCenter01","VCenter02","VCenter03"
ForEach ($VCSrv in $VCSrvs){
Connect-VIServer -server $VCSrv
Get-VM | % {
$VM = $_
$Disk_T = "{0:N1}" -f(($VM | Get-HardDisk | measure-object -property CapacityKB -sum).Sum/1mb)
$Row = "" | Select Name,Environment,MemoryMB,NumCPU,Total_DiskGB
$Row.Name = $VM.Name
$Row.Environment = $VCSrv
$Row.MemoryMb = $VM.MemoryMb
$Row.NumCpu = $VM.NumCpu
$Row.Total_DiskGB = $Disk_T
$CsvData += $Row
}}
$CsvData | Export-Csv $CsvFile -noTypeInformation

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , , ,
Posted in PowerShell, VMWare | No Comments »

Getting Remote Registry WSUS Config

September 25th, 2009

Using PowerShell to examine the registry is a simple process but to really get the power from PowerShell you need the data in an object.
Once in the object it is far easier to manipulate the data in a fashion that you are used to in the PowerShell command line.

I used the below PowerShell script to verify the “Automatic Update” configuration on a Windows 2003 Sp2 Server. The servers being analyzed are stored in a .txt file on the C:\ partition.
The path of the input list file is stored in the $File string. This method of using a custom object can be very usefull for non-object based data.

Code:

$WSUSREGALL = @()
[String]$File = "C:\server.txt"
$Servers = Get-Content $File
ForEach($Server In $Servers)
{
$Registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Server)
$RegKey0 = $Registry.OpenSubKey("Software\policies\Microsoft\Windows\WindowsUpdate\" )
$RegKey1 = $Registry.OpenSubKey("Software\policies\Microsoft\Windows\WindowsUpdate\AU\")
 
$WSUSREG = New-Object System.Object
$WSUSREG | Add-Member -MemberType NoteProperty -Name "Server" -Value $Server
$WSUSREG | Add-Member -MemberType NoteProperty -Name WUServer -Value $RegKey0.GetValue("WUServer")
$WSUSREG | Add-Member -MemberType NoteProperty -Name WUStatusServer -Value $RegKey0.GetValue("WUStatusServer")
$WSUSREG | Add-Member -MemberType NoteProperty -Name TargetGroupEnabled -Value $RegKey0.GetValue("TargetGroupEnabled")
$WSUSREG | Add-Member -MemberType NoteProperty -Name AcceptTrustedPublisherCerts -Value $RegKey0.GetValue("AcceptTrustedPublisherCerts")
$WSUSREG | Add-Member -MemberType NoteProperty -Name AUOptions -Value $RegKey1.GetValue("AUOptions")
$WSUSREG | Add-Member -MemberType NoteProperty -Name NoAutoUpdate -Value $RegKey1.GetValue("NoAutoUpdate")
$WSUSREG | Add-Member -MemberType NoteProperty -Name NoAUShutdownOption -Value $RegKey1.GetValue("NoAUShutdownOption")
$WSUSREG | Add-Member -MemberType NoteProperty -Name NoAUAsDefaultShutdownOption -Value $RegKey1.GetValue("NoAUAsDefaultShutdownOption")
$WSUSREG | Add-Member -MemberType NoteProperty -Name ScheduledInstallDay -Value $RegKey1.GetValue("ScheduledInstallDay")
$WSUSREG | Add-Member -MemberType NoteProperty -Name ScheduledInstallTime -Value $RegKey1.GetValue("ScheduledInstallTime")
$WSUSREG | Add-Member -MemberType NoteProperty -Name UseWUServer -Value $RegKey1.GetValue("UseWUServer")
$WSUSREG | Add-Member -MemberType NoteProperty -Name RescheduleWaitTimeEnabled -Value $RegKey1.GetValue("RescheduleWaitTimeEnabled")
$WSUSREG | Add-Member -MemberType NoteProperty -Name RescheduleWaitTime -Value $RegKey1.GetValue("RescheduleWaitTime")
$WSUSREG | Add-Member -MemberType NoteProperty -Name NoAutoRebootWithLoggedOnUsers -Value $RegKey1.GetValue("NoAutoRebootWithLoggedOnUsers")
$WSUSREG | Add-Member -MemberType NoteProperty -Name DetectionFrequencyEnabled -Value $RegKey1.GetValue("DetectionFrequencyEnabled")
$WSUSREG | Add-Member -MemberType NoteProperty -Name DetectionFrequency -Value $RegKey1.GetValue("DetectionFrequency")
$WSUSREG | Add-Member -MemberType NoteProperty -Name AutoInstallMinorUpdates -Value $RegKey1.GetValue("AutoInstallMinorUpdates")
$WSUSREG | Add-Member -MemberType NoteProperty -Name RebootWarningTimeoutEnabled -Value $RegKey1.GetValue("RebootWarningTimeoutEnabled")
$WSUSREG | Add-Member -MemberType NoteProperty -Name RebootWarningTimeout -Value $RegKey1.GetValue("RebootWarningTimeout")
$WSUSREG | Add-Member -MemberType NoteProperty -Name RebootRelaunchTimeoutEnabled -Value $RegKey1.GetValue("RebootRelaunchTimeoutEnabled")
$WSUSREG | Add-Member -MemberType NoteProperty -Name RebootRelaunchTimeout -Value $RegKey1.GetValue("RebootRelaunchTimeout")
$WSUSREG | Add-Member -MemberType NoteProperty -Name IncludeRecommendedUpdates -Value $RegKey1.GetValue("IncludeRecommendedUpdates")
$WSUSREG | Add-Member -MemberType NoteProperty -Name AUPowerManagement -Value $RegKey1.GetValue("AUPowerManagement")
$WSUSREGALL += $WSUSREG
}
$WSUSREGALL
$WSUSREGALL | Export-Csv "C:\DataWSUS.csv" -NoTypeInformation

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

Tags: , , , , , , ,
Posted in PowerShell, Windows 2003 | No Comments »

Exporting Data to Excel using PowerShell

August 20th, 2009

I 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.

Tags: , , ,
Posted in PowerShell, Windows 2003 | No Comments »

Disconnected Mailboxes in Exchange 2003

August 19th, 2009

I 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.

Tags: , , , , , , ,
Posted in Exchange 2003, PowerShell | No Comments »

« Older Entries
Newer Entries »