Compare columns on two spreadsheets with PowerShell

Mohammed Faruq

— 1 min read

I have two systems that report on assets within the organisation. An Endpoint Manager and a Vulnerability Manager. Ideally, both systems should report the same assets. Same number, same hosts.

However, I noticed some assets reported by the Endpoint Manager were not appearing in the Vulnerability Manager.

I needed to find which assets are missing from the Vulnerability Manager.

Essentially, the task is to compare two reports and output the differences. i.e. find the differences between hostname columns in two spreadsheets.

High level steps:

  1. Get asset report from Endpoint Manager
  2. Get asset report from Vulnerability Manager.
  3. Run some script to compare them
  4. Automate steps 1–3

NOTE: There is obviously more than one way to do this. This is what I did.

I used PowerShell to do the comparison. Thanks to my friend, Yi, for his help on this.

$EndpointManager_Assets = import-csv .\EndpointManager_Report.csv #import hostnames from spreadsheet 
$VM_Assets = import-csv .\VM_Report.csv #import hostnames from spreadsheet 
 
$Result_Missing_in_VM = compare-object -ReferenceObject $EndpointManager_Assets.<hostname_column> -DifferenceObject $VM_assets.<hostname_column> -IncludeEqual  
#compare hostnames in the two arrays and store in $Result_Missing_in_VM 
#Lists results with a side indicator as follows 
# == hostname exists in both reports 
# <= hostname exists in endpoint manager report only 
# => hostname exists in VM tool only 
 
$Result_Missing_in_VM | where-object{$_.SideIndicator -match '<='} | Out-File assets_missing_in_VM.txt  
# get assets that exist in endpoint manager report only and output to a text file

Read more