/build/static/layout/Breadcrumb_cap_w.png

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.



0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 4 years ago
Red Belt
2

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
I don't see where the HP data for ship date is stored, if it is there, but the Lenovo data is in LENOVO_ASSET. Adding that to the query looks like this:
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
Hope that helps.





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 3 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 3 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