Oracle Scheduler Jobs and Email Notifications vs crontab

Question: How do I run scheduled jobs and get a notification via email?

The above is something that our business does all the time, but the answer was, to do that you’ll have to create a crontab direct job on the server.

I thought surely not.. So I thought about it and with a bit of Googling I figured out that the above wasn’t true, it’s possible to do these as scheduled jobs in SQL Developer.

Create Stored Procedure

  • In SQL Developer create a new Procedure
    • Right click on ‘Procedures’ and select ‘New Procedure’, give it a logical name describing what it does, spaces are not allowed, use underscores.
  • Type in your SQL over where the blank Procedure template says ‘NULL;’

Create Scheduled Job

Or jump straight to creating the job if you want to run a block of SQL which doesn’t suit being a Procedure, you can put the SQL directly into the job.

  • Go to the Schema in your database which has the rights to run a scheduled job
  • Under there expand ‘Scheduler’ and ‘Jobs’, right click on ‘Jobs’ and select ‘New Job (Wizard)…’
  • Fill in the field like below
    • Use logical names and give a full description of what the job is doing
    • Either put the SQL in directly in ‘PL/SQL Block’ or select the Procedure you created

JobWizard

When to execute the job?

  • use the ‘When to Execute Job’ drop-down to select ‘Repeating’ and click the pencil this will make the ‘Repeat Interval’ dialog box appear, shown above
  • Select required time and days for the job to run, click ‘OK’.
    • I selected Daily as well as the actual days, just to be sure 🙂
  • Once happy with your choices click ‘Next >’
  • Set Destination to ‘Local’ for it to run on the server, click ‘Next >’
  • Skip over ‘Job Arguments’ to ‘Notification’
  • Now add in ‘job_succeded’ just whilst you are checking the whether your emails are working when the job ran, add it by holding down Ctrl and clicking it, otherwise you will lose your current default options of : job_broken, job_chain_stalled, job_failed, job_over_max_dur, job_sch_lim_reached
  • Move Date: %event_timestamp% from the body to the bottom, as I’ve noticed that it doesn’t create a carriage return after it so will bunch up all the notification output.
    • Also fix the line Error code: by moving the end % back up a line from Error message. This might be a bug on my version of SQL Developer and will be fixed on yours.

From like this:

Retry count: %retry_count%
Error code: %error_code
%Error message: %error_message%

To like this:

Retry count: %retry_count%
Error code: %error_code%
Error message: %error_message%
  • Now Next through the rest of the setting and click ‘Finish’

How to Set up the email side of things

  • Edit and run the following SQL using your System account
BEGIN 

DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'outlook.blah.co.uk:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'noreply@blah.co.uk');

END;

That should be it, all that is left to do is to run your job. You can do that by right clicking the job and selecting ‘Run Job…’

Now when people start to automate jobs, they will be visible to your whole team, rather than hidden away on the server in a crontab.

How to Update an expiring / expired Certificate

This is a process which we have to do every 2 years, so I thought I’d better create a post about it, so I don’t lose it.

  • Create CSR (Certificate Signing Request) using local or server IIS – Other methods are avaliable ie: open SSL
    • Open IIS, click ‘Server Certificates’, click ‘Create Certificate Request…’
    • Upload that CSR to your chosen certificate provider and purchase certificate
  • Once request is approved download the Certificate bundle
  • Extract bundle
  • Complete Certificate in IIS where you created the CSR
    • Open IIS, click ‘Server Certificates’, click ‘Complete Certificate Request…’
  • Export Certificate as PFX
    • Open IIS, click ‘Server Certificates’, Right click certificate and Export
  • Import the PFX to the requires servers
    • Run certlm.msc, Personnel, Certificates, Right click and Import, point at PFX
  • Delete the old Expiring Certificate
    • Run certlm.msc, Personnel, Certificates, Find old certificate based on expiry date and delete.
  • Change IIS site binding for 443 or required secure port to point at the new certificate
    • Open IIS, browse in tree to show site, click ‘Bindings’, find SSL port double click and change Certificate in ‘SSL certificate:’ drop down box.

Test new certificate is working

  • Browse to site in Chrome, right click the Secure / Padlock area click ‘Certificate (Valid)’ check Valid From / To entries.

SSL Server’s certificate chain incomplete?

Does your SSLLABS report say ‘This server’s certificate chain is incomplete. Grade capped to B’?

Simple fix:

Concatenate the certificate file with the Intermediate CA.

Open your Certificate file and Intermediate CA in a text editor, copy all of the Intermediate CA file and paste it after the end certificate section.

