netnerds.net

18Jun/130

Update NetApp Virtual Storage Console SSL Certs with your own Windows Domain CA Certificates using PowerShell

Ahhh, it seems like replacing SSL certificates in vSphere is a never-ending process. My vSphere farm was not prompting me about untrusted SSL certs until I installed the NetApp Virtual Storage Console. Using the template from my previous posts, however, I was able to quickly update VSC's certs using a combination of the practical admin's post and NetApp's KB (login required).

The pratical admin post kept VSC's keystore password encrypted, but with vSphere keystore passwords being so easily available on the Internet and NetApp's KB suggesting to place the password on the filesystem in plain-text, I did it the easy way and kept the password (netapp) in clear text in the config file. I've tested this script on both VSC 4.1 and 4.2 and it worked quite well.

You can copy and paste the code below, or download the script directly here.

Note: this script uses the Windows CA default WebServer Certificate Template. It also makes backups of your original certificates.

#########################################################################################
#
#   NetApp Virtual Storage Console SSL Generation and Replacement script version 0.5
#   Tested on VSC 4.1 and 4.2
#   No guarantees, warranties, etc.
#   Blog post: http://goo.gl/Cdlhb
#
#########################################################################################

# Place the certs on a network location if your farm is larger than one server
$basedir = "\\fileserver\share\Certs"

# Enter your Windows Certificate Authority information
# below. Make sure it responds to certutil requests.
$rootCA = "dc.base.local"
$rootCAName = "BASE-DC-CA"
$email = "vmware@base.local"
$org = "NetNerds"
$city = "Kaplan"
$state = "LA"
$country = "US"

# Enter the path of your VSC Installation
$vscdir = "C:\Program Files\NetApp\Virtual Storage Console"

# Enter the path of your openssl.exe (0.x and 1.x are supported).
# If you don't have OpenSSL already, the script will download it for you.
$openssldir = "C:\OpenSSL-Win32"
$openssl = $openssldir+"\bin\openssl.exe"

##############################################################################################
#
#	You probably don't need to change anything below.
#
##############################################################################################

$thisfqdn = ("$env:computername.$env:userdnsdomain").ToLower()
$backuptime = (Get-Date -uformat "%m%d%Y%H%M%S")
$wc = New-Object System.Net.WebClient

if (!(Test-Path "$basedir")) { $null = New-Item -Type Directory "$basedir" }

Write-Host -Foreground "DarkBlue" -Background "White" "Downloading root CA Cert.."
$url = "http://$rootCA/certsrv/certnew.cer?ReqID=CACert&Renewal=0&Enc=b64"
$root64 = "$basedir\Root64.cer"
$wc.UseDefaultCredentials = $true; $wc.DownloadFile($url,$root64)

if (!(Test-Path($openssl))) {
	Write-Host -Foreground "DarkBlue" -Background "White" "Downloading OpenSSL.."
	$null = New-Item -Type Directory $openssldir
	$sslurl = "https://openssl-for-windows.googlecode.com/files/openssl-0.9.8k_WIN32.zip"
	$sslzip = "$env:temp\openssl.zip"
	$wc.DownloadFile($sslurl,$sslzip)
	$env:path = $env:path + ";$openssldir"

	Write-Host -Foreground "DarkBlue" -Background "White" "Extracting OpenSSL.."
	$shellApplication = new-object -com shell.application
	$zipPackage = $shellApplication.NameSpace($sslzip)
	$destinationFolder = $shellApplication.NameSpace($openssldir)
	$destinationFolder.CopyHere($zipPackage.Items())
	Remove-Item $sslzip
} 

######################################################################
#
#	Generate Certs
#
######################################################################

Write-Host -Foreground "DarkBlue" -Background "White" "Generating service certs.."
	$service = "$thisfqdn-netapp"
	$server = $thisfqdn.Substring(0,$thisfqdn.IndexOf("."))

	$servicedir = "$basedir\$service"
	$servicecfg = "$servicedir\$service.cfg"
	$tempkey = "$servicedir\temp.key"
	$netappkey = "$servicedir\netapp.key"
	$netappcsr = "$servicedir\netapp.csr"
	$netappcrt = "$servicedir\netapp.crt"
	$netapppfx = "$servicedir\netapp.pfx"
	$chainpem = "$servicedir\chain.pem"
	$backupdir = "$servicedir\backup-$backuptime"
	$keyalias = "netapp" 

	if (Test-Path($servicedir)) { $null = Remove-Item "$servicedir\*.*" } else {$null = mkdir $servicedir } 

	Set-Content $servicecfg "[ req ]"
	Add-Content $servicecfg " default_md = sha512"
	Add-Content $servicecfg " default_bits = 2048"
	Add-Content $servicecfg " default_keyfile = netapp.key"
	Add-Content $servicecfg " distinguished_name = req_distinguished_name"
	Add-Content $servicecfg " encrypt_key = no"
	Add-Content $servicecfg " prompt = no"
	Add-Content $servicecfg " string_mask = nombstr"
	Add-Content $servicecfg " req_extensions = v3_req"
	Add-Content $servicecfg "`n[ v3_req ]"
	Add-Content $servicecfg " basicConstraints = CA:FALSE"
	Add-Content $servicecfg " keyUsage = digitalSignature, keyEncipherment, dataEncipherment"
	Add-Content $servicecfg " extendedKeyUsage = serverAuth"
	Add-Content $servicecfg " subjectAltName = DNS:$server, DNS:$thisfqdn"
	Add-Content $servicecfg "`n[ req_distinguished_name ]"
	Add-Content $servicecfg " countryName = $country"
	Add-Content $servicecfg " stateOrProvinceName = $state"
	Add-Content $servicecfg " localityName = $city"
	Add-Content $servicecfg " 0.organizationName = $org"
	Add-Content $servicecfg " organizationalUnitName = $service"
	Add-Content $servicecfg " commonName = $thisfqdn"

	&$openssl req -new -nodes -out $netappcsr -keyout $tempkey -config $servicecfg
	&$openssl rsa -in $tempkey -out $netappkey
	Remove-Item $tempkey
	certreq -submit -config "$rootCA\$rootCAName" -attrib "CertificateTemplate:WebServer" $netappcsr $netappcrt
	&$openssl pkcs12 -export -in $netappcrt -inkey $netappkey -certfile $root64 -name $keyalias -passout pass:netapp -out $netapppfx
	Get-Content $netappcrt > $chainpem; Get-Content $root64 >> $chainpem

