Kace - Create Dell Warranty Report
I'm new to Kace and trying to figure out how to get a report for my Dell equipment's warranty expiration sorted by the expiration date and showing the computers name, service tag, and expiration. I have tried to modify the Dell Warranty reports that came loaded with Kace but it’s not working, any assistance or suggestions would be greatly appreciated.
Answers (1)
Here's a few versions for you.
The first does what you asked for (with a few more columns I find useful in my own environment).
The second one is sorted and grouped by machine name, which is useful when a machine has more than 1 warranty tagged to it (fairly common in my environment).
The third one only pulls warranties for systems in the "server" label group (an LDAP label I have setup, but you could use any type). Just change the next to the last line from "%server%" to whatever machine label you want to target and it should work (thanks to dchristian for the JOIN statements targetting a label in that one - the built-in report's statements for doing the same thing weren't working well for me in this report, probably due to my own MySQL ignorance).
To create any of these, go to Reporting > Reports > Choose Action > Add New SQL Report and populate it with this info and then hit Save.
Hope that helps!
John
_______________________________________
*Title*
Warranties by Expiration Date
*Category:
Warranty (Custom)
*Description*
Machine warranty report (sorted by exipration date ascending)
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
JOIN KBSYS.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 '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
ORDER BY EXPIRATION_DATE, DW.SERVICE_LEVEL_DESCRIPTION
Show Line Number Column: x
_______________________________________
*Title*
Warranties by Machine
*Category:
Warranty (Custom)
*Description*
Machine warranty report (sorted by machine
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
JOIN KBSYS.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 '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
ORDER BY MACHINE_NAME, EXPIRATION_DATE DESC, DW.SERVICE_LEVEL_DESCRIPTION
*Break on Columns*
MACHINE_NAME
Show Line Number Column: x
_______________________________________
*Title*
Warranties for Server Label
*Category:
Warranty (Custom)
*Description*
Server warranty report (only pulls machines in "server" label group)
*SQL Select Statement*
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
JOIN KBSYS.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)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND L.NAME LIKE '%server%'
ORDER BY MACHINE_NAME, EXPIRATION_DATE DESC, DW.SERVICE_LEVEL_DESCRIPTION
*Break on Columns*
MACHINE_NAME
Show Line Number Column: x
_______________________________________
Comments:
-
I tried running the third option and it timed out and locked up my KBOX. Any ideas? - sburkey2 11 years ago
-
Ditto here, the first two reports run OK, but the third keeps running (I'm using MySQL Workbench to test) and I have to abort. I modified the query to look for 'L.NAME LIKE '%IST-Computers%' . IST-Computers is a Label which I have manually applied to a specific group of computers in our default KBOX organization.
What else can I check to see what is happening?
JBC - joncutler 11 years ago -
Make sure you have a label named "server" that targets your servers (second to the last line of SQL code), or change it to a label you already are using:
AND L.NAME LIKE '%your server label here%'
That's probably what's locking things up.
John - jverbosk 11 years ago -
-
Great , thanks you - sandeeprai 10 years ago
-
I use pretty much the same report with a few modifications. I found that ordering by DW.END_DATE I can add it to my custom spreadsheet I send to the customers easier. I would be curious to know if I could upload my custom spreadsheet into K1000 so that when I pull these reports it already comes on this spreadsheet. Thoughts?
SELECT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, USER_FULLNAME, M.USER_NAME, DA.SERVICE_TAG, DA.SHIP_DATE,
DW.SERVICE_LEVEL_CODE, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER,
DW.END_DATE AS EXPIRATION_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.SERVICE_TAG OR M.BIOS_SERIAL_NUMBER = DA.PARENT_SERVICE_TAG)
join MACHINE_LABEL_JT on M.ID = MACHINE_LABEL_JT.MACHINE_ID
WHERE MACHINE_LABEL_JT.LABEL_ID = 62
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
AND DW.END_DATE = (SELECT MAX(END_DATE) FROM DELL_WARRANTY DW2 WHERE DW2.SERVICE_TAG=DW.SERVICE_TAG AND DW2.SERVICE_LEVEL_CODE=DW.SERVICE_LEVEL_CODE)
order by DW.END_DATE; - Zach_Lundberg 9 years ago
mysql error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'DELL_WARRANTY'] in EXECUTE(
"SELECT DISTINCT M.NAME AS MACHINE_NAME, M.CS_MODEL AS MODEL, DA.SERVICE_TAG, DW.SERVICE_LEVEL_DESCRIPTION, DW.SERVICE_PROVIDER, DW.END_DATE AS EXPIRATION_DATE
FROM KBSYS.DELL_WARRANTY DW
JOIN KBSYS.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 '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
AND DA.DISABLED != 1
ORDER BY EXPIRATION_DATE, DW.SERVICE_LEVEL_DESCRIPTION LIMIT 0")
Can anyone point out to me what could have gone wrong? Thank you. - Pava 10 years ago