JOVtnRpn3coVfSR/0rz0XKVXeZGnKztGdIMQhWMTxvZ1UpmRAH2Ab2QnVo1fkPVy
qNSJces5Y/VKpIvLBk5Jj55fvK8ME/9ASa+LtLrIms8iYHl75cupuYZZlg8=
-----END CERTIFICATE----- 

Leaving just the Certificate and the Intermediate Certificate in the file.

Restart your web server and retest in SSLLABS.

SSL Chain issues – Contains anchor

Does your SSLLABS report mention ‘Chain issues – Contains anchor’?

Simple fix:

Remove the Root CA from the concatenated certificate file.

Use a text editor open your Root CA file as well as your Certificate file, check what the Root CA starts and end with and remove that section.

-----BEGIN CERTIFICATE-----
MIIGuDCCBKCgAwIBAgIUUk/B8W400XArhKE/sEK7zHw8kDIwDQYJKoZIhvcNAQEL
BQAwSDELMAkGA1UEBhMCQk0xGTAXBgNVBAoTEFF1b1ZhZGlzIExpbWl0ZWQxHjAc

Blah Blah

JOVtnRpn3coVfSR/0rz0XKVXeZGnKztGdIMQhWMTxvZ1UpmRAH2Ab2QnVo1fkPVy
qNSJces5Y/VKpIvLBk5Jj55fvK8ME/9ASa+LtLrIms8iYHl75cupuYZZlg8=
-----END CERTIFICATE----- 

Leaving just the Certificate and the Intermediate Certificate in the file.

Restart your web server and retest in SSLLABS.

How to copy a Moodle theme

We needed to take a standard Moodle theme ‘Adaptable’, alter it and save that with a new name. This was so it then wasn’t accidentally overwritten with the unaltered standard ‘Adaptable’ theme if we ever had our site rebuilt by our hosting partners. This way we could also package up our extra custom CSS into the theme.

I very much doubt that this post will prove to be useful to you or anyone else.

I’m creating it so I don’t forget how I did it and can reference this in years to come.

Step 1 – Download a fresh copy of your chosen theme

This should give you a .zip file with the theme, unzip it somewhere on your machine.

Step 2 – Find and Replace – Find All

Get hold of a text editor like Notepad ++
In the menu click ‘Search’ / ‘Find in Files…’

Change the following options:

Find what: adaptable
Directory: C:\blah\blah\Adaptable theme 1.8\
Search Mode: Normal
Make sure ‘In all sub-folders‘ is ticked.
Click ‘Find All’

In my case this brings back 1773 hits in 69 files.

Step 3 – Replace in Files

Now you need to do a find and replace to replace the above

Use ‘Find in Files’ again
This time put in the new name of your theme in the ‘Replace with:’ field so lets say ‘adaptable_ray’.
*NOTE never use a minus in the name here xxxx-xxx as it will not work, use an underscore.
Make sure ‘Match case’ is ticked.
Now click ‘Replace in Files’

Step 4 – Theme displayed name

Now you need to Replace the actual displayed name of the theme

In the case of Adaptable this is ‘Adaptable’, so in the ‘Find what:’ field put in ‘Adaptable’ and ‘Replace with:’ ‘Adaptable-Ray’

Step 5 – Tweak JQuery settings

Because of step two you have changed the name of the main themes .js file, but not the actual name of the file. You can either edit /jquery/plugins.php

‘adaptable_ray’ => array(‘files’ => array(‘adaptable_ray.js’)),

To:

‘adaptable_ray’ => array(‘files’ => array(‘adaptable.js’)),

Or Rename the file adaptable.js to adaptable_ray.js

Step 6 – Change the name of the Language file

Rename ‘adaptable_ray\lang\en\theme_adaptable.php’ to ‘theme_adaptable_ray.php’
And any subsequent language packs in \es etc folders.

Step 7 – Change settings files

Rename file ‘adaptable_ray\settings\adaptable_admin_setting_putprops.php’ to ‘adaptable_ray\settings\adaptable_ray_admin_setting_putprops.php’
and
‘adaptable_ray\settings\adaptable_admin_setting_getprops.php’ to ‘adaptable_ray\settings\adaptable_ray_admin_setting_getprops.php’

Step 8 – Change Theme CSS name

Rename the main CSS file from ‘adaptable_ray/style/adaptable.css’ to ‘adaptable_ray/style/adaptable_ray.css’

or

