/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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.
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Community Chosen Answer


Answers

2
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.
Answered 03/02/2018 by: chucksteel
Red Belt

  • Thank you! This worked perfectly. One question, how do I change the ship date to purchase date.
    • I don't think that the database actually stores the purchase date, unless you are capturing that elsewhere.
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Answers

1
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.


Answered 03/02/2018 by: chucksteel
Red Belt

  • Awesome! Thank you for this info!
  • 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
    • 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",
      • 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.
      • 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?
      • Yes, the 10007 was actually giving me the warranty date, 10008 is giving the correct date.
      • 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.
Please log in to comment
0
You would probably want to create a ticket rule that address only your device assets and adds 5 years to the purchase date
Answered 03/02/2018 by: Hobbsy
Red Belt

  • Thank you for the response, I am still very new to Kace and am not sure what you mean by a ticket rule.
Please log in to comment
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share