Advanced Mail Merge : Multiple clickable URL entries per mail to user

We are putting into place a video repository deletion rule, for the first run we need to alert the 170 video owners that their videos fall into this policy and will be deleted.

Here’s how I went about notify those users with a email merge

Start off by creating the data set of Mail Merge Recipients in Excel

How my data set looks:

Now start a Mail Merge in Word and load in the above data set as the recipients

Step 4 of the Step-by-Step Mail Merge Wizard is to write your e-mail message

This is how mine looks once the Paragraph button has been pressed

& the Field codes are showing, press Alt + F9 to toggle to this code

What does the above all mean!! – I’ll try and explain as easily as I can

Dear GivenName is displaying the GivenName of the users ID taken from Azure AD using the following method this has been add in Word by simply using Insert Merge Field then the GivenName column.

I’m next displaying the Owner ID at this point and Setting an ID1 flag again that field, to create the extra { } you must use Ctrl + F9 – Do NOT type them, your merge will not work

I’m then comparing the Owner column with an offset by one on Owner_Compare column, this is so the merge knows when I’ve come to the end of entries for one particular user. By an offset of 1 I mean this (copy the full Owner column addresses, insert a blank line at A2 across and paste in what is in your clipboard to Owner_Compare column then delete the row A1) so you have something like this:

Then on the main code block line To do this use Ctrl + F9 to insert { } then type in IF and Insert Merge Field from the menu of MERGEFIELD Owner then = and insert more { } braces and insert MERGEFIELD Owner_Compare from the menu. Then insert move braces { } and put in { SET ID2 “{MERGEFIELD Full_URL}” }

