Tiny Deathstars of Foulness

A SQL JOIN clause combines rows from tables, based on a field shared between them (often a joining or ID field). There are four types of JOINs:

  • INNER JOIN: Show rows when there’s a match in BOTH tables
  • LEFT JOIN: Show rows in the left table with the rows that match up from the right table
  • RIGHT JOIN: Show rows in the right table with rows that match up in the left table
  • FULL JOIN: Show rows with a match in at least one table

In this article, we’ll use the same “Customers” table from our first articles:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

Let’s look at a selection from the “IPs” table:

101 1
102 2
103 3
104 4
105 5

In the above two tables, each has their own ID field, with ID being the Customers table ID and IPID being the ID of an IP address. We have a series of IPs for SiteIDs 3-5, with 104 and 105 being the same IP address. Note that the IPs are fake. I made ’em up. Sorry about not using production data, but it didn’t make that much sense…

Here, we’ll use a SELECT and identify the fields, defining the table followed by a dot and then the field name as the data to load into memory. We’ll pull that from our initial table and use an INNER JOIN with the second, using an ON to define which field is used to map out results:

INNER JOIN Customers
ON IPs.IPID=Customers.ID;

We’ll then get something like this, with the three fields that we defined in the SELECT as the columns to display in the output:

101 1
102 2
103 3
104 4
105 5

This is a pretty basic JOIN. But shows the power we have. We can then use a LEFT JOIN alongside an ORDER BY:

SELECT Customers.Site, IPs.IP
FROM Customers
ON Customers.ID=IPs.IPID
ORDER BY Customers.Site;

The output would then appear as follows:

Site IP

The different types of JOINs give you the ability to string together some pretty awesome logic, to locate assets in multiple tables and display them. For example, let's say I also had a Registrar table. I could then also use that ID attribute from our original database as a key to access data connected to our initial table from other tables. Overall, simple and straight forward means of querying data and displaying those results, or leveraging them into a variable or file in order to make use of them elsewhere (e.g. with a script).

February 12th, 2016

Posted In: Uncategorized

Tags: , , , , , , ,

Leave a Comment

The directory services options in OS X has quietly been going through some slow changes over the past couple of years. Many of the tools we use to manage accounts look similar on the outside but sometimes work a little differently under the hood. Account information is still stored in the /var/db/dslocal/nodes directory. Here, the local directory service pulls files from within directories recursively when accountsd loads. You can still create a second instance of the local directory service by copying the Default directory. For example, here we’ll copy the Default directory node to a directory node called NEW:

sudo cp -prnv /var/db/dslocal/nodes/Default /var/db/dslocal/nodes/NEW

If you killall accountsd then wait (this is slower than doing a killall of DirectoryService was), you’ll then see and be able to use this new directory node:

sudo killall accountsd

This is one way to go about forklifting large collections of accounts from one system to another. The dsmemberutil account can still be used to obtain certain information from accounts. For example, you can check group membership by feeding in a uid with the -u option (here using the uid of 509) and a gid with the -g (here a gid of 10) option:

dsmemberutil checkmembership -u 509 -g 10

Each account still has a uuid. This can be obtained with -u for a user or -g for a group (ids):

dsmemberutil getuuid -u 509

And, you can use dsmemberutil to flush the directory services cache resolver, using the flushcache verb:

dsmemberutil flushcache

The files that comprise accounts can also be viewed and changed manually. Here, we’re going to just look at an account called charles:

sudo defaults read /var/db/dslocal/nodes/Default/users/charles.plist

If we used a tool like defaults, plistbuddy or plutil to manually augment one of these accounts, we’d also need to kill accountsd as we did earlier.

October 3rd, 2015

Posted In: Uncategorized

There’s lots for businesses to be excited about with iOS 9. From easier multitasking on iPad, commuting with Transit view in Maps, enhanced notes, to an all new News app—there are lots of ways that business users can make the most of their iPads and iPhones by migrating to iOS 9. We encourage users to take advantage of iOS 9. Their devices will remain fully compatible with Bushel.

Read More About Bushel’s Same Day Support for iOS 9 On The Bushel Blog

September 17th, 2015

Posted In: Uncategorized

September 16th, 2015

Posted In: Uncategorized

There are a number of tools that you can use to encrypt a Mac. Many of these cost around $100 per year, per system. And these days, most of the tools for the Mac simply use the built-in options in OS X, which leverage a technology called FileVault. These options include enabling the encryption process, defining a place to put keys to decrypt a drive if you need them, and configuring basic options for the keys.

Read More About The Cost Of Full Disk Encryption On The Bushel Blog

