Blog

Dude, where’s my data?



Office 365 Logo

A play on the film title from “Dude, where’s my car?” in which, well actually, we can’t remember the plot but we do remember the title and something about a scene with tattoos.

Putting movies to one side, customers often want to know where their mailboxes reside in Office 365 and Exchange Online.  If my billing address is in Europe I can assume my data centres are located in Dublin and Amsterdam but can I verify that?  Also, the mailboxes for individual users and resources might be in either of those locations.  Here’s where a little PowerShell comes in handy.

There’s a useful getting started with Powershell resource on the Microsoft website.  You can also search the web for myriad explanations but be aware many of these will be out of date especially if you’re using Windows 8.1.

Windows 8.1 includes PowerShell by default so on the Start screen, type PowerShell, right-click Windows PowerShell in the search results and select Run as Administrator.  I’ll explain why you need to run it as admin in a moment.

How to run Windows powerShell

 

So many memories of the old DOS window.  PowerShell doesn’t speak Office 365 by default so we have to teach it by installing the Windows PowerShell cmdlets for Office 365 management and deployment.  Your keyboard should now be bristling with all the power you have at your fingertips.

1 – Set the PowerShell execution policy.  The default setting for execution policy is restricted which would not allow PowerShell to run scripts at all; secure but not very useful.  RemoteSigned tells PowerShell it can run scripts on the local machine but any scripts downloaded from the Internet must be signed by a trusted publisher.  This is also why you need to run PowerShell as administrator; in order to change this setting.

Set-ExecutionPolicy RemoteSigned

2 – Set your Office 365 admin credentials.  A popup window will appear where you type in your Office 365 admin user and password.  This will also tell the PowerShell cmdlets which Office 365 tenant you want to connect to.

$cred = Get-Credential

3 – In order to perform administrative tasks on Office 365 you can connect to the online service using the credentials you supplied.

Connect-MsolService -cred $cred

4 – You can list all the Office 365 admin commands.  Note that Exchange, SharePoint and the other cloud services have their own set of commands.

Get-Command –Module msonline

5 – To connect to Exchange Online, type the following commands.  You don’t need to change anything here; again, the tenant to connect to is specified by the login ID in the credentials.

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $cred -Authentication Basic -AllowRedirection

Import-PSSession $Session

6 – Return your mailbox information to see where they’re located.  If the server name begins with db you’re in Dublin and if it’s am you’re in Amsterdam.  Happy travels!

Get-Mailbox


Lync 2013 vs Lync Basic



Logo for Microsoft Lync

The new Office 365 Business and Business Premium plans released in October do not include the full Lync client so what are users expected to use for instant messaging, presence and online meetings?

There are a number of possible Lync clients including the Lync Windows Store App (Modern UI), Lync Web App and mobile Lync clients for Windows Phone, iPhone, iPad and Android devices. The most likely alternative for users will be Lync Basic which is a no-cost application to connect to Lync Online or Lync Server.  For users already on Office 365 Small Business Premium or Midsize Business plans who are upgrading to the new Business plans, the update process will uninstall the Access & Lync applications and users will be able to download Lync Basic from their software portal in Office 365.

Although the functionality of Lync Basic was recently updated to more closely match that of the full Office Lync client, there are some quite significant differences including the lack of spellchecker for instant messages (after a long time of asking Microsoft to add a spell checker to Lync, losing it now is disappointing), using OneNote for Lync meeting notes and the ability to record meetings.  Some features are not so relevant for SMB customers and there are also features which require on-premises Lync Server or SharePoint Server such as Skill Search and Persistent Chat; those features would not be available to Office 365 users even with the full Lync client.

If you’d like to understand the differences between Lync, Lync Basic and the other clients, you can view this TechNet page.  There’s also a dedicated page comparing functionality for the Lync mobile clients.


6 and 12 Month Pricing for Azure



Microsoft Azure Logo

