Jump to content


  • 0
blaf

Hardware age report

Question

Hello,

 

I have to create report presenting age of computers in our company.

 

We have hardware refresh cycle of 3 years and I need computer age report to calculate how many deployments to schedule per month/year.

 

I was trying to create custom query, use SQL Report Builder inside of SCCM but without much experience and success.

 

Kent Agerlund posted interesting link in one of the forums, for this tool:

 

http://www.enhansoft.com/pages/warranty-information-reporting.aspx

 

 

Before trying 3rd party tools it would be great if this report could be created directly in SCCM.

 

Thanks,

 

Blaf

 

 

 

Share this post


Link to post
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Blaf,

 

Are you looking for a query statement that provides you with the age of every computer in your company or are you looking for a report to tell you a number of how many computers needs to be replaced this year?

 

My company is moving to a 3 year hardware refresh and I am using SCCM to run a query statement that pulls the BIOS date from our computers and we use that data to determine what computers need to be replaced that year. I am not a SQL or SCCM expert but this is what I do:

  1. Open Microsoft SQL Server Report Builder, click on the circle in the top left and select: New Report
  2. Select Table or Matrix Wizard
  3. Select Create a dataset and click Next
  4. Select your Data Source Connection (you might need to enter your credentials) and click Next
  5. On the Design a query page, select Edit as Text and copy the following SQL code into the blank white box:
    SELECT  distinct 
     CS.name0 as 'Computer Name', 
     CS.domain0 as 'Domain', 
     CS.UserName0 as 'User', 
     OS.Description0 as 'Description',
     BIOS.SerialNumber0 as 'Bios serial', 
     BIOS.ReleaseDate0 as 'Bios Date',
     CS.Manufacturer0 as 'Manufacturer', 
     CS.Model0 as 'model', 
     OS.Caption0 as 'OS', 
     RAA.SMS_Assigned_Sites0 as 'Site', 
     RAM.TotalPhysicalMemory0 as 'Total Memory', 
     sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size', 
     sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space', 
     Processor.MaxClockSpeed0 as 'CPU Speed' 
    from  
      v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
     right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID  
     right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID  
     right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
     right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
     right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
    JOIN v_GS_PROCESSOR Processor on SYS.ResourceID=Processor.ResourceID 
    right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID 
    where 
     LDisk.DriveType0 =3
    group by 
     CS.Name0, 
     CS.domain0,
     CS.Username0, 
     BIOS.SerialNumber0, 
     CS.Manufacturer0, 
     CS.Model0, 
     OS.Caption0, 
     RAA.SMS_Assigned_Sites0,
     RAM.TotalPhysicalMemory0, 
     Processor.MaxClockSpeed0,
     BIOS.ReleaseDate0,
     OS.Description0
    
  6. Click on the Red ! to run a test and see if this line of code works in your environment. If it runs click Next.
  7. Drag all the desired items from the Available Fields to Values box and click next (You might be able to add something to the Row group to sort by the year but I'm not sure)
  8. If you don't have anything in the Row Group, just click Next
  9. Select a style that you like and click Finish.
  10. Edit the form as you desire

Every environment is different and you will most likely have to edit the SQL statement to fit what you need, but hopefully this will point you in the right direction.

 

TH0MA5

Share this post


Link to post
Share on other sites

  • 0

Keep in mind that I work for Enhansoft. Therefore my view is slight biased. ;-)

 

The question you need to ask is, how accurate do you want your results?

 

Warranty Information Reporting (WIR) will give you accurate results. My PCs warranty on started on May 16, 2013.

 

WIR.png

 

 

If I used the BIOS date, my warranty started on Sept 24 2014. That is 496 days AFTER my warranty started.

 

BIOS.png

 

 

If I look at the OS install date, my warranty started on Jun 19 2014, that is 399 days after the real warranty start date but almost 100 days before the BIOS date, How do you explain that to a manager?!?!?!

 

OS.png

 

So how accurate do you want your results?

 

If you ever want a demo or have questions, let me know and I will do my best to make sure that I’m the one doing the demo.

Share this post


Link to post
Share on other sites

  • 0

Hi Thoma5,

 

I have to apologize for not replying on your letter before. I will test this query right away and let you know if it was successful.

 

This is actually what we want, to determine how many computers we need to replace this year moving forward.

 

Anyway, this is much appreciated, can't wait to test it.

Thanks for your time.

 

Blaf

Share this post


Link to post
Share on other sites

  • 0

Hi GarthMJ,

 

I am sure that Enhansoft is excellent tool to pull this report, will try to run query posted by Thoma5 and see if there is need now for Enhansoft.

 

It's interesting to know difference between warranty date and BIOS, might even contact HP to try to get more information how they calculate warranty and use that as reference.

 

Demo would be interesting as well, if both solutions are working it would be interest to compare results.

 

Thanks for posting this extensive report,

 

Blaf

Share this post


Link to post
Share on other sites

  • 0

Hi TH0MA5,

 

Just to confirm that I was able to finish report presenting computer age.

 

I used query which you provided and pulled the BIOS dates for all computers in our domain, which is sufficient to use as reference for future deployments.

 

Thank you so much for posting this SQL code, it was extremely helpful.

 

Best regards,

 

Blaf

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.