Contents

How to Find a Mac's Manufacture Date Using Osquery

Creating virtual lookup tables in SQLite

A screenshot of an Intercom support chat. A customer request reads: "Is it possible to view manufacture dates for workstations? I believe the dates are encoded in the serial number of the machine?

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 pre-2021 Mac devices simultaneously using osquery without any extensions or additional APIs.

TL;DR: 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 a pre-2021, 12 character, Apple serial number to better understand the task at hand.

An information graphic breaking down the sections of a 12 character Mac serial number. The first 3 characters designate the manufacturing location. The 4th character is the manufacture year (& year half). The 5th character is the manufacturing week. The 6th through 8th characters are a unique identifier. The final 9th through 12th characters are the Mac model number.

*Up until the time of the original publication of this blog entry, Apple 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.

For devices manufactured prior to the second half of 2021 (Apple MacBook Pros and iMacs with Apple M series CPU’s), 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:

+-------------+-----------+-----------+-------------+------+
| character_4 | year      | year_half | character_5 | week |
+-------------+-----------+-----------+-------------+------+
| C           | 2010/2020 | 1         | 1           | 1    |
| D           | 2010/2020 | 2         | 2           | 2    |
| F           | 2011/2021 | 1         | 3           | 3    |
| G           | 2011/2021 | 2         | 4           | 4    |
| H           | 2012      | 1         | 5           | 5    |
| J           | 2012      | 2         | 6           | 6    |
| K           | 2013      | 1         | 7           | 7    |
| L           | 2013      | 2         | 8           | 8    |
| M           | 2014      | 1         | 9           | 9    |
| N           | 2014      | 2         | C           | 10   |
| P           | 2015      | 1         | D           | 11   |
| Q           | 2015      | 2         | F           | 12   |
| R           | 2016      | 1         | G           | 13   |
| S           | 2016      | 2         | H           | 14   |
| T           | 2017      | 1         | J           | 15   |
| V           | 2017      | 2         | K           | 16   |
| W           | 2018      | 1         | M           | 17   |
| X           | 2018      | 2         | N           | 18   |
| Y           | 2019      | 1         | L           | 19   |
| Z           | 2019      | 2         | P           | 20   |
|             |           |           | Q           | 21   |
|             |           |           | R           | 22   |
|             |           |           | T           | 23   |
|             |           |           | V           | 24   |
|             |           |           | W           | 25   |
|             |           |           | X           | 26   |
|             |           |           | Y           | 27   |
+-------------+-----------+-----------+-------------+------+

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

SELECT hardware_serial FROM system_info;
+-----------------+
| hardware_serial |
+-----------------+
| C02VT3WTHX87    |
+-----------------+

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:

A screenshot of the "About This Mac" screen with the serial number portion emphasized

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:

V = 2017 (Second Half)

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

T = 23rd Week (Of Second Half)

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:

osqueryi

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

Extracting Characters from a String Using SUBSTR

An information graphic showing the 4th and 5th characters of a 12 character Mac serial number represent the manufacture year (& year half) and date.

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:

SELECT SUBSTR({{example_string}},{{starting_character}},{{number of characters to extract}})

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:

SELECT SUBSTR('my_test_value',9,5);

+-----------------------------+
| SUBSTR('my_test_value',9,5) |
+-----------------------------+
| value                       |
+-----------------------------+

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:

SELECT SUBSTR(hardware_serial,4,1) AS char_4 FROM system_info;

+--------+
| char_4 |
+--------+
| V      |
+--------+

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

SELECT
  SUBSTR(hardware_serial,4,1) AS char_4,
  SUBSTR(hardware_serial,5,1) AS char_5
FROM system_info;

+--------+--------+
| char_4 | char_5 |
+--------+--------+
| V      | T      |
+--------+--------+

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:

WITH
serial_partial AS (
  SELECT
    SUBSTR(hardware_serial,4,1) AS char_4,
    SUBSTR(hardware_serial,5,1) AS char_5
  FROM system_info
)
SELECT * FROM serial_partial;

+--------+--------+
| char_4 | char_5 |
+--------+--------+
| V      | T      |
+--------+--------+

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:

WITH
my_lookup_table(letter,number) AS (
  VALUES
    ('A',1),
    ('B',2),
    ('C',3)
)
SELECT * FROM my_lookup_table;

+--------+--------+
| letter | number |
+--------+--------+
| A      | 1      |
| B      | 2      |
| C      | 3      |
+--------+--------+

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:

WITH
mac_manufacture_year(char_4,year,offset) AS (
VALUES
('C','2020',0),('D','2020',26),('F','2021',0),('G','2021',26),('H','2022',0),('J','2022',26),('K','2013',0),('L','2013',26),('M','2014',0),('N','2014',26),('P','2015',0),('Q','2015',26),('R','2016',0),('S','2016',26),('T','2017',0),('V','2017',26),('W','2018',0),('X','2018',26),('Y','2019',0),('Z','2019',26)
)
SELECT * FROM mac_manufacture_year;

