Avatar of ITguy565
ITguy565Flag for United States of America asked on

Powershell : Query AD and Update Values on SpreadSheet

Experts,

I need some assistance with this powershell script:

function Compare-ADcomputers(){

$tempfile = "C:\temp\ServerInfo.csv"
$Data_Store = ""|select Spreadsheet, spreadsheet_Orig, Spreadsheet_Modified, ActiveDirectory
$Data_Store.ActiveDirectory = $null
$Data_Store.Spreadsheet = $null

$Data_Store.spreadsheet_Orig = Import-Csv $tempfile |select -Property @{name="Name";expression={$($_.Server_Name)}}, *
$Data_Store.ActiveDirectory = Get-ADComputer -filter * -Properties OperatingSystem |? {$_.OperatingSystem -like "*Server*"}|select Name
$Data_Store.Spreadsheet = $Data_Store.spreadsheet_Orig |select -Property @{name="Name";expression={$($_.Server_Name)}}
$data_col = @()
$data_col += $Data_Store.ActiveDirectory
$Data_col += $Data_Store.Spreadsheet

$data_col = $data_col |select Name -Unique

#fall back code for testing 
    #$data_col1 = $data_col


$data_col |Add-Member -MemberType NoteProperty -name ActiveDirectory -value $($null)
$data_col |Add-Member -MemberType NoteProperty -name SpreadSheet -value $($null)
$data_col |Add-Member -MemberType NoteProperty -name Reachable -value $($null)
Function invoke-check($type){
        foreach ($item in $data_col.name){
            $action = "Processing $item"
            write-host -ForegroundColor DarkYellow $action
            if ($($Data_Store.$type|?{$_.name -like "$item"})){
                write-host -ForegroundColor Green "   Found Item In $type"
                ($data_col|? {$_.name -like "*$item*"}).$type = "True"
            }else {write-host -ForegroundColor Red "   Computer NOT found in $type";($data_col|? {$_.name -like "*$item*"}).$type = "False"}
        }
}

Function invoke-check-reachability(){
        foreach ($item in $data_col.name){
            $action = "Processing $item"
            write-host -ForegroundColor DarkYellow $action
            if ($(test-connection -ComputerName $item -Count 1 -Quiet)){
                    write-host -ForegroundColor Green "   Computer was Reachable";
                    ($data_col|? {$_.name -like "*$item*"}).Reachable = "True";
                }else { write-host -ForegroundColor Red "   Computer was NOT Reachable";
                        ($data_col|? {$_.name -like "*$item*"}).Reachable = "False"}
        }
}

. invoke-check -type ActiveDirectory
. invoke-check -type SpreadSheet
. invoke-check-reachability

$reachable = {write-host "The Following Computers are reachable"
             $data_col |? {$_.Reachable -like "*True*"} |Select Name, Reachable}

$Notreachable = {write-host "The Following Computers are Not Reachable"
                $data_col |? {$_.Reachable -like "*False*"} |Select Name, Reachable}

$AD_NotReachable = {write-host "The Following Computers are listed in AD and Not Reachable"
                $data_col |? {(($_.Reachable -like "*False*") -and ($_.ActiveDirectory -like "*True*"))} |Select Name, Reachable}

$Spreadsheet_NotReachable = {write-host "The Following Computers are listed in the Spreadsheet and Not Reachable"
                $data_col |? {(($_.Reachable -like "*False*") -and ($_.Spreadsheet -like "*True*"))} |Select Name, Reachable}

$ActiveDirectory_NotSpreadsheet_Reachable = {#write-host "The Following Servers are listed in the Spreadsheet and Not Reachable"
                $data_col |? {(($_.Reachable -like "*True*") -and ($_.Spreadsheet -like "*False*") -and ($_.ActiveDirectory -like "*True*"))} |Select *}

write-host "The Following Servers are listed ActiveDirectory and not in the Spreadsheet and are Reachable"
&$ActiveDirectory_NotSpreadsheet_Reachable
&$ActiveDirectory_NotSpreadsheet_Reachable |Out-GridView
write-host "Total Count : $((&$ActiveDirectory_NotSpreadsheet_Reachable).count)"

}


