Hello,

I am trying to generate a device report with warranty information, however, when I create a report using the wizard, the formatting of the report doesn't make too much logic. I wanted to create a report that can display the outcome below, however, I am not too familiar using SQL for generate a report. Does anyone have a similar report or can tell me how to generate a similar report?

Device IDUser LoggedSystem ModelLocationDell Service TagShip DateDays RemainingEnd DateProviderShip Date











Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • All our computers are labeled. How would I add that to the report? I would like to sort by labels.
Please log in to comment

Answer Chosen by the Author

1
One of the reasons the wizard report might not look right is that your Dell's may have two warranties, they tend to ship with a one year warranty and also an extended warranty. Because of this you want to look at the maximum warranty date. This report captures most of what you are looking to find:
SELECT  DISTINCT(MACHINE.NAME), MACHINE.ID, MACHINE.BIOS_SERIAL_NUMBER,
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",
MACHINE.USER_LOGGED
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME

Answered 12/14/2015 by: chucksteel
Red Belt

  • Thanks! This report worked out just as I wanted. A small question though, If I wanted to add a custom asset field to this report, how would I do that?
    • You need to join to the MACHINE_CUSTOM_INVENTORY table which requires the ID of the custom inventory field. Open the custom inventory field in Inventory/Software using the adminui to find the ID (it will be part of the URL). If the ID is 59336 then the join looks like this:

      JOIN MACHINE_CUSTOM_INVENTORY FIELD on FIELD.ID = MACHINE.ID and FIELD.SOFTWARE_ID = 59336

      That line goes with the other JOIN lines in the above query. Now in the select portion of the statement you have access to the columns in that table as FIELD.COLUMN_NAME. If your custom inventory rule is pulling in a string, then you need FIELD.STR_FIELD_VALUE.

      To make the MySQL a little more user friendly you can set the alias for the custom field appropriately. For instance, if you have a custom inventory rule that is setting the location I would recommend setting up the join like this:

      JOIN MACHINE_CUSTOM_INVENTORY LOCATION ON LOCATION.ID = MACHINE.ID and LOCATION.SOFTWARE_ID = 59336

      You would then get the string value using
      LOCATION.STR_FIELD_VALUE
      • All our computers are labeled. How would I add their coinciding labels to the above report? I would like to sort by labels.
Please log in to comment

Answers

0
To answer clarkml's follow up question:
In order to include machine labels you need two additional joins. The first is to the MACHINE_LABEL_JT table, this gets the relationships between computers and labels. The second is to the LABEL table which gets the label information. This query will include all of the labels assigned to computers:
SELECT  DISTINCT(MACHINE.NAME), MACHINE.ID, MACHINE.BIOS_SERIAL_NUMBER,
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",
MACHINE.USER_LOGGED,
GROUP_CONCAT(LABEL.NAME) as LABELS
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
LEFT JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
LEFT JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME
If you want to include labels from a specific label group there is another join you need to make, to the LABEL_LABEL_JT table. For example, we have labels for locations on our campus. The Locations label group has an ID of 439 in our database. This query includes just labels from that group in a column:
SELECT  DISTINCT(MACHINE.NAME), MACHINE.ID, MACHINE.BIOS_SERIAL_NUMBER,
MACHINE.OS_NAME, 
MACHINE.CS_MODEL, 
MACHINE.CS_MANUFACTURER, 
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",
MACHINE.USER_LOGGED,
LABEL.NAME as LOCATION
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
LEFT JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
LEFT JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 439)
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
AND LABEL_LABEL_JT.LABEL_ID = 439
GROUP BY MACHINE.NAME
ORDER BY "Ship Date", MACHINE.NAME

Note that the ID for the group label is specified in the join statement and in the where clause.
Answered 02/08/2016 by: chucksteel
Red Belt

  • Thanks so much chucksteel!!! Where do I find the label ID. I just created a label group.
    • If you login to your appliance using /adminui instead of /admin it will be in the URL when editing the label group.
      • Got it!! Awesome. Thanks, again.
Please log in to comment
Answer this question or Comment on this question for clarity