Powershell : Query AD and Update Values on SpreadSheet

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

Select allOpen in new window

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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Select allOpen in new window

#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

Select allOpen in new window