Tip of the day – Excel INDEX MATCH in 10 seconds

microsoft_excel_2013_logo-svgI originally meant to write this post last summer the first time I used the magic of INDEX MATCH but for some reason never got around to it. I did however leave myself a template spreadsheet but even that took a bit of time to decipher what I’d done so this time around I’ve decided to make the post happen!

The need to delve back into my archives came about when a colleague in the HR department asked me if there was a way to look up information from one set of data against another in Excel and mentioned VLOOKUP as an option.

That got me thinking about a similar scenario I’d had the previous summer when I needed to so something similar with user accounts after some fun with Office 365 DirSync experiences: synced OUs and user deletion

I also remember swiftly dropping VLOOKUP in favour of the lesser-used but (imo) more flexible INDEX MATCH formula. Some of the advantages of the latter include:

  • lookup columns from anywhere in the sheet
  • no need to worry about messing up the formula if you insert \ move columns around

Of the websites I’ve looked at this one gives the best explanation and real-world examples so give it a read for further background:

Ref: https://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup

What’s the answer?

However I wanted to write the formula out in even simpler plain-English so it would take me no longer than 10 seconds to remember how it works should my future self need a quick reminder.

Initially I went with the classic method of a post-it note but to save anyone needing to decipher my typically IT-techie scrawl here’s a much nicer version I made earlier🙂

index-match

  • In the example I’m using a value in cell A2 of Sheet1 to find an equivalent value in Sheet2 column A
    Once found the formula returns a related record for the item in question from Sheet 2 column D
  • You can fill the formula downwards if you have multiple inputs that need matching (e.g. a list of IDs that each need a value against them)
  • To help illustrate I’ve made a sample file that uses a fictional student’s ID number to return their grade and date of birth from another sheet.
  • If the value isn’t found in the data source Excel returns an N\A value
  • As always the file is available in my Public OneDrive folder

Further tips

  1. to save having to define exact cell ranges for the data just use D:D (or whichever column you require) to search the whole lot, handy if you’re likely to replace the data source with a refreshed version at some point.
  2. If you’re typing this formula in manually and selecting columns across tabs make sure you don’t follow your natural instinct to click back in the formula cell to complete it; if you do you’ll end up changing the tab’s reference back to the one the cell exists in, which will play havoc with your results!
  3. if you want to use the INDEX MATCH to return multiple values from the source data I find it easier to copy the formula into notepad, adjust the first cell reference then paste it back. Sometimes Excel tries to be too clever when copying \ filling across formulas and ends up causing more errors than it helps to solve!

In the end INDEX MATCH did the trick perfectly and earned me a Freddo chocolate bar for my troubles, which at the current ever-increasing price of chocolate these days is a pretty fair trade!

Save yourself from insanity… Google and Outlook contacts on Android

2000px-Android_dance.svgRecently I had to factory reset my HTC One M8 whilst it was in for a repair (thanks to a stray bottle of soy sauce landing square on the screen, ouch!) but since reinstalling all my apps I noticed my contacts sync wasn’t working correctly.

Although my Google account had synced contacts when first setting things up the People app would’t let me add a new contact to my Google account. Rather it would default to SIM instead. Very strange I thought, it’s never done that before and I could still see everything else that was already there. Oddly the filter menu wouldn’t list “Google” as an option either.

Initial thoughts

First I thought maybe the app permissions after the Android M update may have gone wonky so checked those, no problems there (People app had access to Contacts permission).

Next… maybe the Google Account sync had Contacts sync turned off but after checking it’s there and working fine.
As another test I created a new contact online via Google Contacts and then forced a sync on the phone… contact didn’t appear. Very odd.

Tried a few other ideas like clearing App caches, also cleared the Android cache partition via Recovery as I’d been having some issues with the HTC Camera app as well but no joy there either (although Camera app now seems to have sorted itself out so a bit of a bonus there).

Solution – turn off Outlook contacts!