Edit config.php and change the line $THEME->sheets = array( ‘adaptable_ray’,
to $THEME->sheets = array( ‘adaptable’,

Step 9 – Change the folder name

Now change the folder name to your new name in lowercase the same name as in Step 3.

Step 10 – Zip up the theme

Zip up the theme as ‘adaptable_ray.zip’

Step 11 – Deploy the theme

Deploy the theme to your local installation and check it all works.

Moodle alerts using New Relic Query Language NRQL

I’ve been using New Relic to monitor our Moodle installation for the past 4 years. Recently New Relic have added some lovely alerting features which use their NRQL language to trigger any application issues.

With my recent change of job roles (Technology Enhanced Learning Manager) I have now being using NR on a daily basis, trying to get the best out of it.

Whilst reading through the help on NRQL alerting available on NR site, NR blog and in the NR forums I hit upon a few issues.

What if you know your site performs early morning syncs with other systems and these syncs will always trigger your alerts. Something that I didn’t want happening. How can you stop this from happening?

This is how I managed it:

NRQL Database spike alert : ignores 2 to 4:59 am

SELECT average(databaseDuration) * 100 FROM Transaction WHERE hourOf(timestamp) IN ('0:00','1:00','5:00','6:00','7:00','8:00','9:00','10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00','22:00','23:00') with TIMEZONE 'Europe/London'

The above is checking average database duration in the Transaction only within the hours 0:00-01:59 skipping 02:00 to 4:59 and checking during 05:00-23:59. The * 100 gives the query value the same representation as what you can see in the APM / Database section.

NRQL Errors > 20% : ignores 2 to 4:59 am

SELECT filter(count(*), WHERE `error.message` is not null)*100 / filter(count(*), WHERE duration is not null) as 'Errors' from Transaction, TransactionError WHERE hourOf(timestamp) IN ('0:00','1:00','5:00','6:00','7:00','8:00','9:00','10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00','22:00','23:00') with TIMEZONE 'Europe/London'

The above is converting the query value into the same as what you see when looking at the Error rate on the APM / Overview. This is checking in the Transaction only within the hours 0:00-01:59 skipping 02:00 to 4:59 and checking during 05:00-23:59.

NRQL APDEX < 0.5 for 2 minutes

SELECT apdex(duration, 0.5) FROM Transaction WHERE hourOf(timestamp) IN ('0:00','1:00','2:00','3:00','4:00','5:00','6:00','7:00','8:00','9:00','10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00','22:00','23:00') with TIMEZONE 'Europe/London'

The above is converting the query value into the same as what you see when looking at the Apdex score APM / Overview. This is checking in the Transaction within all 24hrs in a day.

I’ve got this set to only trigger if the duration is longer than 2 minutes as this can trigger in the early am. When using the threshold type of static and query value below 0.5 you cannot use ‘hourOf(timestamp) IN’ and skip some hours as those hours will trigger as zero which will throw the alert.

When I first tried setting up these alerts I was using ‘hourOf(timestamp) NOT IN’ rather than ‘hourOf(timestamp) IN’ it was thanks to a NR support person who helped me figure out that ‘NOT IN’ wasn’t working correctly.

Hopefully you can get some use out of the above, please leave any examples in the comments below.

Automate copying IIS logs to SQL Database using Powershell, Log Parser and Task Scheduler

I decided not to go down the route of writing an SSIS package as mentioned in my previous post

So how else can you do what I wanted to achieve?

  • Remove all copied logs from the Database server
  • Copy yesterdays logs from 9 web servers
  • Import the logs to a SQL Database

This is how I did it:

Step 1 : Create 3 Powershell scripts

Copy IIS files from yesterday:

$Date = Get-Date
$Date = $Date.adddays(-1)
$Date2Str = $Date.ToString("yyyMMdd")

$Files1 = gci "\\server1\c$\inetpub\logs\LogFiles\W3SVC2"
ForEach ($File in $Files1){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "C:\\Logs\\1718\\server1"}
}

$Files2 = gci "\\server2\c$\inetpub\logs\LogFiles\W3SVC2"
ForEach ($File in $Files2){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "C:\\Logs\\1718\\server2"}
}

$Files3 = gci "\\server3\c$\inetpub\logs\LogFiles\W3SVC2"
ForEach ($File in $Files3){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "C:\\Logs\\1718\\server3"}
}

Remove all local iis log files:

$ErrorActionPreference = "Stop"

Import-Module Pscx -EA 0

function RemoveLogFiles
{
    Write-Host "Removing log files..."
    Remove-Item ($httpLogPath1)
	Remove-Item ($httpLogPath2)
	Remove-Item ($httpLogPath3)
	Remove-Item ($httpLogPath4)
	Remove-Item ($httpLogPath5)
	Remove-Item ($httpLogPath6)
	Remove-Item ($httpLogPath7)
	Remove-Item ($httpLogPath8)
	Remove-Item ($httpLogPath9)
}

