Update two different SNMP OID values through Powershell

Question

I'm trying to update info from 4 ups's with two different OID values through powershell. I can update one but when I try to update both values I receive an error. I figured out why it's not updating the values by inserting the values onto a new table. When it inserts/updates the values the script enters both values into the table column instead of having one value for temp and one value for battery. My question is how can I update both values if there is a way. Below is my loop I am running.

    # If success go call func SNMP
    if($ping_reply.status -eq "Success"){
        try {
            $frm_snmp = Invoke-SNMPget $ups_ip $oidTemp, $oidBatload "public"
        } catch {
            Write-Host "$ups_ip SNMP Get error: $_"
            Return null
        }


    # if the data doesn't match record update ups_data
    if([String]::IsNullOrWhiteSpace($frm_snmp.Data)){
         Write-Host "Given string is NULL"
    }else{
        if(($ups_temp -and $battery_load -ne $frm_snmp.Data)) { 
            Write-Output "database update needed"
            Write-Output $ups_ip, $ups_upsname $frm_snmp.Data

            $new_temp = $frm_snmp.Data
            $new_battery_load = $frm_snmp.Data
            $update_con = New-Object System.Data.SqlClient.SqlConnection
            $update_con.ConnectionString = "connection info"
            $update_con.Open()

            $SQLstmt = "update ups_data set temp = '$new_temp', batteryload = '$new_battery_load' where ip_address = '$ups_ip'"

            $up_cmd = $update_con.CreateCommand() 
            $up_cmd.CommandText = $SQLstmt
            $up_cmd.ExecuteNonQuery()
            $update_con.Close()

Where, specifically, does the error occur? In the abstract it sounds like a 2-element array getting stringified. Generally, please try to get as close to an [MCVE (Minimal, Complete, and Verifiable Example)](http://stackoverflow.com/help/mcve) as possible. — mklement0, Jun 07 '18 at 22:56
the error that I receive when I update it the way this code looks is that the error that I receive when I update it the way this code looks is the Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated. The statement has been terminated." — Celestialchippy, Jun 07 '18 at 22:59
That just tells you that that you're passing a value that is too long for the target column, which is consistent with two values accidentally getting submitted at once. Generally, please update your question _directly_ with such information. So, the question is: _which_ of the values in the `update` statement is affected? — mklement0, Jun 07 '18 at 23:03
Okay I knew that but what I'm trying to figure out is why the values are getting 'stringified' the values are `$new_temp` `$new_battery_load` they are both printing to the same column twice is there a way to seperate these values? Thats What I'm generally trying to figure out. — Celestialchippy, Jun 07 '18 at 23:07
If they both contain the same value twice and that is what you expect, you can simply reference the first one, right? E.g.. `$new_temp` -> `'$($new_temp[0])'` — mklement0, Jun 07 '18 at 23:10
I did not expect it to be that simple. I felt like I was running all over the place looking for a solution. — Celestialchippy, Jun 07 '18 at 23:19
Well, I trust you to know why the two values are by design always the same - if you had focused your original question just on how to incorporate _one_ of the two values into your command, you would have received an answer sooner (and the question would be of more general interest). To learn about PowerShell's string expansion (interpolation) rules, see https://stackoverflow.com/a/40445998/45375 — mklement0, Jun 07 '18 at 23:23

score 0 · Answer 1 · answered Jun 07 '18 at 23:18

This is the working code below

# If success go call func SNMP
        if($ping_reply.status -eq "Success"){
            try {
                $frm_snmp = Invoke-SNMPget $ups_ip $oidTemp, $oidBatload "public"
            } catch {
                Write-Host "$ups_ip SNMP Get error: $_"
                Return null
            }


    # if the data doesn't match record update ups_data
    if([String]::IsNullOrWhiteSpace($frm_snmp.Data)){
         Write-Host "Given string is NULL"
    }else{
        if(($ups_temp -and $battery_load -ne $frm_snmp.Data)) { 
            Write-Output "database update needed"
            Write-Output $ups_ip, $ups_upsname $frm_snmp.Data

            $new_temp = $frm_snmp.Data
            $new_battery_load = $frm_snmp.Data
            $update_con = New-Object System.Data.SqlClient.SqlConnection
            $update_con.ConnectionString = "connection info"
            $update_con.Open()

            $SQLstmt = "update ups_data set temp = '$($new_temp[0])', batteryload = '$($new_battery_load[1])' where ip_address = '$ups_ip'"

            $up_cmd = $update_con.CreateCommand() 
            $up_cmd.CommandText = $SQLstmt
            $up_cmd.ExecuteNonQuery()
            $update_con.Close()

Update two different SNMP OID values through Powershell

1 Answers1