Tip of the day – Windows Update fixes for 7 and 8.1

20013670043_113a55f0bf_z

Back in the good old days (aka a few years ago) Windows Update tended to be something that just… worked. You’d take a fresh Windows install, pop it through the update process and after a bit of chugging you’d get a fully patched OS.

Recently Microsoft seem to have made a bit of a mess of things and I’ve spent far too much time forcing recalcitrant machines to do what should be a simple task.

Hopefully once the cumulative updates start rolling everything into the monthly patch cycle this post may become irrelevant. Until then here’s the quick way to persuading a Windows 7 / 8.1 machine through the Update process…

High CPU hotfix

Install this one first if you’re faced with a particularly out-of-date installation otherwise you’ll be stuck for days “searching for updates” while your CPU goes crazy (100% utilisation) for very little return…

Windows 7 https://support.microsoft.com/en-gb/kb/3102810
Windows 8 https://support.microsoft.com/en-gb/kb/3102812

Windows Update Agent

Next install this to update your updating software in order to download new updates (!)

https://support.microsoft.com/en-gb/kb/949104

Reset Windows Update Agent script

Sometimes Windows Update still won’t work in spite of the patches above so run this script from TechNet to reset the Windows Update subsystem in case something has gone awry

https://gallery.technet.microsoft.com/scriptcenter/Reset-Windows-Update-Agent-d824badc

Round trip limit exceeded

Despite all of the above Windows Update can still fail because of a hard-coded limit in how it talks to WSUS (this only applies to managed Windows desktops rather than home users). In which case you need to take advice from this song…


“you can get it if you really want but you must try, try and try, try and try… you’ll succeed at last”

Basically just keep clicking the retry button until WSUS gets through enough trips to serve you all the updates Windows needs.

Ref: http://trentent.blogspot.co.uk/2016/03/wsus-clients-fail-with-warning-exceeded.html
Ref: https://blogs.technet.microsoft.com/sus/2008/09/18/wsus-clients-fail-with-warning-syncserverupdatesinternal-failed-0x80244010/

You may also be able to speed things up by cleaning up your WSUS server, which can be aided via this very useful script

https://community.spiceworks.com/how_to/103094-automate-wsus-cleanup

or this one…

https://community.spiceworks.com/scripts/show/2998-adamj-clean-wsus

Now that’s sorted you can make yourself a cup of tea and wait for that progress bar to crawl across the screen! Will be interesting to see how the cumulative update process goes but if it means an easier way of rolling an out-of-date machine up with one single download then it’ll have some benefits for convenience albeit at the expense of granular control… swings and roundabouts I guess…

image credit Christiaan Colen
https://www.flickr.com/photos/132889348@N07/20013670043

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.

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

Tools of the trade

ToolkitBecause the list of all the best little tools and utilities only gets larger over time I’ve decided to take it out of my head and starting writing them out on a post here.

Works well for me as there’s somewhere to refer back to and works well for anyone reading this to discover something very handy that may not yet have crossed your path.

I’ve tried to split into categories so skip to the one that’s most relevant. The list is by no means exhaustive so I’ll keep adding more as I remember or discover them 🙂


 

Construction Worker-50Hobbyist

like to create your own electronics? Look no further…


 

Speaker-50Multimedia

tools for video, audio etc.


 

Network-50Network & Server

Local and online utilities for your day-to-day networking needs


 

Console-50Scripting

Development tools and reference


 

System Task-50System

An assortment of tools for your local machine


 

Domain-50Web

Web development tools

 

Disclaimer: although I’ve used all the tools in the list and recommend them due to their effectiveness and usually zero cost be aware that they may not stay that way forever! Keep a local copy of any program you find especially useful and always watch installers carefully in case the developer decides to go down the adware-supported route at some point in the future. Forewarned is forearmed…

Image credit: Icons8 https://icons8.com

Office 365 PowerShell – watch out for the Object Graph error

powershell

