All posts by Jack

Some misc powershell commands for working with azure PS modules

This is one of those posts I’m doing mostly for myself to keep things in one place.

Remove an old version of a module:

$Latest = Get-InstalledModule (modulename); Get-InstalledModule (modulename) -AllVersions | ? {$_.Version -ne $Latest.Version} | Uninstall-Module -WhatIf

another way to do the same:

get-installedmodule modname -requiredversion  x.x.x | uninstall-module

(taken from

Show what modules are installed:


Show what versions of a specific module are installed

get-installedmodule -name modname -allversions


Update the azureRM module:

update-module azurerm -force

Install over a stubborn module:

install-module azurerm.cognitiveservices -force




Using a Mac in a Microsoft world: SQLPro from Hankinsoft

While most of this blog covers my professional experience with SharePoint running on the windows platform, I’ve also spent a bunch of time as a Mac user.

In the past, I’ve almost always remoted into windows machines to get work done – SharePoint, Powershell, SQL Management Studio, etc.. all required this approach.

This was pretty much the only option when things were on premise but now that the cloud is such a focus, there was one tool I was particularly interested in:

A tool for interacting with Microsoft SQL Server in Azure from my mac.

I tried a handful of options including some open source cross platform tools, some paid cross platform tools and a mac only gem called SQLPRO for MSSQL.

The free options were kinda 1990’s java ugly. One wouldn’t even connect, and one connected but I ran into problems enumerating fields.

Of the paid options, the best bang for the buck for me was Hankinsoft’s SQLPro for MSSQL.


This tool is a native mac application (not a java port) and it shows: the app is fast, responsive, and looks like a mac app, supporting newer features such as the mac’s special full screen mode.

I had no trouble connecting to PaaS SQL in Azure, and was able to do all the things I’d normally want to do. The tool has code completion,  the ability to drag and drop field and table names from the navigation pane on the left to the editor, some nice features like the ability to auto indent sql code, and the ability to run multiple queries (separated by semicolons;) and display the results from each one in the results window, just like MS SQL Management Studio does!

SQLPro is a pretty stable product, and that’s no surprise, as the developer has been making database tools for quite some time now. (There are versions for MySQL, SQLite, and Postgres, as well as a ‘studio’ version that supports multiple types of databases)

SQLPro was $79.99 as of this writing – This is a one time, buy it, it’s yours price, and it’s very competitive vs other tools that also offer the ability to connect to and work with Microsoft SQL server.

So far I’m really enjoying being able to run queries against SQL in Azure natively on a mac – This is a great tool and highly recommended!

SQLPro for MSSQL for Mac
by Hankinsoft Development

Timewatch, a PowerShell Function for reporting how much time is left in a loop

I had to loop through a bunch of things the other day and wanted to have a nice way to report how far I was through the loop.

This way if I had 1000 things to loop through, and each one took 10 seconds, I knew I had 10,000 seconds to wait, or roughly 2.7 hours.

Armed with this information, I could be comfortable stepping away for a bit with a good guess as to when my script would finish.

To use the function above, you need to paste it into your script, somewhere above where you intend to call it.

Then in your script you likely have a loop like this:

To make use of the timewatch function, we’ll modify our loop as follows:

That’s it, now the timewatch function will track the average time per iteration, and based on how many remaining interations are left, will report both the amount of time left, and an ETA time that the looping would complete.

Having PowerShell talk to you is actually useful!

PowerShell can talk to you.

My favorite use for it, is when I have a script that’s going to run a long time, say 10-20 minutes.
We all KNOW that we’re not going to sit there and watch the output!

Having PowerShell talk when some condition comes up can be really useful if you’ve relegated the window to the background and are doing something else like reading email.

Doing it was super easy.

Shoutout to Michael Blumenthal for suggesting this!

Modify-Sublicense PowerShell function for modifying Office 365 Sublicenses


An example call would be:

Modify-Sublicense -upn "" -PrimaryLicense "TENANT:ENTERPRISEPACK -SublicensesToRemove @("SWAY","YAMMER_ENTERPRISE") -SublicensesToAdd @("SHAREPOINTENTERPRISE", "SHAREPOINTWAC")

If you’re new to powershell, scroll down past the code below for some additional tips.

Naturally, sublicenses you had before will REMAIN unless you’ve removed them.
Sublicenses you DID NOT HAVE before will NOT BE ADDED unless you specifically add them.

This is important because of how the licensing works, which has been covered in other blog posts my myself and others.

I’ve used the function below as part of a larger script with good results.


New to powershell?

Here are a few tips:

  • Since the code is a function, you’ll need to copy-paste it to a script of your own before you can use it
  • In powershell, functions need to appear in your script above/before you use them.
  • There are ways to load the function in memory, so you can call it as if it was a native command. See How to add functions to your powershell session
  • An array in powershell can have zero or 1 or many items, if you need to pass a single value, just pass it as an array with one value. That would look like this: @("Value1", "Value2")

– Jack

Script to dump all your assigned Office 365 user licenses

As part of a licence transition from one level of office 365 to another, I wanted a way to record/audit who had what licences before/after the audit.

The script below dumps all the relevant information to a tab delimited text file.

Before we get to the script, I want to talk about some of the design considerations….

If we were to normalize the data we’d have 3 different sources of raw data:

  • List of users
  • List of possible licenses levels
  • List of possible sublicense levels.

To record all this in a relational database, you’d want a table for each of the above.

Then you’d need a few table to record the relationships and which users have which licenses.

Since many business users aren’t comfortable with relational data structures, I decided to de-normalize the data in favor of making it easy to consume.

In english, that means that much of the data will be repeated.

The fields we collect are:

  • UPN (usually email)
  • Lastname
  • FirstName
  • Displayname (It’s common for utility accounts like conference rooms to not have a first/last name so I include the displayname)
  • The Name of the Licence assigned to the user (the ‘primary’ license, otherwise known as the “MSOLAccountSKU”
  • The name of the sublicense that goes with the Primary *(See below)*
  • The deployment status of the sublicense


Notes about the ‘sublicense’ what I mean here is this: Say you have an E3 license, this is composed of a bunch of ‘sub’ liceneses that you can turn on-off independent of one another, examples of sub-licenses would be Exchange, SharePoint, Sway, Office Online, etc…

The sublicense values are not uniqe, for example, both E1 and E3 have a ‘Sway’ sublicense type.

Here’s another interesting tidbit: All primary licenses have at least one sublicense.

I wanted to flatten the data as much as possible so the sublicense field is a combo of the primary and sublicense

This will all be a lot clearer once you run the script so here it is…


PS Script to list all Office 365 licenses and sublicenses available (AccountSKUID and ServiceName)

We’re transitioning some licensing at work and I thought it would be helpful to have a ‘catalog’ of all the available options.

The script below will produce such a list…


Office 365 Client Performance Analyzer

Microsoft has a tool available for measuring Office 365 performance.

The tool tests dns response time, latency to the datacenter where your SPO or Exchange are located, and bandwidth.

It then displays results in either green or red based on the performance you’re getting.

The article linked above has both the download link and an explanation of each value.

It’s a handy tool, in that you can give it to your users if they say SPO is slow, and collect the data back. It’s much easier than asking them to time a page load, plus the page load wouldn’t say why the load is slow, just that it is slow. This tool will help provide data that can explain the why.

  • Jack

Replace an expiring Client Secret in an app for SPO

What are we even calling these things these days? Apps for SharePoint Online? Apps for Office 365?

This article is about the apps we build using the new app model that was introduced along with SharePoint 2013. It’s the main way of developing functionality for SharePoint Online.

The apps are hosted in Azure (or on the hosting provider of your choosing)

When these Apps are created/installed a Client secret is used to ensure that communication between your externally hosted app and SharePoint Online is secure and not coming from an attacker.

Unfortunately these certificates expire.

Ours have.

The article below talks about replacing them.

We also opened a ticket with Microsoft Premier Support which revealed a few more tidbits.

  • It takes like 24 hours for the new certificate to propogate through your system, leaving your app out of commission for at least that long if you don’t renew before it expires.
  • The article above mentions, but does not give an example of, extending the date from the default 1 year to 3 years. I’ve copied some of the correspondence with Mustaq Patel from Microsoft, who helped us through the process (Thanks Mustaq!)

Note for the scripts below, you’ll need your clientID this is in the web.config of your website that’s hosted in Azure.  As luck would have it, the person at our company who would have had this info was on vacation. Since it’s in the web.config of the running app, it made sense to just pull the actual web.config in use. I did this via FTP, using the steps in this article to configure an FTP account to gain access to the server:

Update: You can also find the clientID by going to any sharepoint site that uses the app, Site Settings->Site App Permissions.

It’ll be the guid between the last pipe symbol and the @ symbol

Example i:oi.t|blahblahblah|abcdef-1234-this-is-aguid-and-is-what-you-want@7a534-no-the-guid-you-want-123

(thanks to Mustaq for pointing this out!)

  1. Connect to MSOnline using tenant admin user with below powershell in SharePoint 2013 powershell
  2. Get ServicePrincipals and keys. Printing $keys will give 3 records, replace each KeyId in key1, key2 and key3. You can also see EndDate of each key. Confirm if your expired key shows there. Also note that clientId needs to match as per your clientId.

  3. Generate new ClientSecret for this clientID. Please note it uses clientId set in #2. Also ClientSecret is valid for 3 years.

  4. Copy the output of $newClientSecret.

  5. Replace the Web.config with this ClientId and ClientSecret. Please note we don’t need SecondaryClientSecret appsettings.

  6. Wait for 24 hours to propagate ClientSecret to SPO

Sharepoint Online Permissions oddity

One of my co-workers had an issue with SPO where a user she added to the owners group still had no permissions on the site.

We opened a case with Microsoft and the issue was some weird permissions issue : the Site owners group didn’t have the correct permissions to grant or modify permissions for users or to view the related settings pages in site settings. Microsoft said it was related to the access request list. 

MS provided a KB article:

My Super-Awesome-Fantastic Co-Worker Melissa Seals did all the work on this case, so I can’t claim to have any part in the solution, but it seemed obscure enough that it’s worth documenting here should we ever need it again. (She followed the steps in the article and got it working!)

  • Jack