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,786 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
[X]
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
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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
 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retrieving JSON data as OUT parameter in Sql Server CLR procedure 1 43
T-SQL Query 9 37
Server 2016 FTP 5 23
VB.Net CLR Assembly type Datatable into SQL 8 26
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

759 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