K1000 Report help - Device Ship date, Name, Model, Chassis Type, Service Tag, Last Check-in
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!
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
6 years ago
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
Posted by:
chucksteel
6 years ago
Top Answer
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.
