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:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
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)
FROM MACHINE
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
1 Comment
[ + ] Show comment
-
I been having a similar issue, however when i ran the script there is no information regarding the Warranty end date. Am I missing something? - IT_Guy82 4 years ago
-
If you look at a machine in the inventory, is the warranty information populated? - chucksteel 4 years ago
Answers (1)
Please log in to answer
Posted by:
ondrar
5 years ago
Add this to the query:
L.NAME
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!
Comments:
-
Forgive my ignorance, I have zero experience with SQL. Where does the L.NAME go, and where do I add the Join section? Ideally I'd like to be able to just input the location name to generate a report that's isolated just to those PC's. Thank you for your help! - PFKurt 5 years ago
-
No problem. I started there, too. It'll look like this:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
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),
L.NAME
FROM MACHINE
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
JOIN ASSET A ON A.MAPPED_ID = MACHINE.ID
JOIN ASSET L ON A.LOCATION_ID = L.ID
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 - ondrar 5 years ago
-
When joining to the ASSET table I have found that it is best to specify that the asset is type 5, otherwise you risk getting an asset that happens to have the same ID as the machine.
JOIN ASSET A on A.MAPPED_ID = MACHINE.ID and A.ASSET_TYPE_ID = 5
It doesn't happen often, but it is possible. - chucksteel 5 years ago-
Good addition! - ondrar 5 years ago
