System Uptime / Availability Calculator Template






I created this System Uptime / Downtime / Availability Calculator Template as an easy way to work out the effect of any outage on our Service Level Agreements. This template is using Australian sites & timezones as this was what I needed, but the logic is pretty clear & you can easily adapt it to suit your requirements. All times are calculated in WST, so please be sure to input your outage start & end times in WST. Leave me a comment if you have found this useful when managing your SLA’s.

Basically, the intention here is to count the number of business hours (ignoring weekends) that any outage affected multiple sites with different opening & closing times.

You will need to load the Analysis Toolpak to Excel to utilise the NetworkDays function listed below: http://office.microsoft.com/en-au/excel-help/load-the-analysis-toolpak-HP001127724.aspx

From this example, you can see that the a two hour outage occurring between 6 and 8am WST has no impact on the Perth office, as the outage occurred before the start of business in that office. Adelaide recorded a lower outage duration, as they are half an hour behind EST and therefore were only affected for 1.5 hours:

image001

The SpreadSheet Explained
When a time & date are entered into the top fields, the times are calculated into three sections underneath: The corresponding times in Adelaide, Brisbane and EST. Logic is then applied to these converted values in order to ensure that any input values fall within the specified 8am to 5pm range. Without this validation a 5pm WST outage will clock in as 7pm Brisbane time (for example) and the formula will calculate a higher Downtime, as it counts the time overnight as part of the Outage

image003

Variables
To keep the formulas modular, the variables are specfied in this section & used as references within the rest of the spreadsheet. This allows us to easily change the time difference to Melbourne / Sydney when the clocks change, as well as the number of hours per working week, and the opening / closing times we are measuring against:

image002

Logic
StartTime = Office’s opening time
EndTime = Office’s closing time
Converted Start Time: If StartTime earlier than OpenTime, then OpenTime. If StartTime later than CloseTime, then CloseTime. Otherwise, the input value is used.
Converted End Time: If EndTime later than CloseTime, then CloseTime. If EndTime earlier than StartTime, then StartTime. Otherwise, the input value is used.
Actual End Time: The WST end time of the incident, plus or minus the time difference specified in the Variables column

Forumula
The main calculation is using Excel’s ‘NetworkDays’ function to ignore weekends, and uses the DayEnd & DayStart times input into the spreadsheet to calculate the actual business hours each outage affected.

image004

Users
The Users section is not used in any formulas above, however it is intended to make it easier to calculate the true impact of any outage. For example, a 2 hour outage will incur a 1.3% downtime (98.7% uptime) for all sites, assuming the outage falls within the open & close times of all sites. However if only one site (29% of users) was affected by the outage the true downtime would be:

image005

Users (29%) x Downtime (1.3%) = 0.38% actual downtime (99.62% uptime) for the entire organization over the course of the month.

The user-specified fields are in orange, and roll up into the total via AutoSum at the top. The AutoSum value is then used as 100% of all users, which is then broken down to the site percentage of the total.

Download Uptime Calculator.xlsx (21kb):
Uptime Calculator

Posted in MS Excel | Tagged , , , , , , | 1 Comment

Excel VBA Macro > Create XLS to PDF & Email

This is the Excel version of a previous post, Word VBA Macro > Create DOC to PDF & Email, to allow you to automatically generate a PDF attachment to a draft message in Outlook with a custom filename, from the click of a button in Excel. The current method to generate a PDF (with a custom file name) is to:

  • Save to PDF (File > Save As > .PDF / File > Print > Adobe PDF)
  • Choose target folder & save
  • Rename newly created PDF (as it creates with the source Excel document’s name)
  • Attach to Outlook & send
  • Delete the PDF from disk

The process is now as follows:

1: User completes their Excel document & hits the shortcut to run the EmailPDF macro:

Excel Macro Button

2: Macro executes & prompts for the filename to use:

Macro prompts for filename to be used

3: Email message with the renamed attachment:

Outlook Message With PDF Attachment

You will need to add a reference in Word to the Microsoft Outlook 14.0 Object Library to get this to work. If you find this useful, please leave me a comment because I would love to know. Any suggestions to improve the below would also be welcomed.

You can swap out any of the target folders etc as required, and I’ve left examples in the code to use cell references to input CC: addresses etc.

Sub EmailPDF()
 Sub EmailPDF()
 fdir = "c:\\temp\\"
 fname = InputBox("Please Enter Filename")
 fpath = fdir & fname & ".pdf"
 With Application
.EnableEvents = True
.ScreenUpdating = False
 End With

ToAddress = "email@stott.asia"
CCAddress = Range("B22")
CCAddress2 = Range("i10")
CCAddress3 = CCAddress & "; " & CCAddress2
MailSub = "Emailing " & fname

'Generate PDF document to c:\. Substitute ActiveSheet for ActiveWorkbook to PDF the entire document
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fpath, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Create Mail & attach PDF
 Set oOutlookApp = CreateObject("Outlook.Application")

'Create a new message
 Set oItem = oOutlookApp.CreateItem(olMailItem)
 With oItem
 .To = ToAddress
 .CC = CCAddress3
 .Subject = MailSub

'Bring up new mail window
 oItem.Display

'Add Attachment
oItem.Attachments.Add fpath

'Cleanup , baby
 Set OutMail = Nothing
 Set OutApp = Nothing
 Set fs = CreateObject("Scripting.FileSystemObject")

fs.deletefile fpath

End With
 End Sub





Posted in Macro, MS Excel, VBA | Tagged , , , | 1 Comment

Music

Well, here’s some music I’ve made over the last 10 years or so. You might like it, you might hate it… it’s all gravy baby. Some free 320 MP3’s for your musical pleasure, drum & bass & electro. Will add some more when I can be bothered.

After a couple of comments from people I’d just like to say, yes I do know who Ulterior Motive are, and no, I’m not pretending I’m them / bogarting their name. We’ve been producing as Ulteria Motive for 15 odd years, and if we were even 10% as good / successful as Ulterior Motive it would maybe be an issue. We’re not that good, and frankly I can’t be bothered thinking up another name for 5+ year old tunes which are never going to get any airplay anyway. Peace.

Symbolism- Electro boogie!

Download

 

Wardance – Ragga Jungle, sort of

Download
 

Violet – Jump up

Download

 

Posted in Music | 1 Comment

Add a Reference To The Microsoft Office Object Library

Here are some steps to follow, to allow you to add a reference to the Microsoft Office Object Library from within Excel. Steps 1, 2 & 3 can be skipped via ALT+F11, but it’s handy to have the developer tab added for later on.

Depending on your version of Office, these steps or the names of the objects may differ slightly – Office 2010 is 14.0, Office 2012 is 15.0 etc

1: Firstly, add the Developer tab to Excel – Right-click anywhere in the ribbon & select ‘Customise The Ribbon’:

1

2: Put a check in the ‘Developer’ box & hit ‘OK’:

2

3: Switch to the Developer tab, and hit the Visual Basic shortcut:

3

4: Select Tools > References:

4

5: Scroll down to the required Library, add a check box, and hit ‘OK’

5

Posted in MS Excel, VBA | Tagged , , , , | Leave a comment