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,706 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 13

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Integrate social media with email signatures

Is your company active on social media? Do you also use email signatures? Including social media icons in your email signature is a great way to get fans for free. Let all your email users know you’re on social media quickly and easily, in a single click.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Know what services you can and cannot, should and should not combine on your server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

705 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

16 Experts available now in Live!

Get 1:1 Help Now