How do I effectively get SCCM 2007 R2 to inventory computers by serial number and date it first hit the network.

Posted on 2011-02-17
Last Modified: 2013-11-21
We currently have a wierd way of inventorying, for when computers were purchased so that we know when to have the computers replaced. I'm looking for a way to be able to have SCCM pull up a query or collection or something, that will be based on serial number and the date that serial number first hit the network. I need to be able to determine which computers are 5 years old.  Once a computer is five years old, needs to be repalced. . I am new to SCCM so please be as detailed as possible, thanks.
Question by:ChemungAdmin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 14

Expert Comment

by:Abduljalil Abou Alzahab
ID: 34922074
This report may help you. but it's without determining which computers are 5 years old, take a look

Accepted Solution

socrates2012 earned 500 total points
ID: 34944136
This is a report I use for giving a really detailed look at all systems.  The two you can sort by that would assist in your particular search would be BIOS release date and OS install date:

SELECT DISTINCT v_R_System.Name0, v_R_System.User_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_PC_BIOS.ReleaseDate0, v_R_System.Operating_System_Name_and0, v_HS_OPERATING_SYSTEM.CSDVersion0 as ServicePack, v_HS_OPERATING_SYSTEM.InstallDate0, v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024 As C083 from v_R_System JOIN v_FullCollectionMembership ON v_R_System.ResourceID=v_FullCollectionMembership.ResourceID JOIN v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID JOIN v_GS_X86_PC_MEMORY ON v_R_System.ResourceID=v_GS_X86_PC_MEMORY.ResourceID JOIN v_GS_PC_BIOS ON v_R_System.ResourceID=v_GS_PC_BIOS.ResourceID JOIN v_HS_OPERATING_SYSTEM ON v_R_System.ResourceID=v_HS_OPERATING_SYSTEM.ResourceID where v_FullCollectionMembership.CollectionID=SMS00001

Author Comment

ID: 34952378

 I used what you gave me and it gives me lots of good stuff. Except its not giving me the origonal date that the serial hit the network (if thats possible :\ ). Ill give an example with what you gave me:

 Name, User Name, Serial Number,  Release Date, Operating System Name, ServicePack, Install Date,   Manufacturer, Model, Current Physical Memory (MB)

CCIT-18L   gsscott   FGSSTL1   7/30/2010   Microsoft Windows NT Workstation 6.1     1/19/2011 8:46:00 PM   Dell Inc.   Precision M6400   4083  

 CCIT-18L   gsscott   FGSSTL1   7/30/2010   Microsoft Windows NT Workstation 6.1     2/11/2011 12:47:00 PM   Dell Inc.   Precision M6400   4083  

 Now this is my computer. I know for a fact that my boss had this computer befor me at the beging of last year and the reason you see 2 is because i just reinstalled the os. It would be fine not to see his user name and all, but I need to see some other date that stays constant even if I were to say upgrade the bios or reinstall the os. Is there a way to pull up a report that has atleast the computer name and serial # based upon this mystery date? I could, if all els fails, used the bios release date. It would just be thrown off alittle if I end up doing a bios up date on one of our computers.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Assisted Solution

socrates2012 earned 500 total points
ID: 34953250
Yeah, getting when your machine hit the network, depending on how you reimage machines, is possibly going to be in AD but not in SCCM and even that is sketchy at best.  That is almost calling for utilizing Asset Intelligence and a proactive inventorying.  I will say that you can add v_R_System.Creation_Date0 to the select statement to get when the record was first made in SCCM but will look close to the OS build date if the record is being recreated upon each reimage.

Author Comment

ID: 34955409
One last thing and I think I will be all set. I was looking and couldnt see a way to add a colume for what AD group its associated with (along with the report given to me by socrates2012). Is it because its not there or did i just overlook it?

Expert Comment

ID: 34955703
The views you might be able to use for that are v_RA_System_SystemOUName for OUs or v_RA_System_SystemGroupName for AD groups.  I haven't attempted to tie AD groups and OUs inside of SCCM and don't use the discovery tools for anything other than AD system discovery.  If you are discovering everything you may make better use of those than I, wish I could be more help in that respect.

Author Closing Comment

ID: 34960653
So I talked to one of our database admins and he said in sql he is going to add a triger that send all the info do a separate database that wont allow the bios release date to chage, but it will allow every thing els to up date properly. So now I should be able to pull up a report that from that data base separatly with out changing the origonal. Only down side, so far, is that if micrsoft pushes an update it might change, triger. So we will have to put it back in if it does.

Author Comment

ID: 34960661
Thank you very much. : )

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question