Buying Microsoft Azure directly from Microsoft is as easy as online grocery shopping and for some people might even be cheaper (Waitrose does have a tempting cheese selection). Until recently, the pricing calculator on www.azure.com offered pay-as-you-go rates or discounted monetary commitment rates if you paid £300 or more per month for 6 or 12 months. You can read our previous blog post about this.

From July 26th, those commitment options are no longer shown, despite the website mentioning them.  No notice, no announcement; one could even use the phrase ‘swept under the carpet’.  Why is this?

As of 1st August, any Microsoft reseller can sell Azure monetary commitment to any customer in the same way they sell other Microsoft software such as Windows or Office (previously, Azure could only be purchased directly from Microsoft or through an Enterprise Volume Licensing Agreement).  Customers can now turn to their IT provider and buy Azure credit in $100 chunks (approx. £65 of Azure services).  This credit will then last for 12 months from the time the customer redeems the code online.  So if you want £1,800 of Azure credit, you would buy 27 or 28 of the Azure monetary commitment chunks.  We’ll run a blog post in the next week or so showing the experience in more detail.

As an example of how this credit might be used, a customer buying £1,800 worth of Azure monetary commitment from a reseller would go on to pay £34.75 per month for a 200GB Azure Backup Vault.  Around 52 months’ worth of Azure Backup (the azure credit only lasts 12 months so they’d spend the rest on other services but work with us here, this is a simple example).

A customer buying direct through Azure.com and pre-paying £300 for 6 months (a total of £1,800) would gain a discount of 22.5% off the pay as you go price and bring the Azure Backup Vault cost down to around £26.93 per month.  Around 67 months’ worth of Azure backup.  Both methods cost the customer the same £1,800 up front so which would you go for?  This is obviously detrimental to Microsoft resellers who wish to transact Azure as they wouldn’t be able to compete against those discounts.

So that is my understanding why the 6 month, 12 month and pre-payment pricing options on Azure.com  were hidden in the little attic room.  I believe it’s only for the time being and Microsoft is working towards rationalising the pricing waterfall and discounts across buying direct and via a reseller.

 


Monthly Microsoft Licensing Webinar



Microsoft Volume Licensing Logo

We’re proud to be working in conjunction with Microsoft to deliver monthly webinars on licensing.  The 60-minute calls run on the second Wednesday of every month and will take you through a different topic each month kicking off with Azure in the Open Volume Licensing programme in August.  The calls also include updates on products, technologies and promotions and each month’s call is also recorded so you can catch up with previous topics.

There are three calls on the day, each targeted to a specific audience segment:

10am – 11am Microsoft small and medium business (SMB) partners and distributors

12pm – 1pm Microsoft customers with volume licensing agreements

2pm – 3pm Microsoft Large Solution Providers (LSPs)

Registrations are monitored so please do not register for calls outside your segment or try to register with generic email addresses such as @gmail or @hotmail.

Details for August’s call can be found on the TechNet blog.

If you are an LSP, please contact your Microsoft account manager to register.

If you are an SMB partner primarily transacting Open and Open Value licence agreements, please contact your preferred distributor who can provide details of the registration links.

If you are a Microsoft Volume licensing customer, you have access to a great new UK specific volume licensing site.  The site is a hub through which you can access UK specific VL content and resources. To view details on the Spotlight Calls as well as our upcoming 2-day Licensing Fundamentals events, please click on the events link in the rotating banner.

Microsoft Volume Licnesing webpage

 


How to Access the Old Print Preview



Microsoft have to please over a billion users of Office so there will always be some elements that people aren’t so keen on. You can tell we’re being diplomatic here can’t you? I must admit I stayed with the Windows Classic start menu when I used Windows Vista back in the day.

I had a question from a user at Land Securities during an Office demonstration session. The person was working on a small device and the Word print preview in Office 2010 and 2013 only shows the document in half the window. He missed the old print preview dialogue box where he could view the document as full screen.

Office Print Preview Window

 

When features of Office are deprecated, some of them remain in the product but are moved into the background. This is one of those features. To access the old-style Print Preview window you need to customise the Ribbon or Quick Access Toolbar.  Let’s use the Quick Access Toolbar for our example.  To add commands to the toolbar, click the arrow to the far right.  As the command in question is not in the common commands list, select More Commands….

