K1000 Device Report with Warrenty Information
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 ID | User Logged | System Model | Location | Dell Service Tag | Ship Date | Days Remaining | End Date | Provider | Ship Date |
1 Comment
[ + ] Show comment
-
All our computers are labeled. How would I add that to the report? I would like to sort by labels. - clarkml 8 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
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
Comments:
-
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? - pregiec 8 years ago
-
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 - chucksteel 8 years ago-
All our computers are labeled. How would I add their coinciding labels to the above report? I would like to sort by labels. - clarkml 8 years ago
-
Hey Guys,
Thanks for sharing this code. It has helped me on getting an inventory report on warranty. There is a line item I am trying to figure out. I am not a coding expert or anything. I noticed that in the code, it has the line item
MAX(DW.END_DATE) AS "Warranty End Date",
reflects the " Dell Digitial Delivery" end date instead of the "ProSupport" or "Onsite Service After Remote Diagnosis (Consumer Customer)/ Next Business Day Onsite After Remote Diagnosis (Commercial Customer)."
Do I have to define a value for "ProSupport" or something to get the accurate "Warranty End Date"? - cthai168 4 years ago-
this post is 4 years old you may want to create a new question and state you found this report (paste in the report and https://www.itninja.com/question/k1000-device-report-with-warrenty-information) and need help modifying it - SMal.tmcc 4 years ago
-
Thanks SMal.tmcc. I will do that moving forward. - cthai168 4 years ago
-
Yeah, Dell started to include those on their systems at some point. The easiest way to exclude those is to add a line in the where clause.
After this line:
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
Add this:
and DW.SERVICE_LEVEL_DESCRIPTION not like '%Digital%'
If you only want to report on the ProSupport warranty, then you would use a line like:
and DW.SERVICE_LEVEL_DESCRIPTION like '%ProSupport%' - chucksteel 4 years ago-
Thanks Chucksteel! It works, but the warranty date is off by 2 days in the report. What I did was make the adjust change from "max" to "date"
MAX(DW.END_DATE) AS "Warranty End Date", - cthai168 4 years ago
Posted by:
chucksteel
8 years ago
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.
Comments:
-
Thanks so much chucksteel!!! Where do I find the label ID. I just created a label group. - clarkml 8 years ago
-
If you login to your appliance using /adminui instead of /admin it will be in the URL when editing the label group. - chucksteel 8 years ago
-
Got it!! Awesome. Thanks, again. - clarkml 8 years ago
