Search This Blog

Monday, June 27, 2016

SQL Server: Allow readonly access to all databases (including future)

Recently had to setup readonly access to all databases on a SQL Server, in the past this would have involved assigning roles to a login and that login to all databases, and then repeating for all new databases.

In SQL 2014 a new command allows for this to be simplified.





Tuesday, June 21, 2016

Powershell: sending emails via office365 TLS

Recently had to work on sending emails from a powershell script using the office365 smtp server. This means I had to use TLS and also use authentication.

Script below is pretty simple and does not encrypt the password in the script this just a poc.

The script creates an array of users to send the email to ($users), then this is looped through.

This line creates the email object, this is the best way to send email and I believe the only way if you need to enable TLS.

$client = New-Object system.Net.Mail.SmtpClient $smtpserver, $port 

Using the new email object this line enables TLS

$client.EnableSsl = $true 

This next line creates the user credentials that the object will use to authenticate with office365 system when it tries to send the email.

$client.Credentials = New-Object System.Net.NetworkCredential( $office365User , $office365Password ); 

I leave in, but its commented out, but you can get it you use the account the script is run under.

#$client.Credentials = [system.Net.CredentialCache]::DefaultNetworkCredentials 

Full script

[string]$to = ""
[String]$from = "<>"
[String]$subject = "<subject>"
[String[]]$users = "<>, <>"
[String]$smtpserver = ""
[string]$office365User = '<>';
[string]$office365Password = '<office365UserPassword>';
[String]$port = 25

foreach ($user in $users)
        #$CRLF added because usual `r`n in string doesnot work within trap.
        [string]$CRLF = [char]13 + [char]10
        $script:errorMessage += '"' + $smtpserver + '" : Error: {0}' -f $_.Exception.Message + $CRLF;
    $to      = $user
    $output = "<pre>" + "<font color='#800000'>scriptErrors: `r`n" + $script:errorMessage + "</font>`r`n"
    $output += "<b>" + $outputHeader + "</b><font color='#800000'>" + $outputAlert + "</font>" + "<b>" + $outputSubHeader + "</b>" + $outputAll + "</pre>"

    # Create mail message
    $message = New-Object
    $message.From = $from;
    foreach ($useremail in $to)
    $message.Subject = $subject;
    $message.Body = $output;
    $message.IsBodyHtml = $true;
    #Create SMTP client
    $client = New-Object system.Net.Mail.SmtpClient $smtpserver, $port
    $client.EnableSsl = $true
    # Credentials are necessary if the server requires the client # to authenticate before it will send e-mail on the client's behalf.
    #$client.Credentials = [system.Net.CredentialCache]::DefaultNetworkCredentials
    $client.Credentials = New-Object System.Net.NetworkCredential( $office365User , $office365Password );
    # Try to send the message
    # reset variables
    $body = ""


Thursday, June 16, 2016

Powershell: IIS: Set application pool idle-timeout and application pool recycle time.

Most of my IIS scripts will be setting the IIS config options in the applicationhost.config file. This is a personal preference, it puts all config in one central location and prevents the web.config files potentially being modified and deleted in code deployments.

Add-PSSnapin WebAdministration -ErrorAction SilentlyContinue
Import-Module WebAdministration -ErrorAction SilentlyContinue
#<name> is the application pool name
--Set the idle time to 0(off)
Set-ItemProperty ("IIS:\AppPools\<name>") -Name processModel.idleTimeout -value ( [TimeSpan]::FromMinutes(0))
--disable the regular time of 1740 minutes
Set-ItemProperty ("IIS:\AppPools\<name>") -Name Recycling.periodicRestart.time -Value "00:00:00"
--Clear any scheduled restart times
Clear-ItemProperty ("IIS:\AppPools\<name>") -Name Recycling.periodicRestart.schedule
--Set scheduled restart times
Set-ItemProperty ("IIS:\AppPools\<name>") -Name Recycling.periodicRestart.schedule -value @{value="05:00:00"}


Tuesday, June 14, 2016

Powershell: Ping with timestamp, logging and tail