Whilst in the Office 365 admin console the other day we noticed that many of our new user accounts that are being readied for the new term were missing their Office 365 licenses. We run a script every night to automatically license our users so something wasn’t right… let the investigation begin…

Everything *looks* OK?

Initially I thought maybe the scheduled task on the server wasn’t firing so I logged on and verified the script had completed successfully the night before, at least it appeared that way as the last result code was 0x0 (success).

I double-checked the script to ensure the logic was still sound in light of changing our MIS database and subsequent student rollover for the new academic year… nothing out the ordinary there as the script just checks for users starting with a student ID code where no licenses are applied.

Run the script step-by-step

My next plan of attack was then to run the script manually in a PowerShell window line-by-line to see what was going on. The first parts were OK, authenticating into the Office 365 service with no issues then I ran the initial filter command:

Get-MsolUser -All | Where-Object {$_.isLicensed -eq $false}

But instead of returning a list of users the command bombed out with this error:

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://provisioning.microsoftonline.com/:ListUsersResult. The InnerException message was 'Maximum number of items that can be serialized or deserialized in an object graph is '65536'. Change the object graph or increase the MaxItemsInObjectGraph quota. '. Please see InnerException for more details.

Looking around it seems the error is fairly common, I’m not sure whether it was triggered either by the number of users in our tenancy going past a certain level (and thus exceeding the query limit) or maybe a software update.

Either way the solution is explained clearly by the posters on a thread on serverfault.com:

http://serverfault.com/questions/691667/maximum-number-of-items-that-can-be-serialized-or-deserialized-in-an-object-gra

Basically the process goes as follows for an x64 server:

  • navigate to C:\Windows\Microsoft.NET\Framework64
  • go into the correct version folder based on the version of PowerShell you’re using
  • edit the machine.config file with the lines summarised in the link above
  • save and close config file

One step that isn’t mentioned was in my case the change didn’t seem to take effect until I rebooted the server

Running the unlicensed user query again after the rebooted returned the expected (large) list of unlicensed users, which I then followed up with a full run of the script to sort out all the student accounts. After that the list of unlicensed users was back at normal levels (shared mailboxes, archives etc.)

Field notes: delprof2 shutdown script

A new twist on a familiar tale is the best way to describe this post! Recently we’d noticed quite a few of our first-gen SSD machines (60GB drives) were running low on disk space, particularly in open-access areas where lots of different users were logging on.


didn’t think I’d be seeing you again…

This probably comes as no surprise to most education network admins as it’s something we used to deal with in the days of small HDDs but became almost irrelevant as larger local drives became the norm. To some extent history has repeated itself with SSD drives and we have no such problems with our newer Samsung Evo 120GB drives.

That said a solution still needed to be found for the machines with the 60GB drives. The first port of call was the easy option, enable automatic profile cleanup via GPO after a set period of days:

Computer Configuration > Policies > Administrative Templates > System/User Profiles > Delete user profiles older than a specified number of days on system restart

A bit more thought required

However after thinking about it for a couple of seconds it’s not that easy…

Our domain structure  places the Active Directory objects into OUs based on their location (room). Nothing unusual there. However what we don’t know based on OU alone is whether the machine is a classroom PC or one that lives in a staffroom \ office.

This information is very important because our Office 365 Outlook cache, amongst other per-user profile customisations needs to stay persistent and the last thing we want is to wipe staff profiles if the user has been away for a week on holiday!

With that in mind I seemed to have two choices

  1. Alter the OU structure to split machines into class and admin
    This would take a fair bit of administration and ongoing maintenance so wasn’t keen on this option
  2. Find a way to add some logic to the profile cleanup process
    Give the process some intelligence and get it to decide what to do by looking at the machine type

Registry key

registryOne of the sections in my custom imaging scripts asks the technician what the intended role of the machine is when they start the imaging process (name and location are done at the same time).

This information then goes into a custom registry key I create in HKLM\Software\HCFHE\WorkstationType for future reference.

