Report for End Of Life Workstations
Hello all,
I am new to KACE and I created a report that pulls the computer name and purchase date for all computer with a workstation label. I am wondering how I would add a new column to this with an end of life date that is 5 years added onto the purchase date.
I am new to KACE and I created a report that pulls the computer name and purchase date for all computer with a workstation label. I am wondering how I would add a new column to this with an end of life date that is 5 years added onto the purchase date.
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
chucksteel
6 years ago
Here is the report that I use for Dell computers:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
DA.SHIP_DATE AS "Ship Date",
MAX(DW.END_DATE) AS "Warranty End Date",
DW.SERVICE_LEVEL_DESCRIPTION as "Service Level",
DATE_ADD(DA.SHIP_DATE, INTERVAL 5 YEAR) as "Replacement Date"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
and LABEL.NAME = "Library Services"
GROUP BY MACHINE.NAME
ORDER BY `Warranty End Date`, MACHINE.NAME
You can change the LABEL.NAME = "Library Services" to the appropriate label.
Comments:
-
Thank you! This worked perfectly. One question, how do I change the ship date to purchase date. - jrunkles1221 6 years ago
-
I don't think that the database actually stores the purchase date, unless you are capturing that elsewhere. - chucksteel 6 years ago
-
It is something that we put in the asset field, when I create the report through the wizard it comes up with ASSET_DATA_5.FIELD_10007 for the purchase date. - jrunkles1221 6 years ago
Posted by:
chucksteel
6 years ago
We also store the purchase date in the asset, here is a report that joins to the ASSET and ASSET_DATA_5 tables:
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
ASSET_DATA_5.FIELD_10007 AS "Purchase Date",
DATE_ADD(ASSET_DATA_5.FIELD_10007, INTERVAL 5 YEAR) as "Replacement Date"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
LEFT JOIN ASSET on ASSET.MAPPED_ID = MACHINE.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
and LABEL.NAME = "Library Services"
GROUP BY MACHINE.NAME
ORDER BY MACHINE.NAME
I set the columns to FIELD_10007, it is a different field number in my database, but this should work for you. If you are storing purchase data for other manufacturers then you can remove the restriction in the where clause that limits these results to just Dell computers.
For instance, here is the basic form of the report that we use for replacement projections:
SELECT M.ID as UID, M.NAME, M.MAC, M.IP, M.OS_NAME,
M.BIOS_SERIAL_NUMBER, CS_MANUFACTURER, CS_MODEL, RAM_TOTAL,
LAST_USER, USER_FULLNAME, MONITOR,
ASSET_DATA_1.FIELD_40 AS LIAISON,
LOCATION.NAME as LOCATION,
ASSET_DATA_5.FIELD_32 as "PO Number",
ASSET_DATA_5.FIELD_10013 as "PO Date",
ASSET_DATA_5.FIELD_10015 as "Upgrade Cycle",
DATE_ADD(CAST(ASSET_DATA_5.FIELD_10013 as DATE), INTERVAL ASSET_DATA_5.FIELD_10015 YEAR) as "Upgrade Date",
USER.FULL_NAME as "Assigned To",
USER.USER_NAME as "Assigned to User"
FROM MACHINE M
LEFT JOIN ASSET on ASSET.MAPPED_ID = M.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET LOCATION on LOCATION.ID = ASSET.LOCATION_ID
LEFT JOIN ASSET_DATA_1 ON ASSET_DATA_1.ID = LOCATION.ASSET_DATA_ID
LEFT JOIN ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
LEFT JOIN USER on USER.ID = ASSET.OWNER_ID
Note that our computer asset also includes a "Upgrade Cycle" field (FIELD_10015 for us) that holds the number of years that the asset will be in the field. We have different cycles for laptops and desktops, so this report accounts for that.
Comments:
-
Awesome! Thank you for this info! - jrunkles1221 6 years ago
-
So I have the below code which pulls all the info I need but does not do the addition part. Am I missing something
SELECT DISTINCT(MACHINE.NAME),
MACHINE.OS_NAME,
MACHINE.CS_MODEL,
MACHINE.CS_MANUFACTURER,
ASSET_DATA_5.FIELD_10008 AS "Purchase Date",
DATE_ADD(ASSET_DATA_5.FIELD_10008, INTERVAL 5 YEAR) as "Upgrade Date"
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG
LEFT JOIN DELL_WARRANTY DW on MACHINE.BIOS_SERIAL_NUMBER = DW.SERVICE_TAG
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
LEFT JOIN ASSET on ASSET.MAPPED_ID = MACHINE.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE LABEL.NAME = "Workstations"
GROUP BY MACHINE.NAME
ORDER BY MACHINE.NAME - jrunkles1221 6 years ago-
The asset data field might be a text field, so it won't do the date addition. Replace that line with this:
DATE_ADD(CAST(ASSET_DATA_5.FIELD_10008 as DATE), INTERVAL 5 YEAR) as "Upgrade Date", - chucksteel 6 years ago-
I actually did steal that from your examples and tried it with still no result in the "Upgrade Date" column. Instead of a Date_Add is there a regular add option? In excel i can add 1825 (days in a year) to it and get an output. - jrunkles1221 6 years ago
-
Are you sure you are selecting the right field? In your previous comment you said FIELD_10007, but in your query you have FIELD_10008. Is the purchase date correct? - chucksteel 6 years ago
-
Yes, the 10007 was actually giving me the warranty date, 10008 is giving the correct date. - jrunkles1221 6 years ago
-
How is your computer asset configured to match the inventory? Under Assets, Asset Types, Computer we have Mapped Inventory Field set to Serial Number and Matching Asset Field set to Name. - chucksteel 6 years ago
Posted by:
Hobbsy
6 years ago
You would probably want to create a ticket rule that address only your device assets and adds 5 years to the purchase date
Comments:
-
Thank you for the response, I am still very new to Kace and am not sure what you mean by a ticket rule. - jrunkles1221 6 years ago