I include a refernce to the page where I found out how to timestamp each ping request at the bottom of this article.

This code excerpt will timestamp each ping request, redirect it to a log file and then we can use the get-content command to look at the contents of the log file as it is written to (like tail in linux)

filter timestamp {"$(Get-Date -Format o): $_"}
ping servername -t | timestamp > c:\temp\logging.txt
#tail log
Get-Content c:\temp\logging.txt -wait

To tail in powershell

Get-Content c:\temp\logging.txt -Tail 10 –Wait

Returns last 10 lines,  -wait will then wait for new entries



Wednesday, June 08, 2016

Powershell: Find and Kill defined processes owned by specific user.

[string]$userName = 'user1';
[string]$exeName = 'cmd.exe';
Get-WmiObject -Query "Select * from Win32_Process where name = "$exeName" | Select Name, Handle, @{Label='Owner';Expression={$_.GetOwner().User}} | Where-object {$_.Owner -like "$userName"} | Select-object @{Label='Handle';Expression={[int]$_.Handle}} |foreach { stop-process -id $_.Handle -force }


Powershell: IIS add request filtering (CFIDE)

Recently had to add some requestfiltering deny rules to a  number of servers. This was because of a detected vunerabiltiy with Coldfusion and its admin portal.

So to achieve this quickly and consistently I developed the following powershell script.

It adds a deny rule at the server level and then an removes the rule for the CF administration portal site.

The first two lines ensure that the web administration tools are imported in all versions of powershell.

I like my config to be in the central applicationhost.config file, which runs less risk of developers overwriting the config in a web.config file. The line


ensures the config is saved in the applicationhost.config file.

–location "CFAdminPortal”

This line ensures that the config is applied to the site called CFAdminPortal

Add-PSSnapin WebAdministration
Import-Module WebAdministration
#Add requestfiltering
Add-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST' -filter "system.webServer/security/requestFiltering/denyUrlSequences" -name "." -value @{sequence='/CFIDE'}
#Remove requestfiltering  for the CFIDE site
Remove-WebConfigurationProperty -pspath 'MACHINE/WEBROOT/APPHOST' –location "CFAdminPortal" -filter "system.webServer/security/requestFiltering/denyUrlSequences" -name "." -AtElement @{sequence='/CFIDE'}


Powershell: Cloudflare autoupdate public IP and send SMS

We had a connection that ran our company VPN. The VPN was available on a couple of URLs. This connection also had a backup connection, so if the primary link failed it would swap to the backup. However this would change the IP address.
I wanted to make it so that the when the connection failed over the VPN URLs were updated.
The script below achieves that by updating the DNS records in cloudflare with the new public IP of the connection.
It looks at the public IP and then compares it to the record in dns, if the same do nothing. If different then update and send SMS
The SMS api maybe different for you depending on the company you use.
function Get-ScriptDirectory
    #Determine the folder in which the script lives.
    $Invocation = (Get-Variable MyInvocation -Scope 1).Value
    Split-Path $Invocation.MyCommand.Path
#needed to find the location of the script.
$scriptPath = Get-ScriptDirectory

[String]$scriptCurrentDateTime = Get-Date -format "yyyyMMddHHmmss"

