Hardware age report

[blaf]

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

 

 

 

[TH0MA5]

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

[GarthMJ]

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.

 

 

 

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

 

 

 

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?!?!?!

 

 

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.

[blaf]

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

[blaf]

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

[blaf]

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