Customise the Ofice Quick Access Toolbar

 

This will show the Customise Quick Access Toolbar page in the options window.  Click on the dropbox and select All Commands to display an alphabetical list of all the Word commands; a surprising number of them.

Office Ribbon Commands

 

Scroll down in the list until you see Print Preview Edit Mode.  Double-click the command to add it to your Quick Access Toolbar.

Add a command to the ribbon

 

And now you have the Office 2007 style Print Preview window at the touch of a button.

Command added to the Quick Access Toolbar

 

Whether it will still be there in the next version of Office is anyone’s guess of course, so caveat emptor or another suitable Latin phrase for be careful when using old Office commands.

Old style print preview

 


New Office 365 Plans for Small Business



Update August 2014 – We’re proud to be working with Ingram Micro to deliver a series of webinars on the Microsoft Cloud platform.  In this recording we go through the new Office 365 Business plans.

In almost every cloud event we run, I mention how frequently cloud services change.  Think about Office; there’s Office 2013.  Before that came Office 2010, Office 2007, Office 2003 and so on.  A nice predictable release cadence of three years or so.  A similar pattern exists with server products and OSs.  The cloud has a much faster release cycle and from October 1st there will be new Office 365 plans for small business: Office 365 Business, Office 365 Business Essentials and Office 365 Business Premium.  All three of these are aimed at the small and medium business (SMB) market and will replace the current lineup (Small Business, Small Business Premium and Midsize Business) by late 2015.

 

New office 365 Plans

 

This is a nice improvement on the current, fragmented SMB position, because you can now mix these three plans to your hearts content, up to 300 seats of each.  You cannot currently mix and match Small Business and Midsized Business plans.  The 300 seat limit is per plan, not per tenant, so you can have up to 900 seats across these three plans if necessary.

Office 365 new plans described

 

Before you get too excited, these plans also have clear omissions.

Office 365 business Plan vs ProPlus

Office 365 Business doesn’t include the Office apps and features that Midsize Business did; Access and Lync.  I think this is almost the wrong way around.  Access shouldn’t be chosen over SQL Server for enterprises and SMB love the power that Access can bring.  Lync is marvellous for all types of organisation and shouldn’t be limited to just enterprise.

My personal position remains that customers and partners should start with the Office 365 Enterprise plans and then work downwards if absolutely necessary, not the other way around:

  1. There’s more choice with the Enterprise family of services, not just plans E1, E3 and E4 but all of the standalones
  2. You can mix and match so office users can go for plan E3 for example whilst lighter workers may just require hosted email
  3. More control over the Office installation and updates via the admin centre
  4. More services to grow into when you’re ready, without having to consider migrating to a higher plan
  5. Lower cost entry point: Small Business Premium is £8.40 per user per month; Exchange Online is just £2.60 per user per month
  6. Lots more reasons but I forget them

Here’s the FAQ which covers a lot more detail.


Is Office 365 Subject to the Patriot Act?



Great Seal of the United States

 

David from Newcastle upon Tyne asks “is Office 365 subject to the Patriot Act?”

This is a very common question when dealing with cloud services, not just office 365.  In short, the USA Patriot Act makes lawful access to stored data easier in certain instances.  If the request is lawful and obligatory, Microsoft cannot simply refuse.  Nor can any other company.  And don’t forget, the UK has similar powers and made almost as many law enforcement disclosure requests as the US between July 2013 and December 2013 (4,213 requests from the UK against 5,652 from the US).

Customers can be assured that Microsoft follows clear principles in responding to any government legal demands for customer data (whether from the US government, UK or other bodies):

There must be a valid subpoena or legal equivalent before Microsoft will consider releasing a customer’s non-content data to law enforcement;
There must be a court order or warrant before Microsoft will consider releasing a customer’s content data;
In each instance, Microsoft carefully examines the requests received for a customer’s information to make sure they are in accord with the laws, rules and procedures that apply.