###############################################################################
#
# NetApp Virtual Storage Console
#
###############################################################################

	Write-Host -Foreground "DarkBlue" -Background "White" "Updating NetApp Virtual Storage Console.."

	Stop-Service NVPF

	Write-Host -Foreground "DarkBlue" -Background "White" "Backing up current keystore.."
	$null = (New-Item -Type Directory $backupdir)
	Move-Item "$vscdir\etc\keystore.properties" $backupdir
	Move-Item "$vscdir\etc\nvpf.keystore" $backupdir

	Set-Content "$vscdir\etc\keystore.properties" "http.ssl.keystore.file=etc/nvpf.keystore"
	Add-Content "$vscdir\etc\keystore.properties" "http.ssl.keystore.password=netapp"
	Add-Content "$vscdir\etc\keystore.properties" "http.ssl.key.password=netapp"

	Write-Host -Foreground "DarkBlue" -Background "White" "Creating new NetApp Virtual Storage Console keystore.."
	$null = (&"$vscdir\jre\bin\keytool.exe" -v -importkeystore -srckeystore "$servicedir\netapp.pfx" -srcstoretype pkcs12 -srcstorepass netapp -srcalias "netapp" -destkeystore "$vscdir\etc\nvpf.keystore" -deststoretype JKS -deststorepass netapp -destkeypass netapp -destalias "netapp")
	$null = (&"$vscdir\jre\bin\keytool.exe" -alias "netapp" -noprompt -v -importcert -keystore "$vscdir\etc\nvpf.keystore" -deststoretype JKS -storepass netapp -file $netappcrt)

	Start-Service NVPF

Write-Host -Foreground "DarkBlue" -Background "White" "Done!"

Done!

Posted by: Chrissy LeMaire   Filed under: PowerShell, Security, Virtualization No Comments
11Jun/130

Update vSphere 4.1U3 and 5.0 SSL Certs with your own Windows Domain CA Certificates using PowerShell

While it took quite awhile to figure out how to replace vSphere 5.1 and 5.1U1's SSL certs, converting that script to work with 4.1U3 and 5.0. It probably helps that SSO doesn't exist (or I couldn't find it -- I haven't used vCenter on a regular basis since about 2006, but I've learned quite a bit from these SSL replacement scripts in my lab environment.)

I was surprised to find that that vSphere 4.1 and 5.0 are far more architecturally similar than 5.0 and 5.1. The 5.0 script required just one extra line of code to adjust for a different registry entry, then it worked very well on 4.1U3.

So without further ado, you can download ReplaceSSL-vSphere41-50.ps1, modify the variables as necessary and run it on each of your farm servers. This script requires you to modify just 10 variables as seen in the snippet below:

# Place the certs on a network location if your farm is larger than one server
$basedir = "\\fileserver\share\Certs"

# Enter your Windows Certificate Authority information below.
# Make sure it responds to certutil and web requests.
$rootCA = "dc.base.local"
$rootCAName = "BASE-DC-CA"
$email = "vmware@base.local"
$org = "NetNerds"
$city = "Kaplan"
$state = "LA"
$country = "US"

# Make sure you follow Derek Seaman's instructions
# to create a new certificate template @ http://goo.gl/m98FE
$certTemplate = "CertificateTemplate:VMware-SSL"

# Enter the path of your openssl.exe (0.x and 1.x are supported).
# If you don't have OpenSSL already, the script will download it for you.
$openssldir = "C:\OpenSSL-Win32"
$openssl = $openssldir+"\bin\openssl.exe"



If you are interested in the approximate steps taken, you can browse the vSphere 5.1 SSL replacement post. Just be aware that the SSO section does not apply.

Posted by: Chrissy LeMaire   Filed under: PowerShell, Security, Virtualization No Comments
11Jun/130

Update Your ESX’s SSL Certs with your own Windows Domain CA Certificates using PowerCLI

Replacing ESX SSL is the easiest of all the vSphere components, in my opinion. Unlike vSphere 5.1, you can use Microsoft's Web Server SSL template, and there's no need to use the Java keytool or reregister the service with SSO.

Below is a script I use in conjunction with my vSphere/PowerShell Replace SSL script.

This is the first time I've actually used PowerCLI so I'm unsure if this script follows Best Practices, but hey, it worked for me in my lab environment ;)

"What it does.."

  • Creates the certificate directory if it does not exist
  • Logs into specified vSphere Server
  • Automatically downloads Root64.cer from the CA's web service
  • Downloads and extracts OpenSSL if the files do not exist in the specified path
  • Generates all SSL certificates for each of the services on the server.

If $upsateesx is set to true..

  • Downloads Putty SCP
  • Checks to see if SSH is running on the esx host. If not, it temporarily enables it
  • Prompts for and validates credentials
  • Backs up all SSL Certs on the server
  • Uploads the new certs
  • Returns SSH to previous state

Once the new certs have been uploaded, you will have to restart the ESX host, or set it into maintenance mode and restart the Management services.

##############################################################################################
#
#   ESX Certificate Generation and Upload version 0.5
#   Tested on:  ESX 5.1 / vCenter 5.1U1 / PowerCLI 5.1 Release 2
#               ESX 4.1 / vCenter 4.1U3
#   No guarantees, warranties, etc.
#   Blog post: http://goo.gl/OdIlF
#
##############################################################################################

# vCenter Server FQDN
$vcserver = "vcenter41.base.local"

# It is recommended that you place the certs on a network location
$basedir = "\\fileserver\share\Certs"

# Enter your Windows Certificate Authority information
# below. Make sure your $rootCA responds to certutil and web requests.
$rootCA = "dc.base.local"
$rootCAName = "BASE-DC-CA"
$email = "vmware@base.local"
$org = "NetNerds"
$city = "Kaplan"
$state = "LA"
$country = "US"

# This can be WebServer or the VMware-SSL certificate
# template found here: http://goo.gl/m98FE
$certTemplate = "CertificateTemplate:WebServer"

# Enter the path of your openssl.exe (0.x and 1.x are supported).
# If you don't have OpenSSL already, the script will download it for you.
$openssldir = "C:\OpenSSL-Win32"
$openssl = $openssldir+"\bin\openssl.exe"

# Do you want the script to automatically backup the old ESX certs
# and upload the new certs to each esx host?
$updateesx = $true

##############################################################################################
#
#	You shouldn't need to change anything below.
#
##############################################################################################
if (!(Test-Path("$basedir"))) { $null = mkdir "$basedir" }

$backuptime = (get-date -uformat "%m%d%Y%H%M%S")
$esxhosts = @{}

Write-Host -Foreground "Black" -Background "White" "Logging into $vcserver."
if ($global:DefaultVIServers.Count -eq 0 -or ($global:DefaultVIServers).Name -ne $vcserver) {Connect-ViServer $vcserver}

Write-Host -Foreground "Black" -Background "White" "Getting list of esx servers."
$esxServers = (Get-VMHost).Name
foreach ($esxServer in $esxServers) {
	$esxdir = "$basedir\$esxServer-esx"
	$esxhosts.Add("$esxServer-esx", $esxServer)
}

Write-Host -Foreground "Black" -Background "White" "Downloading root CA Cert.."
$wc = New-Object System.Net.WebClient
$url = "http://$rootCA/certsrv/certnew.cer?ReqID=CACert&Renewal=0&Enc=b64"
$root64 = "$basedir\Root64.cer"
$wc.UseDefaultCredentials = $true
$wc.DownloadFile($url,$root64)

if (!(Test-Path($openssl))) {
	Write-Host -Foreground "Black" -Background "White" "Downloading OpenSSL.."
	$null = mkdir $openssldir
	$sslurl = "https://openssl-for-windows.googlecode.com/files/openssl-0.9.8k_WIN32.zip"
	$sslzip = "$env:temp\openssl.zip"
	$wc.DownloadFile($sslurl,$sslzip)
	$env:path = $env:path + ";$openssldir"

	Write-Host -Foreground "Black" -Background "White" "Extracting OpenSSL.."
	$shellApplication = new-object -com shell.application
	$zipPackage = $shellApplication.NameSpace($sslzip)
	$destinationFolder = $shellApplication.NameSpace($openssldir)
	$destinationFolder.CopyHere($zipPackage.Items())
	Remove-Item $sslzip
} 

