Hi everyone!

I'm relatively new to the KACE world.

I need a report that shows me how many days have passed since the ship date. I have tinkered a little bit with the ship date in the assisted method of creating reports, but my results have been poor.

I would need the machine name, service tag and number of days since ship date, sorted by the biggest number of days since ship date.

Could you help me to create the SQL query to achieve this, please? I'm a no go in SQL queries... :(

This environment runs on version 6.0

Thank you!
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Oh, the environment is running on v 6.0
Please log in to comment

Community Chosen Answer

1
what exactly is "ship date"? Is it a custom field of type timestamp in your ticket?
Answered 03/05/2015 by: h2opolo25
Red Belt

Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

0
You can use the following SQL statment. It shows you the Name, User, OS, Manufacturer, Model, Service Tag, Shipdate and the needed Daycount.


select distinct UCASE(m.NAME) as NAME, m.USER_FULLNAME, m.OS_NAME, m.CS_MANUFACTURER, m.CS_MODEL, w.SERVICE_TAG, w.START_DATE, DATEDIFF(Now(), w.START_DATE) as DayCount
from MACHINE as m, DELL_WARRANTY as w 
where m.BIOS_SERIAL_NUMBER = w.SERVICE_TAG
order by w.START_DATE DESC
Answered 03/06/2015 by: aragorn.2003
Red Belt

  • Hi! Sorry for the delay

    Thank you very much! This is on the track of what i need. The only perhaps that I found when I did the report based on this query was that there were multiple entries per workstation

    Ie.: desktop TI006 has 3 entries, and one of them the "start date" is 2015-11-28, which gave me a -262 as day count (hey, the counter worked like a charm!)

    I believe the query was based on warranty, thus the multiple entries. Since this is basically a Dell-only environment, there is a field named "ship date", which consults the service tag and gets what was the date that the equipment was shipped to us.

    I believe that this is the only adjust that is missing, as the query is perfect for what I need.

    Anyways, thank you very much again!
    • This content is currently hidden from public view.
      Reason: Removed by member request
      For more information, visit our FAQ's.
  • select distinct UCASE(m.NAME) as NAME, m.USER_FULLNAME, m.OS_NAME, m.CS_MODEL, w.SERVICE_TAG, w.SHIP_DATE, DATEDIFF(Now(), w.SHIP_DATE) as Dias_em_uso
    from MACHINE as m, DELL_ASSET as w
    where m.BIOS_SERIAL_NUMBER = w.SERVICE_TAG
    order by Dias_em_uso DESC
  • Many many thanks! I managed to open the KACE MySQL Database with MySQL Workbench and modified the options that I needed. Insteat of using the table DELL_WARRANTY, I used the DELL_ASSET, which has the SHIP_DATE entry that I wished, BTW how do I insert a box in the comments so that I respects the "enters" that I give? Like aragorn.2003 post? Thank you.
    • Cool mchawk. DidnĀ“t know the DELL_ASSET table. You can use the code style only in answers, not in comments.
  • Much appreciated as it helps
Please log in to comment
Answer this question or Comment on this question for clarity