My script has partial output ... Need help

rated by 0 users
This post has 8 Replies | 1 Follower

Top 100 Contributor
Posts 28
jbvu2k Posted: 03-01-2012 11:23 AM

This script is pulling info from a Domain Controller text file and output to an exel. I don't get ouput for "OperationsMasterRoles" (Column 6) and first IP addresses of "Forwarders" (column 8), should be multiple Ip addresses. Thanks in advance for look into this.

========================================================

$a = New-Object -comobject Excel.Application
$a.visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "DC Name"
$c.Cells.Item(1,2) = "IPv4 Address"
$c.Cells.Item(1,3) = "Global Catalog"
$c.Cells.Item(1,4) = "Operating System"
$c.Cells.Item(1,5) = "OS Service Pack"
$c.Cells.Item(1,6) = "OperationMasterRoles"
$c.Cells.Item(1,7) = "Site"
$c.Cells.Item(1,8) = "Forwarders"
$c.Cells.Item(1,9) = "Last Boot Time"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()
$m = 2

$x = get-content C:\MyPS\DCList.txt
foreach ($i in $x)
{
 $y = Get-ADDomainController -Identity $i | Select-Object Name, IPv4Address, IsGlobalCatalog,       OperatingSystem, OperatingSystemServicePack, OperationMasterRoles, Site
foreach ($j in $y)
 {$c.Cells.Item($m, 1) = $j.Name
   $c.Cells.Item($m, 2) = $j.IPv4Address
         $c.Cells.Item($m, 3) = $j.IsGlobalCatalog
         $c.Cells.Item($m, 4) = $j.OperatingSystem
   $c.Cells.Item($m, 5) = $j.OperatingSystemServicePack
   $c.Cells.Item($m, 6) = $j.OperationMasterRoles
   $c.Cells.Item($m, 7) = $j.Site}

 $f = gwmi -Namespace "root\MicrosoftDNS" -Class MicrosoftDNS_Server -ComputerName $i | Select-Object "Forwarders"
foreach ($j in $f)
 {$c.Cells.Item($m, 8) = $j.Forwarders}

$date = new-object -com WbemScripting.SWbemDateTime
$z = get-wmiobject Win32_OperatingSystem -computername $i
foreach ($k in $z)
{$date.value = $k.lastBootupTime
If ($k.Version -eq "*" )
{$c.Cells.Item($m, 9) = $Date.GetVarDate($True)}
Else
{$c.Cells.Item($m, 9) = $Date.GetVarDate($False)}
}
$m = $m + 1
}

 =========================================================

Top 100 Contributor
Posts 32

My DC apparently doesn't have any master roles, so I cannot fully test this, but it might be one of these...

 

$c.cells.item($m,6)=$j | select -expand OperationMasterRoles

 

Try that out and let us know

Top 100 Contributor
Posts 28

It returned "1" in the operationmasterroles column but not specify the role names .

For the " forwarders", $c.Cells.Item($m, 8) = $j.Forwarders , how do I get all ipaddresses?

 

Top 100 Contributor
Posts 28

I still need help on this.... Thanks[:'(]

Top 10 Contributor
Posts 628
Microsoft MVP
Top Contributor

you cannot write arrays to excel cells. So if one of the properties is an array, you first need to convert it to a string.

You can use the -join operator for that. So if $object.Property contained an array (multiple values), you would use something like this:

... = ($object.Property -join ',')

This would get you a comma-separated list.

Top 100 Contributor
Posts 28

Dr. Tobias,

Very appreciated your time. -Join has resolved the multiple value.Big Smile

One last thing is can you elaborate a bit more on the OperationsMasterRoles since I still can't get this output. Thanks

Top 10 Contributor
Posts 628
Microsoft MVP
Top Contributor

From the top of my head, you may want to have PowerShell convert that value to readable text like so:

$text = $xyz.OperationMasterRole | Out-String

Then, the content in $text should show up in Excel when you add it to a cell.

Top 100 Contributor
Posts 28

Here is my final version and many thanks to StarStarStarTobias StarStarStar

=================================================

$a = New-Object -comobject Excel.Application
$a.visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "DC Name"
$c.Cells.Item(1,2) = "IPv4 Address"
$c.Cells.Item(1,3) = "Global Catalog"
$c.Cells.Item(1,4) = "Operating System"
$c.Cells.Item(1,5) = "OS Service Pack"
$c.Cells.Item(1,6) = "OperationMasterRoles"
$c.Cells.Item(1,7) = "Site"
$c.Cells.Item(1,8) = "Forwarders"
$c.Cells.Item(1,9) = "Last Boot Time"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True
$d.EntireColumn.AutoFit()
$d.EntireRow.AutoFit()
$m = 2

$x = get-content C:\MyPS\DCList.txt
foreach ($i in $x)
{
 $y = Get-ADDomainController -Identity $i | Select-Object Name, IPv4Address, IsGlobalCatalog, OperatingSystem, OperatingSystemServicePack, OperationMasterRoles, Site
foreach ($j in $y)
 {$c.Cells.Item($m, 1) = $j.Name
   $c.Cells.Item($m, 2) = $j.IPv4Address
   $c.Cells.Item($m, 3) = $j.IsGlobalCatalog
   $c.Cells.Item($m, 4) = $j.OperatingSystem
   $c.Cells.Item($m, 5) = $j.OperatingSystemServicePack
   $opmr = $j.OperationMasterRoles | Out-String
   $c.Cells.Item($m, 6) = $opmr
   $c.Cells.Item($m, 7) = $j.Site}

 $f = gwmi -Namespace "root\MicrosoftDNS" -Class MicrosoftDNS_Server -ComputerName $i | Select-Object "Forwarders"
foreach ($j in $f)
 {$c.Cells.Item($m, 8) = ($j.Forwarders -Join ',')}

$date = new-object -com WbemScripting.SWbemDateTime
$z = get-wmiobject Win32_OperatingSystem -computername $i
foreach ($k in $z)
{$date.value = $k.lastBootupTime
If ($k.Version -eq "*" )
{$c.Cells.Item($m, 9) = $Date.GetVarDate($True)}
Else
{$c.Cells.Item($m, 9) = $Date.GetVarDate($False)}
}
$m = $m + 1
}

 

Top 10 Contributor
Posts 628
Microsoft MVP
Top Contributor

Congrats! Smile

Page 1 of 1 (9 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.