$wc = New-Object System.Net.WebClient
if ($updateesx -eq $true) {
	Write-Host -Foreground "Black" -Background "White" "Downloading Putty SCP.."
	$scpurl = "http://tartarus.org/simon/20090227-kbdint-batch/x86/pscp.exe" # patched version for keyhost prompt issue
	$scp = "$env:temp\pscp.exe"
	$wc.DownloadFile($scpurl,$scp)
}

######################################################################
#
#	Generate Certs
#
######################################################################
Write-Host -Foreground "Black" -Background "White" "Generating service certs.."
foreach ($esxhost in $esxhosts.GetEnumerator()) {
	$service = $esxhost.Name
	$esxserverfqdn = $esxhost.Value
	$esxserver = $esxserverfqdn.Substring(0,$esxserverfqdn.IndexOf("."))

	$servicedir = "$basedir\$service"
	$servicecfg = "$servicedir\$service.cfg"
	$tempkey = "$servicedir\temp.key"
	$ruikey = "$servicedir\rui.key"
	$ruicsr = "$servicedir\rui.csr"
	$ruicrt = "$servicedir\rui.crt"
	$ruipfx = "$servicedir\rui.pfx"
	$chainpem = "$servicedir\chain.pem"
	$backupdir = "$servicedir\backup-$backuptime"
	$keyalias = "rui" 

	if (Test-Path($servicedir)) { $null = Remove-Item "$servicedir\*.*" } else {$null = mkdir $servicedir }

	Set-Content $servicecfg "[ req ]"
	Add-Content $servicecfg " default_md = sha512"
	Add-Content $servicecfg " default_bits = 2048"
	Add-Content $servicecfg " default_keyfile = rui.key"
	Add-Content $servicecfg " distinguished_name = req_distinguished_name"
	Add-Content $servicecfg " encrypt_key = no"
	Add-Content $servicecfg " prompt = no"
	Add-Content $servicecfg " string_mask = nombstr"
	Add-Content $servicecfg " req_extensions = v3_req"
	Add-Content $servicecfg "`n[ v3_req ]"
	Add-Content $servicecfg " basicConstraints = CA:FALSE"
	Add-Content $servicecfg " keyUsage = digitalSignature, keyEncipherment, dataEncipherment"
	Add-Content $servicecfg " extendedKeyUsage = serverAuth"
	Add-Content $servicecfg " subjectAltName = DNS:$esxserver, DNS:$esxserverfqdn"
	Add-Content $servicecfg "`n[ req_distinguished_name ]"
	Add-Content $servicecfg " countryName = $country"
	Add-Content $servicecfg " stateOrProvinceName = $state"
	Add-Content $servicecfg " localityName = $city"
	Add-Content $servicecfg " 0.organizationName = $org"
	Add-Content $servicecfg " organizationalUnitName = $service"
	Add-Content $servicecfg " commonName = $esxserverfqdn"

	&$openssl req -new -nodes -out $ruicsr -keyout $tempkey -config $servicecfg
	&$openssl rsa -in $tempkey -out $ruikey
	Remove-Item $tempkey
	certreq -submit -config "$rootCA\$rootCAName" -attrib $certTemplate $ruicsr $ruicrt
	&$openssl pkcs12 -export -in $ruicrt -inkey $ruikey -certfile $root64 -name $keyalias -passout pass:testpassword -out $ruipfx
	Get-Content $ruicrt > $chainpem; Get-Content $root64 >> $chainpem

	### Start ESX cert upload if updateesx is true and certificate generation is successful
	if ($updateesx -eq $true -and (Test-Path($ruikey)) -and (Test-Path($ruicrt))) {
		$disablessh = $null; $failedauth = 0
		$sshservice = (Get-VMHostService -VMHost $esxserverfqdn -Server $vcserver | Where { $_.Key -eq "TSM-SSH"})

		if ($sshservice.Running -eq $false) {
			Write-Host -Foreground "Black" -Background "White" "Temporarily enabling SSH on $esxserverfqdn" ; $disablessh = $true
			$null = Start-VMHostService -HostService $sshservice -Confirm:$false
		}

		Write-Host -Foreground "Black" -Background "White" "Validating authentication."
		Write-Host -Foreground "Black" -Background "White" "You can ignore any SSH keyhost prompts you may see.."
		do {
				$msg = "Enter the username and password for $esxserverfqdn";
				$creds = $Host.UI.PromptForCredential($caption,$msg,"root",$domain)
				$esxusername = $creds.username;	$esxpassword = $creds.GetNetworkCredential().password
				$esxsslpath = "$esxusername@$esxserverfqdn"+":/etc/vmware/ssl/"
				$authenticated = $null
				$checkauth = (Echo "Y" | &($scp) -scp -pw $esxpassword -ls $esxsslpath)

				if ($checkauth -eq $null) {
					$authenticated = $false
					$failedauth++
				}
			}	until ($authenticated -ne $false -or $failedauth -gt 4)	

		if ($failedauth -gt 4) { Write-Host -Foreground "Black" -Background "White" "Sorry, too many failed logins."; Break }
		Write-Host -Foreground "Black" -Background "White" "`rAuthentication accepted!"

		Write-Host -Foreground "Black" -Background "White" "Backing up current certs.."
		$null = (New-Item -Type Directory $backupdir)
		echo "Y" | &($scp) -scp -batch -pw $esxpassword "$esxsslpath/rui.key" $backupdir
		echo "Y" | &($scp) -scp -batch -pw $esxpassword "$esxsslpath/rui.crt" $backupdir

		Write-Host -Foreground "Black" -Background "White" "Uploading new certs.."
		echo "Y" | &($scp) -scp -batch -pw $esxpassword "$ruikey" $esxsslpath
		echo "Y" | &($scp) -scp -batch -pw $esxpassword "$ruicrt" $esxsslpath

		if ($disablessh) {
			Write-Host -Foreground "Black" -Background "White" "Returning SSH to disabled state on $esxserverfqdn"
			$null = Stop-VMHostService -HostService $sshservice -Confirm:$false
		}

		Write-Host -Foreground "Black" -Background "White" "Finished uploading files on $esxserverfqdn. Reboot the ESX host to activate new certificates."
	}

}

if ($updateesx -eq $true) { $null = Remove-Item $scp }

Alternatively, you can download the .ps1 file from here.

Note that you will have to re-add ESX to vCenter because the host's SSL thumbprint has changed. Regarding updating ESX's SSL, Derek Seaman suggests:

If your ESXi host is already managed by vCenter, the HA agent can get very confused by the new SSL certificate thumbprint. I would strongly suggest you first put your host in maintenance mode, remove it from the vCenter inventory, update the SSL certificate, reboot the ESXi host, then re-add it to the vCenter inventory.

Posted by: Chrissy LeMaire   Filed under: PowerShell, Security, Virtualization No Comments
11Jun/130

