How to Find a Mac's Manufacture Date Using Osquery (2024)

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 oneserial at a time.

Thankfully, you can quickly return the estimated manufacture date of all yourpre-2021 Mac devices simultaneously using osquery without any extensions oradditional APIs.

TL;DR: I try to write articles so that any level background ofosquery/SQLite can follow along; if you just want the finished query, you canjump 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.

*Up until the time of the original publication of this blog entry, Apple encodedvarious details about devices into their hardware serial number. An individualwith the serial could determine things such as the hardware model, themanufacturing location, the manufacture date, and even the device enclosurecolor.

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

For devices manufactured prior to the second half of 2021 (Apple MacBook Prosand iMacs with Apple M series CPU’s), this approach still works, and serves as auseful example for how to perform string and date manipulations as well as howto utilize external data through the creation of temporary lookup tables.

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

The lookup table below displays the mapping of various years and weeks totheir 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 yourown serial by opening the About This Mac dialog from the Apple menu:

As we mentioned earlier, the 4th character represents the year in which thedevice 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, soin this example:

T = 23rd Week (Of Second Half)

Here is the first important note to ensure your success. The fact that we aretalking about the 23rd week of the second half of the year means that we mustaccount for the offset. The first half of the year was 26 weeks, so we willneed 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 ofthe 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 ofinformation, that data can sometimes require massaging and manipulation toanswer the question that you are after. I will describe in detail the techniquesused 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 Queryin lieu of osqueryi in your Terminal.

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

Extracting Characters from a String Using SUBSTR

The most basic component required for this exercise is the ability to retrieveonly the part of the serial which we are interested in. Using the SUBSTR(substring) function we can extract characters from a string based on theirposition 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 theword 'value', I tell SUBSTR to start at the 9th character (v) and returnthe 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 5thcharacters. The serial number is returned by the system_info table as thecolumn 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_5FROM system_info;

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

At this stage I like to start creating temporary tables using common-tableexpressions (CTEs) to manage the various parts of my query. While they soundcomplex, 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 tocreate our first temporary table:

WITHserial_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 asif it were its own table!

While this may seem trivial or redundant now, CTEs can be an invaluable toolfor managing the composition of large queries and compartmentalizing yourapproach; 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 withstatic data which is otherwise inaccessible to osquery.

At the beginning of this blog-post I referenced a spreadsheet of charactersand their mappings to manufacturing year and week. This data exists on theinternet, and in this blog-post, but it is not something that exists on thedevice. 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:

WITHmy_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 containedat the start of this blog-post:

WITHmac_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 whichhalf of the year, I instead supplied an offset value (26 or 0). This willmake 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 asabove:

WITHmac_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 workcompartmentalized. You can create as many temporary tables as you like usingCTES by separating each temporary table with a comma. You only need to useWITH once, at the start of your query, when defining your first temporarytable.

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

WITHserial_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 referencedin your final query will be used.

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

WITHserial_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_partialJOIN 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 iscorrectly identified as being manufactured in the year 2017. Let’s do somesimple math operations to return the appropriate number for week by adding ouroffset column to our week column:

WITHserial_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))SELECTyear,(week + offset) AS weekFROM serial_partialJOIN 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 datesin terms of their week number. Let’s return a traditional datestamp bymanipulating 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 atemporary lookup table using our previously returned values, to reduce thecomplexity of our rapidly growing query:

WITHsample_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 suitablecomplete date formatted string (not only a year). Let’s turn our existingyear 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:

WITHsample_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_startby multiplying our weeks into days and concatenating a + and the stringdays to create an offset that can be interpreted by the date function:

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

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

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

WITHsample_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_dateFROM 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 themanufacture data from our Mac’s serial number.

The Final Query

WITHserial_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_dateFROM 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 belong until this approach is rendered ineffective by Apple, but for thetime-being you can utilize it to track down your End-of-Life hardware andjustify its retirement.

But wait there’s more!

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

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