HI Team,

               I need a help to identify the machines which are lying more than 3years in my network not by using a warranty expired report.
               Because i need to create a detail report about the machines which are 2yrs, 3yrs, 4yrs .. older.

Thanks
Raja Singh
1 Comment   [ + ] Show Comment

Comments

  • The challenge you have is finding a date that is when the machine was installed. If the device is a dell machine you can use the shipped date field within the warranty section of the inventory. Otherwise if you have had KACE for years you could use the Created date, or if the machine has not been reimaged then you can use the OS install date.
Please log in to comment

Answers

0
Or try using this SQL in a report and see what results you get ;o)

SELECT
MACHINE.CS_MODEL as 'Model',
COUNT(DISTINCT CASE WHEN YEAR(DELL_WARRANTY.START_DATE) = YEAR(NOW())-1 THEN MACHINE.ID END) as '1Yr',
COUNT(DISTINCT CASE WHEN YEAR(DELL_WARRANTY.START_DATE) = YEAR(NOW())-2 THEN MACHINE.ID END) as '2Yr',
COUNT(DISTINCT CASE WHEN YEAR(DELL_WARRANTY.START_DATE) = YEAR(NOW())-3 THEN MACHINE.ID END) as '3Yr',
COUNT(DISTINCT CASE WHEN YEAR(DELL_WARRANTY.START_DATE) = YEAR(NOW())-4 THEN MACHINE.ID END) as '4Yr',
COUNT(DISTINCT CASE WHEN YEAR(DELL_WARRANTY.START_DATE) = YEAR(NOW())-5 THEN MACHINE.ID END) as '5Yr',
COUNT(DISTINCT CASE WHEN YEAR(DELL_WARRANTY.START_DATE) <= YEAR(NOW())-6 THEN MACHINE.ID END) as '6+Yr',

FROM
DELL_WARRANTY
LEFT JOIN MACHINE ON DELL_WARRANTY.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER 
GROUP BY 
MACHINE.CS_MODEL
ORDER BY
Answered 03/17/2015 by: Hobbsy
Red Belt

Please log in to comment
0
Based on the shipped date you can run the following SQL script

select distinct UCASE(m.NAME), m.CS_MANUFACTURER, m.CS_MODEL, w.SERVICE_TAG, w.START_DATE
from MACHINE as m, DELL_WARRANTY as w
where m.BIOS_SERIAL_NUMBER = w.SERVICE_TAG
and w.START_DATE < MAKEDATE(YEAR(NOW()),MONTH(NOW())) - interval 5 year
order by w.START_DATE
Answered 03/04/2015 by: aragorn.2003
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share