Update vSphere 5.1 SSL Certs with your own Windows Domain CA Certificates using PowerShell

One month ago when I finally got my vSphere lab set up, I had no idea that getting rid of those annoying untrusted SSL errors would be such a colossal undertaking. I have my own domain CA and thought it would be easy to automate the process of replacing the self-signed vSphere SSl certs with my own trusted certs.

At first, I attempted to use strictly Windows commands (certutil, certreq, etc) and PowerShell, but eventually gave in and incorporated OpenSSL into my script. Generating the certs were just the beginning, though.

Replacing the certificates in an automated fashion and getting each service to behave after the change was an extremely time-consuming task. VMware's documentation and KB articles leave a lot of room for improvement, but fortunately, David Seaman's blog was able to provide a lot of information that was either easy to miss, or missing entirely.

Numerous articles suggested using VMware's Certificate Automation Tool but the tool wasn't automated enough for my liking, even with supplemental scripts provided by other bloggers. I looked into guts of the Certificate Automation tool and, after a good bit of trial and error, replicated many of its techniques using PowerShell. Using these techniques, and following the suggestions found on forums and blogs, I was able to create a script that can replace the SSL certs of all of my vSphere lab servers in under 20 minutes, a majority of which is spent watching PowerShell stop and start services.

This script requires you to enter less than 15 variables as seen in the snippet below:

# SSO Server FQDN
$ssoserver = "vcenter.base.local"

# Place the certs on a network location if your farm is larger than one server
$basedir = "\\fileserver\share\Certs"

# Enter your SSO master password below. You will be prompted for your vCenter Server
# credentials at runtime.
$masteradmin = "admin@System-Domain"
$masterpass = "Fakepass.123"

# Enter your Windows Certificate Authority information below.
# Make sure it responds to certutil and web requests.
$rootCA = "dc.base.local"
$rootCAName = "BASE-DC-CA"
$email = "vmware@base.local"
$org = "NetNerds"
$city = "Kaplan"
$state = "LA"
$country = "US"

# Make sure you follow Derek Seaman's instructions
# to create a new certificate template @ http://goo.gl/m98FE
$certTemplate = "CertificateTemplate:VMware-SSL"

# Enter the path of your openssl.exe (0.x and 1.x are supported).
# If you don't have OpenSSL already, the script will download it for you.

$openssldir = "C:\OpenSSL-Win32"
$openssl = $openssldir+"\bin\openssl.exe"


You can see that the SSO admin username and password are in plain text. Unlike vCenter credentials, there was no easy way to validate the SSO username/password and the pros of placing the username and password there in plain text outweighed the cons. vCenter credentials were easier to validate and more of a priority for me to protect since they're usually Windows credentials.

Also, note that the default "Web Server" SSL certificate template is no longer adequate. Please visit Derek Seaman's blog for instructions on how to create a certificate template which will work for all of the vSphere services.

"What it does.."

Start up

  • If the server running the script is not the SSO server, it ensures the remote SSO Server's SSL certs have been updated first
  • Checks the registry to see which vSphere services exist on the server running the script and sets service variables
  • Creates the certificate directory if it does not exist
  • Backs up all SSL Certs on the server
  • Validates vCenter authentication if vCenter or VUM exist on the server
  • Automatically downloads Root64.cer from the CA's web service
  • Downloads and extracts OpenSSL if the files do not exist in the specified path
  • Generates all SSL certificates for each of the services on the server. Uses server name + service name as the OU so that each cert can be distinguished.

If SSO service exists

  • Stops SSO Service
  • Generates new SSO keystore using the newly created SSO SSL certificate
  • Copies Root64.cer to %programdata%\VMware\SSL\ca_certificates.cer
  • Creates new hash file in %programdata%\VMware\SSL
  • Updates SSO using rsautil.cmd
  • Starts SSO Service
  • Automatically builds service.properties and service_id files and stores them in %programdata%\VMware\ServiceIDs
  • Reregisters all services using new root certificate
  • Restarts SSO, and if they exist Log Browser, Web Client and Inventory services.

If Inventory service exists

  • Unregisters Inventory service with SSO
  • Stops Inventory service
  • Copies new certs to the Inventory service SSL directory
  • Starts Inventory Service
  • Registers Inventory service with SSO

If vCenter service exists

  • Copies new certs to the vCenter service SSL directory
  • Using credentials previously entered, logs into vCenter service's mob website to automatically invoke reloadSslCertificate
  • Restarts all vCenter related services
  • Reregisters vCenter with Inventory Service

If WebClient services exists

  • Stops WebClient and LogBrowser services
  • Removes all files from SerenityDB directory
  • Copies new certs to the Web Client and Log Browser service SSL directories
  • Stops vCenter and Inventory Services if they exist on the local server
  • Restarts SSO service on local or remote server
  • Starts vCenter and Inventory Services if they exist on the local server
  • Starts WebClient and LogBrowser services

If Update Manager exists

  • Stops Update Manager services
  • Copies new certs to the Update Manager service SSL directory
  • Generates new Update Manager keystore using the newly minted Update Manager Certificates
  • Updates registry entry with keystore password (testpassword)
  • Runs vciInstallUtils to update VUM using credentials previously entered
  • Starts Update Manager services

If Orchestrator exists

  • Copies new certs to the Orchestrator service SSL directory
  • Stops services if necessary
  • Generates new Orchestrator keystore using newly created Orchestrator certificates
  • Adds SSO Certificate to keystore
  • Restarts Orchestrator services then returns them to their previous state of Running or Stopped

If you have vCenter servers in linked mode and are running the Web Client, you may run into the error message "Cannot connect to Inventory Service on [server]" when logging into the Web Client. I have not found a predictable way to fix this. Usually, it can be solved by first restarting the server running Web Client, then restarting the vCenter/Inventory server.

This script also has other limitations, many of them similar to the Certificate Automation Tool.

Limitations

Limitations specific to this script

  • Only uses Windows Domain Certificate Authorities
  • Does not account for intermediary CAs
  • Has not been tested in large environments with HA and DRS
  • Has not been tested with environments running: VMware Site Recovery Manager, vSphere Data Recovery, vCloud Director, or third-party solutions
  • Does not have a rollback feature, yet. For rollback, I relied on Snapshots and database backups.
  • Does not have advanced logging
  • I don't recommend running this in a production environment until it's been vetted by far more people