Because Microsoft is committed to transparency in regards to who has access to customers’ data, when and under what circumstances, they publish the details of the number of demands they receive each year in a Law Enforcement Requests Report which is updated twice a year.  They have just released a report on US government requests (as opposed to law enforcement requests) and between January 2013 and June 2013 there were less than 1,000 orders seeking disclosure of customer content.  Brad Smith, Microsoft’s General Counsel & Executive Vice President of Legal & Corporate Affairs highlights that “while our customers number hundreds of millions… only a fraction of a percent of our users are affected by these orders.”

It’s also possible for customers on Microsoft’s cloud services to find out whether someone has accessed their data.

Due to the frequency of this question, the Office 365 Trust Centre is a useful source of information around privacy and transparency.


Internal Use Rights for Software



A question from Nigel: “Do MS Partners have to take out MAP or a Silver/Gold Competency to get access to Office 365?”

Excellent question Nigel and I fear you already know the answer; yes.

Internal use rights (IUR) are licences that allow companies to use selected Microsoft software for their production use.  As you can imagine this can save thousands.  So why does Microsoft allow this and how can commercial companies get these?

Why Does Microsoft Allow Internal Use Rights?

Why do Ford sales staff drive Ford company cars?  They don’t normally need to travel as they’re showroom-based.  The dealership tends to do this so the salespeople understand the product better and that translates into sales figures.  Microsoft wants their partner channel to do the same.  The amount of partners I see that aren’t using Office 2013 on their primary machines is pretty high and these are often the same partners that struggle to sell Office 2013.  Use the software and you’ll soon discover little gems that make brilliant anecdotes, demos and proof points for customers.

How Can a Partner Obtain Internal Use Rights?

Up until the early part of 2014, any organisation registered on the Microsoft Partner Network could enrol into a program called Cloud Essentials which allowed partners to sell Office 365 and other cloud solutions.  Becoming a registered partner is free and very easy; my mum could do it.  Furthermore, becoming a Cloud Essentials partner was easy.  It required no minimum purchase and just involved taking an online test (which shows you the answers afterwards) and signing an online agreement.  Again, my mum could have registered, taken the test once, got it wrong but noted the answers and taken it again.  Cloud Essentials partners then had access to 250 seats of Office 365 E3 plan, 100 seats of Dynamics CRM online as well as Intune.  This was over £50,000 in software and more than most small and medium business (SMB) partners required.  It also devalued ‘real’ partners.  By that I mean Microsoft has a lot of inactive registered partners; companies that perhaps only registered to get access to a webpage that asked them to register or companies that only registered to gain the IUR.  I’ve even seen a couple of registrations where the partner’s registered web address was along the lines of Daddy@TheStevensFamily.org (names changed to protect the innocent).

Cloud Essentials is now closed to new enrolments and existing IUR benefits will not be renewed at the end of the enrolment period for those partners who were Cloud Essentials partners.

Internal Use Rights are now obtained by purchasing a Microsoft Action Pack (MAP) or by gaining a competency (e.g. Small Business, Communications, Server Platform or Learning).  Simply being a registered partner (aka community) does not accrue IUR.  This makes them harder to obtain but means Microsoft are concentrating on committed partners.  IUR have also changed to become more flexible as they allow partners to allocate their licences on-premises, in the cloud or as a mixture of both.  At the time of writing the Action Pack subscription costs £310 + VAT.  Competency fees vary according to the competency you earn but are in the ball park of £1,000-£2,000 for Silver and £2,000-£3,500 for Gold.

Microsoft partner Network Levels

Again, as a guide an Action Pack would earn 10 Windows 8.1 Pro licences and 5 Office licences which can be a mix of Office 2013 on-premises or Office 365 E3.  This is in addition to a host of other software titles.  A Silver competency would earn 25 windows 8.1 Pro and 25 office licences.  A Gold competency would earn 100 of each.

