Propagating SCCM DB for "Hardware 2A - Estimated computer age by ranges within a collection" report

As you know or have just found Microsoft hasn't been updating the LU_CPU table in SCCM since 2006. So running a computer age report  "Hardware 02A - Estimated computer age by ranges within a collection" shows Not Available for computers have CPUs launched after 2006.

But you can update this data manually if you really need to run these reports. I did it in 1 day, it was boring but seeing the result was a big relief. Now I know I will not have to import hundreds of records again.

Result :



Steps? Here : )
1. Find discovered CPU information in v_GS_Processor, and exclude CPUs already exist in LU_CPU table.

SELECT DISTINCT
 .[v_GS_PROCESSOR].CPUHash0 AS CPUHash,
 .[v_GS_PROCESSOR].Manufacturer0 AS Manufacturer,
 .[v_GS_PROCESSOR].BrandID0 AS BrandID,
 .[v_GS_PROCESSOR].PCache0 AS PCache,
 .[v_GS_PROCESSOR].NormSpeed0 AS NormSpeed,
 .[v_GS_PROCESSOR].IsMobile0 AS Mobile,
 .[v_GS_PROCESSOR].Name0 AS Name
 FROM
 .[v_GS_PROCESSOR]
 LEFT JOIN
 .[LU_CPU] as LC
 ON
 .[v_GS_PROCESSOR].CPUHash0 = LC.CPUHash
 WHERE
 LC.CPUHash is null

After running query and seeing the results you might want to add something like these to make it shorter by excluding bad looking ones to check them at the and.

AND
.[v_GS_PROCESSOR].Name0 Like '%[0-9]%' /* Exclude rogue entries */
 AND
 .[v_GS_PROCESSOR].Manufacturer0 Like '%Intel%'
 AND
 .[v_GS_PROCESSOR].Name0  Not Like '[" "]%' /*Exclude duplicates and rogues*/

Now you have as much as usable data from the view, Column names match the LU_CPU as well.

 

 2. Now you can take the query output to excel or to a temporary sql table to work on it, choice is yours. I used Excel to trim some information, because CPU names don't have a standard pattern to automatize parsing/filling.



I duplicated the Name Column and deleted all information by Find/Replace to leave CPU Model alone.



 3. Then visited Intel's ARK ( http://ark.intel.com/search/advanced/ ) website to obtain a detailed CPU list, and imported it to another sheet.

 

 4. Converted Launch dates to yyyy-mm-dd format, I written this to do that.
" DATE((CONCATENATE(20,RIGHT(B3,2))),(3*(MID(B3,2,1)))-2,1) "

 

 5. Copied values of Processor Number and CPU_Birth to the Sheet I created at first with data from the first query. (VLOOKUP caused me problem when I used it in multiple sheet)

I added a column next to Name and with " =VLOOKUP(H2,P:Q,2,FALSE) " formula, P:Q here is the two columns from ARK table.

 

 6. And added other necessary columns, also checked models names with eye and corrected names like M 430 to 430M etc. (Boring bit)

 

7. Rest is the inserting the table to LU_CPU, you can do that as you wish. You can concatenate columns and use with SQL's Insert Into.

  

This is lazy - non-creative way but very useful for future updates, as you can only add a new cpu and run it's command on SQL to add it. You can try to import from Excel file directly to LU_CPU too.

  

For remaining CPUs don't have CPU_Birth information you can use the SCCM reporting to find machines by clicking the Not Available on the top of the report, search machine with the query below

 SELECT * FROM v_GS_PROCESSOR where SystemName0 = 'JK04-LT'

Add your findings to your table and find birth date manually on the web, ( I recommend http://www.cpu-world.com/ ) then insert you DB LU_CPU again.

You can also query LU_CPU table for NULL CPU_Births

SELECT DISTINCT [LU_CPU].Name 
From [LU_CPU] where CPU_Birth is NULL

then find the Launch date manually on the web and update the column via Name

UPDATE [LU_CPU]            
  SET CPU_Birth = '2005-03-01'            
  Where Name = 'Intel(R) Celeron(R) M processor         1.50GHz'

Good luck !