/build/static/layout/Breadcrumb_cap_w.png

K1000 report: Day count since Ship Date

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!

2 Comments   [ + ] Show comments
  • Oh, the environment is running on v 6.0 - mchawk 5 years ago
  • This report only shows Windows 7, not windows 8 or windows 10. I can't figure out why. - jlackman 3 years ago

Answers (2)

Answer Summary:
Posted by: h2opolo25 5 years ago
Red Belt
1
what exactly is "ship date"? Is it a custom field of type timestamp in your ticket?
Posted by: aragorn.2003 5 years ago
Red Belt
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

Comments:
  • 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! - mchawk 5 years ago
  • 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 - mchawk 5 years ago
  • 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. - mchawk 5 years ago
    • Cool mchawk. DidnĀ“t know the DELL_ASSET table. You can use the code style only in answers, not in comments. - aragorn.2003 5 years ago
  • Much appreciated as it helps - hjansari 5 years ago
  • Any way you could help me out with this? http://www.itninja.com/question/k1000-report-help-device-ship-date-name-model-chassis-type-service-tag-last-check-in - ISEKOLD 2 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