Powershell and MySQL can be Friends

While using Powershell to collect data from a group of windows servers, I realized I would need a database to store the information collected. I was happy to learn that Powershell can utilize the MySQL.NET connector, so I could install MySQL on a server and have Powershell funnel the data with standard SQL commands into the database. All that I needed to do was install the .NET connector for MySQL on any machine I wished to connect to the database, in my case I installed it on all my servers. If you are worried about installing this connector, it has a small footprint and is nothing like some other database connectors like Oracles bulky ODBC driver. I have compiled a step by step list of how to configure and use MySQL within Powershell:

1. Install MySQL.NET 5.0.9, there is newer versions, but I choose 5.0.9 because it works with all my scripts.
   • http://dev.mysql.com/downloads/connector/net/5.0.html
2. If you are running a Powershell script as a task, you will need to change the execution policy in Powershell
   • Set-ExecutionPolicy unrestricted
3. On servers if you want to run a Powershell script from a network share without prompt, you will need to add the share to Internet Explorers trusted sites list, apparently IE policy effects file share script execution
4. You will need to now load the connector into your powershell script, first is for 32-bit windows, the second is for 64-bit version of windows
   • [void][system.reflection.Assembly]::LoadFrom(“C:\Program Files\MySQL\MySQL Connector Net 5.0.9\Binaries\.NET 2.0\MySQL.Data.dll”)
   • [void][system.reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\MySQL Connector Net 5.0.9\Binaries\.NET 2.0\MySQL.Data.dll”)
5. Now we will want to connect to our database
   • $dbconnect = New-Object MySql.Data.MySqlClient.MySqlConnection
   • $dbconnect.ConnectionString = “server=servername;user id=dbusername;password=dbpassword;database=dbname;pooling=false”
   • $dbconnect.Open()
6. Once we have connected, we can now create a MySQL command object, below is an example SQL command
   • $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
   • $sql.Connection = $dbconnect
   • $sql.CommandText = “INSERT INTO computer_details (computer_id, mac, dhcp, model, domain, manufacturer, type, memory, ip, servicetag, lastimagedate, servicepack, os, biosrev, scriptversion, lastrun, ou) VALUES (‘$resultID’, ‘$macAddress’, ‘$dhcp’, ‘$model’, ‘$domain’, ‘$manufacturer’, ‘$systemType’, ‘$memory’, ‘$ipAddress’, ‘$servicetag’, NOW(), ‘$servicePack’, ‘$operatingSystem’, ‘$biosrev’, ‘$version’, NOW(), ‘$ou’ )”
   • $sql.ExecuteNonQuery()
   • $dbconnect.Close()

Using this example you can now easily collect data from your servers and insert it into a database. You can as well use any SQL statement you wish, including querying a database, and using Powershell to output/manipulate this data as needed. In future posts I will get into some examples of what I have done with powershell and mysql.