Friday, 21 July 2017

Querying a PostgreSQL database

Firstly ensure your user has the adequate permissions to connect to the postgres server in pg_hba.conf.

For the purposes of this tutorial I will be using the postgres user:

sudo su - postgres

psql
\list
\connect snorby

or

psql snorby

For help we can issue:

\?

to list the databases:

\l

and to view the tables:

\dt

to get a description of the table we issue:

\d+ <table-name>

we can then query the table e.g.:

select * from <table> where <column-name> between '2017-07-19 15:31:09.444' and '2017-07-21 15:31:09.444';

and to quite:

\q

Thursday, 20 July 2017

Resolved: wkhtmltopdf: cannot connect to X server

Unfortunately the latest versions of wkhtmltopdf are not headless and as a result you will need to download wkhtmltopdf version 0.12.2 in order to get it running in a CLI environment. I haven't had any luck with any other versions - but please let me know if there are any other versions confirmed working.

The other alternative is to fake an X server - however (personally) I prefer to avoid this approach.

You can download version 0.12.2 from here:

cd /tmp
wget https://github.com/wkhtmltopdf/wkhtmltopdf/releases/download/0.12.2/wkhtmltox-0.12.2_linux-centos7-amd64.rpm
rpm -i wkhtmltox-0.12.2_linux-centos7-amd64.rpm
rpm -ql wkhtmltox

/usr/local/bin/wkhtmltoimage
/usr/local/bin/wkhtmltopdf
/usr/local/include/wkhtmltox/dllbegin.inc
/usr/local/include/wkhtmltox/dllend.inc
/usr/local/include/wkhtmltox/image.h
/usr/local/include/wkhtmltox/pdf.h
/usr/local/lib/libwkhtmltox.so
/usr/local/lib/libwkhtmltox.so.0
/usr/local/lib/libwkhtmltox.so.0.12
/usr/local/lib/libwkhtmltox.so.0.12.2
/usr/local/share/man/man1/wkhtmltoimage.1.gz
/usr/local/share/man/man1/wkhtmltopdf.1.gz

Tuesday, 18 July 2017

Exporting MSSQL Databases (schema and data) from Azure

Because Microsoft have disabled the ability to perform backups / exports of MSSQL databases from Azure directly from the SQL Management Studio (why?!) we now have to perform this from the Azure Portal.

A new format introduced as a 'bacpac' file allows you to store both the database schema and data within a single (compressed) file.

Open up the Resource Group in the Azure Portal, select the relevant database >> Overview >> and then select the 'Export' button:

Database export

Unfortunately these can't be downloaded directly and will need to be placed into a storage account.

If you wish to import this into another Azure Tenant or an on-premise SQL server you'll need to download the Azure Storage Explorer to download the backup.

Friday, 14 July 2017

A crash course on Bash / Shell scripting concepts

if statement

if [[ $1 == "123" ]]
then echo "Argument 1 equals to 123"
else
echo "Argument 1 does not equal to 123"
fi

inverted if statement

if ! [[ $1 == "123" ]]
then echo "Argument 1 does not equal to 123"
fi

regular expression (checking for number)

regex='^[0-9]+$'
if [[ $num =~ regex ]]
then echo "This is a valid number!"
fi

while loop

NUMBER=1
while [[ $NUMBER -le "20" ]]
do echo "The number ($NUMBER) is less than 20"
NUMBER=$((NUMBER + 1))
done

awk (separate by char)

LINE=this,is,a,test
echo "$LINE" | awk -F ',' '{print The first element is $1}'

functions

MyFunction testing123
function MyFunction {
                    echo $1
                  }

read a file

while read -r LINE
do echo "Line: $LINE"

done < /tmp/inputfile.txt

case statement

case $1 in
[1-2]*) echo "Number is between 1 and 2"
        ;;
[3-4]*) echo "Number is between 3 and 4"
        ;;
5) echo "Number is 5"
        ;;
6) echo "Number is 6"
        ;;
*) echo "Something else..."
        ;;
esac

for loop

for arg in $*
do echo $arg
done

arrays

myarray=(20 "test 123" 50)
myarray+=("new element")

for element in {$myarray[@]}
do echo $element
done

getting user input

echo Please enter how your age:
read userinput
echo You have entered $userinput!

