We help IT Professionals succeed at work.

Powershell : Query AD and Update Values on SpreadSheet

ITguy565
ITguy565 asked
on
64 Views
1 Endorsement
Last Modified: 2020-09-17
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018

Commented:
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

CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
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]
CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.