Powershell Compare value to multiple arrays

Question

I am trying to compare data to multiple sources and then give me a report of the errors. Due to the changing nature of exceptions, I wanted to build an exception table in csv format that I can change on the fly.

I am going to give the data the best I can and show you what I'm trying to achieve and show you where I'm coming into problems.

The exceptions list holds the prefix to different types of accounts:

Exceptions List
_______________
FQ
Q
HQ
E

So if my Account was BND123 then I may have an account called FQBND123 or QBND123 I want to be able to add to this list if one of the teams decides they need to make a JQ account or anything like that in the future.

This is an example of Inventoryreport.csv I'm looking to parse:

Safe        Target system user name
HUMAN_ABC       QABC
HUMAN_CDE       QCDE
HUMAN_FGHIJ     QFGHIJ
HUMAN_P123456   root
HUMAN_KLMNO     QKLMNO1
HUMAN_P789123   FQ789123

So I am looking to compare target system username to the safe name, and if the leading account is in the exception list, it passes it up, and if it does not, then it throws it as an error.

So in the case of the data above the 2 rows would throw an error below.

HUMAN_P123456   root
HUMAN_KLMNO     QKLMNO1

Root for obvious reason and the KLMNO account because of the trailing 1.

The problem I am getting is that it is saying everything is an error. If I hand type it in to the loop everything is fine.

I had the exceptions in a foreach loop too inside the one for the inventory, but it keep looping over the same results and still spitting out everything.

Hopefully this is an OK explanation, I'm sure I'm making this harder than it needs to be.

$loc = $scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
$D = $loc + "\Exceptions\Exceptions.csv"
$E = $loc + "\Import\InventoryReport.csv"

$exceptions = Import-Csv -LiteralPath $D 
$inventory = Import-Csv -LiteralPath $E

$list2 = 'Inventory Report Exceptions'
$list3 = 'Target system user name'

$DO = $loc + "\Report\Inventory Report Errors" + "$((Get-Date).ToString('MM-dd-yyyy')).CSV"
$time = (Get-Date).ToString()

foreach ($item in $inventory) {
    $input1 = $item.Safe -replace "HUMAN_"
    $input4 = $item.Safe -replace "HUMAN_P"
    $input2 = $item.$list3
    $input3 = $item.Safe

    if ($input2 -eq ($exceptions.$list2 + $input1) -or $input2 -eq ($exceptions.$list2 + $input4)) {
        return
    }
    else {
        $newitem = New-Object -TypeName PSCustomObject -Property  @{
            Safe  = $input1
            Owner = $input2
        }| Export-CSV -LiteralPath $DO -NoTypeInformation -append
    }
}

score 1 · Accepted Answer · 2018-11-02 12:57:32Z

1

Your question is a bit long and not very clear...

Let's look if I got it right:

I shortened the exceptions list to a regular expression anchored at begin
simulate the inventory.csv with a here string
append a column Pass to that
iterate the entries comparing the split'ed values for equality and save in the new col.

## Q:\Test\2018\11\02\SO_53109141.ps1
$Inventory = @"
Safe,Target system user name
HUMAN_ABC,QABC
HUMAN_CDE,QCDE
HUMAN_FGHIJ,QFGHIJ
HUMAN_P123456,root
HUMAN_KLMNO,QKLMNO1
HUMAN_P789123,FQ789123
"@ | ConvertFrom-Csv

#$Exception = [regex]'^(FQ|Q|HQ|E)'
$Exception = [RegEx]("^("+((Import-Csv .\exceptions.csv).'Exceptions List' -join '|')+")")

$Fail = $Inventory | Select-Object *,Pass | ForEach-Object {
   if ( ($_.Safe -split '_P?')[1] -ne ($_.'Target system user name' -split $Exeption)[2]){
       [PSCustomObject]@{
          Safe = ($_.Safe -split '_')[1]
         Owner= $_.'Target system user name'
       }
    }
}
$Fail | Export-Csv '.\new.csv' -NoTypeInformation

Sample output:

Safe          Target system user name  Pass
----          -----------------------  ----
HUMAN_ABC     QABC                     True
HUMAN_CDE     QCDE                     True
HUMAN_FGHIJ   QFGHIJ                   True
HUMAN_P123456 root                    False
HUMAN_KLMNO   QKLMNO1                 False
HUMAN_P789123 FQ789123                 True

EDIT you can read in the exception from a file:

> import-csv .\exceptions.csv

Exceptions List
---------------
FQ
Q
HQ
E

And build a RegEx from the content:

$Exception = [RegEx]("^("+((Import-Csv .\exceptions.csv).'Exceptions List' -join '|')+")")

edited Nov 2, 2018 at 12:57

answered Nov 2, 2018 at 12:17

user6811411

Sign up to request clarification or add additional context in comments.

15 Comments

user6811411 Over a year ago

See changed answer how to construct a RegEx from a read in csv.

user6811411 Over a year ago

Edit answer again to output a [PCustomObject] for only the fails into $Fail and export that at the end

Bnd10706 Over a year ago

I just wanted to say I figured out how! Thank you so much, I am very very new to Powershell so learning from people like you helps me grow. Thank you again for your patients and your amazing answer.

JohnLBevan Over a year ago

FYI: It may be worth adding [Regex]::Escape; i.e. amending ((Import-Csv .\exceptions.csv).'Exceptions List' to (((Import-Csv .\exceptions.csv).'Exceptions List' | %{[Regex]::Escape($_)}); so that if any special characters are included in the values, they don't interfere with the intended logic.

user6811411 Over a year ago

The list of new rules is getting longer and IMO beyonf the scope of a single question. It's not clear to me how to handle the HUMAN_Pxx and trailing _CA, that should have been part of this question -- or a new question, but only one at a time.

|

Collectives™ on Stack Overflow

Powershell Compare value to multiple arrays

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related