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.

 

 

Answer Summary:
Cancel
3 Comments   [ + ] Show Comments

Comments

  • After upgrading to v5.4 you need to remove all instances of KBSYS. from table calls to avoid access denied to 'R1'@localhost
  • Hi guys, i was running a similar report on our kace but i got this error instead;

    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.
  • what im trying pull is dell service info from machine hardware with fields having service tag detail, system type, ship date, country and last updated
Please log in to comment

Answers

3

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
_______________________________________

 

Answered 04/13/2012 by: jverbosk
Red Belt

  • I tried running the third option and it timed out and locked up my KBOX. Any ideas?
  • 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
  • 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
    • Thanks John (jverbosk)! Works like a charm!

      Jenny Lynne
  • Glad to hear it! ^_^

    John
  • Great , thanks you
  • 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;
    • This content is currently hidden from public view.
      Reason: Removed by member request
      For more information, visit our FAQ's.
Please log in to comment
Answer this question or Comment on this question for clarity