function Main
{	

	[string] $httpLogPath1 = "C:\Logs\1718\server1\*.log"
	[string] $httpLogPath2 = "C:\Logs\1718\server2\*.log"
	[string] $httpLogPath3 = "C:\Logs\1718\server3\*.log"

	[string] $httpLogPath4 = "C:\Logs\1718\server4\W3SVC1\*.log"
	[string] $httpLogPath5 = "C:\Logs\1718\server5\W3SVC1\*.log"
	[string] $httpLogPath6 = "C:\Logs\1718\server6\W3SVC1\*.log"

	[string] $httpLogPath7 = "C:\Logs\1718\server7\W3SVC2\*.log"
	[string] $httpLogPath8 = "C:\Logs\1718\server8\W3SVC2\*.log"
	[string] $httpLogPath9 = "C:\Logs\1718\server9\W3SVC2\*.log"

    RemoveLogFiles 

    Write-Host -Fore Green "Successfully removed log files."
}

Main

Import Website Logs to Database:

$ErrorActionPreference = "Stop"

Import-Module Pscx -EA 0

function ExtractLogFiles(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."
    }

    [string] $httpLogArchive = $httpLogPath + "c:\logs\1718\*.log"

    If ((Test-Path $httpLogArchive) -eq $false)
    {
        Write-Host "Creating archive folder for compressed log files..."
        New-Item -ItemType directory -Path $httpLogArchive | Out-Null
    }

    Write-Host "Extracting compressed log files..."

    Get-ChildItem $httpLogPath -Filter "*.zip" |
        ForEach-Object {
            Expand-Archive $_ -OutputPath $httpLogPath

            Move-Item $_.FullName $httpLogArchive
        }
}

function ImportLogFilesServer1(
    [string] $httpLogPath1)
{
    If ([string]::IsNullOrEmpty($httpLogPath1) -eq $true)
    {
        Throw "The log path must be specified."
    }

    [string] $logParser = "${env:ProgramFiles(x86)}" `
        + "\Log Parser 2.2\LogParser.exe "

    [string] $query = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
			+ ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath1"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=;Database=;Trusted_Connection=yes;"

    [string[]] $parameters = @()

	$parameters += $query
    $parameters += "-i:W3C"
	$parameters += "-e:-1"
	#$parameters += "-recurse:-1"
    $parameters += "-o:SQL"
	$parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function ImportLogFilesServer2(
    [string] $httpLogPath2)
{
    If ([string]::IsNullOrEmpty($httpLogPath2) -eq $true)
    {
        Throw "The log path must be specified."
    }

    [string] $logParser = "${env:ProgramFiles(x86)}" `
        + "\Log Parser 2.2\LogParser.exe "

    [string] $query = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
			+ ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath2"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=;Database=;Trusted_Connection=yes;"

    [string[]] $parameters = @()

	$parameters += $query
    $parameters += "-i:W3C"
	$parameters += "-e:-1"
	#$parameters += "-recurse:-1"
    $parameters += "-o:SQL"
	$parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function ImportLogFilesServer3(
    [string] $httpLogPath3)
{
    If ([string]::IsNullOrEmpty($httpLogPath3) -eq $true)
    {
        Throw "The log path must be specified."
    }

    [string] $logParser = "${env:ProgramFiles(x86)}" `
        + "\Log Parser 2.2\LogParser.exe "

    [string] $query = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
			+ ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath3"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=;Database=;Trusted_Connection=yes;"

    [string[]] $parameters = @()

	$parameters += $query
    $parameters += "-i:W3C"
	$parameters += "-e:-1"
	#$parameters += "-recurse:-1"
    $parameters += "-o:SQL"
	$parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function Main
{
	[string] $httpLogPath1 = "C:\Logs\1718\Server1\*.log"
	[string] $httpLogPath2 = "C:\Logs\1718\Server2\*.log"
	[string] $httpLogPath3 = "C:\Logs\1718\Server3\*.log"

#Repeat above for all other server

    ImportLogFilesServer1 $httpLogPath1
    ImportLogFilesServer2 $httpLogPath2
    ImportLogFilesServer3 $httpLogPath3	

#Repeat above for all other server

    Write-Host -Fore Green "Successfully imported log files."
}

Main

Finally you need to setup some scheduled tasks:

There’s a few little bits here which wouldn’t work for me without the following arguments:

Powershell.exe in Program/script:

-ExecutionPolicy Bypass -file “C:\powershell iis scripts\remove all iis log files.ps1” in Add arguments (optional):

task

The ‘Copy IIS files from yesterday’ script required some work with our server and storage team to add a service account to be able to run batch scripts without being logged on, this is in the Local Group Policy, This was due to being on a domain and the policy was set to not allow me to set this for my user. More details to come on this, its time consuming to explain in great detail.