Solved

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
8
2,727 Views
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.
0
Comment
Question by:ChemungAdmin
  • 4
  • 3
8 Comments
 
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
http://www.ms-csm.com/SCCM/?p=190
0
 
LVL 3

Accepted Solution

by:
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
0
 

Author Comment

by:ChemungAdmin
ID: 34952378
socrates2012

 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.
0
 
LVL 3

Assisted Solution

by:socrates2012
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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ChemungAdmin
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?
0
 
LVL 3

Expert Comment

by:socrates2012
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.
0
 

Author Closing Comment

by:ChemungAdmin
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.
0
 

Author Comment

by:ChemungAdmin
ID: 34960661
Thank you very much. : )
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 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

28 Experts available now in Live!

Get 1:1 Help Now