/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000 Report help - Device Ship date, Name, Model, Chassis Type, Service Tag, Last Check-in

01/25/2018 1255 views
I found a report that got me close to the answer I need: http://www.itninja.com/question/k1000-report-day-count-since-ship-date

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

A problem I found with this report is that it does not display any computers where the name starts with a number. For example "3N_312" does not show up in this report.

Information that is not in this report is the chassis type and last check-in. Bonus points if the report could also tell the number of monitors attached to a device (This matters because we have docking stations for laptops that are no longer compatible with new laptop hardware and need to be replaced as well. I can guess this number if needed).

I am doing this to try and find the computers that need to be replaced in our next budget year. I don't know SQL, so any help would be a huge help!

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
Adding the chassis type and last inventory date isn't difficult, here's an updated query:
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, m.CHASSIS_TYPE, LAST_INVENTORY
from MACHINE as m, DELL_ASSET as w
where m.BIOS_SERIAL_NUMBER = w.SERVICE_TAG
order by NAME ASC
Note that I changed the order to verify that machine names beginning with a number were at the top. I'm not sure why they aren't appearing for you.

As for the number of monitors, the database doesn't include that directly. If you are including the monitor in your asset history settings, you could conceivably check the ASSET_HISTORY table to find the distinct values that have been recorded for monitor on each device, but that wouldn't be a guaranteed accurate number of monitors connected to a system. I have not found a field in the database that can be used to determine if a laptop is connected to a docking station. We are also facing the end of the E-Series line that will make our current docks incompatible.

Answered 01/26/2018 by: chucksteel
Red Belt

All Answers

0
I couldn't find a ship date in the database, so I used the date the record was created i.e. the agent was installed, but there are other choices such as bios created date, however, try this SQL

SELECT MACHINE.CREATED, MACHINE.NAME AS SYSTEM_NAME, CS_MANUFACTURER, CS_MODEL, CHASSIS_TYPE, CSP_ID_NUMBER, LAST_INVENTORY  FROM MACHINE    ORDER BY CREATED

Built using the reporting wizard in KACE 
Answered 01/26/2018 by: Hobbsy
Red Belt

Don't be a Stranger!

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

Sign up! or login

View more:

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