PowerShell: Remove duplicated items from array

Question

I am trying to locate discrepancies in BIND DNS records. I would like to output a CSV file that only has those discrepancies. I have a CSV file that has all records from all locations in BIND (ns.prvt, ns.pub, common, includes). What I'm trying to figure out is how to output a CSV that only shows the discrepancies. For 2 records to be considered a discrepancy, they must meet the following criteria:

Both records have the same RecordName and RecordType.
Both records have different Data or TTL.
Both records come from different locations.

I am almost there with the following script but it keeps showing me a couple of rows that don't necessarily meet the above criteria.

$Records = Import-Csv C:\Temp\Domain_ALL.csv | Select * | Sort Data,Location
$RecordsRev = @()
$Records | % {
    $Record = $_
    $Records | % {
        $DataFE = $_
        If (
        ([string]($Record | ? {($_.RecordName -eq $DataFE.RecordName)}).RecordName -eq $DataFE.RecordName) -and 
        ([string]($Record | ? {($_.RecordName -eq $DataFE.RecordName)}).RecordType -eq $DataFE.RecordType) -and 
        ([string]($Record | ? {($_.RecordName -eq $DataFE.RecordName)}).Location -ne $DataFE.Location) -and 
        (([string]($Record | ? {($_.RecordName -eq $DataFE.RecordName)}).Data -ne $DataFE.Data) -or 
        ([string]($Record | ? {($_.RecordName -eq $DataFE.RecordName)}).TTL -ne $DataFE.TTL))
        ) {
            $RecordsRev += $_
        }
    }
}
$RecordsRev | Export-Csv C:\Temp\Domain_Discrepancies.csv -NoType

The results that I get are:

RecordName RecordType Data                           TTL Location
---------- ---------- ----                           --- --------
domain.com TXT        "MS=abc1234566"                600 Includes
domain.com TXT        "MS=abc1234566"                600 Common  
domain.com TXT        "site-verification=abcd1234"   600 Includes
domain.com TXT        "site-verification=abcd1234"   600 Common  
www        CNAME      somedomain.com.test.           600 Includes
www        CNAME      somedomain.com.                600 Common

The results that I expect are:

RecordName RecordType Data                           TTL Location
---------- ---------- ----                           --- -------- 
www        CNAME      somedomain.com.test.           600 Includes
www        CNAME      somedomain.com.                600 Common

How do I delete all duplicated rows in the array? This is different from "Select * -unique" as I don't want to keep any row that contains the duplicated information.

EDIT: I think the main problem is that, since the script checks each record against every record in the CSV, it technically is a discrepancy. For example, in the below table, record 1 meets the criteria to be a discrepancy because it differs from record 4. However, since record 1 is the same as record 2, it should actually be omitted from the results.

RecordNumber RecordName RecordType Data                           TTL Location
------------ ---------- ---------- ----                           --- --------
1            domain.com TXT        "MS=abc1234566"                600 Includes
2            domain.com TXT        "MS=abc1234566"                600 Common  
3            domain.com TXT        "site-verification=abcd1234"   600 Includes
4            domain.com TXT        "site-verification=abcd1234"   600 Common  
5            www        CNAME      somedomain.com.test.           600 Includes
6            www        CNAME      somedomain.com.                600 Common

Any help would be greatly appreciated.

Kyle

Oops. I meant that record 1 is the same as record 2. Edited. Thank you. — Kyle
– Kyle, Commented Jul 5, 2017 at 22:25
I still have the same question. The Location column between record 1 and 2 are different. Hence they're not duplicates, according to your criteria. — Bill_Stewart
– Bill_Stewart, Commented Jul 5, 2017 at 22:26
I only want records to show up if they meet the following criteria: 1. Both records have the same RecordName and RecordType. 2. Both records have different Data or TTL. 3. Both records come from different locations. — Kyle
– Kyle, Commented Jul 5, 2017 at 22:40
Right, so 1 and 2 would both be listed, since they meet your criteria (location is different). — Bill_Stewart
– Bill_Stewart, Commented Jul 5, 2017 at 23:17

Kyle · Accepted Answer · 2017-07-06 00:01:08Z

I was able to figure this out with the help of someone who deleted their post... Here is the script that I am using now to find all records that meet ALL of the following criteria:

Both records have the same RecordName and RecordType. -AND
Both records have different Data or TTL. -AND

Both records come from different locations.

$Records = Import-Csv C:\Temp\Domain_ALL.csv | Select * | Sort Data,Location
$Discrepancies = @()
$GoodRecords = @()
$BadRecords = @()

$Records | ForEach-Object { 

    # for each record $_, compare it against every other record..
    foreach ($R in $Records) {

        # if Both records have the same RecordName and RecordType.. 
        if (($_.RecordName -eq $R.RecordName) -and ($_.RecordType -eq $R.RecordType)) {

            # and if Both records come from different locations..
            if ($_.Location -ne $R.Location) {

                # if Both records have the same Data and TTL then they are considered good:
                if (($_.Data -eq $R.Data) -and ($_.TTL -eq $R.TTL)) {
                    $GoodRecords += $_
                }
                Else{
                    # if Both records have different Data or TTL then they are considered bad:
                    $BadRecords += $_
                }
            }
        }
    }

} 

ForEach ($BadRecord in $BadRecords){
    If (($GoodRecords -notcontains $BadRecord)){
        $Discrepancies += $BadRecord
    }
}
$Discrepancies | Select * -Unique | Sort RecordName,Location,Data | ft

Collectives™ on Stack Overflow

PowerShell: Remove duplicated items from array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related