$headers = @{
        'X-Auth-Key' = '<auth-key>';
        'X-Auth-Email' = '<email>';
        'Content-Type' = 'application/json'

[string]$urlRequest = '';
[string]$smsUsername = '<username>';
[string]$smsPassword = '<password>';
[string]$smsNumbers = '<phonenumber1>,<phonenumber2>,<phonenumber3>';
[string]$smsFrom = '<Company Name>';
[string]$smsMessage = [uri]::EscapeDataString('Office IP Address has changed. Attempting auto update.');

$ZoneRecords = @(

[string]$Zone = '';
[string]$cloudFlareApiBaseUrl = '';

# Get Zone ID from cloudflare
        $request = Invoke-WebRequest -Uri "${cloudFlareApiBaseUrl}/zones/?name=${Zone}" -Method 'GET' -Headers $headers
        $MyError = $_
        Throw $MyError
    $zoneId = $(ConvertFrom-Json $request.Content).result[0].id

#Lookup external ip
        $public_IP = (Invoke-RestMethod
        $MyError = $_
        Throw $MyError

Foreach ($Element IN $ZoneRecords) {

    #GET record info from cloudflare
    #$RecordVPNweb = Invoke-WebRequest -Uri "${cloudFlareApiBaseUrl}/zones/${ZoneId}/dns_records/?name=${Element}" -Method 'GET' -Headers $headers
        $RecordVPN = Invoke-RestMethod -Uri "${cloudFlareApiBaseUrl}/zones/${ZoneId}/dns_records/?name=${Element}" -Method 'GET' -Headers $headers -ContentType 'application/json'   
        $MyError = $_
        Throw $MyError
    #When using webrequest need to convert from jason
    #$RecordVPN_ID = $(ConvertFrom-Json $RecordVPN.Content).result[0].id
    #$RecordVPN_IP = $(ConvertFrom-Json $RecordVPN.Content).result[0].content
    $RecordVPN_ID = $RecordVPN.result[0].id
    $RecordVPN_IP = $RecordVPN.result[0].content
    if ($RecordVPN_IP -ne $public_IP) {

        $smsMessage = $smsMessage + [uri]::EscapeDataString("Changing from $RecordVPN_IP to new $public_IP");
        $queryString = "username=$smsUsername&password=${smsPassword}&to=${smsNumbers}&from=${smsFrom}&message=${smsMessage}";
        [string]$textfileName = "$scriptPath\IPChanged$scriptCurrentDateTime.txt"
            #send sms
            [String]$scriptIPChangeDateTime = Get-Date -format "yyyy-MM-dd HHmmss"
            [string]$textfileContent = "IP Change attempted at $scriptIPChangeDateTime : ${urlRequest}+${queryString}"
            out-file -filepath $textfileName -inputobject $textfileContent -encoding ASCII
            Invoke-RestMethod -Uri "${urlRequest}+${queryString}" -Method 'GET';   
            $MyError = $_
            Throw $MyError

        $Data = @{
        'id' = "${RecordVPN_ID}";
        'type' = 'A';
        'name' = "${Element}";
        'content' = "${public_IP}";
        # If it exists, UPDATE (PUT), if not, CREATE (POST)
        [string]$method = 'PUT';
        if ($RecordVPN.result.Count -eq 0) {
            $method = 'POST';

        $DataJson = ConvertTo-Json $Data

            $JSONResponse = Invoke-RestMethod -Uri "${cloudFlareApiBaseUrl}/zones/${ZoneId}/dns_records/${RecordVPN_ID}/" -Headers $Headers -Body $DataJson -Method "${method}" -ContentType 'application/json' -ErrorAction Stop
            $MyError = $_
            Throw $MyError


Tuesday, June 07, 2016

Creating Code block for blogger

Its great news that Live writer has ben made open source by Microsoft. Please see Open Live Writer (OLW)

There is the code plugin for wordpress

this has been updated to work with Open Live Writer. However I am still waiting on a plugin for blogger in OLW. So in the meantime my process is this.

  1. Open the code in notepad++, which does nice simple syntax highlighting.
  2. Use the nppExport plugin to export to html. (note: I could not get this to work to the clipboard I had to export to a file. A minor pain.
  3. Then grab the <style> section, this details the css and how the code will be formatted. Paste this into the html view of the blog post in OLW.
  4. The following CSS has been inserted into the blogger template. Defines a class to apply to the div containing the code and then a style to apply to pre tags within that div.

divCodeBlock {
  word-wrap: break-word;
  width: 95%;
  background: #ffffff;
  white-space: pre-line;
  border-top: black thin solid;
  border-right: black thin solid;
  border-bottom: black thin solid;
  border-left: black thin solid;
  padding-bottom: 2px;
  padding-top: 2px;
  padding-left: 2px;
  padding-right: 2px;
  margin-left: auto;
  margin-right: auto;
  line-height: 1;

div.divCodeBlock pre {
  white-space: pre-wrap; /* Since CSS 2.1 */
  white-space: -moz-pre-wrap; /* Mozilla, since 1999 */
  white-space: -pre-wrap; /* Opera 4-6 */
  white-space: -o-pre-wrap; /* Opera 7 */
  word-wrap: break-word; /* Internet Explorer 5.5+ */

  1. In source view in OLW insert the following div tag where you want to place your code block.
    <div class="divCodeBlock"> <pre></pre></div>
  2. Now grab the page content in notepad++, this will be within <div> tags in the <body>.  And place into the
    <div class="divCodeBlock"> <pre><content here></pre></div>

Publish and bang, “Bob's your auntie's live-in lover”


Cloudflare API delete multiple records via Powershell

Recently whilst setting up cloudflare DNS, I had to delete a lot of records. With the API you can only do it one at a time. With thousands of records to remove I put together a little script tot do this.

Please ensure you fully test what you are doing with this, as I take no responsibility, but it worked for me.

You will various things to make this work, and I will assume you know how to use the API, by supplying your API key and email etc….

The script below can, with a small change, either read in the records to delete from a file or you can manually create the array in the script.

The script also contains output debugging that I left in.

Hope it helps somebody Smile

$headers = @{
"X-Auth-Key" = '<you need to get this from cloudflare>';
"X-Auth-Email" = '<replace this with email address>';
"Content-Type" = 'application/json'
#this may need changing when Cloudflare update the api
[string]$cloudFlareApiBaseUrl = ""
#file with records in it
[string]$recordlist= 'c:\path\fileRecordList.txt';
#dns domain to use
[string]$Zone = "";

#Manually create array, add records here to delete.
$ZoneRecords = @(

#Create array from file, records to delete.
$ZoneRecords = Get-content -Path ".\${recordlist}"

# Get Zone ID from cloudflare
$request = Invoke-WebRequest -Uri "${cloudFlareApiBaseUrl}/zones/?name=${Zone}" -Method "GET" -Headers $headers
$zoneId = $(ConvertFrom-Json $request.Content).result[0].id

Foreach ($Element IN $ZoneRecords) {

#GET record info from cloudflare
$Record = Invoke-WebRequest -Uri "${cloudFlareApiBaseUrl}/zones/${ZoneId}/dns_records/?name=${Element}" -Method "GET" -Headers $headers
$RecordID = $(ConvertFrom-Json $Record.Content).result[0].id
$RecordName = $(ConvertFrom-Json $Record.Content).result[2].id


#DELETE record info from cloudflare
$RecordDelete = Invoke-WebRequest -Uri "${cloudFlareApiBaseUrl}/zones/${ZoneId}/dns_records/${RecordID}" -Method "DELETE" -Headers $headers

'RecordName:' + $RecordName;
'RecordID:' + $RecordID;

The format of the text file is just a flat text file


Saturday, June 04, 2016

Setting Kossel Delta 3D printer

I have recently purchased a Kosssel Delta printer from aliexpress.

Trying to set this thing up was a lot more involved than I was hoping, and in addition having a family tends to not leave a lot of time to play. And the instructions are in chinglish Smile, saying that I managed to construct the printer following the user manual supplied, so its not that bad. However the calibration is a bit more involved.

If you are reading this and a kossel delta printer is going to be your first printer I would recommend you purchase a normal 3d printer, they are a lot easier to get up and running.

Anyway, like most of my blog, these are notes really for more myself. So I am assuming some knowledge pronterface/arduino.

Set Max Height


Click Home (G28)

Current Pos (M114) – Note Z reading (Zmax)

Now get the head down close to the bed using the controls in pronterface put the head near the printer bed. Place a piece of paper between the head and the platform bed, using the controls (0.1mm each step) get the head down onto the paper, until you are unable to move the paper.

Current Pos (M114) – Note Z reading (Zmin)

Now work out the Z range

Zmax – Zmin = Zrange

294.60 – (-5.69) = 300.29

Now disconnect pronterface from the printer.

Fire up arduino software and open the Marlin.ino file, this will open the project. We need to edit the configuration.h file. We need to update MANUAL_Z_HOME_POS

// The position of the homing switches
#define MANUAL_HOME_POSITIONS  // If defined, MANUAL_*_HOME_POS below will be used
#define BED_CENTER_AT_0_0  // If defined, the center of the bed is at (X=0, Y=0)

//Manual homing switch locations:
// For deltabots this means top and center of the cartesian print volume.
#define MANUAL_Z_HOME_POS 300.29
// For delta: Distance between nozzle and print surface after homing.

OK, so now the bed height at [X0 Y0] should be 0. So we will call this Zm.

Ok, so we are started. The Z height is set. now lets do some manual leveling… We are going to take the Z readings from various points (X,Y) over the bed.


Using the same method we used to get the Z reading a X=0, Y=0. edge the head down until you unable to move the A4 paper.

  1. [G1 X0 Y66 Z10] (Z Tower)
  2. Adjust the height until you cannot move A4 paper
  3. [M114] = X0 Y66 Za – record Za
  4. Adjust the stop screw on the roller for the nearest tower. If Za is positive we need to decrease the height of the screw and vice versa.
  5. [G28] (home)
  6. Try again, until Za is 0.

Now repeat for

  • [G1 X-66 Y-66 Z10] (X Tower): Zb
  • [G1 X566 Y-66 Z10] (Y Tower): Zc

Once you have Za, Zb, Zc readings at 0. Read them all again to ensure they are all still 0, adjust if necessary,

G28 return to home

Now we are going to adjust for the curvature (not sure that is the right term but we are going to be setting the SMOOTH_ROD_OFFSET setting in the configuration.h file.)

Now lets read the Z bed at [G1 X0 Y0]  again (using the A4 process). Lets call this Zz. You should find its no longer 0. Now there probably is some scientific way to do this but my manual was just trial and error…. SmileNow Zm was 0.

  • Zz < Zm : increase SMOOTH_ROD_OFFSET
  • Zz > Zm : decrease SMOOTH_ROD_OFFSET

Adjust in the configuration.h file and upload to arduino again.

-0.5 is less than 0, so I need to increase

// Horizontal offset from middle of printer to smooth rod center.
#define DELTA_SMOOTH_ROD_OFFSET 154// mm

So I adjusted to 155

// Horizontal offset from middle of printer to smooth rod center.
#define DELTA_SMOOTH_ROD_OFFSET 155// mm

Now we are just trying to get Za,Zb,Zc,Zz to the same value, so don’t expect Zz to change. You must keep taking measurement of all and adjust the various settings…. Its a pain…

I had to adjust the stops again on the Z Tower roller. Then I retook the readings. Zz was –0.7 and Za,Zb,Zc were –0.5. So increase the ROD offset

// Horizontal offset from middle of printer to smooth rod center.
#define DELTA_SMOOTH_ROD_OFFSET 155.5// mm

With this settings and a few adjusts of the stops on each roller I got all Z values to match at -0.7

Ok, now we need to set the max height again.

Zmax – Zmin = Zrange

300.29 – (-0.7) = 300.99

#define MANUAL_Z_HOME_POS 300.29

Note: In trying to setup my printer I had to go through this process about 15 times (thats no joke). Many failed prints…

Things learnt, don’t adjust the flowrate, i thought some of my jams were being caused by this. But in the end I reckon I just made it worse.

Best result I got was setting temp to 195. I did jump into repetier and adjust the speed in the slicer config. but in the end did that fix anything… who knows…. Smile

One thing I did in one of the many hot end rebuids was to push the filament sleeve all the way down inside the hot end. Original I just pushed it into the connector as I thought it was just guiding the filament in. However I now think this provides guidance and some heat protection as it travels down. I think this was a caused of the jams I was having.

Also I believe I had the feed wheels clamped too tight, which was marking/crushing the filament. As such this caused the filament to jam.

In all I think I have spent around 20 –30 hours setting this thing up…

The future is now….


p.s. One other thing when rebuilding the hot end, let it heat up and then tighten again. I did a run without doing this and it leaked out from the screw threads…