For the Greater Good

I get asked every so often by those in enterprise IT why I spend my nights and weekends answering e-mails, doing custom coding, and building analytical reports for my teacher and administrator colleagues.  I get the impression from those counterparts that they’re only in IT for the 30% of the time they get to play with new technology and that the remaining 70% of the time when they have to actually do their jobs and help their colleagues out is a burden and a chore to them.

“Stupid users….” is a frequent utterance.  Maybe it’s the benefit of having been in my position and industry for over 20 years but I don’t see it that way.

I work nights and weekends because my teachers and administrators do.  If they’re on-line, that means they’re trying to finish something up for their customers: their students and their families.

If it wasn’t important to take care of some business for the kids so that the kids can continue to have the best experience in school that they can, they wouldn’t be on their laptops and tablets at home doing this work at 8, 9, 10 o’clock at night.

So when they run into problems, they come to me.

When they do, I have a choice.  I can resort to “I’ll look at it tomorrow when I’m on the clock and in the office.”  Or I can take a quick look to see what will be involved and make a judgment call over whether I can take care of it right away or lay the groundwork for “tomorrow.”

I tend to go for the latter because of that indirect but cascading effect that ultimately ends on the kids’ shoulders.  Because in my work in supporting teachers, it’s more than just grades and assignments that are affected.

Maybe a student is having a really rough time in class because of things happening at home. Without knowing it, he needs just that one little shining hope of success, like knowing he earned a perfect mark on a tough assignment, to make him believe that he’s not a complete waste of time and space like an older relative makes him think he is.

Maybe a student is struggling to comprehend the English language because her family moved here from another country just before the school year began.  She knew no one before walking into the cold, fluorescent brick building full of incomprehensible noises and colors.  And the one piece of familiarity that she can latch onto is a math equation.

Maybe a student who has autism has been in and out of disciplinary measures all year because he has difficulty expressing his frustrations and sensory overload in class, and lashes out violently as a coping mechanism.  And the one thing that can help him, his teachers, and his classmates is for his teacher to finally have access to his IEP and his accommodations list that had additional, helpful details, like “frequent breaks.”  “No sudden movements and low voice.”  “Minimal physical contact.”

None of this seems like it has direct connection with technology, but they do.  Whether it’s technology (my previous life) or data (my current life), they all touch students in some way.

If teachers are working right now at 9:30pm on a Tuesday, there’s a reason.  And if I can support that with answers or suggestions or even a game-plan when they run into problems or have questions, there’s a small chance that I can positively affect a student’s life in the long term, because that’s what we in schools are here for: the students.

This is why I do what I do, when I can. 

Add Former English Learner from PowerSchool Into Illuminate

Our English Language Development department wants to track students who are considered “former English learners” in Illuminate.  The criterion is if they’ve been exited from the EL program within the last four years.  This requires a few preparations.

First, set the code within Illuminate to display the former EL status.  I just chose the next one available.  I’ll use this variable in my SQL script, which comes next.

From the cog, choose Code Management

Look for English Proficiency.  I just start typing “english,” and it appears:

Then add the new code.  In our case, plain number 6 was the next available one.  I have to remember this value:

Next, I need to edit studemo.sql on the server where the SQL extract scripts are installed.  These are the scripts that build queries from the PowerSchool database, assemble the files, and then SFTPs them to Illuminate’s server.

The column that provides English proficiency status is column 14.  For that column, I use a CASE statement:

   when (sc.flaglep = 1) then 3
   when (sc.lepexitdate > to_date('6/15/'||to_char(extract(year from sysdate)-4),'mm/dd/yyyy')) then 6
else null end

The prefix sc refers to an alias for our state reporting code.  PowerSchool users outside of Michigan may have different column names.  Walking through this CASE statement, here’s what I’m saying:

  • If the “is an English learner” checkbox is marked, the value is 1 in the database, so I want to return the value of 3 to send to Illuminate (note in the screen shot above of the code table, where 3 indicates “English Learner”).
  • If the EL exit date is greater than a date of June 15 of the year that’s 4 earlier than the current system date, then return a 6.
  • Otherwise, return nothing

So the possible output values will be 3, 6, or nothing.  This is proven when looking at studemo.txt in Excel:

Once uploaded to Illuminate, this value can then be used in filters for reports, student groups, and assessments:

I Had the Best Music Class in Middle School

I realized just now that the only person I’ve told about this is my wife.  So let’s fix that.

In 7th grade, I, naturally, had to take several classes that are called “specials” now.  These are the arts, outside of general ed curriculum, like music and art.  My music class was unlike any other, and in my 18 years working K-12, I’ve never seen it again.

7th grade, 12 years old.  I learned the history of rock’n’roll.

