ConfMgr SP1 - WSUS Upstream Server

One of the useful things that Configuration Manager 2012 SP1 has brought is allowing usage of WSUS as an upstream server.

Paragraph from http://technet.microsoft.com/en-us/library/jj591552.aspx

At the top-level Configuration Manager site, you can now specify an existing WSUS server as the upstream synchronization source location. During synchronization, the site connects to this location to synchronize software updates. For example, if you have an existing WSUS server that is not part of the Configuration Manager hierarchy, you can specify the existing WSUS server to synchronize software updates.

Propagating SCCM DB for "Hardware 02A - 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 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 !

Using MDT DB to get location specific Domain Joining parameters in SCCM OSD

If you have multiple domains as me and want to automate domain joining task by sensing locations automatically in SCCM OSD, you can use MDT DB to do it easily. You can also do this by adding more actions with different queries to your TS or customizing your coustomsettings.ini. But I prefer using MDT DB as changes there doesn't need to be refresh as packages, GUI makes it easier to manage and more foolproof, etc.

All you need to do is modifying location details on your MDT DB and call these variables in your SCCM TS.

So first edit your location details in MDT Workbench, you'll need to edit four values here as shown below.

DomainAdmin : The name of the account used to join the domain. Example : OSDAdmin

DomainAdminDomain : The domain of the account used to join the domain. Example: Domain1

DomainAdminPassword : The password of the account used to join the domain.

JoinDomain : The name of the domain in which the computer should be placed. Example: Domain1.local

MachineObjectOU : The OU in which the computer account should be created (if it does not already exist)., Exaple: OU=NewComps,DC=Domain1,DC=Local

Then you'll need to modify your "Apply Network Settings" action in your TS to use values in MDT DB. It is very as MDT Workbench displays these as it is. So all you need to do adding % to the start and the end of the variable as below.

Domain : %JoinDomain%
DomainOU : %MachineObjectOU%
Account : %DomainAdmin%
Password : %DomainAdminPassword%

All done, now you can test it.

Please note that you'll need a working SCCM and MDT integration to use this method first.