Auditing Kace assets through the API/Database
Hello everyone,
Between our old appliance and new appliance, I have spent over 100 hours fixing and maintaining our asset section in Kace. After completing another round of fixes for our asset management project, I have considered creating a daily task/integration to email myself and the support manager of assets created over the previous day, as well as the associated data (blank or populated) and who created the asset. Using the Kace API, I was able to get a response of my asset, just to see exactly what is returned. This is great because I can query the API to return something created between a date range.
{
"Count": 1,
"Warnings": [],
"Assets": [
{
"id": 947,
"asset_type_id": 5,
"name": "redacted",
"asset_data_id": 941,
"owner_id": 19,
"modified": "2021-11-02 12:39:48",
"created": "2020-08-21 15:26:52",
"mapped_id": 414,
"asset_class_id": 11006,
"archive": "",
"asset_status_id": 493,
"field_10036": "110.12",
"field_10011": "Primary",
"field_10012": "Replacement Cycle",
"field_10037": "2018-07-20",
"field_10031": "2018-07-23",
"field_10038": "2022-07-23",
"field_10033": "382499340",
"field_10004": "",
"field_10005": "",
"field_10009": "",
"field_10010": "",
"asset_type_name": "Computer",
"asset_subtype_name": "AIO",
"field_10034": {
"id": 1501,
"asset_type_id": 2,
"name": "redacted",
"owner_id": 0,
"asset_class_id": 0,
"asset_status_id": 0
},
"location": {
"id": 1036,
"asset_type_id": 1,
"name": "redacted",
"owner_id": 0,
"asset_class_id": 0,
"asset_status_id": 0
},
"Associations": [
{
"id": 4359,
"asset_type_id": 10212,
"name": "redacted",
"owner_id": 19,
"asset_class_id": 0,
"asset_status_id": 493
},
{
"id": 4360,
"asset_type_id": 10212,
"name": "redacted",
"owner_id": 19,
"asset_class_id": 0,
"asset_status_id": 493
}
],
"Machine": {
"id": 414,
"name": "redacted",
"manual_entry": 0
},
"owner": {
"id": 19,
"user_name": "redacted",
"email": "redacted",
"full_name": "redacted"
}
}
]
}
Unfortunately it appears that the Kace API does not include who created the asset (or how it was created), but I know I can find that in ASSET_HISTORY with some clever SQL
The problem with this is the ASSET_HISTORY table is currently 10M+ rows, and ASSET_HISTORY.NAME is the name of machine mapped to the asset. If procedure is followed properly, the asset would be preloaded (and thus searchable by the serial number, is this correct?).
Which brings up asset history retention.. The default is 3 months, with the option to make sure the data is retained forever. I am worried about how this would slow down auditing (especially when having to use SQL to grab who created the asset) or performance of the K1000.
Does anyone have any experience with asset auditing and if so, what were you looking to track and how did you accomplish it?
Any suggestions would be great.
Answers (1)
First it sounds as if you need to check how your Inventory and Asset Records are linked, that is done in the Device Asset type. We find adding a Serial Number field to the asset record and then linking Inventory to Asset via the BIOS Serial Number and that asset serial number field works best. That is because it maintains a strong link should you rename or rebuild any device whilst still naming the Asset record the same as the Device Name.
I am guessing you have a particular view of what "Auditing" means in your environment? In our BarKode Asset Scanner for the KACE SMA, we have audit functionality based on user or location as this enables location based scanning audits i.e. you have physically seen the asset, as well as enabling Starters and Leavers processes.
Any data you want has to ultimately come from reporting as the API is not really configured to give comprehensive data in that way.
We are currently working on a set of ticket rules that will append key asset audit information into an asset notes field. This will mean that full audit history will be viewable when scanning a barcode in our BarKode app. It will also make reporting potentially quicker and also keep key data beyond the restrictions of the Asset History Retention function.
If you would like us to explore those possibilities for your organisation, please feel to get in touch so we can discuss.
#WeAreQuest
Comments:
-
Is there any practical use for the [GET] API when I have a good handle on our database as well as SQL? I can see how PUT/DELETE/POST could be useful for us in other ways.
Here's the mappings we have for asset types.
ASSET -> ASSET_DATA_5 (Device)
ASSET -> ASSET_DATA_10212 (Monitor)
ASSET -> ASSET_DATA_10211 (Docking Station)
ASSET -> ASSET_DATA_10210 (UPS)
ASSET -> ASSET_DATA_10205 (AV Equipment)
ASSET -> ASSET_DATA_10206 (Network Equipment)
ASSET -> ASSET_DATA_10213 (Peripheral)
ASSET -> ASSET_DATA_10214 (Tablet)
We use ASSET.NAME as the device's serial number and have the mapping as ASSET.NAME -> MACHINE.BIOS_SERIAL_NUMBER. In our old appliance we had an extra serial number field which seemed redundant since we had it set up this way too. In the new appliance that's what I went with since it's what I was used to, and it works fine as long as people touching assets are properly trained. (Retention is a separate issue outside of Kace).
I wanted a way to audit who made an asset, when they made it, the asset type, the data fields that are populated, and what data fields are empty. We have had a bad problem with this in the past.
I strayed away from the API and began working on a python script that I may throw on a server to run daily and export the results to excel and send an email.
I don't know enough SQL to actually get a report this complicated since we have so many different asset types and I am relying on ASSET_HISTORY.
Do you know if there are any issues with increasing the asset history retention period? Is the BarKode app something you use internally or can anyone use it? We have been using the Kace Go app but it's a little outdated I think.
(Can't get formatting to work in this comment.. sorry)
import mysql.connector
formattedAssetList = []
class Computer:
asset_name = ""
date_created = ""
created_by = ""
asset_status = ""
asset_loc = ""
asset_dept = ""
assignment_type = ""
asset_room = ""
assigned_to = ""
funds_used = ""
purchase_date = ""
replacement_date = ""
assigned_date = ""
order_number = ""
def __init__(self, asset_name, date_created, created_by):
self.asset_name = asset_name
self.date_created = date_created
self.created_by = created_by
kdb = mysql.connector.connect (
host="redacted",
user="R1",
password="redacted",
database="ORG1",
raw=False
)
cursor = kdb.cursor()
#Grab serial numbers/Date Created/Who Created it, load into a list
cursor.execute("select distinct ASSET.NAME, ASSET_HISTORY.TIME, ASSET_HISTORY.USER_TEXT, ASSET_HISTORY.VALUE1 from ASSET_HISTORY join ASSET on ASSET.ID = ASSET_HISTORY.ASSET_ID where ASSET_HISTORY.CHANGE_TYPE = 'Creation' and ASSET_HISTORY.TIME between NOW() - INTERVAL 1 DAY and NOW() order by ASSET_HISTORY.TIME desc")
rawAssetList = cursor.fetchall()
#Run through the list, getting each serial number, then lookup the rest of the data and load into an object, adding that object to a list
for x in rawAssetList:
if x[3] == 'Computer':
formattedAssetList.append(Computer(x[0],x[1].strftime('%m/%d/%Y %H:%M:%S'), x[2])) - mlizbeth 2 years ago-
if you are looking to see who it was that created the Asset then you will have to look to the ASSET_HISTORY to find that data. In there if the CHANGE_TYPE column is equal to CREATED you will then be able to read the USER_ID of the analyst that created the record. What I would suggest is that you add a custom field to each asset and then create a ticket rule to add the name of the analyst that created the asset record into that custom field if you need to have the value as reportable. - Hobbsy 2 years ago
