Not able to loop through an array with powershell

Question

Okay i need to loop through an array using the deviceId. Everything inserts fine, the problem is that the obj tags is not pulled from the API, because is an array and is tied to the device id. So i decided to create another table while the script runs it can pull the device ID's and insert them so the tags can be pulled from the API and inserted into the other table where i can later join them. The problem is i did another for each and is just inserting the last ID and no tags are even been pulled. Any suggestions?

#For loop giving variables to the objects
foreach($obj in $Json.devices)
{
    $DeviceIdentifier = $obj.deviceid
    $DeviceNombre = $obj.deviceName
    $DomainNombre = $obj.domainName
    $Description = $obj.description
    $Tags = $obj.tags
    $location = $obj.location
    $Os = $obj.os


    Write-Host ($obj.devicename) -BackgroundColor White -ForegroundColor red
    Write-Host ($obj.domainname) -BackgroundColor White -ForegroundColor red
    Write-Host ($obj.deviceid) -BackgroundColor White -ForegroundColor DarkGreen
    Write-Host ($obj.tags) -BackgroundColor White -ForegroundColor black
    Write-Host ($obj.description) -BackgroundColor White -ForegroundColor blue
    Write-Host ($obj.os) -BackgroundColor White -ForegroundColor DarkBlue

    #Inserting into MYSQL database
    $cmd = $connection.CreateCommand()
    $insert_stmt = "INSERT INTO [dbo].[Tags]([DeviceID],[Device Name],[Domain Name],[Description],[Tags],[Location],[Os])
                            VALUES ('$DeviceIdentifier','$DeviceNombre', '$DomainNombre','$Description','$tags','$location','$Os')" -replace "\s+"," "
    $cmd.CommandText = $insert_stmt
    write-host $insert_stmt -BackgroundColor White -ForegroundColor DarkBlue
    $cmd.ExecuteNonQuery()
}


foreach($Tags in $DeviceIdentifier)
{
    $DeviceIdentifier = $Tags.deviceid
    $Tags = $obj.tags

    Write-Host ($obj.deviceid) -BackgroundColor White -ForegroundColor DarkGreen
    Write-Host ($obj.tags) -BackgroundColor White -ForegroundColor black

    #Inserting into MYSQL database
    $cmd = $connection.CreateCommand()
    $insert_stmt = "INSERT INTO [dbo].[TagsTable]([DeviceID],[Tags])
                            VALUES ('$DeviceIdentifier','$tags')" -replace "\s+"," "
    $cmd.CommandText = $insert_stmt
    write-host $insert_stmt -BackgroundColor White -ForegroundColor DarkBlue
    $cmd.ExecuteNonQuery()
}

$Connection.Close()

sodawillow · Accepted Answer · 2017-09-08 16:19:00Z

1

At each pass of both loops you store one single value in $DeviceIdentifier: $DeviceIdentifier = $obj.deviceid / $DeviceIdentifier = $Tags.deviceid. In the second one you even overwrite the collection being used.

You could (for example) create an array before the loop with $array = @() and add values to it in the loop with $array += $obj.deviceid.

answered Sep 8, 2017 at 16:19

sodawillow

13.3k4 gold badges36 silver badges44 bronze badges

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

Comments

TheMadTechnician · Accepted Answer · 2017-09-08 17:43:13Z

What sodawillow said is accurate and good advice. Don't re-use variables like that, and define arrays before your loops to avoid scope issues. That said, this looks like it should be an easy fix. You run two loops, but it looks like you really just want to iterate the same array and apply updates from that array to two different tables. Cool, just put two SQL statements in each pass of the loop. Also, I'm moving your creation of $cmd outside the loop. There's no need to recreate it each time.

$cmd = $connection.CreateCommand()

#For loop giving variables to the objects
foreach($obj in $Json.devices)
{
    $DeviceIdentifier = $obj.deviceid
    $DeviceNombre = $obj.deviceName
    $DomainNombre = $obj.domainName
    $Description = $obj.description
    $Tags = $obj.tags
    $location = $obj.location
    $Os = $obj.os


    Write-Host ($obj.devicename) -BackgroundColor White -ForegroundColor red
    Write-Host ($obj.domainname) -BackgroundColor White -ForegroundColor red
    Write-Host ($obj.deviceid) -BackgroundColor White -ForegroundColor DarkGreen
    Write-Host ($obj.tags) -BackgroundColor White -ForegroundColor black
    Write-Host ($obj.description) -BackgroundColor White -ForegroundColor blue
    Write-Host ($obj.os) -BackgroundColor White -ForegroundColor DarkBlue

    #Inserting into MYSQL database
    $insert_stmt = "INSERT INTO [dbo].[Tags]([DeviceID],[Device Name],[Domain Name],[Description],[Tags],[Location],[Os])
                            VALUES ('$DeviceIdentifier','$DeviceNombre', '$DomainNombre','$Description','$tags','$location','$Os')" -replace "\s+"," "
    $cmd.CommandText = $insert_stmt
    write-host $insert_stmt -BackgroundColor White -ForegroundColor DarkBlue
    $cmd.ExecuteNonQuery()

    Write-Host ($obj.deviceid) -BackgroundColor White -ForegroundColor DarkGreen
    Write-Host ($obj.tags) -BackgroundColor White -ForegroundColor black

    #Inserting into MYSQL database
    $insert_stmt2 = "INSERT INTO [dbo].[TagsTable]([DeviceID],[Tags])
                            VALUES ('$DeviceIdentifier','$tags')" -replace "\s+"," "
    $cmd.CommandText = $insert_stmt2
    write-host $insert_stmt2 -BackgroundColor White -ForegroundColor DarkBlue
    $cmd.ExecuteNonQuery()
}

$Connection.Close()

Collectives™ on Stack Overflow

Not able to loop through an array with powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related