SCCM 2007 Report for PC Age

Please help!
     I am in dire need of a report that will give me the age of the PCs on our domain.  We're looking to refresh any machines older than three years.  We're using SCCM 2007.
Xeif3rAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
If you have sccm 2007 go to reports and check for the following report name:
Computer with a specific Operating System
it's in Operating System Category

you will find the installation date and last boot up time
0
Xeif3rAuthor Commented:
But what if the machine has been reimaged?  Will the date still be accurate?  What about going of the BIOS date?
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
this report will show the date when last time you install or deploy Windows
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Xeif3rAuthor Commented:
hmmm, ok.  What about the BIOS date?
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
is this information available in BIOS, I'm asking about your machines BIOS ???
0
Xeif3rAuthor Commented:
Here is what I'm working with so far.  The report works except for the fact that many of the models are not "found", and thus do not report a CPU manufacture date (which is the current identifier that i'm using).  Any suggestions?
SELECT DISTINCT 
v_r_system.netbios_name0                  AS "Computer Name",
v_gs_system_console_usage.topconsoleuser0 AS "Top Console User",
v_gs_system_enclosure.serialnumber0       AS "Serial Number",
v_gs_system_enclosure.smbiosassettag0     AS "Asset Tag",
v_GS_PC_BIOS.serialnumber0                AS "PC Bios Serial Number",
v_gs_computer_system.manufacturer0        AS "Computer Manufacturer",
v_gs_computer_system.model0               AS "Computer Model",
"Estimated Date of CPU Manufacture" =
CASE 
  WHEN (CAST(v_lu_cpu.cpu_birth AS VARCHAR) IS NULL) THEN '(Not Available)'
  ELSE  CAST(v_lu_cpu.cpu_birth AS VARCHAR)
END,
ismulticore0                              AS "Is Multi Processor System", 
CASE 
  WHEN v_gs_computer_system.manufacturer0 LIKE 'Dell%' 
  THEN 'http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?servicetag=' + v_gs_system_enclosure.serialnumber0
  WHEN v_gs_computer_system.manufacturer0 LIKE 'Hewlett%'
  THEN 'http://h20000.www2.hp.com/bizsupport/TechSupport/WarrantyResults.jsp?sn=' + v_gs_system_enclosure.serialnumber0 + '&country=US'
  WHEN v_gs_computer_system.manufacturer0 LIKE 'HP%' 
  THEN 'http://h20000.www2.hp.com/bizsupport/TechSupport/WarrantyResults.jsp?sn=' + v_gs_system_enclosure.serialnumber0 + '&country=US'
  WHEN v_gs_computer_system.manufacturer0 LIKE 'IBM%' 
  THEN 'http://www-304.ibm.com/systems/support/supportsite.wss/warranty?type=' + LEFT(REPLACE(v_gs_computer_system.model0,'-[',''), 4) + '&serial=' + v_GS_PC_BIOS.serialnumber0 + '&action=warranty&brandind=5000008'
  ELSE '(Not available)' 
END AS "Warranty Information"
FROM   v_gs_processor 
      INNER JOIN v_r_system 
        ON v_gs_processor.resourceid = v_r_system.resourceid 
      INNER JOIN v_gs_system_enclosure 
        ON v_gs_system_enclosure.resourceid = v_r_system.resourceid 
      INNER JOIN v_GS_PC_BIOS 
        ON v_GS_PC_BIOS.resourceid = v_r_system.resourceid 
      INNER JOIN v_gs_computer_system 
        ON (v_gs_computer_system.resourceid = v_r_system.resourceid) 
      LEFT JOIN v_gs_system_console_usage 
        ON v_gs_system_console_usage.resourceid = v_r_system.resourceid 
      LEFT JOIN v_lu_cpu 
        ON Lower(v_lu_cpu.cpuhash) = Lower(v_gs_processor.cpuhash0) 
WHERE  v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 <> 12 
AND
v_gs_computer_system.manufacturer0 NOT LIKE 'VMware%'

Open in new window

0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
Let me check and will update you...
0
micalkinCommented:
You can try using the report Hardware 02B - Computers within an age range within a collection.  This uses the manufacture date of the cpu.
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
Asset Intelligence functionality is provided by several WMI classes. Each class enables a given set of reports. If any class is disabled, that data won't flow back to the site server and therefore will not be available for that report. We documented the correlation between classes and reports in the CHM file that shipped with the products, but to save time and have the information in one spot, I'll copy/paste that information into this post.