Limitations that exist in VMware's tool that likely exist within this script

  • vCenter Single Sign-On Password cannot contain spaces
  • vCenter Orchestrator may fail to connect when using multiple vCenter Servers.
    • You can update add additional vCenter Server SSL certificates using the VMO Configuration Webpage (https://vmoserver:8283/ default login: vmware/vmware -> vCenter Server -> SSL Certificates.
    • Add all vCenter Certificates found in your Certs directory.
    • Note that if vCenter and VMO are running on the same server, the vCenter cert will be automatically added.
  • Client Not authenticated error when connecting to VMware Inventory service in Linked Mode Configurations. Wait 10 minutes and this should resolve itself.

Why I prefer using this script over VMware's

  • Requires minimal information and interaction
    • Automatically downloads OpenSSL if neccessary
    • Automatically generates the certificates based off of a few variables
    • Automatically detects services and runs the SSL updates in the necessary order without user intervention
    • If vCenter or VUM exists on the server, you wil be prompted for your vCenter credentials. This is the extent of interaction that the script will require:

  • Replaces all of the same certificates: SSO, Inventory Service, vCenter, Update Manager, Web Client/Log Browser, and Orchestrator
  • Also, works on vSphere farms with multiple servers (you must update the SSO server first)
  • It's all contained in just one (nearly 600 line) script
  • Works on 5.1 and 5.1U1

In the end, your Cert collection will look something like this:


* Note that the esx certificate output was created using this script.

And each of your services will be encrypted with trusted certificates:


     


     

And, of course, Web Client, after a couple reboots.

Getting started

  1. Ensure your Windows Domain CA certificate is trusted by members of your domain
  2. Take a snapshot of each vSphere server on which you will run this script
  3. Backup each of your databases
  4. Find a secure location on the network to store your certs (ie. \\fileserver\share\Certs)
  5. Visit Derek Seaman's blog and create a new certificate template
  6. Shut down the following services if they exist: VMware Site Recovery Manager, vSphere Data Recovery, vCloud Director, third-party solutions that connect to vCenter.
  7. Download the ReplaceSSL-vSphere51.ps1 script
  8. Change the variables
  9. Run the script first on the server running the SSO
  10. Run the script on all other servers
  11. If the Web Client connects to multiple vCenter servers, reboot the server running Web Client, as well as the server(s) running Inventory Service and vCenter.
  12. Consider running the complimentary ESX Script

Once the scripts are complete, you can visit each of your sites to confirm the SSL Certificates have been replaced. Please note that Log Browser and the Web Client take up to 5 minutes to fully restart.

Finally, bask in the glory of your trusted SSL certificates:

Posted by: Chrissy LeMaire   Filed under: PowerShell, Security, Virtualization No Comments
10Apr/131

PowerShell Get-WinEvent Bug Workaround on Windows 2008 R2 Server — Importing Windows Forwarded Events into SQL Server using PowerShell

This is sort of a continuation of my earlier post, Importing Windows Forwarded Events into SQL Server using PowerShell, where I mentioned that I was unable to get the script to work on Windows 2008 R2 due to a known bug in Get-WinEvents. I had to end up deploying my solution to a Windows 2008 R2 Server and was required to write a workaround -- here it is. As always, I prefer using natively available commands, so I eschewed LogParser and used wevtutil.exe instead.

# Grab events from the last 65 minutes
[xml]$xml = (wevtutil  /r:dc qe Application /e:Events)
# build the sql data connection
$connectionString = "Data Source=SQLSERVER;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "Events"

#/q:"*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]"
# build the datatable
$dt = New-Object "System.Data.DataTable"
$null = $dt.Columns.Add("ID")
$null = $dt.Columns.Add("LevelDisplayName")
$null = $dt.Columns.Add("LogName")
$null = $dt.Columns.Add("MachineName")
$null = $dt.Columns.Add("Message")
$null = $dt.Columns.Add("ProviderName")
$null = $dt.Columns.Add("RecordID")
$null = $dt.Columns.Add("TaskDisplayName")
$null = $dt.Columns.Add("TimeCreated")

# populate data table
$xml.Events.Event | ForEach-Object {
   $row = $dt.NewRow()  
      $eventID = $_.System.EventID."#text"
      if (!$eventID) { $eventID = $_.System.EventID }
      $row.Item("ID") = $eventID  
      $eventlevel = $_.System.Level
            switch ($eventlevel)
             {
                  1 {$eventLevel = "Critical"}
                  2 {$eventLevel = "Error"}
                  3 {$eventLevel = "Warning"}
                  4 {$eventLevel = "Information"}
             }
      $row.Item("LevelDisplayName") = $eventLevel
      $row.Item("LogName") = $_.System.Channel
      $row.Item("MachineName") = $_.System.Computer
      $row.Item("Message") = $_.RenderingInfo.Message
      $row.Item("ProviderName") = $_.System.Provider.Name
      $row.Item("RecordID") = $_.System.EventRecordID
      $row.Item("TaskDisplayName") = $_.RenderingInfo.Task
      $row.Item("TimeCreated") =  [datetime]$_.System.TimeCreated.SystemTime
   $dt.Rows.Add($row)
}
  
# Write to the database!
$bulkCopy.WriteToServer($dt)



This code imports events from the last 65 minutes. For the initial import, set $xml to wevtutil.exe qe ForwardedEvents /e:Events. As an aside, I was surprised to see that wevtutil is FAR faster than PowerShell's Get-WinEvent, especially during the initial import of a large logs.

PS C:\Scripts> Measure-Command {c:\scripts\final-getwinevent.ps1}
Days              : 0
Hours             : 0
Minutes           : 1
Seconds           : 19
Milliseconds      : 293
Ticks             : 792930218
TotalDays         : 0.00091774330787037
TotalHours        : 0.0220258393888889
TotalMinutes      : 1.32155036333333
TotalSeconds      : 79.2930218
TotalMilliseconds : 79293.0218

PS C:\Scripts> Measure-Command {c:\scripts\final-wevtutil.ps1}
Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 4
Milliseconds      : 957
Ticks             : 49571333
TotalDays         : 5.73742280092593E-05
TotalHours        : 0.00137698147222222
TotalMinutes      : 0.0826188883333333
TotalSeconds      : 4.9571333
TotalMilliseconds : 4957.1333



From 79 seconds to 5 for 5500 records! Looks like having to rewrite this was a good thing, after all.

Posted by: Chrissy LeMaire   Filed under: PowerShell, SQL Server, Windows 1 Comment
5Apr/134

Safely Enable SQL Server Agent MultiServer Administration using PowerShell

Update: You can't even independently schedule slave jobs. Count my organization as yet another that won't be implementing MultiServer Administration. Grrr.

I always forget about Multiserver Administration. I've actually never worked in an environment that uses it, even though it seems to have a lot of potential. I think one of the biggest reasons is that most organizations do not use SSL encryption between SQL Servers, yet out of the box, Multiserver Administration requires SSL encryption for communication between the master and the targets.

Want to change this option? You'll have to modify the registry. Come on, Microsoft: nobody wants to touch a production SQL Server's registry and I think this is the biggest roadblock to mass adoption of Multiserver Administration.

The registry subkey that needs to be changed is MsxEncryptChannelOptions. There isn't a whole lot of information about this subkey (such as what other services it impacts) but I'm hoping that since its prefixed with "Msx" that and sits in the SQLAgent key, the change will be isolated to Multiserver Administration. So here are the 3 options:

0 Disables encryption between this target server and the master server. Choose this option only when the channel between the target server and master server is secured by another means.
1 Enables encryption only between this target server and the master server, but no certificate validation is required.
2 Enables full SSL encryption and certificate validation between this target server and the master server. This setting is the default.


Like the table says, 2 (Encryption+SSL required) is the default. Most blogs I've seen change their option to 0 (No encryption), but I tested it with 1 (Encryption enabled+ SSL not required) and default out of the box SQL encryption settings and it worked. Microsoft says this about the default encryption:

Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.

I always prefer encryption if it's not disruptive, so this is the setting I will recommend, and the setting that is default in the script below. This script asks for the SQL Server version (SQL2k5 is not supported because I no longer use it and instance paths are more challenging.)

Write-Host "***** Set SQL Agent Encryption Options on Target Servers *****`n "

# Menu for SQL Server Version. SQL Server 2005 could work in theory, but
# it's registry values are unpredictable and I didn't want to mess.

[int]$menuChoice = 0
     while ( $menuChoice -lt 1 -or $menuChoice -gt 3 ){
     Write-host "1. SQL Server 2008"
     Write-host "2. SQL Server 2008 R2"
     Write-host "3. SQL Server 2012"
     [Int]$menuChoice = read-host "Select your SQL Server version" }
    
Switch( $menuChoice ){
     1{$SQLVersion = "10"}
     2{$SQLVersion = "10_50"}
     3{$SQLVersion = "11"}
default{$SQLVersion = "10_50"}
}

# Enter the name of your SQL Server
Write-Host "Enter the hostname of SQL Server (do not include instance name)"
$ServerName = Read-Host "If you are using a cluster, enter the individual node name"
$ServerName = $ServerName.ToUpper()

# And the instance
$Instance = Read-Host "Enter Instance Name (leave blank for default)"
if (!$Instance) {$Instance = "MSSQLSERVER" }
$Instance = $Instance.ToUpper()

Write-Host "`nOptions for Encryption`n"
[int]$menuChoice = -1
     while ( $menuChoice -lt 0 -or $menuChoice -gt 2 ){
     Write-host "0. Disables encryption between this target server and the master server."
     Write-host "1. Enables encryption only between this target server and the master server, but no certificate validation is required."
     Write-host "2. Enables full SSL encryption and certificate validation between this target server and the master server. "
     [Int]$menuChoice = read-host "Select Encryption Option for SQL Agent Master/Target Communication" }
Switch( $menuChoice ){
     0{$EncryptionOption = "0"}
     1{$EncryptionOption = "1"}
     2{$EncryptionOption = "2"}
default{$EncryptionOption = "1"}
}

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $ServerName)
$regKey= $reg.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$SQLVersion.$Instance\SQLServerAgent",$true)

