SQLTeam.com | Weblogs | Forums

SQL 2014 v12 - Query - Find CPU Age - How old

Hi All

Trying to do a query to find out how old are the cpu on device. We do have a way of doing it but it involves manual step which we are trying to move from.

Basically now we are taking the CPU Model example INTEL I5 8500 2.4gh, 01/04/2017 ( Date CPU Launch), then do the difference with today's date, which work but we always need to update the table with the CPU Model and date cpu was launch.

Another way we taught was via the BIOS date but this would not work as if the BIOS get updated it would provide the current bios date.

What would be the best approach to do this.

Tks

You can run the SYSTEMINFO utility using the xp_cmdshell procedure from a SQL Server session. It gives you OS Install Dates, versions, BIOS date/version, and some CPU description, but nothing about when the CPU model was first produced or launched.

If any utility provides that (CPU-Z is the only thing I can think of), then you'd still run it as a command line utility via xp_cmdshell and parse the output into a table. That kind of CPU information is not directly available from within SQL Server.

1 Like

Welcome

Are you doing this on all of your sql servers in the domain or just 1 server?

hi tks for your reply basically it is from 2 different sql server gathering the device information from multiple domain.

yes agree we taught about this process as well but we would still have to manually input the launch date manually. This is the part we are trying to see if it can be automated as right now if we dont update it want provide the cpu age or how old is the cpu.

so from 2 sql servers gathering cpu age on all servers in the domain? or gathering the cpu age from 2 sql servers in the domain?

That should have some info on launch/release dates of Intel processors. I found it goes back to 2006, not sure it goes back earlier, but you could probably find older pages on The Internet Archive site.

It shouldn't be that difficult to copy and paste, or even web scrape. You can also use the Data features in Excel to extract info from HTML tables, then reformat that to insert into a SQL Server database.

hi tks for your update

so here's the scenario
Org 1
SQL server 1 = 1k desktop and laptop
database of approx 2k of devices
the query use
sample of it

Select 'Intel(R) Pentium(R) 4 CPU 2.26GHz','01/01/2002'  ( This is the part we enter manually)
union
select distinct 
   v_R_System.Name0 as [System Name],
   DateDiff(
    dd,
    CONVERT(
     CHAR(10),
     CONVERT(
      DATETIME, 
      LEFT(ProcessorReleaseDates.ReleaseDate, 10), 
      105
     ), 
     101
    ), 
    getdate())/365.25 AS [Processor Age],

so what we are trying to do is by pass the manual entry which are all entered at the beginning of the query, we are able to gathered the INTEL CPu part , but we need to manually entered the launch date.

I'm kind of struggling with what you're trying to accomplish, but I'll put that aside as my own misunderstanding. In any case, store your dates as dates. Use the date datatype, you don't need to store time, and storing as date instead of character data simplifies your DATEDIFF() function.

Assuming that you are compiling CPU stats of actual inventory, and not just documenting for historical research:

  1. Comparing a CPU's age to it's class/steppings RELEASE date doesn't make sense. The manufacturing date of the actual chip is more important. A CPU in the same class, manufactured 1 year after the initial release, is not the same age as one manufactured earlier.

  2. Other aspects of a CPUs manufacturing run or date, like susceptibility to Spectre/Meltdown or other hardware bugs, improvements, cache/microcode changes, and so on, may not be reflected in the CPUs description. In your example, "Intel(R) Pentium(R) 4 CPU 2.26GHz" is generic enough to apply to multiple years of different Intel CPU manufacturing runs.

  3. Additionally, a CPU could sit in inventory for a while before it's actually INSTALLED in a computer, and would really determine its useful age. Warranty coverage, support, etc. would more likely be tied to installation, certainly not the first release date of the CPU class.

If none of these bullet points apply to what you're trying to do, please ignore them and forgive the interruption. If all you are trying to do is match Intel CPU descriptions to their release dates, I don't have any advice beyond what I described earlier: scrape/copy the data from Intel Ark and import it into your database.