Mike Says Meh The blog of Sys Admin Mike Kauspedas


Powershell script to automate Windows Updates using PSWindowsUpdate

PSWindowsUpdate is a module that automates windows updates using PowerShell. I work with a lot of desperate environments that aren't connectedand not all machines are on a domain. The ones that are use different domains that are not trusted. That means WSUS isn't an option. There are some third party options that could work like Shavlik, but I'm cheap and there aren't enough servers to warrant the cost.

Using PSWindowsUpdate is great, but what's the point in using it if I have to first manually install it on each server? I'm supposed to be "DevOps" after all and they automate or die. Something like that.

First, it downloads PSWindowsUpdate to the user's downloads folder.
Then unzips it into the modules folder.
Then imports and runs the command to install all and reboot. Warning, this will install ALL updates and reboot. You can edit it to your liking, hide updates, and choose not to reboot. Just check the documentation here. Great guide here as well.

Also, after you have ran this once the module is installed and next maintenance night you can simply run Get-WUInstall.

Invoke-WebRequest -Uri "https://gallery.technet.microsoft.com/scriptcenter/2d191bcd-3308-4edd-9de2-88dff796b0bc/file/41459/43/PSWindowsUpdate.zip" -OutFile "$env:USERPROFILE\Downloads\PSWindowsUpdate.zip"
$shell = new-object -com shell.application
$zip = $shell.NameSpace("$env:USERPROFILE\Downloads\PSWindowsUpdate.zip")
foreach($item in $zip.items())
Import-Module PSWindowsUpdate
Add-WUServiceManager -ServiceID 7971f918-a847-4430-9279-4a52d1efe18d -Confirm:$false
Get-WUInstall –MicrosoftUpdate –AcceptAll –AutoReboot
Filed under: Tech No Comments

Copy blobs between storage accounts using AzCopy

The easy way is using AzCopy. There are also methods to do this asynchronously in powershell but I like the path of least resistance.

  1. Install Microsoft Azure Storage Tools from here. http://aka.ms/downloadazcopy
  2. Get the URL for both storage account containers that contain the blob.
  3. Get the name of the blob
  4. Get the access keys for both storage accounts
  5. Change the values in the script below to match the source, destination, keys, and pattern (blob).
  6. Run this in powershell.
    cd "C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy" #Get to AzCopy dir
    $source = "https://SourceSAName.blob.core.windows.net/ContainerName" #Source storage account and container
    $dest = "https://DestSAName.blob.core.windows.net/ContainerName" #Destination storage account and container
    $sourcekey = "***************" #Source storage account key
    $destkey = "*****************" #Destination storage account key
    $pattern = "AblobFile.vhd" #The blob to copy. Can be anything, just put the full file name including extension
    #Run the command
    .\AzCopy /Source:$source /Dest:$dest /sourcekey:$sourcekey /destkey:$destkey /Pattern:$pattern
Filed under: Tech No Comments

Enable Microsoft AntiMalware UI in Azure virtual machine

Simple powershell to enable the Microsoft Antimalware UI in an Azure virtual machine. This is the antimalware extension you can install on deployment, or through security center.

  1. Seeing this when trying to open System Center Endpoint Protection on an Azure virtual machine?


  2. By default the UI is disabled on an Azure VM. To enable you just need to change a registry value. Here is a quick powershell script to do that.

    $registryPath = "HKLM:\SOFTWARE\Policies\Microsoft\Microsoft Antimalware\UX Configuration"
    $Name = "UILockdown"
    $value = "0"
    New-ItemProperty -Path $registryPath -Name $name -Value $value -PropertyType DWORD -Force | Out-Null

  3. After running that in powershell you can open the UI.



Filed under: Tech No Comments

Bulk add DNS records to domains using GoDaddy DNS

As part of a migration into Azure I need to add the awverify cname record to a LOT of domains. GoDaddy actually made this easy with the bulk command in their portal UI.

  1. Login to GoDaddy and find one of the domains you need to add the records to. Search for the domain and then click on it.
  2. Click on the DNS Zone File tab and add the record(s) you need.
  3. Select the new record(s) by checking the box next to it then click on Bulk Actions at the top and then Copy.
  4. You can either select the domains you want to copy the records to or give GoDaddy a list.
  5. Once you are done give it 10-15 minutes to complete the action.
Filed under: Tech No Comments

