K1000 Device Ship Date Custom Report
I currently have a report that list our inventory and also the ship date based on the Dell Warranty info.
This report was written a while ago before Lenovo, et al, warranty info was supported in KACE.
SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE,
M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG,
SUBSTRING(L.NAME, 1, length(L.NAME) - 10) AS LOCATION,
DATE_FORMAT(DA.SHIP_DATE, '%Y-%m-%d') as SHIP_DATE
FROM MACHINE M
LEFT JOIN DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'computers')
ORDER BY SHIP_DATE
I was wondering if anyone could help me modify this report so it not only gathers ship dates for Dell products, but also Lenovo and the other manufacturers KACE now supports with warranty info.
Thanks.
Answers (1)
Top Answer
You will need to determine which tables the shipping information is stored for each manufacturer. We don't have our appliance setup for any of the other manufacturers, so I can't really test that part, but here is a report that uses a custom inventory field which contains Apple purchasing information as an example.
https://github.com/csteelatgburg/K1000-Database-Queries/blob/master/computers%20with%20purchase%20date.sql
The key to this report is using the case statement to get the correct information based on manufacturer:
CASE
WHEN MACHINE.CS_MANUFACTURER like 'Apple%' THEN MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE
WHEN MACHINE.CS_MANUFACTURER like 'Dell%' THEN DA.SHIP_DATE
END AS PURCHASE_DATE
You also need to add the correct joins to the tables:
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG -- Dell data
LEFT JOIN MACHINE_CUSTOM_INVENTORY on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and SOFTWARE_ID = 25152 -- Apple data
SELECT DISTINCT(MACHINE.NAME), MACHINE.OS_NAME, MACHINE.CS_MODEL, MACHINE.CS_MANUFACTURER,
CASE
WHEN MACHINE.CS_MANUFACTURER like 'Apple%' THEN MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE
WHEN MACHINE.CS_MANUFACTURER like 'Dell%' THEN DA.SHIP_DATE
WHEN MACHINE.CS_MANUFACTURER like 'Lenovo%' THEN LENOVO_ASSET.SHIPPED
END AS PURCHASE_DATE,
MACHINE.BIOS_SERIAL_NUMBER
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN MACHINE_CUSTOM_INVENTORY on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and SOFTWARE_ID = 25152
LEFT JOIN LENOVO_ASSET ON MACHINE.BIOS_SERIAL_NUMBER = LENOVO_ASSET.SERIAL
WHERE (MACHINE.CS_MANUFACTURER like 'Apple%' or
MACHINE.CS_MANUFACTURER like 'Dell%' or
MACHINE.CS_MANUFACTURER like 'Lenovo%')
AND MACHINE.NAME not like '%BC'
ORDER BY PURCHASE_DATE, MACHINE.NAME
Comments:
-
chucksteel, thanks that helped.
I was able to take your example and modify my existing report to contain the info I wanted. - asantia 4 years ago-
Could you share your report? I have the same need. - Leonardo Lucas 4 years ago
-
SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE,
M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG,
SUBSTRING(L.NAME, 1, length(L.NAME) - 10) AS LOCATION,
CASE
WHEN M.CS_MANUFACTURER like 'Apple%' THEN DATE_FORMAT(MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE, '%Y-%m-%d')
WHEN M.CS_MANUFACTURER like 'Dell%' THEN DATE_FORMAT(DA.SHIP_DATE, '%Y-%m-%d')
WHEN M.CS_MANUFACTURER like 'Lenovo%' THEN DATE_FORMAT(LENOVO_ASSET.SHIPPED, '%Y-%m-%d')
END AS SHIP_DATE
FROM MACHINE M
LEFT JOIN DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
LEFT JOIN MACHINE_CUSTOM_INVENTORY ON (M.ID = MACHINE_CUSTOM_INVENTORY.ID) AND SOFTWARE_ID = 25152
LEFT JOIN LENOVO_ASSET ON (LENOVO_ASSET.SERIAL = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'computers')
ORDER BY SHIP_DATE - asantia 4 years ago