executing commands within bash

DATE1=`date +%Y%m%d`
echo Date1 is: $DATE1
DATE2=$(date +%Y%m%d) # preferred way
echo Date2 is: $DATE2

To be continued...

Setting up Octopus Tentacle on Windows Server 2012/2016 Core

For this tutorial I will be setting up Octopus Tentacle in a container running Server 2016 Core.

Let's firstly create our container with:

docker run -it --cpus 2 --memory 4G --network=<network-id> --name windowscore -h <your-hostname> microsoft/windowsservercore cmd.exe

Ensure that your computer name is correct and setup in DNS - so both Octopus and the server running the Tentacle can communicate with each other.

and for the purposes of this tutorial we will use a static IP and also join to to our domain with Powershell:

Get-NetIPInterface | FL # grab the relevant interface ID from here

Ensure DHCP is disabled on the NIC:

Set-NetIPInterface -InterfaceIndex 22 -DHCP Disabled

and assign a static IP address with:

New-NetIPAddress -InterfaceIndex 22 -IPAddress 10.11.12.13 -PrefixLength 24
Remove-NetRoute -InterfaceIndex 22 -DestinationPrefix 0.0.0.0/0
New-NetRoute -DestinationPrefix 0.0.0.0/0 -InterfaceIndex 22 -NextHop 10.11.12.1

and then DNS servers:

Set-DnsClientServerAddress -InterfaceIndex 22 -ServerAddresses {10.1.2.3, 10.3.2.1}

and join to the domain with:

$domain = "myDomain"
$password = "myPassword!" | ConvertTo-SecureString -asPlainText -Force
$username = "$domain\myUserAccount"
$credential = New-Object System.Management.Automation.PSCredential($username,$password)
Add-Computer -DomainName $domain -Credential $credential

and finally reboot the host:

shutdown -t 0
docker start <your-container>
docker attach <your-container>

Let's firstly get into powershell and download the latest version of Octopus tentacle:

powershell.exe
mkdir C:\temp
cd C:\temp
Start-BitsTransfer -Source https://download.octopusdeploy.com/octopus/Octopus.Tentacle.3.15.1-x64.msi -Destination C:\temp

and then perform a quite installation of it:

msiexec /i Octopus.Tentacle.3.15.1-x64.msi /quiet

We now need to configure the tentacle - so let's firstly create a new instance:

exit # exit out of powershell as service installation fails otherwise
cd "C:\Program Files\Octopus Deploy\Tentacle"
tentacle.exe create-instance --instance "Tentacle" --config "C:\Octopus\Tentacle.config" --console

and generate a new certificate for it:

tentacle.exe new-certificate --instance "Tentacle" --if-blank --console
tentacle.exe configure --instance "Tentacle" --reset-trust --console

and create the listener:

Tentacle.exe configure --instance "Tentacle" --home "C:\Octopus" --app "C:\Octopus\Applications" --port "10933" --console

add your Octopus Deploy server footprint in:

Tentacle.exe configure --instance "Tentacle" --trust "YOUR_OCTOPUS_THUMBPRINT" --console

Sort out the firewall exception:

"netsh" advfirewall firewall add rule "name=Octopus Deploy Tentacle" dir=in action=allow protocol=TCP localport=10933

Finally register the tentacle with the Octopus Deploy server:

Tentacle.exe register-with --instance "Tentacle" --server "http://YOUR_OCTOPUS" --apiKey="API-YOUR_API_KEY" --role "web-server" --environment "staging" --comms-style TentaclePassive --console

Note: You will likely need to generate an API key - this can be generated from the Octopus web interface, clicking on your username >> Profile and then hitting the API tab.

and install / start the service with:

Tentacle.exe service --instance "Tentacle" --install --start --console

We can now verify this with:

sc query "OctopusDeploy Tentacle"

Thursday, 13 July 2017

Script to remove bad characters from a set of files

The need for this script was prompted by a series of files being uploaded to Sharepoint which had special characters within their filenames such as an astrix or tilde.

Although there are many ways to achieve this I chose for a simplistic approach using cp and sed.

We can use the sed substitute function to replace any bad characters - we have the following directory we wish to 'cleanse':

ls /tmp/test

