June 18, 2009
Posted in Moodle
at 6:10 pm
WARNING: DO NOT ATTEMPT ANY OF THIS IF YOU DO NOT FULLY UNDERSTAND IT. YOU CAN DESTROY YOUR MOODLE SITE.
Recently I discovered our Moodle gradebook was playing up and not correctly recording marks from assignments as well as been unable to assign categories from quizes.
I tracked the problem down to the Moodle database losing all its default entries for its tables. The cause is unknown but is quite likely the UTF conversion utility I ran previously to make the system compatible with Asian languages for LOTE.
To fix this problem I needed to recreate the Moodle DB structure and then reimport the data.
Backup
First make a backup. I made a mistake the first time I tried repairing this and the backup I made saved me.
[code language="bash"]mysqldump -u backupacc -p -Q --opt moodle > moodleBackup.sql[/code]
I created a duplicate site with a clean DB (copy the moodle folder and create a new DB) and went through the install. This needs to have all your modules and blocks to ensure all tables are created.
I then dumped the structure from this new DB and the old DB and compared them using Meld (or any other diffing program will work).
[code language="bash"] mysqldump -u backupacc -p --no-data --skip-add-drop-table moodletest2 > moodleGoodStruc.sql
mysqldump -u backupacc -p -d --skip-add-drop-table moodle > moodleOldStruc.sql[/code]
Using Meld determine if there are any extra tables or fields in your current system that don’t exist in the clean one.
Then you need to determine if it is safe to delete those tables / field in your main DB or if they need to be added to the clean system. e.g. grade_items_history.decimals and grade_items_history.display were in the main DB but not the new one. After searching for it discovered they can be removed (MDL-15985).
After getting them to match you need to dump your Moodle data from your main site.
You can speed up the next steps by reducing the size of backup_log if you wish. Just do a
[code language="sql"]
Delete
FROM `mdl_backup_log`
WHERE `time` <1242777600
[/code]
Where the time code is calculated as a unix time code from about a month before todays date. This can reduce alot of space of your DB, and improve import/export times.
[code language="bash"]mysqldump -u backupacc -p --no-create-info --extended-insert --complete-insert moodle > moodleData200906171559.sql[/code]
You need the –complete-insert option to ensure each insert is labelled with field name, in case field order is different. I didn’t do this the first time and needed to restore the backup.
Testing Merge
Now you need to test it. Create a new empty DB and import the Structure then import your data.
[code language="bash"]mysql -u root -p moodleTest3 < moodleStruc.sql
mysql -u root -p moodleTest3 < moodleData200906171559.sql[/code]
Check that no errors occurred during import, this is when I discovered most of my problems with extra non existent field.
Prep for Applying
Put your site in Maintenance mode so no users can login while you are testing this. (i.e. when you restore data site will already be in maintenance mode)
When everything is OK do another backup, as after this you are going to delete it all. (I recommend taking your site completely offline for this, not just maintenance mode. Edit your config.php and temporarily change either the username, password or db name or your database settings.)
Now re-export the Data again (assuming your site has been running while you have been doing tests)
[code language="bash"]mysqldump -u backupacc -p --no-create-info --extended-insert --complete-insert moodle > moodleData200906171559.sql[/code]
Applying to Site
WARNING: HAVE A GOOD RELIABLE BACKUP BEFORE THIS STEP. THIS WILL DESTROY YOUR MOODLE SITE.
Now use phpMyAdmin to drop every table in the Moodle main DB. Just use Check All at the bottom and select Drop.
Now you need to redo your test restore above but to your main DB.
Once complete reconnect your config.php file and check everything on your site looks the same. Open some courses, check some gradebooks, have a look around.
When its OK take out of Maintenance mode and you should be good to go.
Tags:
backup,
Meld,
Moodle,
mysqldump,
SQL
Permalink
February 20, 2009
Posted in Windows
at 3:26 pm
Google Earth is a great program for schools, as it allows teachers and students to explore the worlds geography and use layers to examine history, socio-economic information and many other areas.
It used to be fairly easier to deploy on your network, but after version 4.2 it became significantly more difficult if you use a deployment program like Zenworks. A new MSI test was introduced to determine where to install Google Earth. If the current user is not an Admin User it would redirect to [LocalAppDataFolder]Google\Google Earth (usually C:\Documents and Settings\username\Local Settings\Application Data\Google\Google Earth) which made it inaccessible to other users.
As Zenworks uses the System account, which is not an admin user, this would happen when the installer was launched by a student.
To fix this problem there are a few different techniques. You can use either ORCA or Admin Studio Tuner (from Zenworks) to create a transform file that ignores these checks. Alternatively you can use ORCA to edit the msi file directly, I had to use this technique for V5 due to some exisiting problems in the Google Earth MSI.
First download the full version of Google Earth, (V5.0.11337). You should be able to find later versions through Google.
To find what you need to edit you can do a search in ORCA for AdminUser you should find around 3 entries.
The major entries are:
- InstallExecuteSequence: ChangeInstallDirForNonAdmin: NOT AdminUser (Delete this row)
- InstallExecuteSequence: setALLUSERS: AdminUser (remove the word AdminUser so it applies to all installs)
- InstallUISequence: ChangeInstallDirForNonAdmin: NOT AdminUser (Delete this row)
You may also wish to remove AdminUser as a Condition from Component: Plus_Registry_wavdest.ax
Once these are removed or modified it should install in C:\Program Files\Google\Google Earth regardless of which user is logged in.
Tags:
AdminUser,
ChangeInstallDirForNonAdmin,
Condition,
deploy,
deployment program,
google earth,
InstallExecuteSequence,
InstallUISequence,
local settings,
MSI,
orca,
School,
Zenworks
Permalink
February 13, 2009
Posted in Active Directory, Novell
at 12:36 pm
We are gradually migrating our core services from Novell Netware to Windows Server 2003 and 2008. As part of this migration we needed to migrate all our DHCP reservations stored on our Novell servers to Windows Servers.
First a comment, although Microsoft seems to have a much better DNS system than on Netware 6.5, their DHCP implementation leaves a lot to be desired.
On Novell I could create a scope, reservations and options and it is stored in eDirectory. I could then have multiple DHCP servers with different IP ranges to offer. I could use the java console (sometime slow) to add a reservation and it would sync to both servers.
On Windows Server 2003 and 2008 I need to separate create scopes, allocation ranges and reservations on each server. Their is no synchronisation or communication between these servers. This means to migrate my reservations from my current Netware DHCP servers to the target Windows servers the reservations need to be added to each server, as well as adding any future reservations to both servers.
Migration Process
First use the Novell DNS/DHCP console and export the DHCP scope / database you are migrating.
I have written the following Perl code to read in the DHCP3TAB file generated and output a netsh file that will add all your reservations to any number of Windows DHCP servers.
You will need to modify the DHCP servers list to your AD servers and change the scopeName to the correct IP settings.
After running your DHCP3TAB through the perl script, copy output to your server and run netsh exec outputFile.txt to add it to your servers.
[code='perl']
#!C:\Perl\bin\perl.exe
# Designed to read in Rservations from a Novell DHCP Tab file and output a NetSH script file
# From http://technet.microsoft.com/en-us/library/cc787375.aspx
# On the destination server, the exec command is used to load and execute the converted reservations:
# netsh exec AdReservations.txt
# After you use the exec command to load the file, you must reconcile all scopes.
# Use net stop dhcpserver to stop the DHCP Server service and net start dhcpserver to restart it. Once the service is restarted, DHCP database changes take effect.
use strict; use warnings;
my $dhcptabName = "DHCP3TAB.txt";
my $outFile = "AdReservations.txt";
my @dhcpServers = ('\\\\DHCPServer1.win.us.schools.nsw.edu.au',
'\\\\DHCPServer2.win.us.schools.nsw.edu.au');
my $scopeName = "10.10.11.0";
open F, "< $dhcptabName" or die "Can't open $dhcptabName : $!";
open O, "> $outFile" or die "Can't open $outFile : $!";
# File parsing
my $ip;
my $host;
my $mac;
my $type;
my $comment="";
while (my $line = ){
if ($line =~ /^\[IP Address Configuration /i) {
# New entry, clear values
$ip="", $host="", $mac="", $type="", $comment="";
while ((my $entry = ) !~ /^$/){
# Parse and fill in values
if ($entry =~ /IP Address Number = ([\d.]+)/i){
$ip=$1;
}
elsif ($entry =~ /Assignment Type = (\d+)/i){
$type=$1;
}
elsif ($entry =~ /Host Name = ([\w\-_]+)/i){
$host=$1;
}
elsif ($entry =~ /MAC Address = 1 (.+)/i){
$mac=$1;
$mac=~ s/\s+//g;
}
elsif ($entry =~ /Comment = (.+)/i){
$comment=$1;
}
}
# If type != 8 (reservation) discard
next unless ($type == 8);
next if ($mac eq "" or $ip eq "" or $host eq "");
foreach my $server (@dhcpServers){
print O "Dhcp Server $server Scope $scopeName Add reservedip $ip $mac \"$host\" \"$comment\" \"BOTH\"\n";
}
}
}
close F;
close O;
[/code]
Tags:
dhcp,
dhcp reservations,
dhcp server service,
dhcp servers,
eDirectory,
microsoft,
Novell,
novell dhcp,
novell servers,
Servers
Permalink
December 28, 2008
Posted in Active Directory, Moodle, Novell, Utilities
at 12:24 pm
Recently we had to change the login names for around 1200 students.
As part of this rename we needed to change their Novell eDirectory account and home directory. We also needed to ensure linked systems such as an LDAP authenticated Moodle site and an IDM linked Active Directory (AD) domain were properly updated.
To perform the user account and folder rename we used Mass User from HBWare. This is a great program we already used for creating and managing home directories and quotas. There was a small bug in the rename area, but it was quickly fixed when brought to Hans’ attention.
Note: Before beginning you need to have a list of the old usernames and the new usernames. A simple two column Excel or CSV file would be fine.
Novell
Create Mapping File: The mapping file is used by Mass User to know what to rename the existing username to.
Use NDS Report ( http://www.novell.com/coolsolutions/tools/13908.html ) to create a list of all the accounts. You only want the DN and CN fields. Save this as an Excel or CSV file.
NDS Report: Select Student OU
NDS Report: Choose only CN
Create a new MS Access file and import the account list
Import the text / excel file that lists the old and new account names.
You now need to create a query that maps the cn to the old account name, and then use this to generate a list of the full old dn and the new account name.
Access: Mapping Query
Access: Query Results
You can then export this as a text file.
Access: Export as text file
Access: Export Delimited
You will need to set the field separate as ‘=’ and set the test qualifier to none.
Access: = as Delimiter and no Text Qualifier
You should end up with each line having format:
OLDNAME.OU.C=NEWNAME
Once the mapping file is generated you can apply it either to individual OUs (e.g. year groups), or to the entire Users container. I would recommend applying to small containers initially to allow checking for errors. After verifying all renames were performed correctly you can then apply the rename to your entire users’ container.
Mass User: Rename
IDM 3.0 – Active Directory
Before doing the mass rename we did some simple tests of renaming eDirectory accounts to see how they replicated to Active Directory. We determined that the individual renames were successfully propagated through IDM to automatically change the pre-2000 and logon name fields to match the new eDirectory account.
When performing the mass rename of accounts, AD was checked after doing each OU to check propagation was successful. We experienced no problems with IDM and all accounts were successfully synchronised with Active Directory.
Moodle
For Moodle we wished for users to retain their own accounts including all their settings and course information. To do this the username stored in the Moodle Database (DB) would need to be changed to the new username.
Before making changes to Moodle I recommend putting the site in admin mode and temporarily disabling your LDAP authentication. I only put the site in Admin mode and then had problems during migration as some students had attempted to logon after I had renamed the eDirectory accounts, but before migrating Moodle accounts. This created new user accounts in Moodle that prevented the update queries running due to duplicate key name problems (ie the old account would not rename to new one if one has already been created with the new name)
First you need to import your list of old and new account names into the database. If you already have it in a separate DB on your Moodle server you can use that, otherwise it is best to just to create a new table in the Moodle DB.
I used this query to check the mapping fields. You will need to adjust the fields and collation types for your own setup.
I use the IDNumber field to store the full DN, if you use a different field you will need to adjust this.
Validation:
# Shows the current username, the new username and the new IDNumber field for validation check.
SELECT mdl.`username` , ern.`StudentId` , replace( `idnumber` , mdl.`username` , ern.`StudentId`
COLLATE latin1_swedish_ci )
FROM moodle.`mdl_user` mdl, sbhsdata.`oasisStudentIdMap` ern
WHERE mdl.`username`
COLLATE latin1_general_ci = ern.`OldStudentId`
Update:
# Replaces the IDNumber field with the new username.
UPDATE moodle.`mdl_user` mdl, sbhsdata.`oasisStudentIdMap` ern
SET `idnumber` = replace( `idnumber` , mdl.`username` , ern.`StudentId` COLLATE latin1_swedish_ci )
WHERE mdl.`username`
COLLATE latin1_general_ci = ern.`OldStudentId`
# Replaces the mdl_user field with the new username.
UPDATE `mdl_user` mdl, `rename` ren
SET mdl.`username` = ren.`newname`
WHERE mdl.`username`= ren.`oldname`
Note: As can be seen by the queries, during the migration I did two separate updates, one to update the IDNumber field and another to update the mdl_user field. When I had completed migration I realised I had only needed to update the mdl_user field as Moodle would automatically update the value in IDNumber at next log on.
Round Up
The migration to the new accounts went well with no problems from student passwords or accounts, although we had a few instances of students using the old username instead of the new one.
Since performing the change we have also implemented a Papercut system which would have required the additional step of renaming all of its accounts to ensure student balances were carried across.
Tags:
account,
Active Directory,
Database,
eDirectory,
home directories,
login names,
Moodle,
Novell,
novell edirectory,
Password,
username,
usernames
Permalink
December 9, 2008
Posted in Active Directory, Utilities, Windows
at 7:28 pm
Those who in the past have used the Novell Change Pass utility may have been missing it when moving to Active Directory.
A great tool is Wisesoft Password Control which allows you to just type in the username, it will display info about the account and give you the option to change the password, enable/disable the account and unlock the account.
It is ideal to make available to teachers for resetting the student passwords if you use the Delegate control option in AD Users and Computers for your student OU’s.
The other useful tool on the website is a pair of tools Bulk Password Control and Bulk Modify. These allow you to bulk reset the password for a large group of students, or modify the attributes for a large number of users. It can read these in from a CSV file, and match the CSV entires to either sAMaccountName or some other user attribute. You can also set the attribute based on their existing attributes.
After downloading it will ask you for a code that you can get with a free registration on their site.
Tags:
Active Directory,
Modify,
Password,
password control,
sAMaccountName,
student passwords,
Tools,
username,
utility,
Wisesoft
Permalink
June 25, 2008
Posted in Utilities, Windows
at 10:41 pm
Recently I needed to identify where all the space was going on our server. Usually I just use the Folder Size tab extension but it requires scanning every time you close the Properties dialogue.
After looking around online I found a great free utility that displays the info in an easy to explore graphical view. OverDisk scans the drive or folder (this took around 10 mins for a drive containing hundreds of home directories) and then you can save the data so it does not need to constantly rescan (unless you make changes to files).You can also have it only rescan a certain subfolder rather than the entire drive again.
It presents the information in a colour coded pie chart, allowing you to easily see which folders and files are using the most space.
You can click on the folder and the pie chart will change to reflect that folder or click the middle of graph to go up a level. Right clicking on a folder gives you an easy option to Open or Explore in Explorer.
Links:
Tags:
Folder,
folders,
graphical view,
overdisk,
pie chart,
Size,
Space,
utility
Permalink
May 6, 2008
Posted in Active Directory, Windows
at 6:55 pm
A great new feature in Windows Server 2003 is Access-based Enumeration (ABE).
What ABE does is hide any file or folder that a user does not have access to. So for example the folder where you store all your users home drives, would usually appear jam packed with folders, most of which would return an Access Denied error. However, with ABE installed users would only see the folders they have access to, usually their own.
This is great especially if you are coming from a Novell background where this is the standard behaviour. It is also very useful in a school situation to keep the students from seeing things they shouldn’t.
To use ABE you need to download the management tools from Microsoft ABE Management Tools, then after installation either enable it on all shares or bring up properties and manually add it to shares.
A better description and walk through is available WindowsNetworking: Implementing Access-Based Enumeration in Windows Server 2003 R2
Links in this post:
Tags:
Access-based,
Active Directory,
Enumeration,
Folder,
management tools,
microsoft,
Novell,
School,
school situation,
Server,
windows server 2003 r2,
WindowsNetworking
Permalink
April 24, 2008
Posted in Utilities
at 9:33 pm
Notepad++ is a freeware text editor that has a wide range of great features.
I use it for nearly all my text based programming (PHP, HTML, CSS, CMD, JS, etc), for editing configuration files and reading Linux text files (i.e. only LF no CR).
It has replaced my previous one, ConTEXT, as my primary editor.
- It has a tabbed interface which makes it easy to work on multiple files
- good syntax highlighting for a wide range of languages
- good regular expression find and replace, as well as find in files in a directory
- can change shortcut keys
- does manually indenting but to my knowledge does not auto-indent.
It used to be even better, but most recent version does not include the HexEditor plugin from previous releases (although it does appear to still work if it is installed).
The Hex Editor is now available as a plugin for the Unicode version.
Overall a great freeware application.
Available from Source forge: Notepad++
Tags:
ConTEXT,
freeware,
freeware text editor,
HTML,
html css,
notepad,
programming,
regular expression,
tabbed interface,
Tools
Permalink
January 1, 2008
Posted in Novell, Windows
at 11:36 am
When you install Internet Explorer 7 (IE7) on Windows XP or Windows Server 2003 it may display a security warning when you access applications and files stored on Novell drive mappings if it does not consider them part of your local intranet.
It may also prevent MS Access from opening databases from the network as they are considered a security threat.
To see if your mapped drive is considered as either Internet or Local Intranet: first make sure Status Bar is on (View -> Status Bar), then browse to a sub folder of drive and look in lower right hand corner. 
or 
Testing Security Settings or Configure for Individual PC
- Open the “Internet Options” control panel
- Click Security Tab, Local Intranet, Sites
- Untick Automatically detect intranet network then tick Include all local (intranet) sites not listed in other zones and Include all network paths (UNC).
I find these are the minimum required. However I find these still occasionally don’t work so I add the server names and server IP range to intranet list.
- Click Advanced and add the names and IPs of your servers. This should be in form MyServer, and IP ranges as 10.1.1.2-10.
- Browse to network location and check if Explorer shows Local Intranet in lower right of screen.
Check if files and applications now open without requiring verification.
Deploying Settings using Group Policies
If performing the above has fixed the problem you probably need to deploy these settings to all users, which can be done using Group Policies.
- Open the Group Policy for machines affected (this may just be local GPEdit.msc on a terminal server or the Zenworks Workstation Policy in ConsoleOne).
- Make sure the IE7 version of inetres.adm is loaded (~2.3MB). If not it can be download from MS IE7 ADM.
- Go to Computer Configuration -> Windows Components -> Internet Explorer -> Internet Control Panel -> Security Page and set the following:
- Exit Group Policy editor
Other resources:
Tags:
Access,
ConsoleOne,
Group Policy,
Novell,
security,
Servers,
UNC,
Zenworks
Permalink
December 15, 2007
Posted in Moodle
at 1:28 am
I occasionally use the following SQL statements to clean-up our Moodle system and identify old courses. I recommend backing up your database before using any and knowing enough SQL to understand what they are doing before running them. I usually use phpMyAdmin for execution and export, but use what ever works best for you.
Find Empty Moodle Courses
If you use an auto-enrolment module you will often find you have lots of courses created that are never used. We have used the LDAP and Database enrolment before and both have created large numbers of courses. Some are not used by teachers,others are just used as child courses for year wide meta courses. Either way at the end of the year you may wish to identify any class that hasn’t been used. (Perhaps to delete using the Bulk Deletion options).
No Content
Following SQL code I’ve written will generate a list containing a sum of most of the resources and activities a course has, allowing you to quickly identify those with little to no content. It bases this count on having no labels, resources, assignments, etc.
[code='sql']
SELECT m.id, m.`shortname` , m.fullname, cCount.totalcount
FROM mdl_course m
LEFT JOIN (
SELECT courseCount.course, sum( courseCount.subcount ) AS totalcount
FROM (
SELECT course, count( * ) AS subcount
FROM mdl_resource
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_quiz
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_assignment
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_survey
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_label
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_glossary
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_homework
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_wiki
GROUP BY course
) AS courseCount
GROUP BY courseCount.course
) AS cCount ON cCount.course = m.id
[/code]
You may need to edit above code if you do not have an activity installed (eg homework).
No Users
The following code generates a list of all your courses together with how many students are enrolled in each. Useful to find out if you have any courses with no one enrolled.
[code='sql']
SELECT cr.shortname, cr.fullname, count( ra.id ) AS enrolled
FROM `mdl_course` cr
JOIN `mdl_context` ct ON ( ct.instanceid = cr.id )
LEFT JOIN `mdl_role_assignments` ra ON ( ra.contextid = ct.id )
WHERE ct.contextlevel =50
GROUP BY cr.shortname, cr.fullname
ORDER BY `enrolled` ASC
[/code]
You can export either of the above queries into Excel and manipulate it from there. Order by the counts then copy the short names’ of courses to delete, paste into a text file and upload to the Bulk Course Deletion addon. Goodbye excess courses.
Data Cleanup: Roles without Users
Sometimes when a user gets deleted Moodle doesn’t clean up after it self as well as it should. The following code will list all the rows in your role assignments table that no longer match to a user:
[code='sql']SELECT *
FROM `mdl_role_assignments`
WHERE `userid` NOT
IN (
SELECT id
FROM mdl_user
)
[/code]
If you wish to the delete all of these just run following. As always make sure you have a good backup before deleting anything from DB.
[code='sql']DELETE
FROM `mdl_role_assignments`
WHERE `userid` NOT
IN (
SELECT id
FROM mdl_user
)
[/code]
Tags:
Bulk Operations,
Delete,
Moodle,
Roles,
SQL,
Statistics
Permalink