Partners who were enrolled in Cloud Essentials could enjoy £65 of free credit for Microsoft Azure per month; the offer states this is no longer available but it will continue for previously-enrolled Cloud Essentials Partners.  I’m hoping this free credit will still be available to any registered partner but I’m yet to test this.  Looks like I’ll need to set up one of those Daddy@TheStevensFamily.org partner accounts.

For more information on the number of licences you can obtain through IUR as well as how to increase the IUR seats, you can read this page directly on the Microsoft Partner Network.

If you currently enjoy IUR and you want to read up on the transition, this Cloud IUR guide will help you.


Office 365 Roadmap



The Office 365 roadmap monthly email is a thing of beauty.  It lays out the changes and service improvements to the Office 365 Services, billing and licensing for 3-6 months ahead with the information coming directly from the product team.  Essential for keeping up to date with such a regularly changing service.  However it was always under NDA (non-disclosure agreement) so recipients weren’t allowed to disclose the contents.  Not even with a nod and a wink.

Office 365 Public Roadmap

The public roadmap site was announced yesterday and can accessed via the OnRamp site.

Office 365 Roadmap shot

In addition to the public roadmap, there’s also a new program called First Release which allows customers (once they opt-in) the opportunity to get significant enhancements to Office 365 at least two weeks before customers in the standard release group.

Please read the blog release for more details on the Office 365 Roadmap, the First Release program and an FAQ.

And importantly, bookmark that futures page!


Excel Chart Conditional Formatting



Craig from Lloyds Banking Group asked if Excel’s conditional formatting can be applied to charts.  I’d like to provide the answer in the form of a news sandwich (good news, bad news, good news).

It’s a nice sunny day as I’m writing this article.

No, you can’t I’m afraid, Craig.

You can create a pseudo conditionally formatted chart.

Excel Conditional Formatting – a Recap

You have a list of customer accounts and due dates in Excel.  You want to highlight customers who have due dates within the next week in orange and you want to highlight customers who have due dates within 2 days in red.  That makes your list more readable and Excel more human.  Back in the old days of Excel 2003 you’d need to do some functions to achieve this and you were limited to 3 conditions per cell.  Conditional Formatting was introduced in Excel 2007 and has improved ever since.  It allows the user to create some nifty formatting based on cell values with just a few clicks.

Excel Conditional Formatting Date Example

 

You can also choose conditions based on averages, top %, text occurring, duplicate values and many more.  You can also use icons such as red, amber and green or perhaps smiley faces.  And also you can choose data bars which allow you to visualise the value according to magnitude.

Excel 2013 Data Bars screenshot

 

So Craig’s question asked if one can apply this Conditional Formatting feature to bars or segments within Excel charts?

And if you recall, the answer is no but you can create a similar experience by categorising the data into distinct, banded columns and charting those.  Let’s take a table (below) with data series and values which we want to chart.

Excel table ready for conditional formatting

 

A default chart will represent each column in the same colour (below).

Excel chart without conditional formatting

We want each bar coloured according to the band of values it falls into.  To do this we can create the bands we want to chart as additional columns in the table (below).  In rows 1 and 2 we list the boundaries of each band (e.g. 0 and 500).   Each value cell (G4-M16) is then calculated using a formula =IF(AND(G$1<$F4,$F4<=G$2),$F4,””).  For each cell, the Amount (F4-F16) is compared to the boundaries.  If the value falls within a boundary then value is written into that cell, otherwise it’s left blank.  The result is our mapped table below.

Chart with additional columns for conditional formatting

 

Now if we click on the default chart we created earlier, Excel highlights the table data used for the chart (below).

Excel data used for the default chart

 

Drag the red and blue boxes to select our new data (below).

New data selection for chart

 

And voila, we have our chart with conditionally coloured bands and a key to those bands (below).  Note, in true Blue Peter fashion I’ve adjusted the colours for each band and also adjusted the gap and spacing of the bars (simply double-click on any of the bars and you’ll have access to those settings).  This is a rough and ready but effective solution.  It won’t be the most efficient for large volumes of data because we are running the calculations for every cell repeatedly.  We could use VBA for a more efficient method but that’s another blog entry for the future…

Excel chart with conditional formatting