Powershell Mysql helper functions

Some Powershell functions for Preparing Mysql Statements and executing them.

Requires the MySQL .NET Connector

$Dbserv = "server"
$Dbname = "db"
$Dbuser = "user"
$Dbpass = "pass"

#MySQL Database connection
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") | Out-Null
$SQLConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$SQLConnection.ConnectionString = "server=$Dbserv;port=3306;uid=$Dbuser;pwd=$Dbpass;database=$Dbname;Integrated Security=False"

function MySQLprepare {
	[OutputType([MySql.Data.MySqlClient.MySqlCommand])]
	param (
		# Query
		[Parameter(
			Mandatory = $true
		)]
		[String]
		$query,

		# Connection
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlConnection]
		$connection
	)

	$command = New-Object MySql.Data.MySqlClient.MySqlCommand
	$command.Connection = $connection
	$command.CommandText = $query

	(Select-String "@\w+" -input $query -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters.AddWithValue($_, "") }
	} | Out-Null

	$command.Prepare()

	return , $command
}

function MySQLexec {
	[OutputType([Int])]
	param (
		# QueryParams
		[Parameter(
			Mandatory = $false
		)]
		[hashtable]
		$queryParams = @{},

		# Command
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlCommand]
		$command
	)

	(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
	} | Out-Null

	$affectedRows = $command.ExecuteNonQuery()

	return $affectedRows
}

function MySQLquery {
	[OutputType([System.Data.DataTable])]
	param (
		# QueryParams
		[Parameter(
			Mandatory = $false
		)]
		[hashtable]
		$queryParams = @{},

		# Command
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlCommand]
		$command
	)

	(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
	} | Out-Null

	$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
	$dataSet = New-Object System.Data.DataSet
	$dataAdapter.Fill($dataSet, "data") | Out-Null
	return , $dataSet.Tables["data"]
}

Examples

Preparing some statements

$insertVM = $SQLConnection | MySQLprepare -query "INSERT INTO virtual_machines ( id, name, description, status ) VALUES
( @id, @name, @description, 'new' );"
$selectVMs = $SQLConnection | MySQLprepare -query "SELECT * FROM virtual_machines WHERE status LIKE @status;"
$updateVM = $SQLConnection | MySQLprepare -query "UPDATE virtual_machines SET status = @status WHERE id = @id;"

Executing them

$insertVM | MySQLexec -queryParams @{"@id" = 1; "@name" = 'test'; "@description" = 'example vm'} | Out-Null

$dtVMs = $selectVMs | MySQLquery -queryParams @{"@status" = 'new'}
foreach ($vm in $dtVMs.Rows) {
	Write-Host "Working on VM" $vm["id"] -ForegroundColor Cyan

	$updateVM | MySQLexec -queryParams @{"@id" = $vm["id"]; "@status" = 'ready'} | Out-Null
}

Cisco Catalyst 9300 ios upgrade

  1. Remove Unwanted Packages
    install remove inactive
  2. Copy New Image to Flash
    copy usbflash0:/cat9k_iosxe.x.x.x.SPA.bin flash:/
  3. Set Boot Variable
    (config) boot system flash:packages.conf
    wr
    show boot system
  4. Software Install Image to Flash
    install add file flash:cat9k_iosxe.x.x.x.SPA.bin activate commit
  5. Verify New Packages and Image after reload
    dir flash:*.pkg
  6. Check Version and New Bootloader
    show version
  7. Clean up
    install remove inactive

ESXi Raw Device Mapping RDM

To configure a local device as an RDM disk:

  1. Open an SSH session to the ESXi host.
  2. Run this command to list the disks that are attached to the ESXi host:
    ls -l /vmfs/devices/disks
  3. From the list, identify the local device you want to configure as an RDM and copy the device name.
    Note: The device name is likely be prefixed with t10. and look similar to: t10.F405E46494C4540046F455B64787D285941707D203F45765
  4. To configure the device as an RDM and output the RDM pointer file to your chosen destination, run this command:
    vmkfstools -z /vmfs/devices/disks/diskname /vmfs/volumes/datastorename/vmfolder/vmname.vmdk

For example:

vmkfstools -z /vmfs/devices/disks/t10.F405E46494C4540046F455B64787D285941707D203F45765 /vmfs/volumes/Datastore2/localrdm1/localrdm1.vmdk

https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1017530

Test Jumbo Frames / MTU 9000

Linux:

ping -M do -s 8972 xxx.xxx.xxx.xxx

OSX/BSD:

ping -D -s 8184 xxx.xxx.xxx.xxx

Windows:

ping -f -l 9000 xxx.xxx.xxx.xxx

Example results on Linux

If you’ve forgotten to enable jumbo frames/9k MTU on your client device you’re sending the ping from:

PING xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx): 8184 data bytes
ping: sendto: Message too long

If you have enabled jumbo frames on your client but not the destination (or a switch in between):

PING xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx): 8184 data bytes
Request timeout for icmp_seq 0

If you’ve done everything righ:

PING xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx): 8184 data bytes
8192 bytes from xxx.xxx.xxx.xxx: icmp_seq=0 ttl=128 time=0.714 ms