Finally I came across this…

http://forums.androidcentral.com/google-nexus-5/350303-phone-contacts-not-syncing-google-account-contacts-2.html

Credit to “haneyman” for this…

Confirmed, you cannot have Outlook sync contacts and expect Google contacts to sync. As soon as I unlinked the Outlook account on my phone, the Google contacts appeared.

So it seems the Outlook app is the culprit. To confirm I went into the sync options for my Outlook.com account and sure enough contacts sync was enabled. Turned that off, cleared my running apps then sure enough on next load the People app was letting me create and sync Google contacts again.

Maybe having accounts on both Google and Microsoft is a bit unusual but definitely one to watch out for if you have a foot in both camps and use an Android smartphone.

Save yourself from insanity: Aruba Captive Portal RADIUS Accounting

raidusI’ve been meaning to post this one for a while but got there in the end! Recently we changed our content filtering provider and one of the aims of the new system was to ensure tighter integration between the Wi-Fi controller and filter for authentication \ identification of users.

We particuarly needed the framed-ip-address attribute as that’s used to tie a device to a user on our particular filtering product. In theory the setup sounds fairly straightforward:

  • set up Windows Network Policy Server to handle RADIUS authentication
  • set up RADIUS authentication profile against a new Wi-Fi SSID
  • set up RADIUS accounting on the wireless controller
  • set up RADIUS accounting on the filtering server

Initially all went well and we were able to authenticate users smoothly onto the Wi-Fi network via the existing captive portal… but (and isn’t there always a but!) we saw nothing on the filtering server, just an empty void of white space where user account activity should’ve been😦

Initial troubleshooting steps

So I checked the simple things first…

  1. Check RADIUS Interim Accounting option is enabled on the AAA profile
  2. Check if shared secret is too complex \ typo when entering it into various config pages
  3. Ensure accounting server options in Windows NPS are configured correctly
  4. Confirm configuration of accounting server details on Wi-Fi controller
  5. Ensure ports for accounting information are set as they should be

Everything checked out correctly and authentication still worked fine despite me trying to break it, which made accounting failing even more strange. With that in mind it was time to move onto some more in-depth troubleshooting.

Delving deeper

Next step was to try and see if any accounting traffic was actually being sent so trusty Wireshark was spooled up to watch traffic for anything on port 1813. We saw plenty on 1812 for authentication but consistently nothing on 1813. At one stage I was beginning to wonder if the NPS server had something to do with it but replies to my posts to TechNet forums suggested otherwise.

A case was then opened with Aruba support which involved upgrading the controller to latest firmware 6.4.2.12 before further troubleshooting could be performed. A few useful commands came out of this process, which should be ran before upgrading to ensure the controller has enough resources to run the upgrade:

show memory
show storage

As an aside the upgrade did give us a nice new(er) feature called AppRF that basically brings application-level monitoring to the Aruba UI. It saves going through the firewall to find the same information and allows us to see at-a-glance where the bandwidth is going on the wireless network and to which user(s):


image credit: Aruba Networks

The update also made packet captures on the controller a bit simpler, which further proved our theory that no accounting traffic was being sent as the controller itself didn’t log anything on 1813 in its direct captures. However despite the upgrade we were still no closer to resolving the accounting issue.

The breakthrough

After escalating through various levels of Aruba support and product management one of the technical team finally found our issue, which turned out to be a deceptively simple fix. It’s a sneaky little setting squirrelled away named Captive Portal Check for Accounting

The setting in question lives within the Misc. Configuration section of Security > User Roles.

You need to edit the settings of the role that is assigned as the 802.1X User Default Role for the the AAA Profile associated with your RADIUS-enabled VAP (what a sentence that is!)

aruba role misc settings

Basically untick that box and everything starts working…

By default the Captive Portal Check for Accounting box is ticked and therefore accounting won’t work if the user has authenticated via a captive portal. The Aruba documentation has this to say about it:

The check-for-accounting parameter is introduced in ArubaOS 6.3.1.7. If disabled, RADIUS accounting is done for an authenticated users irrespective of the captive-portal profile in the role of an authenticated user. If enabled, accounting is not done as long as the user’s role has a captive portal profile on it. Accounting will start when Auth/XML-Add/CoA changes the role of an authenticated user to a role which doesn’t have captive portal profile. This parameter is enabled by default.

As soon as the box was cleared accounting information came flooding in and I was pleasantly surprised to see how quick the interim updates were also processed, as some vendors’ interpretations of the RADIUS accounting standards aren’t quite so amiable from what I read during my research.

Was certainly a voyage of discovery to get to the solution but we have gained a few new features along the way and I’ve also become well acquainted with the ArubaOS CLI for troubleshooting purposes, so the process has added some valuable knowledge too🙂

Video streaming update and a visit to BVE 2016

Print

This week I was able to visit the BVE expo to have a look for the next generation of video mixing and streaming equipment for our media block. There was plenty on show, including an interesting talk on using drones in TV production that drew quite a crowd!

It’s really interesting to see how networking and video technologies are converging and definitely something I’d like to do more work with in the future.

Our equipment is used by students in their classes for as well as projects such as HC Radio and our yearly Havering Asks TV programme. The visit also reminded me to write a little about some of the new kit we used in our recent productions.

vMix updates

After using the free version of vMix for the video stream of HC Radio we decided to purchase the vMix HD edition for video production use. The additional inputs and extra features such as Video list were what we needed to add pre-recorded content into the live show production.

Ref: http://www.vmix.com/purchase/

One thing we found with the video list is that the UI started to lag when we loaded 20+ videos into vMix. A workaround from the support team was to use VLC to generate a playlist and load the content in that way instead. End output was the same but this method seemed a lot more CPU friendly. We’ll need to check this again as new versions of vMix are released.

We’ve also since found out about the free vMix Social plugin which will allow live updates to be posted as on-screen graphics so will be trying that out next time round as well.

vmix-logo-large  Planet-eStream

For those wanting to record the output to Planet eStream use either of these methods, credit to eStream support for the below as they were testing vMix around the same time we did. Great minds and all that🙂

1) On vMix there is an option for ‘External’ at the bottom, if you go to the settings next to ‘External’ then go to ‘Outputs’ make sure that Recording/External is set as output and all overlays selected. Now when you click ‘External’ and it goes red you can open an instance of the encoding application, on the same machine and there will be a video device called ‘vMix Video’ this will allow you to record the output window on vMix.

2) Stream it through eStream by editing the settings next to ‘Stream’ at the bottom. You can create a custom RTMP server. The settings will be:
Server: rtmp://svrestream/HCBcast
Stream Key: vMix

Now you can go to the encoder machine on another pc and use a network video source. Use the URL rtmp://svrestream/HCBcast/vMix please note capitalisation is important.

vMix GO

vmix-goThis is one of the new (to me anyway!) products I spotted at BVE today. It’s a self-contained, portable production system with all the inputs etc. you need integrated with a suitably powerful PC and vMix Pro included. It provides an interesting alternative to the Blackmagic Design kit I also went to see today, which is hardware-based rather than vMix’s software approach.

Streaming across multiple locations

One of the new requirements for Havering Asks 2015 was to provide an additional video source so we could transition between the live show taking place in our performance area “The Space” as well as our TV studio in the media block. vMix would then be used to mix the inputs and provide the stream to our YouTube channel.

Given that the two buildings are at opposite ends of the college it was a pretty simple decision required that we needed to use the network to get video from one place to the other. The question was how best to do it. We also wanted to use whatever solution we found for future events so it needed to be robust and easy to set up going forward.

From a cost perspective we thought of using a PC \ laptop but after adding an external capture card the solution seemed rather clunky. There’s also a fair bit to go wrong and once you put all the hardware prices together it’s not particularly cost-effective either. We then moved onto dedicated streamers to see what was available and looked at a couple of different products:

I liked the look of the Teradek and the output LCD would made it easy to use with DHCP as we could easily spot what address it had obtained as it gets moved around. Unfortuately it’s HDMI only and was the most expensive of the three options. It also turns out not to be supported with Planet eStream so we continued onto the other options.

The unbranded Chinese device did its basic job of streaming but, as is often the case with these no-name products had some odd firmware issues that meant we couldn’t 100% trust it. The main one was with DHCP, where the stream output link seemed to stick with the previous address it had been assigned, rather than the current lease. This presented a problem for us as setting up a static port each time we wanted to stream would add an extra administrative burden.

Now we come to the NVS-25, which does a great job of offering lots of flexibility at a great price:

  • SDI, HDMI and composite video inputs
  • RCA and XLR audio inputs
  • multiple streaming protocols
  • USB port for recording of video stream

The multiple inputs are particularly good as it means we can use our current hardware over SDI \ CVBS and then in future have the flexibility to move to HDMI should we want to.

I had a look around BVE for similar devices and was rather pleased to see one of the suppliers rate it as the best devices for feature set in its price range, always a relief to hear we chose wisely!

Experiences with the NVS-25

We learnt a few things from setting up and using the Datavideo device so here’s a few lessons learned to save anyone else the trouble:

Networking

The IP scanner utility is very handy and helps get up and running quickly.
I hear that an NVS-30 is on the cards and if Datavideo can get a screen on the new product it’ll be even better!

Storage

The front USB port should only be used with USB sticks or, at a push SSD drives on an adapter. It won’t run USB hard drives that don’t have their own external power and the side effect is that the encoder will freeze up until you do a hard power off and disconnect the offending drive. The media should also be formatted as FAT32.

Firmware

Update the firmware to the latest version as there are bugs in previous versions relating to how streams are presented. We had problems getting an RTSP stream into vMix due to incorrect header information in the stream. Apparently from what I was told at BVE an update has since been released to resolve this. As a workaround we changed over to RTMP instead, which worked OK.

IMAG1029
Datavideo NVS-25 in action connected up to our mixing desk

Whilst on the Datavideo stand their tablet-oriented autocue caught my eye. Again rather reasonably priced it syncs the script with multiple devices and allows central control from another station wirelessly. Perhaps one for the 2016 productions🙂

IMAG1198

Activate Office 365 Education email encryption using your free Azure RMS licenses

ome-iconIn order to meet Data Protection requirements for sending data to external recipients we needed to find a method of providing encrypted email functionality for our users. In Office 365 this is provided as a native feature via Azure Rights Management Services.

I vaguely remembered seeing something a while back about these licenses being available at zero cost and sure enough soon found a link confirming this as part of the plan changes that also brought us eDiscovery features.

Ordering licenses

In a similar vein to how the Student Advantage licenses were made available you’ll need to ask your EES reseller to get them activated against your O365 tenancy. For reference here’s the names and part numbers of the licenses you’ll need:

azure-rms-order

Assigning licenses

Once the order has been assigned you’ll need to add the license to any user you want to be able to use the RMS features i.e. in our case anyone who needs to send an encrypted message. If you’re using the GUI look for this:

azure-rms-o365-license

Given the number of users to assign licenses to the quickest way was via PowerShell, using a variation on the script that originally assigned our student licenses.

Tip: I initially scared the living daylights out of myself when checking which licenses were assigned after I’d ran the update script as it appeared users no longer had their Office 365 licenses.
The script (below) uses column position [0] to search the field AccountSkuID, which is all well and good until your users have multiple licenses assigned and for whatever reason they aren’t all listed in the same order (!)

I ended up having to run this code twice, once with Licenses[0] and again with Licenses[1] to pick up all the staff accounts, then checked a few random samples in the GUI for good measure:

Get-MsolUser -All | select UserPrincipalName,Licenses | Where-Object {$_.Licenses[0].AccountSkuID -eq "YOURORG:STANDARDWOFFPACK_FACULTY"} | Set-MsolUserLicense -AddLicenses "YOURORG:RIGHTSMANAGEMENT_STANDARD_FACULTY"

Once done I then ran GetMsolAccountSku and confirmed the numbers match up.
The number of office 365 licenses assigned to each staff user is now 3:

  • Office 365 Education
  • Office 365 ProPlus
  • Azure RMS

I’ve since found this very handy looking GUI license assignment tool via the Office 365 Yammer group which may make any further bulk maintenance tasks a bit less scary🙂

https://gallery.technet.microsoft.com/office/Office365-License-cfd9489c

Usual disclaimer applies, be very careful running license update scripts, especially in bulk!

Configuring Azure RMS and Office 365 Message Encryption (OME)

Now your users are licensed jump into the Admin Portal > Service Settings > Rights Management then follow this excellent guide to switch on Azure RMS, then configure Office 365 Message Encryption.

http://office365support.ca/setup-and-enable-office-365-message-encryption/

There’s not much else to say for this step as the guide is spot on🙂

Once you’ve set up a Transport Rule in Exchange settings sending yourself a test email with the keyword(s) you specify will generate this at the recipient’s end (sample screenshot of the message arriving in a GMail inbox).

ome-email

OneDrive storage saga.. Microsoft sees sense at last

9550939064_bf4b0be0bc_zAfter making a monumentally stupid decision to claw back storage space from consumer OneDrive accounts it seems Microsoft have finally seen the light and relented on their decision… in part anyway.

Logging in this evening I spotted an interesting looking email from the Uservoice forum. Basically Microsoft have done what they should’ve in the first place and left long-term users’ current storage alone.

The backtrack on “unlimited” space has stayed in place though, which isn’t surprising really given how it was being used.

Unfortunately Microsoft have done themselves a lot of reputational damage in what they had left of the consumer space. This announcement is the first step in getting some pride back but judging by the comments it may be a bit too late to regain the trust of many contributors on the site.

Like most I signed up to Google Photos after the announcement but now end up in a better position having backups across both services so in a roundabout way it’s worked out well!

Many said that Microsoft wouldn’t go back on their policy but it just goes to show if enough people speak up it can make a difference… unless you take the more cynical view that this whole show is just a way of managing opposition to the reversal of the “unlimited” promises of barely a year ago😉

onedrive email

If you currently have 15GB loyalty and \ or 15GB camera roll storage make sure you visit the link below asap to claim back your storage. Once done you should see the screens below🙂

http://aka.ms/onedrivestorage

onedrive-storage

onedrive-storage2

For more commentary on the climbdown head over to the links below:

Ref: http://www.theregister.co.uk/2015/12/11/microsoft_onedrive_reduces_free_storage/
Ref: http://arstechnica.com/information-technology/2015/12/microsoft-to-give-back-some-of-the-free-onedrive-storage-its-taking-away/

Header image credit – Chris Marquardt
https://www.flickr.com/photos/nubui/9550939064

Office 365 service outage

7612.Office-365-logo_thumb_58DAF1E4

As many of you are experiencing right now Microsoft have had a major issue in Azure AD that has affected the Office 365 platform.

We can’t get to the Service Status page as it’s stuck behind the login page (!) but the Azure status seems to be best source of information at present:

Ref: https://azure.microsoft.com/en-us/status/#current

azure status

The outage seems to have some relation to the random issues we were seeing on DirSync in the last day or so, receiving messages stating “The following errors occurred during synchronization:” but with an empty Error Description field.

Ref: https://social.msdn.microsoft.com/Forums/en-US/2050fdd2-2392-4a93-aeb2-ac0c1120d314/aadconnect-identity-synchronization-error-report?forum=windowsazuremanagement

More to follow…