drwxrwxr-x.  2 limited limited  120 Jul 13 13:45 .
drwxrwxrwt. 40 root    root    1280 Jul 13 13:43 ..
-rw-rw-r--.  1 limited limited    0 Jul 13 13:45 'fran^k.txt'
-rw-rw-r--.  1 limited limited    0 Jul 13 13:44 @note.txt
-rw-rw-r--.  1 limited limited    0 Jul 13 13:44 'rubbi'\''sh.txt'
-rw-rw-r--.  1 limited limited    0 Jul 13 13:43 'test`.txt'

We can run a quick test to see what the results would look like just piping the result out to stdout:

#!/bin/bash
cd /tmp/test
FileList=*
for file in $FileList; 
    do (echo $file | sed s/[\'\`^@]/_/g ); 
done;

Note: The 'g' option instructs sed to substitute all matches on each line.

Or an even better approach (adapted from here):

#!/bin/bash
cd /tmp/test
FileList=*
for file in $FileList; 
    do (echo $file | sed s/[^a-zA-Z0-9._-]/_/g ); 
done;

The addition of the caret (^) usually means match at the beginning of the line in a normal regex - however in the context where the brace ([ ]) operators are used in inverse the operation - so anything that does not match the specified is replaced with the underscore character.   

If we are happy with the results we can get cp to copy the files into our 'sanitised directory':

#!/bin/bash
cd /tmp/test
FileList=*
OutputDirectory=/tmp/output/
for file in $FileList; 
    do cp $file $OutputDirectory$(printf $file | sed s/[^a-zA-Z0-9._-]/_/g); 
done;

There are some limitations to this however - for example the above script will not work with sub directories properly - so in order to cater for this we need to make a few changes:

#!/bin/bash

if [ $# -eq 0 ]
  then
    echo "Usage: stripbadchars.sh <source-directory> <output-directory>"
    exit
fi

FileList=`find $1 | tail -n +2` # we need to exclude the first line (as it's a directory path)
OutputDirectory=$2
for file in $FileList
    do BASENAME=$(basename $file)
    BASEPATH=$(dirname $file)
    SANITISEDFNAME=`echo $BASENAME | sed s/[^a-zA-Z0-9._-]/_/g`
    # cp won't create the directory structure for us - so we need to do it ourself
    mkdir -p $OutputDirectory/$BASEPATH
    echo "Writing file: $OutputDirectory$BASEPATH/$SANITISEDFNAME"
    cp -R $file $OutputDirectory$BASEPATH/$SANITISEDFNAME
done

Note: Simple bash variables will not list all files recursively - so instead we can use the 'find' command to do this for us.

vi stripbadchars.sh
chmod 700 stripbadchars.sh

and execute with:

./stripbadchars.sh /tmp/test /tmp/output




Configuring NICs on Windows Server 2016 from the command line / Powershell

It seems that in Server 2016 they have removed some of the functionality in some older utilities such as netsh, netdom etc.

So in order to configure IP addresses from the command line it looks like we should put our trust solely in Powershell (*cringes*.)

In order to set a static IP address there are a few commands we need to run - firstly disabling the DHCP on the relevant NIC:

Get-NetIPInterface | FL # grab the relevant interface ID from here
Set-NetIPInterface -InterfaceIndex 22 -DHCP Disabled

*Note: We can also use the 'InterfaceAlias' switch to provide the NIC's name - however personally I prefer using the Index.

In my experience disabling DHCP on the interface does not always wipe any existing configuration - so we should remove any exsiting IP address's with:

Remove-NetIPAddress -InterfaceIndex 22 -IPAddress 10.11.12.13 -PrefixLength 24

and any default route associated with the interface:

Remove-NetRoute -InterfaceIndex 22 -DestinationPrefix 0.0.0.0/0

And then assign the IP address, subnet mask and default route (if any) with:

New-NetIPAddress -InterfaceIndex 22 -IPAddress 12.13.14.15 -PrefixLength 24
New-NetRoute -DestinationPrefix 0.0.0.0/0 -InterfaceIndex 22 -NextHop 12.13.14.1

and finally any DNS servers:

Set-DnsClientServerAddress -InterfaceIndex 22 -ServerAddresses {8.8.8.8,8.8.4.4}