Now the really complex part on how to get clickable text in the email which will take you to a URL – It might look as simple as writing { IF ID1 = ID2 “” “{ HYPERLINK { MERGEFIELD Full_URL }{ SET HL1 “”} \ * MERGEFORMAT } but it isnt.

**If you do not want a clickable hyperlink, then just replace the above with { IF ID1 = ID2 “” “{ MERGEFIELD Full_URL } and now jump to the end of this post**

To get the HYPERLINK you need to do the following :

In Word Select Insert tab & Find Quick Parts / Field…

Select Hyperlink

This will paste in the following to your merge document { HYPERLINK \* MERGEFORMAT } now press Alt + F9 and you will see Error! Hyperlink reference not valid. double click on the word Hyperlink to highlight it, now use Insert Merge Field to select the text of the link, in my case this is Name now you will see something like

Which is the current recipients file inserted in the text, delete all the text around this, so you are then left with

Now press Alt + F9 & insert the merge field of the URL so you have something which looks like this

Now copy that into the code block

**Be careful after editing your mail merge several times you might notice that this then shows as something like this, at this stage your whole code is messed up and you will need to paste back in the above formatted code, otherwise all your links will be incorrect locations based on one of your users and not necessarily the user you are on.**

I noticed the above happened on every single load of Word !!! But do not worry, if you followed the above you’ve wont see the issue I had. The explanation is below.

Completely unacceptable, so I did so more searching and figured out that you need to add a Bookmark to the HYPERLINK to stop the static URLs, so it looks something like the below, the part you care about is that it has a { SET HL2 “” } before the \ *. This should stop you from having to edit the Mail Merge document each time before you run a merge. Please note, I’ve also noticed that there seems to be a limit of Bookmarks allowed – its around 44 – 48 I think. I need 50 ! Typical..

Okay now copy your IF block and change the IF to a NEXTIF (I’ve noticed people not doing this and some versions of Word then ignore your first record). Now paste that down the page as many times as you like or as many times as your computer can handle, you may notice your machine slowing down a this stage. I have it 50 times in my email merge – meaning it’ll display up to 50 entries per email, if there are that many available for that user.

Right now is the time to test your email merge to a small set of email addresses and check that the URLs been sent are correct, test again before the day comes that you send it to the full amount of people – and just before, do make sure that in Alt + F9 mode, it hasnt replaced the formatting of the url to a actual url.

One further part to note, I attempted to get to be sent from a alternative mailbox, I couldn’t because Office365 needed a licence on the alternative account, which it did not have.

Good luck – this took me 4 days to crack.

Search CSV for matching Azure AD accounts

I needed to search our Azure AD for who of the usernames contained in a CSV were real ID and who were not.

This is how I did it.

First of all you need to installed the Azure Active Directory module in Powershell, make sure you launch as a Administrator

Install-Module -Name AzureAD

Connect-AzureAD

Then

$data = Import-Csv ‘C:\Users\OneDrive\ids.csv’

$data | ForEach-Object {
Get-AzureADUser -ObjectId $_.UserPrincipalName | Select-Object DisplayName,UserPrincipalName,Department | Export-csv -append -notypeinformation “C:\Users\OneDrive\names.csv”
}

In the ids.csv I have a list with the header of ‘UserPrincipalName’

Run the above one part at a time and it’ll write DisplayName,UserPrincipalName,Department into the names.csv

When I get chance I’ll change the above to show a username does not exist entry if that is the case, current it just tells you this in the powershell window.

Selenium / New Relic Synthetics login using Microsoft AD + Check for text

After the recent Microsoft Azure AD outage of 15/0/3/2021 I thought how can I actually see whether our page is displaying what should be there and not just a API call error generated because of the Microsoft outage.

To do that I added step 6 ‘find text’ see below.

/**
 * Script Name: {Moodle Login}
 * 
 */

/** CONFIGURATIONS **/

// Theshold for duration of entire script - fails test if script lasts longer than X (in ms)
var ScriptTimeout = 180000;
// Script-wide timeout for all wait and waitAndFind functions (in ms)
var DefaultTimeout = 30000;
// Change to any User Agent you want to use.
// Leave as "default" or empty to use the Synthetics default.
var UserAgent = "default";

/** HELPER VARIABLES AND FUNCTIONS **/

const assert = require('assert'),
	By = $driver.By,
	browser = $browser.manage()
/** BEGINNING OF SCRIPT **/

console.log('Starting synthetics script: {Untitled Test Case}');
console.log('Default timeout is set to ' + (DefaultTimeout/1000) + ' seconds');

// Setting User Agent is not then-able, so we do this first (if defined and not default)
if (UserAgent && (0 !== UserAgent.trim().length) && (UserAgent != 'default')) {
  $browser.addHeader('User-Agent', UserAgent);
  console.log('Setting User-Agent to ' + UserAgent);
}

// Get browser capabilities and do nothing with it, so that we start with a then-able command
$browser.getCapabilities().then(function () { })
	.then(() => {
            logger.log(1, "https://your.site.co.uk/login/index.php");
            return $browser.get("https://your.site.co.uk/login/index.php"), DefaultTimeout;
        })
	.then(() => {
            logger.log(2, "click Sign in on Moodle page");
            return $browser.waitForAndFindElement(By.linkText("Sign in"), DefaultTimeout)
                .then(function (el) {
                    el.click();
                })
        })
	.then(() => {
            logger.log(3, "Pass Test Username");
            return $browser.waitForAndFindElement(By.name("loginfmt"), DefaultTimeout)
                .then(function (el) {
                    el.sendKeys($secure.USER);
                })               
        }).then(function(el){
            //Find and click the login button.
            return $browser.waitForAndFindElement(By.xpath("//input[@value='Next']"), DefaultTimeout)
                .then(function (el) {
                    el.click();
                })
        })            
	.then(() => {
            logger.log(4, "pass the password securely");
            return $browser.waitForElement(By.name("passwd"), DefaultTimeout)
                .then(function (el) {
                    el.sendKeys($secure.PASS_ID);
                })               
        }).then(function(el){
            //Find and click the login button.
            return $browser.waitForAndFindElement(By.xpath("//input[@value='Sign in']"), DefaultTimeout)
                .then(function (el) {
                    el.click();
                })
        })      
    .then(() => {
            logger.log(5, "find YOUR text");
            var textToFind = "YOUR TEXT";
            var pageText = $browser.waitForAndFindElement(By.tagName("html"), DefaultTimeout).getText()
                .then(function(body){
                    assert.ok(body.indexOf(textToFind) != -1,"Text "+ textToFind+ " not found in page");
	        });
        })             
	.then(function() {
		logger.end();
		console.log('Browser script execution SUCCEEDED.');
	}, function(err) {
		logger.end();
		console.log ('Browser script execution FAILED.');
		throw(err);
	});


//** Export Functions
const logger=(function (timeout=3000, mode='production') {

    var startTime = Date.now(),
        stepStartTime = Date.now(),
        prevMsg = '',
        prevStep = 0;


    if (typeof $util == 'undefined'  ){
        $util = {
            insights: {
                set: (msg) => {
                    console.log(`dryRun: sending to Insights using ${msg}`)
                }
            }
        }

    }

    function log(thisStep, thisMsg) {

        if (thisStep > prevStep && prevStep != 0) {
            end()
        }

        stepStartTime = Date.now() - startTime;

        if (mode != "production") {
            stepStartTime = 0

        }

        console.log(`Step ${thisStep}: ${thisMsg} STARTED at ${stepStartTime}ms.`);

        prevMsg = thisMsg;
        prevStep = thisStep;

    }

    function end() {
        var totalTimeElapsed = Date.now() - startTime;
        var prevStepTimeElapsed = totalTimeElapsed - stepStartTime;

        if (mode != 'production') {
            prevStepTimeElapsed = 0
            totalTimeElapsed = 0
        }

        console.log(`Step ${prevStep}: ${prevMsg} FINISHED. It took ${prevStepTimeElapsed}ms to complete.`);

        $util.insights.set(`Step ${prevStep}: ${prevMsg}`, prevStepTimeElapsed);
        if (timeout > 0 && totalTimeElapsed > timeout) {
            throw new Error('Script timed out. ' + totalTimeElapsed + 'ms is longer than script timeout threshold of ' + timeout + 'ms.');
        }
    }

    return {
        log,
        end
    }
})(ScriptTimeout)

Clone a Moodle Theme 3.10 >

Time has come around again for a major upgrade of Moodle 3.8 to 3.10, with this bring the fun of making sure the theme is working okay. Adaptable has just released their latest theme for Moodle 3.10, so I’ve taken this and cloned it. For this makes it easier to know which theme works on which version of Moodle we have installed. In the past we’ve needed to flip the DNS to the new production server.

Hopefully the below will work for you, it took me 10 attempts to crack it, without having random php / file errors showing on the browser or in dev tools.

Step 1

Download the latest version of your theme, we are using the 3.10 version of adaptable here. In Notepad ++ use the ‘Find in Files’ function to locate everything ‘Adaptable‘ which is the current name of the theme, replace that with your new name ‘ManMet-Adaptable‘. This should be two hits in one file, then hit ‘Replace in Files’. This just changes the naming in the language pack

Step 2

Now search for ‘theme/adaptable‘ and replace all with ‘theme/manmet_adaptable‘ (dont use a minus symbol in the theme path). This should find 24 hits in 8 files, now replace all with ‘Replace in Files’.

Step 3

Search for the original name of the theme ‘adaptable‘, change this to ‘manmet_adaptable‘, this is a massive file and replace with 3199 hits in 149 files.

Step 4

Now search the theme code for ‘/adaptable/‘ and replace with ‘/manmet_adaptable/‘. This should replace 33 hits in 9 files.

Step 5

Now find ‘theme_adaptable‘ and change that to ‘theme_yourname‘, in my case this is ‘theme_manmet_adaptable‘. This should be replacing around a large 2201 hits in 148 files.

Step 6

The unknown step.. I think it is required 🙂 I think it’s to do with the tabs in the theme settings. Search for ‘THEME_ADAPTABLE‘ and replace with ‘THEME_MANMET_ADAPTABLE‘, this should return around 18 hits in 7 files.

Step 7

Check places like config.php in the root of your theme and see that the main theme’s .css matches what is in /styles dir, also check that the naming in \jquery\plugins.php matches the name of the .js file in \jquery it should be under

$plugins = array(
    'manmet_adaptable' => array('files' => array('adaptable_v2_1_1_2.js')),

Step 8

Change the name of the theme folder to match, zip it up and deploy for testing – Good luck ! should the above method not work for you, do try the one which was working on < Moodle 3.9 which I wrote in 2018.

How to Change Moodle Azure AD Tenancy

I need to switch a UAT instance of Moodle to an alternative Azure AD Tenancy, this is how I went about it.

See my original post here on how to setup the app registration in Azure here then head to /admin/settings.php?section=local_o365 within the Moodle instance which you want to change over

In the below you need to change over you Application ID & Application Key, you get these from Azure

Lower down on the same page you need to also change these two fields to the new Tenancy and Sharepoint URL

Then Save the page at the very bottom.

Now on your instances Moodle database you need to run something like the following to change all your existing users over to the new Tenancy

#SQL to change all students to new UPN format

update mdl_user
set username =
REPLACE(username, '@.xxx.ac.uk', '@.xxx.ac.uk')
WHERE auth = 'oidc'
AND username regexp '^[0-9]{8}\@.xxx.ac.uk$'
AND email like '%@xxx.xxx.ac.uk';

#SQL to change all staff to new UPN format

update mdl_user
set username =
REPLACE(username, '@.xxx.ac.uk', '@.xxx.ac.uk')
WHERE auth = 'oidc'
AND username regexp '^[0-9]{8}\@.xxx.ac.uk$'
AND email like '%@xxx.ac.uk';

Check that this has had the desired effect in /admin/user.php and try to login to the site with the alternative UPN ID – remember in a incognito tab / private browser window on your browser of choice.

#Lockdown Home projector / Large ‘Disney’ wall art project

How do you go about painting a favourite character on your childs wall?

These are my recommended steps

  • Find suitable wall
  • Clean the wall with sugar soap, this will remove the grime
  • Paint wall if not the correct background colour
  • Purchase some acetate sheets
  • Draw a picture on the sheet with a thin permanent marker
    • If your art skills are not up to that:
    • Trace from another drawing or purchase the more expensive printable acetate sheets and print image on using a laser printer (Do not attempt to put in a non printable acetate sheet in a laser printer – they melt)
  • Find a suitable A4 sized box and tape the acetate sheet to it
  • Cut hole for a light source in back of box (I used a LED Maglite torch, which are super powerful)
  • Place the box and torch in a place where they cannot be moved
  • Darken the room and switch on your home projection unit
  • Use a HB pencil to draw on the wall

Now start painting with Arylic paint, I used these Daler-Rowney, System 3 Introduction Set they include all primary & secondary colours in order to mix up any not included

Fin

Selenium / New Relic Synthetics login using Microsoft AD

We needed a synthetics script which can tell us when our site authentication is broken. The following is running in our New Relic monitoring, but can be ran in numerous other monitoring tools

The following can be adapted for anyone’s site, please note that your authentication process may not hit the following Microsoft screen in log step 4 so might need to be changed or removed completely, in my case it selects the ‘Work or school account’ using a xpath method.

** Additional, I swapped to using a test accounts which doesn’t go to the account selector page shown below, this seems to be working much better across all synthetics servers hosted in different countries – I noticed that French & Italian based ones occasionally threw issues. The account selector page only appears if you have two Microsoft accounts which are using the same email address :

If you received the "Which account do you want to use?" message when you sign in, it means you have two accounts with Microsoft that use the same email address
/**
 * Script Name: {Moodle Login}
 * 
 */

/** CONFIGURATIONS **/

// Theshold for duration of entire script - fails test if script lasts longer than X (in ms)
var ScriptTimeout = 180000;
// Script-wide timeout for all wait and waitAndFind functions (in ms)
var DefaultTimeout = 30000;
// Change to any User Agent you want to use.
// Leave as "default" or empty to use the Synthetics default.
var UserAgent = "default";

/** HELPER VARIABLES AND FUNCTIONS **/

const assert = require('assert'),
	By = $driver.By,
	browser = $browser.manage()
/** BEGINNING OF SCRIPT **/

console.log('Starting synthetics script: {Untitled Test Case}');
console.log('Default timeout is set to ' + (DefaultTimeout/1000) + ' seconds');

// Setting User Agent is not then-able, so we do this first (if defined and not default)
if (UserAgent && (0 !== UserAgent.trim().length) && (UserAgent != 'default')) {
  $browser.addHeader('User-Agent', UserAgent);
  console.log('Setting User-Agent to ' + UserAgent);
}

// Get browser capabilities and do nothing with it, so that we start with a then-able command
$browser.getCapabilities().then(function () { })
	.then(() => {
            logger.log(1, "https://your.site.ac.uk");
            return $browser.get("https://your.site/login/index.php"), DefaultTimeout;
        })
	.then(() => {
            logger.log(2, "click Sign in on Moodle page");
            return $browser.waitForAndFindElement(By.linkText("Sign in"), DefaultTimeout)
                .then(function (el) {
                    el.click();
                })
        })
	.then(() => {
            logger.log(3, "Pass Username");
            return $browser.waitForAndFindElement(By.name("loginfmt"), DefaultTimeout)
                .then(function (el) {
                    el.sendKeys($secure.USERNAME);
                })               
        }).then(function(el){
            //Find and click the login button.
            return $browser.waitForAndFindElement(By.xpath("//input[@value='Next']"), DefaultTimeout)
                .then(function (el) {
                    el.click();
                })
        })          
 	.then(() => {
            logger.log(4, "Select xpath Work or school account");
            return $browser.waitForAndFindElement(By.xpath("(.//*[normalize-space(text()) and normalize-space(.)='Work or school account'])[1]/following::small[1]"), DefaultTimeout) 
                .then(function (el) {
                    el.click();
                })
        })   
	.then(() => {
            logger.log(5, "Pass the password securely");
            return $browser.waitForAndFindElement(By.name("passwd"), DefaultTimeout)
                .then(function (el) {
                    el.sendKeys($secure.PASSWORD);
                })               
        }).then(function(el){
            //Find and click the login button.
            return $browser.waitForAndFindElement(By.xpath("//input[@value='Sign in']"), DefaultTimeout)
                .then(function (el) {
                    el.click();
                })
        })               
	.then(function() {
		logger.end();
		console.log('Browser script execution SUCCEEDED.');
	}, function(err) {
		logger.end();
		console.log ('Browser script execution FAILED.');
		throw(err);
	});


//** Export Functions
const logger=(function (timeout=3000, mode='production') {

    var startTime = Date.now(),
        stepStartTime = Date.now(),
        prevMsg = '',
        prevStep = 0;


    if (typeof $util == 'undefined'  ){
        $util = {
            insights: {
                set: (msg) => {
                    console.log(`dryRun: sending to Insights using ${msg}`)
                }
            }
        }

    }

    function log(thisStep, thisMsg) {

        if (thisStep > prevStep && prevStep != 0) {
            end()
        }

        stepStartTime = Date.now() - startTime;

        if (mode != "production") {
            stepStartTime = 0

        }

        console.log(`Step ${thisStep}: ${thisMsg} STARTED at ${stepStartTime}ms.`);

        prevMsg = thisMsg;
        prevStep = thisStep;

    }

    function end() {
        var totalTimeElapsed = Date.now() - startTime;
        var prevStepTimeElapsed = totalTimeElapsed - stepStartTime;

        if (mode != 'production') {
            prevStepTimeElapsed = 0
            totalTimeElapsed = 0
        }

        console.log(`Step ${prevStep}: ${prevMsg} FINISHED. It took ${prevStepTimeElapsed}ms to complete.`);

        $util.insights.set(`Step ${prevStep}: ${prevMsg}`, prevStepTimeElapsed);
        if (timeout > 0 && totalTimeElapsed > timeout) {
            throw new Error('Script timed out. ' + totalTimeElapsed + 'ms is longer than script timeout threshold of ' + timeout + 'ms.');
        }
    }

    return {
        log,
        end
    }
})(ScriptTimeout)

Lost your MySQL database password?

create a ‘passwordrecovery.py’ Python file with the following contents:

import win32cryptimport osencrypted_data = open("C:\\Users\\{}\\AppData\\Roaming\\MySQL\\Workbench\\workbench_user_data.dat".format(os.getlogin()), "rb").read()clear_data = win32crypt.CryptUnprotectData(encrypted_data, None, None, None, 0)print(clear_data)

Downloaded Python for Windows and install :  Then run the following

pip install pypiwin32

Upgrade it if it suggests to:

C:\Users\<YourUser>\AppData\Local\Microsoft\WindowsApps\PythonSoftwareFoundation.Python.3.8\python.exe -m pip install --upgrade pip

 Add the following to my PATH: 

C:\Users\<YourUser>\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8\LocalCache\local-packages\Python38\Scripts 

Run the following from the command line :
python c:\python\passwordrecovery.py

It then showed me my passwords 🙂

A UK VLE during Pandemic lock-down

It’s been a busy few months for us, so I just thought I’d sum up what we’ve been doing to attempt to protect us during the COVID-19 pandemic:

  • Moved our Moodle instance to Azure
  • Upgraded from Moodle 3.6.3 to Moodle 3.8.2
    • Moved to a Boost based theme
    • Emails passed to on premise Exchange to be sent

The move to Azure was crucial to best serve our students who are now more than ever relying on our VLE. This year all our exams and coursework submissions are taking place within Moodle.

With the above it goes without saying we needed to be on the securest available version available which happens to be the latest release of Moodle, this is in order to minimise any malicious attacks.

If you are planning such moves, I would suggest moving a like for like instance to the cloud as step #1, then perform any required upgrades, don’t be tempted to combine the two stages.

How to Produce Moodle Reports which are like the ones in the Moodle Logs

Ever wondered why you can not get the Moodle database to produce a report just like it appears within Moodle graphical user interface logs?

Well that because rows like ‘Description’ is made from multiple parts.

Its bugged me for a while and googling turned up a blank, so I wrote the following, with a little help from a friend – Ta Jim.

SELECT
    from_unixtime(l.timecreated) Time,
    CONCAT(u.firstname, ' ', u.lastname) AS 'User full name',
    CONCAT(au.firstname, ' ', au.lastname) AS 'Affected user',
    u.username AS 'Username',
    l.contextid AS 'Event context',
    l.component AS 'Component',
    l.Eventname AS 'Event name',
    CONCAT('The user with id ',u.id,' ',l.action,' the ', l.objecttable,' ',
    CASE
        WHEN l.contextlevel = 10 THEN 'system'
        WHEN l.contextlevel = 50 THEN 'course'
        WHEN l.contextlevel = 30 THEN 'user'
        WHEN l.contextlevel = 40 THEN 'category'
        WHEN l.contextlevel = 70 THEN 'activity'
        WHEN l.contextlevel = 80 THEN 'block'
              END,
    ' with module id ', l.courseid) AS 'Description',
    l.origin AS 'Origin',
    l.ip AS 'IP address'
FROM moodle.mdl_logstore_standard_log l
    INNER JOIN moodle.mdl_user u ON u.id = l.userid
    LEFT JOIN moodle.mdl_user AS au ON au.id = l.relateduserid
#Change l.courseid to the internal id of moodle course you want to check out
WHERE l.courseid = '10001'
ORDER BY time DESC;

This will produce results from the database which are like the following from the Moodle Logs in the GUI

Time

User full name

Affected user

Event context

Component

Event name

Description

Origin

IP address

6 February 2020, 9:02 PM Raymond Reid Coursework: Business 101 Coursework Course module viewed The user with id ‘1480’ viewed the ‘course’ activity with course module id ‘2100’. web x.x.x.x

 

Hope it helps..