Heavy technology, heavy metal
Instead of crawling under a desk with all the nasty dirt and other things that office workers do to their floors, or trying to decipher the faded label from the underside of a laptop, use this command to reveal the serial number:
wmic bios get serialnumber
This would’ve been so much more helpful when I was still a field technician.
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:
case
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:
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:
Illuminate as two main ways to build student groups: choose a common characteristic of those students as the basis or use the results of an assessment to separate out a group of kids. But what if you need to provide an assessment to a group of students who have no common characteristic?
Or what if that common characteristic doesn’t exist in the Illuminate dataset?
For example, a question I was asked was “do kindergarteners who receive a waiver perform differently academically in later years versus those who do not receive a waiver (and therefore, start kindergarten later)?” Another request I received was to be able to generate Illuminate answer sheets for math test-outs being administered to various students of various grades from different buildings, simply because they applied for the test to skip certain math classes.
In both of these cases, Illuminate stored no common characteristic of these students. For the kindergarten waiver question, I could have used an unused field in student demographics to build that common feature (which I’ll discuss in a future post), but because this was an ad hoc report intended to prove a point, it wasn’t worth the extra effort to build automation around it.
The students applying to test out of certain math classes certainly had no common characteristic.
So I used an assessment itself as the tool to establish that commonality to create the student group. Here’s how.
The dummy assessment simply consists of one question. The answer doesn’t matter, as all we’re looking for is a way to capture the specific students we need.
The students we need are identified in our SIS, so the import file can be built out of there. I narrow down the students who had kindergarten waivers, or applied to test out of Math 8 or pre-calculus.
I reduced this CSV to two columns: StudentID and Q1. Q1 (the “answer” to the question) simply needed to contain the same character for each record. This was then uploaded to the dummy assessment, which produced the familiar overview:
Now we know that 403 students in the district received a waiver for kindergarten at some point. The rest of their demographic details are stored in Illuminate already.
Creating a student group from an assessment requires generating a report first. So let’s do that. Clicking on either the donut or the number of students in the summary section will display the list of students:
Click Create Custom Report to create the report consisting of only these students.
From inside that new report, click Advanced then Create Student Group.
And now that student group can be used as any normal student group. That group can be used to generate pre-filled answer sheets for particular assessments, it can be used as the basis of a trend report, or it can even be used as a focus group or intervention group.
What otherwise started as no other commonly bound group of students are now together through a few extra steps.
Two simple cliches that have improved my life over the years:
There’s gotta be an easier way. This means that no matter what problem you’re trying to solve, you’ll probably find yourself deep in the weeds and overgrowth at some point. That’s when you have to stop and say “there’s gotta be an easier way to do this,” because there is. You just have to find it, and finding it means reevaluating what you’re doing and what you’ve done. What you’ve been doing is clearly not the easy way if you’re at this point. Which leads to the next one.
Make the computer do the work. That’s what they’re built for! Automating repetitive tasks, which they’ll do much faster and more accurately anyway. As OSes and software grow and advance, more automation is built in. Learn them and take advantage of them. If you’re doing things manually all the time, you’re doing it wrong.
Dating back to the olden days of earlier versions of SSMS, it likes to remember the login name of the last new user, even if that user has since been deleted. While it’s not a functional deal-breaker in using SSMS, it’s just plain ol’ annoying.
The information is stored in this file (naturally, I’m using Windows 10 here):
%AppData%\Microsoft\SQL Server Management Studio\[version]\SqlStudio.bin
I renamed the existing SqlStudio.bin to SqlStudio.bin.old and the next time I launched SSMS, it recreated a new SqlStudio.bin:
The only downside, of course, is that it blows out everything — saved server connections and every other login detail. But that shouldn’t be a big deal because if you have many servers and many different logins, you should have all of that stored safely in a secure repository elsewhere, right?
I mean, nobody would be silly enough to use the same simple password for “sa” for every SQL server they manage, right? Right?
For the past few months, I’ve been struggling to deploy Sketchup 2017 with SCCM. 2015 seemed to deploy much more smoothly, despite having to implement a few kludgy workarounds. But no matter what I tried, 2017 would fail during a task sequence with the dreaded 0x80004005.
Sketchup’s support site mentioned that 2017 requires .NET Framework 4.5.2, and seemed to imply that 4.5.2 was the only version that it supported. That sounded strange to me, especially since attempting to install 4.5.2 on an updated and patched Win10 introduced even more work than I wanted to put in. And once I ran updates, I had .NET 4.7 anyway.
But they also mentioned needing to install the Visual C++ 2015 Redistributable. My vanilla Win10 install has 2013 installed. OK, so let me give that a try!
I manually installed VC++ 2015, then installed Sketchup. Success!
I wiped the machine and tried to install Sketchup by itself. FAILURE! I forget the exact error message, but it would fail:
For our friendly neighborhood search engine crawlers, the text of the error is:
Module C:\Program Files\SketchUp\SketchUp 2017\ThumbsUp.dll failed to register. HRESULT -214024770. Contact your support personnel.
I grabbed the stand-alone installer for VC++ 2015, packaged that up, then applied it to my task sequence. I also created some basic .CMD files to copy the volume license file and to clean the desktop of the shortcuts that Sketchup created.
In my source folder, I have four files: the Sketchup MSI installer itself, my activation_info.txt, cleanuplinks.cmd, and actinfo.com.
cleanuplinks.cmd:
@echo off del "C:\users\public\Desktop\Layout 2017.lnk" del "C:\users\public\Desktop\SketchUp 2017.lnk" del "C:\users\public\Desktop\Style Builder 2017.lnk" :SUCCESS exit /b 0
actinfo.cmd:
@echo off copy "\\systemcenter\sources\Apps\SketchUp Pro 2017\Files\activation_info.txt" "c:\programdata\sketchup\sketchup 2017"
Naturally, both could be combined into a single batch file, but I kept them separate so that I could activate & deactivate either in my task sequence while I’m still testing.
Finally, how the task sequence is built:
We started deploying Windows 10 on a wider scale to our users in the summer of 2016. However, our SCCM installation was broken at the time, so we weren’t able to provide too much in the ways of updates. We created a from-scratch task sequence of Windows 10 using 1511 as our base, figuring that once that was done ahead of the 2016-17 school year, we could then concentrate on repairing, restoring, or reinstalling SCCM.
Several months later, we reinstalled SCCM and updated to 1702. Now we can get back to work.
I wanted to know which of the machines out there that have Win10 are still on versions older than 1607 so we’d know what we’re dealing with for the forthcoming upgrade project. To do this, I created a query in the Config Mgr console:
Start by clicking Create Query either in the ribbon or right-clicking on “Queries” in the navigation pane or in the results pane. This launches the Create Query Wizard.
Enter a name. I used “Who’s not Win10 1607?” I also limited the search to “All Workstations.” Click “Edit Query Statement…”
Under the “Criteria” tab, I created a new criterion (click the star button).
In Criterion Type, leave the type as “Simple Value” and click “Select…” Choose “Operating System – Version.” For the Operator, choose “is less than” and type “10.0.14393” in “Value.”
Note: if you want to include 1607, change the operator to “less than or equal to” instead.
Also note: the value field is a string value, so we’re lucky in that “10.0.xxxxx” occurs earlier as a string than Windows 7 and Windows 8 versions (which start with 6). If I wanted only Windows 7 or Windows 8 machines, I’d have to get a little fancier with my criteria if I didn’t want to include Windows 10 in the results.
Click OK.
For my query, I only wanted certain fields (columns), not all of them. So I chose Name, Last Logon User Name, Last Logon Time Stamp, Version, Last Boot-up Time, IP Addresses, and Operating System Readiness Branch. You can choose other fields by clicking the “Select” button and going through the desired attributes.
To use this set, you can also use my query in a query language window if you don’t want to go through the click/select process. Click the “Show Query Language” button and copy/paste this:
select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonTimestamp, SMS_G_System_OPERATING_SYSTEM.Version, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_R_System.IPAddresses, SMS_R_System.OSBranch from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version < "10.0.14393" order by SMS_G_System_OPERATING_SYSTEM.Version
Note: Change the < to <= if you want to include 1607.
Once you’re done, run the query. I have 45 devices on versions of Win 10 earlier than 1607:
I can see now that most of my users are on Current Branch. I have three on 1507 (support for which just ended), and given who the users are, they need updates (updating won’t break what they do). Everyone else is on 1511.
When I change my query to <= 10.0.14393 instead, I get over 150 results, so that means most of my deployments are on 1607 (yay!).
Finally, a side benefit I discovered in this query is that I could find a few incorrectly named computers that have Windows 10 deployed. Our naming convention includes the form factor of a device as well as its OS version so that we can see what we’re dealing with at a quick glance without having to look it up. Within the first five entries, I see three computers that need to be renamed (which is a help desk ticket for the technicians responsible for those areas).
An article about password security, aimed at the average person or IT people who work with average people and need another way to explain it.
Social engineering seems to be the easiest way to grab a user’s password, and despite suspicion on the part of IT staff, the average user gets roped in pretty quickly. Phishing attempts are getting bolder and more sophisticated, and objectively speaking, I have to applaud some of the efforts because they’re pretty good.
Not much can be done to secure an account if the account holder willingly gives it up. But mitigating damage from brute-force attacks and even “shoulder surfing” can be much easier.
Use a phrase or sentence.
Mix in a few capitalizations, maybe even skip a character. Try it out here:
https://password.kaspersky.com/
In a nutshell, the more characters there are to try to figure out, the harder it gets mathematically to solve. For illustration’s sake, with a single-digit number, you have a 1 in 10 chance of getting it right. For a human, it’s pretty simple. For a computer, it’s instantaneous.
Add another digit, and the odds increase to 1 in 100.
For a single letter of the English alphabet, the chance of “cracking the code” is 1 in 26. Add another letter (where repetition is allowed) and the odds are 1 in 676 (26 x 26).
Still pretty easy for a computer.
While words of multiple characters and numbers are more complex, computers are able to use dictionaries and heuristics to figure out a password (“heuristic” being essentially the practice of starting with “most likely” then working outward). Add in behavioral analysis (especially through mining big data acquired via social media), and a computer can easily figure out the most popular passwords for a school teacher at this time of year:
Seriously. We went through a lot several years ago to get our teachers to stop doing this.
Conversely, a password like S&4u_sO9%8sS8^2HhYvoO is nearly impossible to crack….but also nearly impossible to remember. Plus, not every system out in the wild can handle all of these requirements; some systems can’t handle special characters, others can’t handle certain special characters, and I still see systems that demand a maximum of 8 characters (in 2017!!!).
A comic to explain more technically
The balance between a password complex enough to make it difficult for computers to crack but simple enough for a human to remember is to use a sentence or phrase. Artificial intelligence is not yet at that point where a system can guess the meaning and impact of certain terms.
Try “My Cat is 17 Years Old” (author’s note: my cat is not 17 years old…I don’t even have a cat……… or do I?).
This isn’t to say that a computer won’t find out your password within seconds or minutes. It could be very, very lucky. But the chance of that happening is incredibly slim. Much slimmer than if your password was cat17. But not as slim as King Illegal Forest to Pig Wild Kill In It A Is.
Preface: use LENGTH for MySQL, LEN for Transact-SQL (SQL Server). I copied the following code from one of my MySQL instances. Couldn’t figure out initially why LEN wasn’t working, before I remembered that I was still thinking in Transact-SQL instead of MySQL. Yay for industry standards! </sarcasm>
SELECT concat(upper(left(first_name,1)), lower(right(first_name, length(first_name) - 1))) as [whateverAlias], concat(upper(left(last_name,1)), lower(right(last_name, length(last_name)-1))) as [whateverAlias] FROM [table]
upper(left(first_name,1)) grabs the first character of your first_name field and changes it to upper case.
lower(right(first_name, length(first_name)-1)): Taking the total length of the first_name field, then subtracting 1, go from the right that many characters, since we’re skipping the first character. Change that to lower case. Remember to use LEN instead of LENGTH in Transact-SQL
concat (….) as whatever: concatenates the upper case letter and lower case remainder into a new whatever value that’s more easy to remember than upper(left(first……).
With the increasing deployment of Chromebooks in a school environment, there will inevitably be an increase in damage to the LCD screen (laptops as well, of course). But when one breaks, it’s not always necessary to rely on an external service provider who might quote you $150 or more to replace the LCD.
You might as well just buy a new Chromebook for that kind of exorbitant price!
I gained a lot of experience replacing Chromebook screens when one particular school of mine experienced a 45% breakage rate (combination of flimsy construction and carelessness on the part of the student body). When a colleague received a quote from a company for $160 per unit, I laughed heartily, then made this video to show exactly how quick and easy it is to replace a screen.
https://youtu.be/Wog5XL7MUfw
A few caveats, of course: