Open in app

Sign in

Write

Sign in

Determining Mac Hardware Manufacture Date Using Osquery

Creating virtual lookup tables in SQLite

Fritz Ifert-Miller
Kolide
14 min readSep 22, 2021

--

Dozens of websites exist to retrieve the manufacturing date for an Apple device, but many use external API calls and almost all require you to submit only one serial at a time.

Thankfully, you can quickly return the estimated manufacture date of ALL your Mac devices simultaneously using osquery without any extensions or additional APIs.

TLDR: I try to write articles so that any level background of osquery/SQLite can follow along; if you just want the finished query, you can jump to the end of this post by clicking here: Skip to the End!

In this post, we will go over the following osquery SQLite techniques:

  • Using common-table expressions (CTEs) to compartmentalize query construction
  • Using SUBSTR to return only segments of a larger string
  • Using the ‘||’ double-pipe operator to concatenate two or more strings
  • Manipulating date-based data using DATE

But first, let’s take a brief look at the anatomy of an Apple serial number to better understand the task at hand.

How Apple historically* encoded manufacturing dates

Up until the time of this blog entry (2021–09–22), Apple has encoded various details about devices into their hardware serial number. An individual with the serial could determine things such as the hardware model, the manufacturing location, the manufacture date, and even the device enclosure color.

The asterisk above is due to the fact that in 2020, Apple announced its intent to do away with procedurally generated serials in favor of random serials. As a result the content of this blog article may be deprecated with regards to future Macs.

In the meantime, this approach still works, and serves as a useful example for how to perform string and date manipulations as well as how to utilize external data through the creation of temporary lookup tables.

To understand the rest of this post it is important to know that the manufacturing date for an Apple device is encoded in the 4th and 5th characters of a serial. For the remainder of this post we will be using my iMac Pro as the example device.

The lookup table below displays the mapping of various years and weeks to their respective characters:

Using osquery we can easily find the serial of my device:

If you are following along and do not have osquery setup yet, you can find your own serial by opening the About This Mac dialog from the Apple menu:

As we mentioned earlier, the 4th character represents the year in which the device was manufactured as well as which half of that year, so in this example:

The 5th character represents the week in which the device was manufactured, so in this example:

Here is the first important note to ensure your success. The fact that we are talking about the 23rd week of the second half of the year means that we must account for the offset. The first half of the year was 26 weeks, so we will need to add 26 to 23 to get the total week value (49).

So now we know that my iMac Pro was manufactured sometime in the 49th week of the year 2017.

Let’s examine, stepwise, how we can return that same information (across all of our Macs) using osquery.

Using Osquery to Programmatically Return Manufacture Date

While osquery is incredibly powerful and has access to an enormous breadth of information, that data can sometimes require massaging and manipulation to answer the question that you are after. I will describe in detail the techniques used to arrive at the final query in the sections below.

To follow along at home you will need a Mac computer with osquery installed.

If you are a Kolide customer you can use Live Query in lieu of osqueryi in your Terminal.

To install osquery download the official installer from: https://osquery.io/downloads/official/

With osquery installed you will need to open a Terminal window and type:

This will launch an interactive osquery session which you can use to test the queries demonstrated below.

Extracting characters from a string using SUBSTR

The most basic component required for this exercise is the ability to retrieve only the part of the serial which we are interested in. Using the SUBSTR (substring) function we can extract characters from a string based on their position in the string.

The basic syntax for SUBSTR is as follows:

So if I take the example string 'my_test_value' and wish to return just the word 'value', I tell SUBSTR to start at the 9th character (v) and return the next 5 characters:

For our serial number → manufacture date task, we need the 4th and 5th characters. The serial number is returned by the system_info table as the column hardware_serial. Let’s start by extracting the 4th character first:

We can now repeat that same approach to get the 5th character of our serial:

At this stage I like to start creating temporary tables using common-table expressions (CTEs) to manage the various parts of my query. While they sound complex, they are actually very intuitive once you’ve seen them in action.

Using Common Table Expressions to reduce complex queries into steps

Taking the previous example, we can wrap the whole query in a WITH statement to create our first temporary table:

Using this CTE approach, we can now SELECT from output of our earlier query as if it were its own table!

While this may seem trivial or redundant now, CTEs can be an invaluable tool for managing the composition of large queries and compartmentalizing your approach; but that’s not all — they also have a super-power!

