K1000 report on Dell Warranty expiration - by location?
All of our PC's are have a location assigned and are labeled accordingly. I'm trying to generate a report with just the warranty expiration date (without the silly Dell Digital Delivery option), but I can't get it by location assigned. I'd like to break it out to just the location assigned so I can provide it to department heads. Here's what I've got so far:
DA.SHIP_DATE AS "Ship Date",
DATE(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",DATE(MACHINE.LAST_SYNC)
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
and DW.SERVICE_LEVEL_CODE not like 'D'
and DW.SERVICE_LEVEL_CODE not like 'DL'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
Please log in to answer
Posted by: ondrar 2 years ago
Add this to the query:
and this to the Join section:
JOIN ASSET A ON A.MAPPED_ID = MACHINE.ID
JOIN ASSET L ON A.LOCATION_ID = L.ID
This maps the Device (MACHINE.ID) to the Asset it's attached to (A.Mapped_ID), then maps Location Assets (L.ID) to the Location ID of all Assets (A.LOCATION_ID).
Then when we query L.NAME, we get the name of the Location Asset to which the Device Asset is assigned.
That's a nice report. I think I'll use it, too!