/build/static/layout/Breadcrumb_cap_w.png
10/17/2019 97 views

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

Comments


All Answers

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



Answered 10/17/2019 by: chucksteel
Red Belt

  • 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