if ($regkey -ne $null) {
     $oldValue = $regKey.GetValue("MsxEncryptChannelOptions")
     $regKey.SetValue("MsxEncryptChannelOptions","0000000$EncryptionOption",[Microsoft.Win32.RegistryValueKind]::DWORD)
     Write-Host "Done"
     $newValue = $regKey.GetValue("MsxEncryptChannelOptions")
     Write-Host "Server: $Servername`nOld value: $oldValue`nNew value: $newValue"
} else
{ "No match. Make sure you typed in the proper hostname and instance name." }



(Thanks to quickclix for the easy PowerShell menu code.)

Once you've run this script and modified the settings on your Target servers, you can easily setup Multiserver Administration. Note that the default setting may create a SQL Server login for the target server automatically. I uncheck that option because I'm trying to get away from local SQL Server logins and all of my SQL Agents run under the same domain account anyway.

Posted by: Chrissy LeMaire   Filed under: PowerShell, SQL Server 4 Comments
20Mar/130

Importing Windows Forwarded Events into SQL Server using PowerShell

Over the past couple weeks, I've looked into a number of ways of parsing and importing Windows Forwarded Events into SQL Server: from using SSIS to LogParser to PowerShell to setting up a linked server to the "Forwarding Events.evtx" file.

Ultimately, the only thing that worked was PowerShell's Get-WinEvent cmdlet. And then, it only worked in one specific case for me -- if the events are collected and parsed on a Windows 2012 server. As of today, there's an unresolved bug in Get-WinEvent that often results in NULL LevelDisplayName, Message, and TaskDisplayName columns. I copied the exact code below on a Win2k8 R2 server and a Win 8 workstation and ran into the NULLs issue repeatedly. Your results may vary, however, as some users have reported success by tweaking a few things in Win2k8 R2 Server.

So, fire up a Windows 2012 box, setup your SQL Server and let's get started:

The SQL Part

After looking at the data returned by Get-WinEvent, I found the following columns to be the most useful: ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated. Then I created a table using those columns:

CREATE DATABASE EventCollections
GO
USE EventCollections
GO
-- the table name loosely relates to the name of my Win Event Subscription name
CREATE TABLE [dbo].[GeneralEvents](
     [Id] [int] NULL,
     [LevelDisplayName] [varchar](50) NULL,
     [LogName] [varchar](50) NULL,
     [MachineName] [varchar](255) NULL,
     [Message] [varchar](max) NULL,
     [ProviderName] [varchar](255) NULL,
     [RecordID] [bigint] NULL,
     [TaskDisplayName] [varchar](50) NULL,
     [TimeCreated] [smalldatetime] NULL
)
-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents]
(
     [RecordID] ASC,
     [MachineName] ASC,
     [LogName] ASC
) WITH (IGNORE_DUP_KEY = ON)
GO



In order to avoid duplicates during the hourly imports, I created the table using a unique index with IGNORE_DUP_KEY = ON on 3 columns: RecordID, MachineName and LogName.

Next I had to decide how I'd get the data from PowerShell into SQL Server. After reading up on sqlservercentral.com and technet, I decided on hourly imports using sqlbulkcopy.

The PowerShell Part

Forwarded Events are a tricky thing. For some reason, the way that one would usually filter Get-WinEvent results using FilterHasTable kept returning the result Get-WinEvent : No events were found that match the specified selection criteria. I found a number of others who ran into this issue, too and similar errors occurred when people attempted to use LogParser. After all that, I didn't have much hope in FilterXML working, but it actually did! So we're going to use that after we perform our initial import.

Here's the code for the initial import which gathers ALL events in Forwarded Events.

$events = Get-WinEvent ForwardedEvents |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  

$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "GeneralEvents"
$dt = New-Object "System.Data.DataTable"

# build the datatable
$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}
  
foreach ($event in $events)
  {
     $row = $dt.NewRow()
     foreach ($col in $cols) { $row.Item($col) = $event.$col }
     $dt.Rows.Add($row)
  }
  
# Write to the database!
$bulkCopy.WriteToServer($dt)



You may noticed that I manually built a datatable instead of using Out-DataTable.ps1, which appears to be a fan favorite. I felt the code above kept things a little more tidy and the performance is still quite good.

Since Event Collection is an on-going thing, you'll likely want to import them on a regular basis. I built the necessary XML query by right clicking on Forwarded Events in Event Viewer -> Filter Current Log... -> Logged: (Change to one hour) -> Click XML tab at top -> Copy/Paste -> Voila.

Actually, using the syntax of this query, I figured out the syntax for FilterHashTable but having the GUI build my query makes it easy, so I stuck with that. Here is the code for the hourly import that you can setup in Task Scheduler.

# While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes.
# This allows the script to run for 5 minutes without any missing any events. Because we setup the
# table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database.

$xml = @'
<QueryList>
  <Query Id="0" Path="ForwardedEvents">
    <Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) &lt;= 3900000]]]</Select>
  </Query>
</QueryList>
'@

$events = Get-WinEvent -FilterXml $xml |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  