SMS_Processor

This is an existing SMS class to which new properties have been added to provide more complete data about processors. The following reports are dependent upon this class:

    Hardware 1A - Summary of Computers in a Specific Collection

    Hardware 2A - Estimated Computer Age by Ranges within a Collection

    Hardware 2B - Computers within an age range with a collection

    Hardware 8A - Hardware that is Not Ready for a Software Upgrade

    Hardware 9A - Search for computers

Please also check the following link:
http://msdn.microsoft.com/en-us/library/cc145394.aspx
0
Xeif3rAuthor Commented:
Yes, I've tried this.  Unfortunatly a lot of the models are not able to locate the CPU date for some reason.  So can....others can't; it's appears to be specific to the model number.
0
Xeif3rAuthor Commented:
How do I see which modules of AI are enabled?
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
Did you enable Asset Intelligence classes?

In the default configuration for Microsoft System Center Configuration Manager 2007, the Asset Intelligence classes are not enabled. Therefore, it is necessary to enable the Asset Intelligence classes that will be used
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
Enabling Asset Intelligence Data Collection should be enabled
0
Xeif3rAuthor Commented:
How can i confirm?  You stated that be default they are not enabled.
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
You may also check the following link:
Hardware Inventory Reporting Classes Required for Asset Intelligence Reports
http://technet.microsoft.com/en-us/library/cc161933.aspx

Asset Intelligence: Reports don't contain data
http://blogs.technet.com/smsandmom/archive/2007/11/01/asset-intelligence-reports-don-t-contain-data.aspx
0
Xeif3rAuthor Commented:
based on the code that i uploaded, any idea why some machines would report a CPU Manufacture date, and others wouldn't?
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
maybe SCCM didn't collect the required information for this machine it CPU very old and support this feature.
0
Xeif3rAuthor Commented:
Hmm....I doubt it.  One of the machines is an HP EliteBook 6930p.  This is a current model.  Is it possible that the CPU Birth date list in SCCM is incomplete?  Any way to upgrade or refresh it with current info?
0
Abduljalil Abou AlzahabInfrastructure Team Leader – Professional ServicesCommented:
I already gave you 2 reasons not one. 2nd one was sccm didn't collect all required data.

did u enable Asset Intelligence and check
Hardware Inventory Reporting Classes Required for Asset Intelligence Reports
http://technet.microsoft.com/en-us/library/cc161933.aspx

Asset Intelligence: Reports don't contain data
http://blogs.technet.com/smsandmom/archive/2007/11/01/asset-intelligence-reports-don-t-contain-data.aspx
0
Xeif3rAuthor Commented:
Ok, I've enabled all AI options.  Will it take some time for SCCM to collect all the info before I can run a report?
 
 
0
micalkinCommented:
Here is a report sql statement that I use and it has the bios release date on it.  I do NOT have asset intelligence running.
SELECT  distinct 
 CS.name0 as 'Computer Name', 
 CS.domain0 as 'Domain', 
 CS.UserName0 as 'User', 
 OS.Description0 as 'Description',
 BIOS.SerialNumber0 as 'Bios serial', 
 BIOS.ReleaseDate0 as 'Bios Date',
 CS.Manufacturer0 as 'Manufacturer', 
 CS.Model0 as 'model', 
 OS.Caption0 as 'OS', 
 RAA.SMS_Assigned_Sites0 as 'Site', 
 RAM.TotalPhysicalMemory0 as 'Total Memory', 
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size', 
 sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space', 
 Processor.MaxClockSpeed0 as 'CPU Speed' 
from  
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
 right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID  
 right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID  
 right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
 right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
JOIN v_GS_PROCESSOR Processor on SYS.ResourceID=Processor.ResourceID 
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID 
where 
 LDisk.DriveType0 =3
group by 
 CS.Name0, 
 CS.domain0,
 CS.Username0, 
 BIOS.SerialNumber0, 
 CS.Manufacturer0, 
 CS.Model0, 
 OS.Caption0, 
 RAA.SMS_Assigned_Sites0,
 RAM.TotalPhysicalMemory0, 
 Processor.MaxClockSpeed0,
 BIOS.ReleaseDate0,
 OS.Description0

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.