Then the lightbulb moment: I can use that registry key as the identifier for the profile cleanup. First thought is can I add a WMI filter on the GPO… computer says no (or not easily at least)

Ref: https://social.technet.microsoft.com/Forums/fr-FR/5cd1b80a-2f90-4d46-bf65-dba52dcf0c56/how-to-make-wmifilter-that-looks-for-a-registrykey-or-filefolder?forum=winserverGP

Time for some scripting

By this point I’d decided that the GPO on its own wasn’t going to give me enough flexibility so I decided to go down the scripting route instead. First things first, we need a tool to run the profile cleanup, time to dip into my list of handy utilities for the excellent (and free) delprof2:

https://helgeklein.com/free-tools/delprof2-user-profile-deletion-tool/

Not only does it do a thorough job of cleaning profiles but it’s also very flexible in terms of specifying which profiles to clean and runs very nicely from a script 🙂

The script needs to do a couple of things:

  1. Check a registry key for the machine type ADMIN, CLASS (or an invalid value) and proceed \ quit accordingly
  2. Run the delprof2 tool with appropriate switches
  3. Write output to the Windows Event Log

For some reason I went for VBScript rather than PowerShell this time around. After a bit of research some very handy links turned up some code snippets to use. All credit to the authors for creating and putting them out there for re-use.

The second snippet is particularly useful, having worked with Linux for a while now I was yearning for a Windows equivalent to the “tee” command and very glad that the code below does the same job!

Check if value exists in the Registry (TechNet)
Save output of command to a variable (StackOverflow)
Write to Event Log (StackOverflow)

The end result

I’ve included a generalised version of the script below. As always grab the code from my OneDrive public folder to avoid any copy \ paste issues.

  • replace REMOVEME with the prefix of accounts to remove i.e. match something consistent in your student account numbers. You can have multiple /id: switches if you have a couple of different patterns to match
  • replace LEAVEME with accounts you want to exclude e.g. accounts you may have created for specialist use with profiles that you don’t want to be removed
  • You can have multiple /id and /ed switches if you have a range of different account name patterns to match
  • change any other delprof2 parameters as required (the example below removes inactive profiles over 7 days old)
  • the use of & vbCrLf after each line of output from delprof2 gives a nicely formatted Event Log entry, otherwise everything ends up on one long line!
Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & _ 
    strComputer & "\root\default:StdRegProv")
 
strKeyPath = "SOFTWARE\HCFHE"
strValueName = "WorkstationType"
objRegistry.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

Set shell = CreateObject("WScript.Shell")

If IsNull(strValue) Then
		shell.LogEvent 4, "Machine type registry key not found, exiting without performing profile cleanup"
		wscript.quit
		
ElseIf strValue="CLASS" Then
		'wscript.Echo "The registry key exists, the type is " & strValue
		
		Dim objShell
		Set objShell = WScript.CreateObject( "WScript.Shell" )
		Dim strCMD 
		strCMD = """\\yourdomain.tld\netlogon\delprof\DelProf2.exe""" & " /u /d:7 /id:REMOVEME* /ed:LEAVEME*"
		'wscript.echo strCMD
		'objShell.Run(strCMD)

		Set objExecObject = objShell.Exec(strCMD)
		strText = ""

		Do While Not objExecObject.StdOut.AtEndOfStream
			strText = strText & objExecObject.StdOut.ReadLine() & vbCrLf
		Loop
		
		shell.LogEvent 4, "Machine type is " & strValue & " - student profile cleanup has been performed" & vbCrLf & strText
		Set objShell = Nothing
		wscript.quit
		
ElseIf strValue="ADMIN" Then
		shell.LogEvent 4, "Machine type is " & strValue & " - profile cleanup not required"
		wscript.quit
		
Else 
		shell.LogEvent 4, "Machine type is unexpected value " & strValue & ", exiting without performing profile cleanup"
		wscript.quit
	
End If

The output in the Event Viewer then looks something like this…