Tag Archives: windows

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
}

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

How to insert hotfixes and drivers and then create a bootable Windows Setup DVD

  • Create local folders c:\temp\src, c:\temp\mount, c:\temp\winremount, c:\temp\hotfix, and c:\temp\drivers.
  • Copy the setup sources from the DVD or a mounted ISO to C:\temp\src.
  • Copy the hotfix (.msu or .cab files) to C:\temp\hotfix.
  • Copy the driver files to c:\temp\drivers.
  • Run Command Prompt as an administrative.
dism /Mount-Wim /WimFile:c:\temp\src\sources\boot.wim /Index:1 /MountDir:c:\temp\mount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
dism /Unmount-Wim /MountDir:C:\temp\mount /Commit
dism /Mount-Wim /WimFile:c:\temp\src\sources\boot.wim /Index:2 /MountDir:c:\temp\mount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
  • Manually sort the folder C:\temp\mount\sources by date, and then copy the updated files to c:\temp\src\sources.
dism /Unmount-Wim /MountDir:C:\temp\mount /commit
  • Obtain the index from the Install.wim information by running the following command, and then check every index to see how many indexes have to be updated.
dism /Get-WimInfo /WimFile:c:\temp\src\sources\install.wim
  • Insert the hotfixes and drivers to install.wim and winre.wim by running the following commands:
dism /Mount-Wim /WimFile:c:\temp\src\sources\install.wim /Index:1 /MountDir:c:\temp\mount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
dism /Mount-Wim /WimFile:c:\temp\mount\windows\system32\recovery\winre.wim /Index:1 /MountDir:c:\temp\winremount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
dism /Unmount-Wim /MountDir:C:\temp\winremount /Commit
dism /Unmount-Wim /MountDir:C:\temp\mount /Commit

Note If there are multiple indexes in step 8, update them one by one.

Create an ISO file by running the following oscdimg commands.

Install the latest Windows ADK for Windows 8.1 update.

  • For Legacy BIOS Boot mode:
oscdimg -LTEST -m -u2 -bC:\temp\src\boot\etfsboot.com C:\temp\src C:\temp\Win7.ISO
  • For Legacy and UEFI BIOS multiple Boot mode:
oscdimg -LTEST -m -u2 -bootdata:2#p0,e,bC:\temp\src\boot\etfsboot.com#pEF,e,bC:\temp\src\efi\microsoft\boot\efisys.bin C:\temp\src C:\temp\Win7.ISO

https://support.microsoft.com/en-au/help/2990941/update-to-add-native-driver-support-in-nvm-express-in-windows-7-and-windows-server-2008-r2

DNS Fun

calc

A calculator supporting addition, substraction, multiplication and division (respectively : ADD, SUB ,MUL, and DIV operations). More information here.

dig 2.8.add.calc.postel.org @dns.postel.org +short
0.10.0.0
dig 8.2.sub.calc.postel.org @dns.postel.org +short
0.6.0.0
dig 2.8.mul.calc.postel.org @dns.postel.org +short
0.16.0.0
dig 8.2.div.calc.postel.org @dns.postel.org +short
0.4.0.0

IP to ASN Mapping

A service providing a way to map IPv4 and IPv6 addresses or prefixs to ASNs, get information about ASNs, and even find possible peers. More details here.

Mapping an IP address or prefix to a corresponding BGP Origin ASN:

dig 4.4.8.8.origin.asn.cymru.com TXT +short
"15169 | 8.8.4.0/24 | US | arin |"
dig 4.8.8.origin.asn.cymru.com TXT +short
"15169 | 8.8.4.0/24 | US | arin |"
dig 4.4.8.8.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.6.8.4.0.6.8.4.1.0.0.2.origin6.asn.cymru.com TXT +short
"15169 | 2001:4860::/32 | US | arin | 2005-03-14"

Getting information about a given ASN:

dig AS15169.asn.cymru.com TXT +short
"15169 | US | arin | 2000-03-30 | GOOGLE - Google Inc.,US"

fun

dig any google-public-dns-a.google.com +noall +answer
; <<>> DiG 9.8.3-P1 <<>> any google-public-dns-a.google.com +noall +answer
;; global options: +cmd
google-public-dns-a.google.com.	38271 IN TXT	"http://xkcd.com/1361/"
google-public-dns-a.google.com.	38271 IN AAAA	2001:4860:4860::8888
google-public-dns-a.google.com.	38271 IN A	8.8.8.8

more stuff at http://www.cambus.net/interesting-dns-hacks/

Get public IP using DNS

show public IP

Google:

dig o-o.myaddr.l.google.com txt @ns1.google.com +short
nslookup -type=txt o-o.myaddr.l.google.com ns1.google.com
"10.11.12.13"

OpenDNS:

dig myip.opendns.com @resolver1.opendns.com +short
nslookup myip.opendns.com resolver1.opendns.com
10.11.12.13

Akamai:

dig whoami.akamai.net. @ns1-1.akamaitech.net. +short
nslookup whoami.akamai.net. ns1-1.akamaitech.net.
10.11.12.13

other usage

dig o-o.myaddr.l.google.com txt
nslookup -type=txt o-o.myaddr.l.google.com

returns txt record of DNS request to authoritative source address
edns0-client-subnet if available, showing original DNS request source address

dig o-o.myaddr.l.google.com txt @8.8.8.8 +short
nslookup -type=txt o-o.myaddr.l.google.com 8.8.8.8
"198.51.100.1"
"edns0-client-subnet 203.0.113.0/24"