Our music class wasn’t a bunch of bored pre-teens sitting around singing boring songs out of tune and off-key.  We weren’t playing instruments badly.  No, we were learning about the impact and influence of early jazz and big band and swing and gospel on what would ultimately become “rock and roll.”

We listened to the Beatles’ “Revolution #9” and because it was the ’80s and record players still existed, we learned about the backwards playing.  And we actually listened to it, and examined its impact on society at the time.  I learned about Led Zeppelin, The Who, The Yardbirds, Cream, Deep Purple, and Jefferson Airplane….and their evolution into Starship, since “We Built This City” was the new hit song on the radio at the time anyway.

It was not just a history of rock and roll, but a history of the ’60s and ’70s at the same time.  If we weren’t listening to records and tapes, we were watching documentaries about Woodstock (sanitized and censored to be appropriate for 12-year-olds, of course).

At the end of class, our teacher would let us play a cassette of our choice.  He encouraged each of us to bring something in to share with the class.  If nobody did, then he would play his own mix-tape of classic rock.  I, of course, would bring in my “Dare to Be Stupid” tape, and not only would my teacher laugh at Al’s parodies but he would dive deep into Al’s musicianship and songwriting skills.

He literally dissected Weird Al Yankovic.

It was the best music class I’d ever had.  And it’s something I’ve never seen since.

When Field Calculations Identify Typos in Sources

The project: aggregate Fountas & Pinnell performance results for the past three school years.

The challenge: put together the assessments from 6 grade levels over 3 years in 2 test periods per year into one table (so a total of 36 separate sources into one).

Result: something isn’t right.

I created the source table in Illuminate and downloaded it to prepare to use JasperSoft Studio Pro to create a BI analysis of performance over time.  That’s when I noticed something weird:

At the same time, I loaded the file into Excel to start a pivot chart, and saw the same thing:

Where is the 2016 academic year for 1st grade?  And what the hell is that 201 doing there?  I clearly had bad data in the source file.

So I went back to Illuminate to look at the custom report that created that data file.  I sorted by academic year to see where that 201 was coming from.

The reason this jumped out at me is because of the use of calculations and translations in the Advanced menu of the report builder.  I needed to somehow grab the grade level of the student and the academic year in order to get some kind of separation in my BI report, but that information isn’t stored within the assessment itself because of how Illuminate manages student visibility to users, depending on permissions and filters.

To get around that, I created the Grade column as a calculation, using the formula SUBSTRING(G FROM 19 FOR 1), which means “take Column G (the one titled Assessment Grade/Administration) and starting at position 19, grab 1 character.”  Because all of the assessments are named according to the same convention, that position would be the grade value (K, 1, 2, etc.).

Then, I created a second calculation column for Academic Year.  That formula was SUBSTRING (G FROM 1 FOR 4), so taking the first four characters of that same column, resulting in values of 2014, 2015, and 2016.

I wasn’t done yet, because those don’t really represent the academic year the way we need it to.  Those values are the start of the school year, when I want the end.  In comes the translation feature in the advanced menu.

I added translations for all three values, making 2014 = 2015, 2015 = 2016, and 2016 = 2017 so that at the very least, when I download the data, it’ll look right and I can do additional manipulation after that.

So when I saw the blank in Grade and 201 in Academic Year, there was something goofy going on with my calculated fields and translation.

Then I looked to the right and saw that they all shared the same assessment source.  2015-16 F&P Grade 1 Spring.  I stared at it for a moment.  Every other assessment grabbed those substring values correctly except for this one.

Which meant that, since the substring calculation for academic year was supposed to grab the first four characters, and the grade field grab the 19th character, but I was only getting the first three of the year and a blank for grade…..

Was there a space in the name of this particular assessment that I can’t see right off the bat?

I went into that assessment and opened up the information window for it, and lo and behold….

The calculations and translations were working correctly.  The calculation for the year was grabbing the first four characters, as designed.  The problem is that the source had an extra character where it shouldn’t have been.  Which caused the 19th character to be the space before the 1.

And since I didn’t build a translation rule for values of ” 201,” it didn’t translate anything for me.

Take out the space, go back to my custom report and…


So even though we don’t want bad data, bad data can still tell a story.


Customizing Windows 10 for Schools

Using Windows 10 1703 Education, I applied a number of customizations, drawn from several expert sources and cobbled together into my own Frankenstein’s-Monster of a deployment, with SCCM 1702.

Strategy and Design

Like with all good projects, we need to figure out what the hell we’re trying to do in the first place before leaping into actually doing it.  So we’re going to move all of our deployed computers throughout the district to Windows 10.



It’s not as simple as “build an image, slap in some software that’s common to everyone, wait for the summer, and hit the uncooled classrooms in the middle of July.”  The default installation of W10 is, at first glance, geared more toward a home-user/personal-experience motif than a professional environment where we take things seriously.