+--------+------+--------+
| char_4 | year | offset |
+--------+------+--------+
| C      | 2020 | 0      |
| D      | 2020 | 26     |
| F      | 2021 | 0      |
| G      | 2021 | 26     |
| H      | 2022 | 0      |
| J      | 2022 | 26     |
| K      | 2013 | 0      |
| L      | 2013 | 26     |
| M      | 2014 | 0      |
| N      | 2014 | 26     |
| P      | 2015 | 0      |
| Q      | 2015 | 26     |
| R      | 2016 | 0      |
| S      | 2016 | 26     |
| T      | 2017 | 0      |
| V      | 2017 | 26     |
| W      | 2018 | 0      |
| X      | 2018 | 26     |
| Y      | 2019 | 0      |
| Z      | 2019 | 26     |
+--------+------+--------+

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:

WITH
mac_manufacture_week(char_5,week) AS (
VALUES
('1',1),('2',2),('3',3),('4',4),('5',5),('6',6),('7',7),('8',8),('9',9),('C',10),('D',11),('F',12),('G',13),('H',14),('J',15),('K',16),('M',17),('N',18),('L',19),('P',20),('Q',21),('R',22),('T',23),('V',24),('W',25),('X',26),('Y',27)
)
SELECT * FROM mac_manufacture_week;

+--------+------+
| char_5 | week |
+--------+------+
| 1      | 1    |
| 2      | 2    |
| 3      | 3    |
| 4      | 4    |
| 5      | 5    |
| 6      | 6    |
| 7      | 7    |
| 8      | 8    |
| 9      | 9    |
| C      | 10   |
| D      | 11   |
| F      | 12   |
| G      | 13   |
| H      | 14   |
| J      | 15   |
| K      | 16   |
| M      | 17   |
| N      | 18   |
| L      | 19   |
| P      | 20   |
| Q      | 21   |
| R      | 22   |
| T      | 23   |
| V      | 24   |
| W      | 25   |
| X      | 26   |
| Y      | 27   |
+--------+------+

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:

WITH
serial_partial AS (
  SELECT
    SUBSTR(hardware_serial,4,1) AS char_4,
    SUBSTR(hardware_serial,5,1) AS char_5
  FROM system_info
),
mac_manufacture_year(char_4,year,offset) AS (
VALUES
('C','2020',0),('D','2020',26),('F','2021',0),('G','2021',26),('H','2022',0),('J','2022',26),('K','2013',0),('L','2013',26),('M','2014',0),('N','2014',26),('P','2015',0),('Q','2015',26),('R','2016',0),('S','2016',26),('T','2017',0),('V','2017',26),('W','2018',0),('X','2018',26),('Y','2019',0),('Z','2019',26)
),
mac_manufacture_week(char_5,week) AS (
VALUES
('1',1),('2',2),('3',3),('4',4),('5',5),('6',6),('7',7),('8',8),('9',9),('C',10),('D',11),('F',12),('G',13),('H',14),('J',15),('K',16),('M',17),('N',18),('L',19),('P',20),('Q',21),('R',22),('T',23),('V',24),('W',25),('X',26),('Y',27)
)
SELECT * FROM serial_partial;

+--------+--------+
| char_4 | char_5 |
+--------+--------+
| V      | T      |
+--------+--------+

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

A flow-chart showing how three tables are JOINED. The three tables and their respective join columns are mac_manufacturing_year:char_4 <-> serial_partial:char_4 | serial_partial:char_5 <-> mac_manufacturing_year:char_5

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:

WITH
serial_partial AS (
  SELECT
    SUBSTR(hardware_serial,4,1) AS char_4,
    SUBSTR(hardware_serial,5,1) AS char_5
  FROM system_info
),
mac_manufacture_year(char_4,year,offset) AS (
VALUES
('C','2020',0),('D','2020',26),('F','2021',0),('G','2021',26),('H','2022',0),('J','2022',26),('K','2013',0),('L','2013',26),('M','2014',0),('N','2014',26),('P','2015',0),('Q','2015',26),('R','2016',0),('S','2016',26),('T','2017',0),('V','2017',26),('W','2018',0),('X','2018',26),('Y','2019',0),('Z','2019',26)
),
mac_manufacture_week(char_5,week) AS (
VALUES
('1',1),('2',2),('3',3),('4',4),('5',5),('6',6),('7',7),('8',8),('9',9),('C',10),('D',11),('F',12),('G',13),('H',14),('J',15),('K',16),('M',17),('N',18),('L',19),('P',20),('Q',21),('R',22),('T',23),('V',24),('W',25),('X',26),('Y',27)
)
SELECT *
FROM serial_partial
JOIN mac_manufacture_year USING(char_4)
JOIN mac_manufacture_week USING(char_5);

+--------+--------+------+--------+------+
| char_4 | char_5 | year | offset | week |
+--------+--------+------+--------+------+
| V      | T      | 2017 | 26     | 23   |
+--------+--------+------+--------+------+

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:

WITH
serial_partial AS (
  SELECT
    SUBSTR(hardware_serial,4,1) AS char_4,
    SUBSTR(hardware_serial,5,1) AS char_5
  FROM system_info
),
mac_manufacture_year(char_4,year,offset) AS (
VALUES
('C','2020',0),('D','2020',26),('F','2021',0),('G','2021',26),('H','2022',0),('J','2022',26),('K','2013',0),('L','2013',26),('M','2014',0),('N','2014',26),('P','2015',0),('Q','2015',26),('R','2016',0),('S','2016',26),('T','2017',0),('V','2017',26),('W','2018',0),('X','2018',26),('Y','2019',0),('Z','2019',26)
),
mac_manufacture_week(char_5,week) AS (
VALUES
('1',1),('2',2),('3',3),('4',4),('5',5),('6',6),('7',7),('8',8),('9',9),('C',10),('D',11),('F',12),('G',13),('H',14),('J',15),('K',16),('M',17),('N',18),('L',19),('P',20),('Q',21),('R',22),('T',23),('V',24),('W',25),('X',26),('Y',27)
)
SELECT
year,
(week + offset) AS week
FROM serial_partial
JOIN mac_manufacture_year USING(char_4)
JOIN mac_manufacture_week USING(char_5);

+------+------+
| year | week |
+------+------+
| 2017 | 49   |
+------+------+

🥳 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:

WITH
sample_data (year,week) AS (
VALUES
('2017',49))
SELECT * FROM sample_data;

+------+------+
| year | week |
+------+------+
| 2017 | 49   |
+------+------+

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:

WITH
sample_data (year,week) AS (
VALUES
('2017',49))
SELECT year || '-01-01' AS year_start FROM sample_data;

+------------+
| year_start |
+------------+
| 2017-01-01 |
+------------+

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

SELECT DATE('now');

+-------------+
| DATE('now') |
+-------------+
| 2021-09-22  |
+-------------+

SELECT DATE('now','+10 days');

+------------------------+
| DATE('now','+10 days') |
+------------------------+
| 2021-10-02             |
+------------------------+

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:

WITH
sample_data (year,week) AS (
VALUES
('2017',49)
)
SELECT
  year || '-01-01' AS year_start,
  ('+' || (week * 7) || ' days') AS offset_days
FROM sample_data;

+------------+-------------+
| year_start | offset_days |
+------------+-------------+
| 2017-01-01 | +343 days   |
+------------+-------------+

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

WITH
sample_data (year,week) AS (
VALUES
('2017',49)
),
date_modified AS (
  SELECT
    year || '-01-01' AS year_start,
    ('+' || (week * 7) || ' days') AS offset_days
  FROM sample_data
)
SELECT *,
  date(year_start,offset_days) AS manufacture_date
FROM date_modified;

+------------+-------------+------------------+
| year_start | offset_days | manufacture_date |
+------------+-------------+------------------+
| 2017-01-01 | +343 days   | 2017-12-10       |
+------------+-------------+------------------+

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

WITH
serial_partial AS (
  SELECT
    SUBSTR(hardware_serial,4,1) AS char_4,
    SUBSTR(hardware_serial,5,1) AS char_5
  FROM system_info
),
mac_manufacture_year(char_4,year,offset) AS (
VALUES
('C','2020',0),('D','2020',26),('F','2021',0),('G','2021',26),('H','2022',0),('J','2022',26),('K','2013',0),('L','2013',26),('M','2014',0),('N','2014',26),('P','2015',0),('Q','2015',26),('R','2016',0),('S','2016',26),('T','2017',0),('V','2017',26),('W','2018',0),('X','2018',26),('Y','2019',0),('Z','2019',26)
),
mac_manufacture_week(char_5,week) AS (
VALUES
('1',1),('2',2),('3',3),('4',4),('5',5),('6',6),('7',7),('8',8),('9',9),('C',10),('D',11),('F',12),('G',13),('H',14),('J',15),('K',16),('M',17),('N',18),('L',19),('P',20),('Q',21),('R',22),('T',23),('V',24),('W',25),('X',26),('Y',27)
),
merge_data AS (
  SELECT
    year,
    (week + offset) AS week
  FROM serial_partial
  JOIN mac_manufacture_year USING(char_4)
  JOIN mac_manufacture_week USING(char_5)
),
date_modified AS (
  SELECT
    year || '-01-01' AS year_start,
    ('+' || (week * 7) || ' days') AS offset_days
  FROM merge_data
)
SELECT *,
  date(year_start,offset_days) AS manufacture_date
FROM date_modified;

+------------+-------------+------------------+
| year_start | offset_days | manufacture_date |
+------------+-------------+------------------+
| 2017-01-01 | +343 days   | 2017-12-10       |
+------------+-------------+------------------+

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

A screenshot of Google Sheets showing the Mac Serial Decoder A zoomed screenshot of Google Sheets showing the Mac Serial Decoder showing the File menu open with the option "Make a copy" is selected

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!