Powershell - Insert into MySQL database table errors?

Question

I have this insert query that's not inserting properly and is really annoying. I will explain briefly what I tried and what I actually want to achieve.

What my PS script does:

ping every computer from a database table
if ping succeeds, get username logged on from the computer
insert to database table the following values : username, hostname, online(true/false)

Now here are the problems that keep showing up.

Problem 1: If I use the following command:

$username = Get-WMIObject -ComputerName $workstation -Class Win32_ComputerSystem | Select-Object -ExpandProperty Username;

It will insert the username values like so: SUPER/user1 which is Okay but I want to get rid of the "SUPER/" and just keep the actual username "user1" before inserting.

I tried using the one below but it doesnt work:

 $username =  Get-WMIObject -ComputerName $workstation -Class Win32_ComputerSystem | Select-Object -ExpandProperty -replace '^.*\\'  Username;

The command in problem 1 will give me these TWO errors:

Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value.

And

Get-WmiObject : The RPC server is unavailable.

How can I ignore those two errors?

I hope someone can help me out because its really annoying. thanks in advance everyone.

Powershell script:

foreach($pcname in $workstation_list){

        $command = $connection.CreateCommand();
        $command.Connection  = $connection;
        $command.CommandText = "INSERT INTO workstation_userlogged
                                 (username,hostname,online)
                                 VALUES (@username,@hostname,@status)";

        ### ============================================================= 
        ### values to be assigned as a parameters
        ### =============================================================           
        $workstation = $pcname;                
        $test_ping   = Test-Connection -ComputerName $workstation -Count 1 -ErrorAction SilentlyContinue;
        $status      = [bool](Test-Connection $workstation -Quiet -Count 1 -ErrorAction SilentlyContinue);

        #if status is TRUE get username
       if($test_ping)
        {
            $username = Get-WMIObject -ComputerName $workstation -Class Win32_ComputerSystem | Select-Object -ExpandProperty Username;
            echo $username;
           #echo -replace '^.*\\' $username;         

           #if ping succeeds but no user is logged
           if($username -eq "") 
           {
                $username = "no user logged";  
           } 

        } #end if

        #if ping DOES NOT succeed set username value to NULL and status to FALSE
        elseif(!$test_ping)
        {
            $username = [DBNull]::Value;
        }

       ### ============================================================= 
       ### Assign parameters with appropriate values
       ### ============================================================= 
        $command.Parameters.Add("@username", $username)     |   Out-Null
        $command.Parameters.Add("@hostname", $workstation)  | Out-Null
        $command.Parameters.Add("@status",   $status)       | Out-null
       ### ============================================================= 
       ### Execute command query
       ### ============================================================= 
        try{
            $command.ExecuteNonQuery() | out-null;
            Write-Host "Successfully inserted in Database";
        }
        catch{
            Write-Host "Caught the exception";
            Write-Host "$_";
        }

       ### ============================================================= 
       ### clear parameter values
       ### =============================================================  
         $command.Dispose()
         $workstation = "";
         $username    = "";
         $status      = "";
         $test_ping   = "";
     }#end for each loop

Mike Shepard · Accepted Answer · 2014-10-22 18:22:56Z

1

I see two things. first, this line:

$username = Get-WMIObject -ComputerName $workstation -Class Win32_ComputerSystem | Select-Object Username #-ExpandProperty

Should probably be this:

$username = Get-WMIObject -ComputerName $workstation -Class Win32_ComputerSystem | Select-Object -ExpandProperty Username

Second, you didn't use any quotes when embedding the username variable in the SQL String. Try this. I quoted all three values since I don't know what types the other columns are.

$command.CommandText = "INSERT INTO workstation_userlogged
                                    (username,hostname,online)
                                    VALUES ('$username','$pc_db','$ping_status')";

answered Oct 22, 2014 at 18:22

Mike Shepard

18.4k7 gold badges59 silver badges72 bronze badges

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

10 Comments

Ansgar Wiechers Over a year ago

+1 The domain part can be removed from the username like this: $Username -replace '^.*\\'

mario Over a year ago

the reason why I removed -ExpandProperty is because it keeps giving me this error : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value.

mario Over a year ago

@AnsgarWiechers I tried to do it but gives me } at the end? What am I doing wrong? thanks. I updated my code above

Ansgar Wiechers Over a year ago

@mario The parameter -ExpandProperty expects exactly one argument: the name of the property you want to expand, so you MUST use select -Expand property. Using select property -Expand will NOT work. And you get the trailing }, because you didn't expand the property before removing the domain part.

Mike Shepard Over a year ago

After you get $username, do this: `$username=$username -replace '^.*\\' The RPC error means that you're having trouble accessing the WMI registry on one of the remote servers. Troubleshooting that issue is a different story altogether. Looks like you did the parameters thing correctly at first glance.

|

Collectives™ on Stack Overflow

Powershell - Insert into MySQL database table errors?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related