We need to get rid of the superfluous stuff in the Start menu, provide only what’s necessary in the task bar, shut down all the privacy holes, but at the same time still allow staff and students to be able to customize the computing experience (within reason).  We’re not so draconian as to prohibit changing wallpapers and color themes, but we’d also like to provide at least a more organization-centric set of options to begin with.

We’d like to see some simplified task sequences in SCCM, so that if we need to make updates district-wide, we’re not stumbling over the entire console, making repetitive changes to every task sequence.  This means more creative use of task sequence variables and making sure our OUs in AD are cleaner (hah!) and appropriate (hee!).

Setting the Stage

In my SC deployment share (\\[SCCMShare]\sources\packages), I created a folder named “Win10Customizations.”  This will house all of the files, scripts, and folders.

The Details

Start Layout

I wanted to use a partial Start layout so that the commonly used apps in our organization are available immediately, all the superfluous nonsense removed, and still allow users customization ability.  I used my reference machine to build out the Start Menu the way I wanted to see it, then exported its XML with Powershell (see Links & Resources below).

I created a GPO simply titled “Win 10 Start Layout Customization” and made the following changes:

Computer Config > Policies > Administrative Templates > Start Menu and Taskbar set to Enabled and added the path to where my XML would reside on the local drive (in my case, C:\StartLayout\start17.xml)

The second step is to use a Powershell script to create the folder and copy start17.xml to the local drive from our SCCM share.  The script that I used (through lots of trial and error, as I’m quite a PS newbie) is:

$folder = "C:\StartLayout"
$file = "start17.xml"
$fullPath = "$folder\$file"

$source = "\\systemcenter\sources\packages\Win10Customizations\Start Layout\start17.xml"

# Check if folder & file already exist

if (!(Test-Path -path $fullPath)) {
 # File & folder do not exist, so check if the folder by itself exists
 if (!(Test-Path -path $folder)) {
 try {
 New-Item $folder -ItemType Directory
 Catch {
 Write-Warning "Error in creating folder: $error"


# Folder has been created or already existed, so copy the file to it

try {
 Copy-Item -path $source -destination $folder
Catch {
 write-warning "File already exists."

As I’m looking at this code, I realize that I made a few inefficient moves already, but again, I’m still learning!  If you have a cleaner or more robust way of accomplishing the same thing, I’m all ears!  But I think you get the point of what I’m trying to do here.

I placed both the Powershell script and the Start layout XML in the same folder.  Then, I created a no-program package in SCCM for the Start Layout.  This is then added to my task sequence, calling a Powershell script referencing the above:

Next, I wanted to get rid of some of the apps in the Start Menu that we’re not going to need in-district.  To do this, I followed Jörgen Nilsson’s instructions for grabbing a list of apps and then manipulating the script to remove them.

Links & Resources

Behavior Management vs. Technology Management

A question came up on one of my mailing lists about what some school districts do to manage students’ personal devices connecting to district networks.  The inquirer’s administration wanted to shut down all guest wifi access as a way to curb social media bullying and other antics, with the assumption that giving students the alternative of having to use their plan minutes would be sufficient as a deterrent.

This seems like an overreaction instead, as well as relying on external forces to manage internal forces.  This is a behavior management issue, as the technology is a tool being misused.  While reducing or eliminating access to those tools would address the issue, how does that impact the rest of the population?

If a handful of students are misbehaving and misusing the technology made available to all students, and that technology is taken away from all students, what are the consequences?  If guest-access wifi is removed, not only does that impact the entire student body, but also any guest speaker, parents, business contacts, and even visitors from neighboring schools.

Drunk driving could easily be resolved by banning all vehicles and alcohol.  Is that the right solution, though?

If there are flies buzzing around from something that’s spoiled in the kitchen, do we remove the entire kitchen?  Or do we hunt around for the rotten meat that fell behind the stove, which is where the flies have congregated?  Yes, it more work to trace the source and to clean up the meat, but it doesn’t go overboard by demolishing the whole kitchen.

Windows 10 Enterprise vs. Education

Numerous posts and bits of info exist on the difference between Windows 10 Enterprise edition and its counterpart, Education.  In a nutshell, they are both functionally the same product.  The main immediate difference is that Win10 Education, which is only available through an educational institution’s volume licensing agreement with Microsoft, allows an in-place upgrade over Windows 10 Home Edition.

This allows schools to purchase devices with the lower-cost Home edition installed, and upgrade to Enterprise-grade Education version on top of it.

However, I overheard at the Midwest Management Summit this past week (May 15 – 18, 2017) that Microsoft is beginning to hint that educational institutions that have been using Win10 Enterprise should really switch over to Education instead.  I haven’t found any confirmation on this or what’s planned for the future to enforce this shift, however.