Powershell – Find the name servers for a list of domains and spit the results to CSV

Change the path of $list to the path of your text file with the domains. The domains need to be one per line in the text file. Change the $exportPath to where you want to save the CSV file. Then copy and paste the code below into a .ps1 file and run from PowerShell.

$list = "C:\meh\Domains.txt"
$exportPath = "C:\meh\meh.csv"
$apps = get-content $list

$nsresults = @()
foreach ($app in $apps) {
$nsresults += Resolve-DnsName -Type 2 -Name $app | select -first 1 Name, NameHost
$nsresults | Export-Csv -Path $exportPath

Filed under: Coding, Tech No Comments

Fix Certificate warning in Outlook for Mac

A secure connection cannot be established with the server <domain.com> because its intermediate or root certificate cannot be found. Do you want to continue?

If you continue, the information that you view and send will be encrypted, but will not be secure.

Microsoft has a suggestion for fixing this, issue a new certificate with the domain added as a SAN (subject alternative name) or just accept the wrong cert. But I bet you actually have a certificate for the domain name you have Office 365 or Exchange setup on, so why not use it? Here's how.

  1. You need to track down where the domain is pointing to determine where the certificate needs to be installed. You may or may not know this, and I understand because like many of you I am a sys admin that takes over control of already existing systems. Just ping the domain name in the certificate warning (the <domain.com> bit, replace that with the domain in your warning). That will give you an IP, now track that IP to whatever web server, load balancer, or firewall it may be.
  2. Get your certificate ready. I work with Microsoft and IIS nearly exclusively so I have a handy PFX (certificate + private key, don't let this out of your sight). Simply install that certificate to wherever that IP address is terminating. When Outlook resolves the domain name it will try and pull the cert from that device/server.

In my case the server was a reverse proxy load balancer, running ARR, IIS, and network load balancer. With ARR I have SSL offloading enabled so the certificate actually comes from this load balancer, not the web server. I added a binding to the site in IIS with the cert and the warning went away.

Filed under: Tech No Comments

Enable change tracking (CDC) on a MSSQL database when getting Msg 22830

Recently we received a request to enable change tracking on a database but kept getting an error even under a user with the SA role.

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 195
Could not update the metadata that indicates database DBNAME is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.

The reason for this error is because you don't have a valid login for the database. This is probably happening, because like me, you restored the DB to another server. So just run the command below to switch the owner and enable CDC. Make sure to change DBNAME to the name of your database.

sp_changedbowner 'sa'

EXEC sys.sp_cdc_enable_db

Filed under: Tech No Comments

It’s Friday – Here is a pic of a SQL server with 1.5TB of RAM

If you're a geek like me then this is pretty f'n cool. Kind of like being able to drive a really fast car. Another cool thing, there are three of these in production.






Filed under: Tech No Comments

FTP access to Azure website with your publishing file

Here is an easy way to gain FTP access to your Azure website.

  1. Download your publishing file from the website dashboard.
  2. Open FileZilla and from the dashboard paste the FTPS address into the address bar. (Always use FTPS when available). The location is on the right hand side. This is an image of mine, yours may be a different address depending on where you website is hosted in Azure.

    azure ftps

  3. Open the publish file with notepad++ (or notepad). The file is going to be named YOUR-SITE-NAME.azurewebsites.net.PublishSettings.
  4. Your user name is the sitename\username. If your site name is contoso and your username is $contosouser in the publish file than your user name would be contoso\$contosouser in FileZilla.

    filezilla azure

  5. that's it, just click connect and accept the certificate.
Filed under: Tech No Comments

Azure website phpmyadmin site extension “No route registered for ‘/phpmyadmin/”

If you aren't already aware there are some helpful tools for Azure websites found at https://YOUR-WEBSITE-NAME-HERE.scm.azurewebsites.net. For example if you website name in Azure websites is mikmeh than your scm URL would be https://mikmeh.scm.azurewebsites.net. You'll need to be logged in, or login to view the site. Lets assume you already know about this and you also discovered the site extensions gallery and you clicked the install button for phpmyadmin. Then after it installed you clicked the play button and you get this.

 No route registered for '/phpmyadmin/ 

There is a super easy fix. Just stop your website in Azure. You don't even need to start it back up, the scm runs under a different worker process. Now you have phpmyadmin the super easy way. It even connects to your existing databases.

Filed under: Tech No Comments