/build/static/layout/Breadcrumb_cap_w.png

Custom report but won't list devices if warranty info isn't available

Hi,

I have a custom report (below) that goes out to key people throughout our organisation that shows some basic information regarding what device belongs to who, its spec and its shipping date (so they know old teh equipment is)

However if KACE can't get the Dell Service information to identify the shipping date it just doesn't show at all on the report.

Is there a way I can still show the device but the column for shipping date would just be blank?


Select

    MACHINE.NAME As SYSTEM_NAME,

    MACHINE.CSP_ID_NUMBER,

    MACHINE.USER_FULLNAME,

    ASSET_OWNER.USER_NAME As ASSIGNEE_LOGIN,

    ASSET_OWNER.EMAIL As ASSIGNEE_EMAIL,

    ASSET_DATA_5.FIELD_10004,

    ASSET_DATA_5.FIELD_10005,

    MACHINE.OS_NAME,

    MACHINE.CS_MANUFACTURER,

    MACHINE.CS_MODEL,

    MACHINE.RAM_TOTAL,

    round(Sum(MACHINE_DISKS.DISK_SIZE), 2) As MACHINE_DISKS_DISK_SIZE,

    DELL_ASSET.SHIP_DATE

From

    MACHINE Left Join

    ASSET On ASSET.MAPPED_ID = MACHINE.ID

        And ASSET.ASSET_TYPE_ID = 5 Left Join

    USER ASSET_OWNER On ASSET_OWNER.ID = ASSET.OWNER_ID Left Join

    ASSET_DATA_5 On ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID Left Join

    MACHINE_DISKS On MACHINE_DISKS.ID = MACHINE.ID Inner Join

    DELL_ASSET On DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER Inner Join

    DELL_WARRANTY On DELL_WARRANTY.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER

Group By

    MACHINE.ID,

      DELL_ASSET.SHIP_DATE

Order By

    ASSET_DATA_5.FIELD_10004



0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 1 year ago
Red Belt
0

Try changing these inner joins:

Inner Join DELL_ASSET On DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER 
Inner Join DELL_WARRANTY On DELL_WARRANTY.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER

to left joins:

LEFT Join DELL_ASSET On DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER
LEFT Join DELL_WARRANTY On DELL_WARRANTY.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER




Comments:
  • Hi,
    Your a star. I managed to get it working with your help but only needed part of it. I did the below and it does exactly what I need so thanks

    I changed DELL_ASSET On

    DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER Inner Join

    to
    DELL_ASSET On DELL_ASSET.SERVICE_TAG = MACHINE.BIOS_SERIAL_NUMBER Left Join - Karllap 1 year 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