CTEs can help you create new static sources of data to JOIN against!

Creating a lookup table using Common Table Expressions

The utility of CTEs are tremendous when it comes to seeding our query with static data which is otherwise inaccessible to osquery.

At the beginning of this blog-post I referenced a spreadsheet of characters and their mappings to manufacturing year and week. This data exists on the internet, and in this blog-post, but it is not something that exists on the device. We need to seed this data into a temporary lookup table and thankfully, we can do just that using a CTE.

A small example of this technique can be seen below:

The basic format is as follows,

  • First, we define the name of the temporary table: my_lookup_table
  • Next, in parentheses, we supply the names of our columns: (letter,number)
  • Finally, we provide a comma-separated list of row data: ('A',1),('B',2),

We will apply this same approach to the Manufacture Date lookup table contained at the start of this blog-post:

As you can see, I have changed the data somewhat, instead of mentioning which half of the year, I instead supplied an offset value (26 or 0). This will make my calculation of the manufacture_week easier in the next step.

I will also create a lookup table for the weeks using the same approach as above:

Multiple CTEs in one query

The other primary utility I personally have for CTEs is keeping my work compartmentalized. You can create as many temporary tables as you like using CTES by separating each temporary table with a comma. You only need to use WITH once, at the start of your query, when defining your first temporary table.

Let’s combine all 3 of the temporary tables we have created so far:

We can now query from any of those temporary tables and only those referenced in your final query will be used.

Let’s join our 3 tables together to start assembling our data. Because our tables share common column names (char_4 and char_5) we can JOIN them with the USING(column_name) shorthand instead of JOIN ON :

Awesome!

We’re really getting somewhere! Already, we can see that the device is correctly identified as being manufactured in the year 2017. Let’s do some simple math operations to return the appropriate number for week by adding our offset column to our week column:

🥳 Even better!

We can see this device was manufactured in the 49th week of the year 2017.

Let’s take this one step further though. Normal people don’t talk about dates in terms of their week number. Let’s return a traditional datestamp by manipulating this data further using the DATE function of SQLite.

Calculating a date from a week or day number

For this step I will focus on just our year and week data, and create a temporary lookup table using our previously returned values, to reduce the complexity of our rapidly growing query:

In order for us to manipulate the date we need to provide a suitable complete date formatted string (not only a year). Let’s turn our existing year into a valid date by transforming it into the YYYY-MM-DD format:

This means we will need to modify our year output by appending '-01-01' to the string using the || (concatenate) operator:

The DATE function allows us to pass modifiers to offset the date like so:

Using the modifier approach demonstrated above, we can add to our year_start by multiplying our weeks into days and concatenating a + and the string days to create an offset that can be interpreted by the date function:

We can thencombine these two columns using DATE to calculate the day:

We can now combine this CTE with the query we wrote earlier to generate the manufacture data from our Mac’s serial number.

The Final Query

And there you have it!

Our complete manufacturing date query. Use it while it lasts! It may not be long until this approach is rendered ineffective by Apple, but for the time-being you can utilize it to track down your End-of-Life hardware and justify its retirement.

But wait there’s more!

As an added bonus, I have created the same transformation and lookup tables in Google Sheets if you don’t have osquery or want to work with an existing spreadsheet of serials: Google Sheets / Mac Serial Decoder

Simply make a copy of the Sheet linked above, and copy/paste your values into the first column, then drag the other columns down as necessary and you can check the manufacture date of as many devices as you want simultaneously!

—

Further Reading

Interested in more how-to guides on osquery? I recommend reading some of my other posts:

Kolide is hiring!

Want to write cool queries like this and get paid? Kolide just raised 17MM in Series B funding and we’re hiring! HMU in the comments or visit our recruiting link below:

kolide.com/careers

About the Author:

Fritz Ifert-Miller is the UX designer for Kolide, an endpoint visibility platform which prioritizes the tenets of Honest Security. Fritz is responsible for Kolide’s design, user experience, and researching innovative ways to collect better ground-truth from devices using Osquery.

Osquery
Sysadmin
Macos
It
Sql

--

--

Fritz Ifert-Miller
Kolide

Written by Fritz Ifert-Miller

45 Followers

Fritz is the UX Designer at Kolide. Prior to Kolide, he worked at BIDMC as a neurology researcher studying Transcranial Magnetic Stimulation (TMS) of the brain.

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams