Kace ship date reports for all computers
I am making a report in kace for ship dates on all of our computers but only out dell computers are showing up. We also have HP, Lenovo, and Mac computers. Here is the sql code I am currently using.
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_PROVIDER, DA.SHIP_DATE AS SHIP_DATE
FROM DELL_WARRANTY DW
JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE "Desktop"
AND M.BIOS_SERIAL_NUMBER!='%dell%'
AND DA.DISABLED != 1
ORDER BY MACHINE_NAME, SHIP_DATE DESC, DW.SERVICE_LEVEL_DESCRIPTION
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_PROVIDER, DA.SHIP_DATE AS SHIP_DATE
FROM DELL_WARRANTY DW
JOIN DELL_ASSET DA ON (DW.SERVICE_TAG = DA.SERVICE_TAG)
JOIN MACHINE M ON (M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG)
WHERE M.CS_MANUFACTURER LIKE "Desktop"
AND M.BIOS_SERIAL_NUMBER!='%dell%'
AND DA.DISABLED != 1
ORDER BY MACHINE_NAME, SHIP_DATE DESC, DW.SERVICE_LEVEL_DESCRIPTION
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
The K1000 only populates ship dates for Dell hardware. If you want to report on other manufacturers you will first need to find a way to get that data into the database.
Comments:
-
I do not need the ship dates for the other machines but i would like them to show up in the same report where the ship date information would just be blank for them. Is this possible? - Nick_210 7 years ago
-
Yes, the trick is to change your starting point. The original query starts with the DELL_WARRANTY table which will only contain records for Dell devices. Instead start with the machine table and then join to the DELL_ASSET table (where the ship date for Dell devices is stored). Also, use a left join so that all rows from the left side of the join (in our case, the machine entry) are included, even if there isn't a matching record in the right side.
SELECT MACHINE.NAME,
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
DA.SHIP_DATE AS "Ship Date"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
ORDER BY MACHINE.NAME - chucksteel 7 years ago-
Thank you. This is exactly what I needed. - Nick_210 7 years ago
