0

I have populated two results which I want to compare. One result comes from a SQL Server query, the other from PowerShell using the dbatools module. The results appear the same, but comparing does not return true. Some sample set up code may explain this better:

-- Sql Server
use tempdb;
create table dbo.Jobs (
    JobName varchar(16)
);
insert dbo.Jobs values ('test');

exec msdb.dbo.sp_add_job @job_name = 'test'


## PowerShell
$exp = Invoke-DbaQuery -SqlInstance $env:computername -Database tempdb -Query "select JobName from dbo.Jobs" | Select-Object -Property JobName -First 1
$rec = Find-DbaAgentJob -SqlInstance $env:computername -JobName test | Select-Object -Property JobName -First 1

$exp # one row
$rec # one row, same value, same header
$exp -eq $rec ## False, expected true
$exp.GetType() # PSCustomObject
$rec.GetType() # PSCustomObject
$exp[0] # one row
$rec[0] # one row, same value, same header
$exp[0] -eq $rec[0] # False, expected true
$exp[0].JobName # one row
$rec[0].JobName # one row, same value
$exp[0].JobName -eq $rec[0].JobName # True (Finally!)

$rec | Where-Object { $_.AgentJobName -notin $exp } # returns the value in $rec, should return nothing
$rec | Where-Object { $_.AgentJobName -in $exp } # returns nothing, should return the value in $rec

I am using PowerShell 5.1. I believe the two PowerShell variables should compare as being the same by just passing the variables alone. Ultimately my goal is to use more complex queries, following the example above, I would like to compare multiple jobs and multiple properties without having to iterate all the values. Initially I had arrays of objects and I used Compare-Object, but the results were incorrect and I believe it comes down to this simplified example.

I cannot use dbachecks because I cannot install PowerBI, I need to store the results this way for use by another reporting tool. Any help is appreciated.

beehive
  • 93
  • 3
  • 11
  • 2
    `PSCustomObject` are not [_equatable_](https://learn.microsoft.com/en-us/dotnet/api/system.iequatable-1?view=net-7.0) or [_comparable_](https://learn.microsoft.com/en-us/dotnet/api/system.icomparable?view=net-7.0), are you looking to compare all properties and values against each other? – Santiago Squarzon Nov 09 '22 at 21:51
  • Yes. I would like to compare multiple jobs in this example, each row having multiple properties, like comparing two database tables, as the data sets _should_ never change. Thank you @SantiagoSquarzon – beehive Nov 09 '22 at 21:54
  • Does this answer your question? https://stackoverflow.com/questions/68447494/prevent-adding-pscustomobject-to-array-if-already-exists/ – Santiago Squarzon Nov 09 '22 at 21:55
  • Comparing 2 datasets of undefined number of properties against each other is not an easy task, not only not easy but will be quite slow too. The linked answers should give you a certain idea on where you can start – Santiago Squarzon Nov 09 '22 at 22:11
  • Thanks @SantiagoSquarzon, it certainly explains the problem I have, sorry I hadn't found this in my research – beehive Nov 09 '22 at 22:17
  • No need to be sorry, regarding your `$rec | Where-Object { $_.AgentJobName -notin $exp }` returns all objects when it should return none. You're comparing a property value against an array of objects, what you actually wanted to do is `$rec | Where-Object { $_.AgentJobName -notin $exp.AgentJobName }` so it's comparing each __Value__ against an array of __Values__ – Santiago Squarzon Nov 10 '22 at 01:34

1 Answers1

0

This Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?) is capable of doing an OuterJoin (everything that is not equal) on all properties (*) at once:

$exp |OuterJoin $rec -On *
iRon
  • 20,463
  • 10
  • 53
  • 79