$spreadsheettest = $Data_Store.spreadsheet_Orig
Function invoke-Modify-Spreadsheet(){
    
    #modify for Deleted items on Network
    foreach ($item in $(&$Spreadsheet_NotReachable).name){
        $action = "Removing $item from Spreadsheet because they are unreachable"
        write-host -ForegroundColor DarkYellow "$action"
        $spreadsheettest = ($spreadsheettest |? {$_.name -notlike "*$item*"})
    } 
    
    #Modify for Name Discripancy in AD to Spreadsheet
    #object Creation : 
    
    foreach ($item in $(&$ActiveDirectory_NotSpreadsheet_Reachable|select Name)){
        $action = "Adding $item To Spreadsheet because It Exists in AD"
        write-host -ForegroundColor DarkYellow "$action"
        $spreadsheettest += $item
    }
}

. Compare-ADcomputers
. invoke-Modify-Spreadsheet

Open in new window



#$serverinfo.csv
Server_Name,IP Address,Server,Server OS,RAM,OMSA,PW Check,Status,Owner,Role,Rest Proc,Moved,VM Mac,Blade,Chassis,Idrac Information,Server Type,Model Information,Row/Location,Rack Number,CLAN_VL
AN2,ISCSI_1,ISCSI_2,Heart_Beat,Live Migrate,DNS HostName,CreateTimeStamp,CanonicalName,ObjectGUID,ObjectSID,DNSSearchOrder,subnetMask,gateway,serviceTagLookup,UpdateStatus,DomainC
heck,OwnerNode,DistinguishedName
server1,X.X.X.X,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
server2,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
server3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Open in new window




When I run this code everything works until it gets to
#Modify for Name Discripancy in AD to Spreadsheet
    #object Creation : 
    
    foreach ($item in $(&$ActiveDirectory_NotSpreadsheet_Reachable|select Name)){
        $action = "Adding $item To Spreadsheet because It Exists in AD"
        write-host -ForegroundColor DarkYellow "$action"
        $spreadsheettest += $item

Open in new window


at which point $spreadsheettest

receives the values from  $(&$ActiveDirectory_NotSpreadsheet_Reachable|select Name) without adding the object properties so it doesn't merge properly.. Can someone assist with this?
Powershell

Avatar of undefined
Last Comment
ITguy565

8/22/2022 - Mon
oBdA

The issue here is that you're stripping all properties except "Name" from the item in line 4 (in the second code block). Drop the "|select Name", and add the Name property inside the loop where required.
    foreach ($item in $(&$ActiveDirectory_NotSpreadsheet_Reachable)) {
        $action = "Adding $($item.Name) To Spreadsheet because It Exists in AD"
        write-host -ForegroundColor DarkYellow "$action"
        $spreadsheettest += $item
    }

Open in new window

ASKER
ITguy565

oBdA,

Thanks for responding so quickly, that "Almost" did what I was looking for.


When I imported into the $spreadsheettest variable using the code you provided I got the following :


Name            : Server1
ActiveDirectory : True
SpreadSheet     : False
Reachable       : True


Whereas the output I am looking for would be :



Name            :Server1
IP Address    :Value
Server           :Value
Server OS     :Value
RAM              :Value
OMSA           :value
PW Check    :value

etc...

just like in the CSV above
ASKER
ITguy565

Essentially, I am just adding an additional item to the Spreadsheet with the Server Name.. only difference is the spreadsheet is in memory and stored in the $spreadsheettest object
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
EXPERT CERTIFIED SOLUTION
oBdA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ITguy565

oBdA

$newRow = '' | Select-Object -Property $spreadsheetProps
        $newRow.Name = $item.Name

Open in new window


returns the following error :

Exception setting "Name": "The property 'Name' cannot be found on this object. Verify that the property exists and can be set."
At line:1 char:1
+ $newRow.Name = $item.Name
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

$spreadsheetprops :

Count
Length
LongLength
Rank
SyncRoot
IsReadOnly
IsFixedSize
IsSynchronized[/quote]
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ITguy565

Thanks as always for going above and Beyond on this question.. I was stumped and had no idea how to do this.

Not to mention every time I ask a question, I get stronger in PowerShell due to your responses.. As usually I will pick apart the code and teach myself any sections I don't understand as you may notice and recognize some of the code in the segments above.

Thanks again!