September 13th, 2015

Posted In: Uncategorized

“But what if…”

It’s one of the most common things you hear when you are trying to bring an innovation to the table. It could be as known a quantity as replacing an ERP system. The innovation could be unique, compelling and clearly alter the trajectory of the organization in so many ways. However, there are always going to be reasons not to do it.

I sat in on a panel at a conference last year and discussed how to get your organization to approve the projects that you as the CTO/Director of IT/IT Manager/Field Tech wanted. The ideas here always start with cost savings. To calculate the financial savings of a change you can just look at factors such as how much payroll will be saved or how much less does it cost to make widgets. And while it’s great to be efficient, you can only get so efficient. And saving people money doesn’t really get you the same level of recognition/pay/whatever motivates you as something far more earth shattering.

Innovation has always been a core part of technology. Perhaps you end up blowing up into a whole new field. Or maybe you just incrementally innovate the field you’re in. But innovation keeps us pushing the envelope forward. But as with all progress, it comes at a cost. When we’re looking at bringing a new innovation into any environment, we must weigh the cost of that innovation against the rewards to be gained. Apple is a great organization to look to for innovation. And the cost to their community continues to be minimal, because they break it to us in parts. As with a Facebook timeline, the constant change can be frustrating but when they’re incremental changes, they’re easier to accept.

September 8th, 2015

Posted In: Uncategorized

I wore a FuelBand for around two years. I loved my FuelBand. I thought perhaps I would never part ways with it. And then I got my Apple Watch. And when I switched the FuelBand out with my Apple Watch I realized something kinda’ rad. I didn’t loose anything. The reason is that the Nike Fuel app now supports “Phone Tracking” which uses the sensors in the phone instead of the sensors in the FuelBand to track your movements. Obviously you’ll need your phone to use it.

So what did the FuelBand do and what does the watch do? Basically, the FuelBand tracked steps, workouts and other typical activity and then converted steps and active calorie expenditure into a number that took as much information as Nike had available to try and provide you a score on how you did. The FuelBands strengths were that it was a simple bangle that synchronized to your phone and lasted a good week at times between charges. The sync was fast, sometimes happened in the background (sometimes not-so-much) and Nike understands competing, so a huge strength of the solution was the resultant Trophies (gamification, yo) and prodding to always do better.

When I decided to write an article comparing these devices, I didn’t realize I’d be getting rid of my FuelBand. But I clearly don’t need three wearables on my wrists (I still wear my FitBit). But I slowly came to realize more and more that if Nike had wanted to remain in the wearables market, they would have needed to really redesign and make serious improvements to the FuelBand two years ago. The original product was awesome for the time, but there’s so much more that can be done today. This is all just a long way of say that the purpose of this article changed a bit to if you’re thinking about dumping a FuelBand and getting an AppleWatch, here are some things to know:

  • The AppleWatch battery needs to be charged every day.
  • The Apple Watch doesn’t have as many, or as cool of trophies (called Achievements).
  • The Apple Watch monitors your heart rate.
  • The FuelBand isn’t necessary for the Nike Fuel, Nike Training Club or Nike Running apps as they can use your phone for motion tracking.
  • There is a Nike Running app for the Apple Watch.
  • There are apps for any of the little things you might want the Apple Watch to do that it doesn’t do out of the box, such as tracking your food intake (calories out are one thing of course but for many, calories in matter more).
  • The Apple Watch can be used to track exercise without the iPhone present. You can also listen to music from the watch without your phone, if you have a Bluetooth headset.

Everything else is way cooler and better and more awesome on the Apple Watch.


August 8th, 2015

Posted In: Uncategorized

More and more mobile devices are making their way into the workplace. In response to this influx of technology assets, companies keep track of their hardware in a number of different ways. Some may file purchase receipts while others log serial numbers. Many companies take organization a step further and assign some sort of standardized asset tag to company-owned devices. This makes keeping everything in order much easier. Of course, then the challenge becomes keeping track of this centralized list. For these kinds of chores, every company has a slightly different process.

Read More About Bushel’s New Asset Tags Feature


August 1st, 2015

Posted In: Uncategorized

I don’t get to spend that much time reading other sites. But when I do, here are a few pages/people I might check out. I thought this might help the Younglings of the MacAdmin community. If you have a site that you’d like added to the list, let me know. And no, I won’t judge you if you nominate your own site. In fact, I might learn something new! The final list will be stored at :)

July 17th, 2015

Posted In: Uncategorized

Feels gooood


June 7th, 2015

Posted In: Uncategorized

Next Page »