Dell Kace: Reporting on Custom Inventory Fields via SQL or Kace Reporting
I am interested in writing a SQL query to pull information that is stored in custom labels under Inventory > Device Detail > Software > Custom Inventory Fields. In what table are these fields? I am storing Mac Warranty information, and want to know which machines that are Macs expire on what dates. The warranty End date is stored in the custom inventory fields. If there is a way to do this using the Kace interface, I can't find it (even under reporting).
Answers (2)
This information should also be available in the Reporting Wizard, which you could then edit in mySQL if needed.
Custom inventory data is in the MACHINE_CUSTOM_INVENTORY table. You'll need the ID for the Software title given to the custom inventory rule to query the data you are looking to retrieve.
Comments:
-
Which table does the ID for the software title correspond too, so I know the machine information? - ems296 9 years ago
-
Software titles are stored in the SOFTWARE table, machines in the machine table. I normally use the adminui when logging into KACE so I just get the software ID from the URL when viewing the software title. You could search the SOFTWARE table for that information as well, but getting it from the URL is faster, I think.
To get the machine information you need to make a join to the MACHINE table. Here's an example query that might help:
SELECT MACHINE.NAME, date(MACHINE_CUSTOM_INVENTORY.DATE_FIELD_VALUE) as PURCHASE_DATE
FROM ORG1.MACHINE_CUSTOM_INVENTORY
JOIN MACHINE on MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
WHERE SOFTWARE_ID = <software id>
Be sure to change <software id> appropriately.
I would recommend using a tool like MySQL Workbench to browse the tables on the appliance. This will allow you to query the tables and see what information is in them. The database layout is pretty good and the column names for different IDs almost always line up well (for instance the ID key from the SOFTWARE table is pretty much always SOFTWARE_ID when it appears in other tables). - chucksteel 9 years ago
