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.txtforeach ($i in $x){ $y = Get-ADDomainController -Identity $i | Select-Object Name, IPv4Address, IsGlobalCatalog, OperatingSystem, OperatingSystemServicePack, OperationMasterRoles, Siteforeach ($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 $iforeach ($k in $z){$date.value = $k.lastBootupTimeIf ($k.Version -eq "*" ){$c.Cells.Item($m, 9) = $Date.GetVarDate($True)}Else{$c.Cells.Item($m, 9) = $Date.GetVarDate($False)}}$m = $m + 1}
=========================================================
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
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?
I still need help on this.... Thanks[:'(]
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.
Dr. Tobias,
Very appreciated your time. -Join has resolved the multiple value.
One last thing is can you elaborate a bit more on the OperationsMasterRoles since I still can't get this output. Thanks
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.
Here is my final version and many thanks to Tobias
=================================================
$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.txtforeach ($i in $x){ $y = Get-ADDomainController -Identity $i | Select-Object Name, IPv4Address, IsGlobalCatalog, OperatingSystem, OperatingSystemServicePack, OperationMasterRoles, Siteforeach ($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 ',')}
Congrats!