CODEWITHSUNDEEP
regexpowershellcsv
Bnd10706
Bnd10706

Reputation: 2373

Powershell Compare value to multiple arrays

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
    }
}

Upvotes: 1

Views: 105

Answers (1)

user6811411
user6811411

Reputation:

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 '|')+")")

Upvotes: 1

Related Questions