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
  • 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.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help on t-sql 2012 10 53
Sql server insert 13 26
SQL Server - Set Field Values ito Zero Based on Related Table 4 21
Tsql query 6 18
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now