PowerShell: Array not filtering correctly with IF statement

Question

I've been stuck on this for a while now... Basically i have a SQL database that my PS Script fetches using Invoke-Sqlcmd:

$nocToolsDatabase = Invoke-Sqlcmd -query "Select * from tb_noctools" -ServerInstance $sqlServer -Database $database -user $user -pass $password

And I am cross referencing the fields in the Table with values from an API in a Foreach loop with IF statements:

foreach ($shItem in $shIncidentsWithNocToolsAndInc) {
    if ($nocToolsDatabase.INC -eq $shItem.INC) {
        "YES IT DOES MATCH"
        if ($shItem.incident_status[0] -notin $nocToolsDatabase.sh_status) {
            "YAY"
        } else {
            "ERROR"
        }
    } else {
        write-host "NO IT DOESNT" -ForegroundColor Red
    }
}

The first IF statement works fine, but the second one compares $shItem.incident_status[0] which has one value to $nocToolsDatabase.sh_status which for some reason compares all results in that table under the sh_status column instead of the individual row based on the previous IF statement filter.

The API values are:

| INC       | incident_status[0] |
|-----------|--------------------|
| INC1234   | resolved           |
| INC123456 | resolved           |

Database table:

| INC       | sh_status     |
|-----------|---------------|
| INC1234   | resolved      |
| INC123456 | investigating |

So basically the second IF statement should return "YAY" for INC123456 because the API value is resolved, however on the database it is set as investigating.

Any ideas?

TIA

Daniel · Accepted Answer · 2021-03-17 03:31:54Z

The reason for this is when you call $nocToolsDatase.INC or $nocToolsDatabase.sh_status you are getting back arrays of all the INC and sh_status values on all the objects/records in $nocToolsDatabase. It looks like you want to only work with a nocTool object that matches your incident number so you need to look for this first.

foreach ($shItem in $shIncidentsWithNocToolsAndInc) {
    # Get nocTool records that match shItem.INC
    $nocTool = $nocToolsDatabase | Where-Object { $_.INC -eq $shitem.INC }

    # if nocTool is not empty then found match
    if ($null -ne $nocTool) {
        'YES IT DOES MATCH'
        if ($shItem.incident_status[0] -notin $nocTool.sh_status) {
            'YAY'
        }
        else {
            'ERROR'
        }
    }
    else {
        Write-Host 'NO IT DOESNT' -ForegroundColor Red
    }
}

This is the best, elegant solution. Thanks a lot for your help!!

Santiago Squarzon · Accepted Answer · 2021-03-17 03:14:04Z

I'm not entirely sure what you're trying to accomplish but check out this code below and let me know if this is what you were looking for:

$nocToolsDatabase = Invoke-Sqlcmd -query "Select * from tb_noctools" -ServerInstance $sqlServer -Database $database -user $user -pass $password

$nocToolsMap=@{}

foreach($line in $nocToolsDatabase)
{
    if(!$nocToolsMap.ContainsKey($line.INC))
    {
        $nocToolsMap.Add($line.INC,$line.sh_status)
    }
}

foreach ($shItem in $shIncidentsWithNocToolsAndInc)
{
    if ($nocToolsMap.ContainsKey($shItem.INC))
    {
        "Incident is in SQL Db"
        if ($nocToolsMap[$shItem.INC] -eq 'Resolved') {
            "Incident {0} is Resolved" -f $shItem.INC
        }
        else
        {
            "Incident {0} is Not Resolved. Status: {1}" -f $shItem.INC,$nocToolsMap[$shItem.INC]
        }
    } else {
        "Incident is not in SQL Db"
    }
}

Basically, if you are going to compare 2 arrays you would need 2 foreach loops or a hashtable to use as lookup map as am doing below. You could also merge both arrays, that's up to your need.

Hey thanks for that sir. Another question, the database, also contains many more columns of data however its not needed for the IF statements but do you know if I can create a function to insert the details into the database. Would that mess anything up with $nocToolsMap?

Collectives™ on Stack Overflow

PowerShell: Array not filtering correctly with IF statement

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related