$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "GeneralEvents"
$dt = New-Object "System.Data.DataTable"

# build the datatable
$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}
  
foreach ($event in $events)
  {
     $row = $dt.NewRow()
     foreach ($col in $cols) { $row.Item($col) = $event.$col }
     $dt.Rows.Add($row)
  }

# Write to the database!
$bulkCopy.WriteToServer($dt)


With any luck, your SQL output should look something like this:

Woo.

EDIT: If you care about speed, check out this post where I write about using wevtutil instead of Get-WinEvent.

Posted by: Chrissy LeMaire   Filed under: PowerShell, SQL Server, Windows No Comments
24May/120

SQL Server Discovery Tools and Scripts

Each time I start a new contract, the first thing I do is search for all SQL Servers across any network/subnet to which I have access. While the documentation I get is usually paltry or non-existent. When it does exist, it's usually only for the production SQL Servers. Sometimes, most production SQL Servers aren't even documented; one of my clients only had 20 documented servers, and I found over 60 (including SQL Express instances, of course.)

So here I am again, starting a new contract and I'm out looking for SQL Discovery Tools. Ultimately, here are the 5 I ended up finding and using, in no particular order:

Idera SQL Discovery is a really great tool within the Idera SQL Toolbox. That one's good and , which has been in beta for years.

A new tool I discovered this time around is the Microsoft Assessment and Planning Toolkit. Free (as in beer), of course.

Next is a quick lil script that grabs all SQL Servers in Active Directory that contain the word "SQL"

import-module activedirectory
get-adcomputer -filter "Name -like '*SQL*'" |select Name

 
Next is a more thorough script written by Colin Smith which grabs a list of servers within a text file and probes their services. I created the list with the script above, though I replaced "*SQL*" with "*Server*"

#####################################################################################
## Author: Colin Smith
## Script: Get_Intstance_names.ps1
## Purpose: This script will read in a file of hotnames that has been
##          Provided of servers with SQL Server running on them. This
## script will then look at the services on that host to find
## the instance name if the instance is named. If the instance
## is a default instance the script will also report that.
#####################################################################################
$Servers = get-content "C:\servers.txt"

echo "Server, Instance" >> "C:\sqltab.txt"
        foreach ($server in $servers)
       {
              $instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}

              if (!$?)
                     {
                          echo "Failure to connect on $server" >> "C:\failures.txt"
                          echo "Failure to connect on $server"
                     }
              else
                     {
                          $instancenames = @()
                          foreach ($name in $instances)
                     {
                          if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*"))
                                   {
                                          $instancenames += $name.name
                                   }
                     }
                          foreach ($iname in $instancenames)
                     {
                                          echo "$server, $iname" >> "C:\sqltab.txt"
                                          echo "$server, $iname"
                     }
              }
         }

 
Next up, I'd like to investigate the SQL Server Active Directory Helper service and see what that's all about.. then get all these SQL Servers registered.

Posted by: Chrissy LeMaire   Filed under: Active Directory, PowerShell, SQL Server No Comments
6May/120

Using Bookpedia, SQLite, Book+ and PowerShell to Organize Your e-Book Collection

This setup is so unlikely, I doubt anyone on the 'net will be looking for this solution but: if you're an eBook loving Windows developer who uses a Macbook Pro for your primary workstation, this may appeal to you.

My Macbook Pro runs Windows 7 within Parallels. It's fast and slick and I <3 it. My eBook reader of choice is the Book+ iPad App. I recently switched from GoodReader to Book+ because of its support for Smart Folders. And I back this all up to SugarSync which I picked for the price, privacy policy and Book+ integration.

My eBook organizer is Bookpedia. If you're unfamiliar with this (MacOS only) program, it's basically a really advanced eBook library/organizer. What I love most about it is that it easily populates your eBook's metadata using various web services - including Amazon! - then stores it all to a SQLite database.

My eBook collection looked nice enough in Bookpedia: Initially, I dragged ebooks onto the dock to import, named them properly and populated their metadata using Amazon. The problem was that my files were all over the place and practically unsyncable with SugarSync and ultimately, Book+. Look how awesome:

I'm only a perfectionist when it comes to some things, and ebook organization is one of them. I want my eBooks' naming convention to be as obvious as possible: Amazon's full book title - Publisher - ISBN. I finallly accomplished this using a number of tools and a few hours of coding. Here's my setup:

  • Windows 7 in Parallels with Z: mapped to my Home directory on my Mac.
  • Amazon developer account
  • eBooks that are scattered all over my home directory but organized nicely in BookPedia using Amazon.com data
  • SQLite Studio for Windows
  • Desired single directory destination: ~/Books in Mac or Z:\Books in Windows

First thing you wanna do is make a backup of your Bookpedia database.

cp ~/Library/Application\ Support/Bookpedia/Database.bookpd ~/Library/Application\ Support/Bookpedia/backup.bookpd

Next, load it up into SQLite. Databases -> Add Database -> Z:\Library\Application Support\Bookpedia\backup.bookpd.

CREATE TABLE export (
    title     TEXT,
    filename    TEXT,
    pubdate TEXT,
    publisher TEXT,
    isbn TEXT,
    asin TEXT
    
);

insert into export
select a.ztitle, b.zurl, strftime('%Y',date(zreleasedate,'unixepoch'))+31 as pubdate,
a.zpublisher, a.zisbn, a.zasin
from zentry a join zlink b on a.z_pk = b.zentry
--where b.zurl not like '%YOUR MAC DESTINATION DIR%'

This script creates a new table because that's the only way SQLiteStudio exports data into tsv (tab separated values, which I generally prefer to CSVs) format. Once the table is created, I use a right click to export the it to C:\bookpedia.tsv, making sure to check the "column names as first row" option and ensuring the output is ascii encoded.

Now that we've got our TSV file, we'll run it against the PowerShell script below. Copy this code and save it as whatever.ps1

PowerShell Code

# winbasedir = Parallels mapped drive
$winbasedir = "Z:\"
$windestination = "Z:\books\"
$macusername = "chrissylemaire"
$csvfile = "C:\newstructure.csv"
$macdir = "file:///Users/$macusername/"

# import csv file that contains two columns, directory and filename
$csv = Import-Csv "c:\bookpedia.tsv" -Delimiter "`t"
$newcsv = @()

