/build/static/layout/Breadcrumb_cap_w.png

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

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 6 years ago
Red Belt
1

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 6 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 6 years ago
      • Thank you. This is exactly what I needed. - Nick_210 6 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