Hello,
I have tried and searched but I cannot figure out how to create a report that takes the Ship Date and then reports back all items that are five years and older and which ones are getting close to five years. Does anyone have something like this?

Thanks.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

2
This query will find Dell computers over five years old:
SELECT MACHINE.NAME, MACHINE.OS_NAME, MACHINE.CS_MANUFACTURER, DA.SHIP_DATE
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG

WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
HAVING DA.SHIP_DATE < DATE_SUB(NOW(), INTERVAL 5 YEAR)
ORDER BY DA.SHIP_DATE, MACHINE.NAME

I'm not sure how you would define "close to five years" but you can play with the interval clause to get different results. 
Answered 09/16/2014 by: chucksteel
Red Belt

  • This only shows windows 7. If I shorten the interval, how do I get it to pull any OS?
    • The report includes Windows 10 machines when I run it on my appliance. It does not include any limits that would make it only include Windows 7 machines.
      • Yeah that's what I expected so I'm not sure what's up here. I have been doing a lot of upgrades on this appliance and I think I will open a ticket see what they say as several reports (even canned ones) have some unusual behavior.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity