Bize Ulaşın / Contact Us

Propagating SCCM DB for “Hardware 2A – Estimated computer age by ranges within a collection” report

11 / 01 / 2013 by SCCM 2003, SCCM 2007 1 Yorum / Comment

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 spent a few hours, 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 :

11-01-2013 13-29-47

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

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.

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

 11-01-2013 13-42-49

 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.

11-01-2013 13-46-26

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

11-01-2013 13-48-37

 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.

 11-01-2013 13-53-08

 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) ”

  11-01-2013 13-56-45

 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.

  11-01-2013 14-01-24

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

  11-01-2013 14-04-56

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.

   11-01-2013 14-09-47

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.

   SCCM Computer Age

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

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

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

Good luck !

Do you feel lazy, and want to download my LU_CPU table :)

Here. The pass for it is SCCMDBLUCPU

One comment on “Propagating SCCM DB for “Hardware 2A – Estimated computer age by ranges within a collection” report

  1. on December 2, 2015

    Hey Osman,
    Great write up. How can I download the file? I am logged in but it says access denied? Any help would be greatly appreciated.
    Joey

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*

Kontrol / Control * Time limit is exhausted. Please reload CAPTCHA.