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 9 years ago
-
This report only shows Windows 7, not windows 8 or windows 10. I can't figure out why. - jlackman 7 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
h2opolo25
9 years ago
what exactly is "ship date"? Is it a custom field of type timestamp in your ticket?
Posted by:
aragorn.2003
9 years ago
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 9 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 9 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 9 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 9 years ago
-
Much appreciated as it helps - hjansari 9 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 6 years ago