foreach ($item in $csv)
{  
  if ($item.filename) {
    $source = $item.filename.Replace($macdir,$winbasedir)
    $source = $source.Replace("/","\")
    $extension = $source.substring($source.length - 4, 4)
    $title = $item.title

    # Set the file's new name. In my case, I wanted Book Title - Year - Publisher - ISBN
    if ($item.pubdate) { $title = $title + " - " + $item.pubdate}
    if ($item.publisher) { $title = $title + " - " + $item.publisher}  
    if ($item.isbn) { $title = $title + " - " + $item.isbn }

    # No clue why GetInvalidFilenameChars doesn't do this right...
    # Copy-Item will not allow the characters below
    $newname = $title.Replace(":"," -")
    $newname = $newname.Replace("  "," ")
    $newname = $newname.Replace("\","-")
    $newname = $newname.Replace("/","-")
    $newname = $newname.Replace("?","") # ascii encoding changes weird chars to "?"
    $newname = $newname.Replace("[","")
    $newname = $newname.Replace("]","")
    $newname = $newname.Replace("*","-")
      
    $target = $windestination+$newname+$extension

    if ((test-path($source)) -and !(test-path($target)))
    {
      Copy-Item -path $source -destination $target -whatif

      # If the copy succeeded, add it to a CSV file that
      # Bookpedia will use to import the new clean structure
      if (test-path($target))
      {
        $newline = New-Object System.Object
        $mactarget = $target.Replace($windestination,$macdir+"Books/")
        $mactarget = $mactarget.Replace("\","/")
        Add-Member -inputobject $newline -name link -value $mactarget -MemberType NoteProperty
        Add-Member -inputobject $newline -name title -value $item.title.Replace("?","") -MemberType NoteProperty
        Add-Member -inputobject $newline -name isbn -value $item.isbn -MemberType NoteProperty
        Add-Member -inputobject $newline -name asin -value $item.asin -MemberType NoteProperty
        $newcsv += $newline
      }
    }
  }
}

$newcsv | export-csv $csvfile -noType -Force
mv $csvfile $winbasedir -Force

One thing to note: I did write this script so that it can be run multiple times. So each time you've got a batch of books that need to be cleaned up, you can just run it again without destroying your library.

So once the script has finished running, the fruits of its labor will look something beautiful like this:

Now that we've got the files copied (I don't recommend moving them, just in case something happens) and the new csv import file has been created, we'll open it in Bookpedia. First, though, I made another backup of this Bookpedia database then deleted my library. Now it's time to import the "clean" collection. Bookpedia -> File -> Import Collection.

The columns will automatically map properly because the script and Bookpedia are awesome like that.

Now Bookpedia has enough information to populate the metadata. As I said before, I chose the Amazon option for this.

So now I use SugarSync to sync up that Books directory and then use Book+ to keep that directory in sync on my iPad. What's super great about Book+, too, are the Smart Folders. Say SugarSync syncs a new PowerShell book, it will automatically appear in my PowerShell Smart Folder. Whaaaat!

Ladies and Gentlemen... the prodigiousness:

Anyone wanna rewrite this in AppleScript for me?

Posted by: Chrissy LeMaire   Filed under: OS X & iDevices, PowerShell No Comments
6Apr/122

[Client-side Workaround] SharePoint 2010 Excel Services cannot Automatically Refresh Data when using SharePoint Lists as a Data Source

So far as I can tell, Excel Services is not capable of automatic data refreshes when using SharePoint Lists as a data source. If you're looking for this functionality, you have two choices: PowerPivot (best) or scheduled client-side data refreshing (ghetto).

In order for Excel Services to communicate with other data sources to do data refreshes without your intervention, it relies on SharePoint's SSO Service, the Secure Store. There are three different methods of connecting - Unattended Service Account, Embedded Connections, & External Data Connections - and I tried all three of them in various configurations but kept running into Access Denied errors. I even tried some of that voodoo magic we were taught growing up and it didn't even help.

I finally stopped trying after reaching a dead end on the Excel Services data refresh flowchart then further confirming said dead end within the ULS which informed me that there was an "Unauthorized attempt to access session by user <username>. Session belonged to user <username of user that started the session> (Event ID: 2011)." Seems that SharePoint does not like Excel Services asking to see its lists.

To confirm, I looked up the error in the SharePoint Technical Reference and it said

Excel Services Application maintains individual user sessions. Sessions maintain state related to workbook calculations, parameters that a user sets, interactions that a user has with a live workbook, and data returned from a data source as a result of a data refresh operation. Sessions are maintained per user per workbook, and can contain private data and information. Sessions are available only to the user that started the session. The issue in this case is that a user who did not start a session attempted to access data from that session.

(Emphasis mine)

FFFFFINNNNEEE, Microsoft. Until we get PowerPivot implemented, I'll just make PowerShell do my dirty work. The script below, which you can schedule, assumes the following:

  • your workstation will be on most of the time
  • you have Excel 2010 Professional installed
  • you can use Windows Explorer view of libraries
  • versioning is turned on and limited to a certain number of versions
    (this makes 24 versions a day)
  • that you want EVERY Excel file in a library to be refreshed
  • you have write permissions to all excel files
  • you have access to do so and the files are checked in

So, it goes to the document library, searches recursively for all Excel files, opens them up, checks them out, opens them, refreshes the data connections, saves the file then checks them in with a new version. K, here goes:

For Document Libraries that require checking out files prior to editing them:

# This is the location of the document library that has the Excel files
# You must have WebDAV enabled on the server (which is default, I think)
# and the webclient service enabled and running on your workstation.
$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"

# Start Excel (it will be invisible unless you do $excel.visible = $true)
$excel = new-object -comobject Excel.Application

# Give Excel time to open or it errors inconsistently
Start-Sleep -s 3

$excelfiles = get-childitem $library -recurse -include "*.xls*"

foreach ($file in $excelfiles)
{
  $workbookpath = $file.fullname

  if ($excel.workbooks.canCheckOut($workbookpath)) {
    # open the worksheet and check it out
    $excelworkbook = $excel.workbooks.Open($workbookpath)
    $excelworkbook = $excel.workbooks.CheckOut($workbookpath)

    # Don't ask cuz I don't know (yet). You have to open it again.
    $excelworkbook = $excel.workbooks.Open($workbookpath)

    # Refresh all the pivot tables with the new data.
    $excelworkbook.RefreshAll()

    # Save and Check it in
    $excelworkbook.Save()
    $excelworkbook.CheckInWithVersion()
  }
}
$excel.quit()



And this code is for libraries that do not require check-out:

# This is the location of the document library that has the Excel files
# You must have WebDAV enabled on the server (which is default, I think)
# and the webclient service enabled and running on your workstation.
$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"

# Start Excel (it will be invisible unless you do $excel.visible = $true)
$excel = new-object -comobject Excel.Application

# Give Excel time to open or it errors inconsistently
Start-Sleep -s 3

$excelfiles = get-childitem $library -recurse -include "*.xls*"

foreach ($file in $excelfiles)
{
  $workbookpath = $file.fullname

    # open the worksheet
    $excelworkbook = $excel.workbooks.Open($workbookpath)

    # Refresh all the pivot tables with the new data.
    $excelworkbook.RefreshAll()

    # Save and Close
    $excelworkbook.Save()
    $excelworkbook.Close()
}
$excel.quit()


Wanna schedule this script hourly? schtasks /create /tn RefreshData /tr "powershell -noninteractive -nologo -command C:\scripts\refresh.ps1" /sc HOURLY. Don't forget to limit Versions in your library as this script creates 24 versions of each workbook per day.

Also, this script is 10x's faster on Windows 7 if you disable the use of the Web Proxy Autodiscovery Protocol (WPAD). IE -> Tools -> Internet Options -> Connections -> LAN Settings -> Uncheck Automatically Detect Settings.

And finally, if you find that your files are opening Read-only, make sure all instances of Excel are closed, then delete your Microsoft Office cache in %userprofile%\local settings\temporary internet files\content.mso\.

Posted by: Chrissy LeMaire   Filed under: BI, PowerShell, SharePoint 2 Comments