Let's pretend that every time a specific computer checked in you wanted to update the location field in it's computer asset record. Since there is no location field by default let us pretend that you have a location field in your MACHINE record. It could be a custom data field but for this we'll just assume the location is in the MACHINE.SYSTEM_DESCRIPTION field (which is the computer description on windows).
You could do this with a ticket rule but then it would be on a schedule and it would update every asset. This only updates what is needed when the inofrmation changes -- on inventory.
- download this chrome extension I wrote ( https://chrome.google.com/webstore/detail/kbox-javascript-demo/domgdoodlbgoeblglfokpbchcfjnecoa)
- open the detail of an example computer asset (note: URL will be something like http://k1000/adminui/asset.php?ID=113 )
- launch the chrome extension running the "assetClarity" demo
- hover over the location field you are using
- find out what the query is the reveals it. If you're using mostly OEM asset settings for location then the query will be very similar to this (but not exactly the same):
select A5.ID, A5.NAME, AA.ASSET_FIELD_ID, A5assoc.ID "Associated ID", A5assoc.NAME "Associated Name", A5assoc.ASSET_TYPE_ID "Associated Type ID", ASSET_TYPE.NAME "Associated Type Name"
If you have a vastly different query then this process is not for you
from ASSET_DATA_5 D5
join ASSET A5 on A5.ASSET_DATA_ID = D5.ID and A5.ASSET_TYPE_ID=5
left join ASSET_ASSOCIATION AA on AA.ASSET_FIELD_ID=19 and AA.ASSET_ID = A5.ID
left join ASSET A5assoc on A5assoc.ID = AA.ASSOCIATED_ASSET_ID and A5assoc.ASSET_TYPE_ID=1
left join ASSET_DATA_1 D5assoc on D5assoc.ID = A5assoc.ASSET_DATA_ID
left join ASSET_TYPE on ASSET_TYPE.ID = A5assoc.ASSET_TYPE_ID
where A5.ID = 2 /* this will be difference */ - Because we're dealing with the more complex case of an asset related to an asset we'll need two smart labels. This one is a smart label that runs (at order 1) to remove any existing location references.
delete from AA
using MACHINE
join ASSET A on A.MAPPED_ID=MACHINE.ID
join ASSET_FIELD_DEFINITION F on F.FIELD_TYPE='ASSET_1' and F.ASSET_TYPE_ID=5
join ASSET_ASSOCIATION AA on AA.ASSET_ID=A.ID and AA.ASSET_FIELD_ID=F.ID
where 1=1 - Use the query in step #5 to create a smart label to update the asset information (actually an insert). If we're talking about the OEM location field then the query would be exactly as follows. Updates of other asset fields may be unique to your kbox.
insert ignore into ASSET_ASSOCIATION (ASSET_ID, ASSET_FIELD_ID, ASSOCIATED_ASSET_ID) select C.ID , F.ID, L.ID from ASSET C join MACHINE on MACHINE.ID=C.MAPPED_ID and C.ASSET_TYPE_ID=5 join ASSET L on L.ASSET_TYPE_ID=1 and L.NAME=MACHINE.SYSTEM_DESCRIPTION join ASSET_FIELD_DEFINITION F on F.ASSET_TYPE_ID=C.ASSET_TYPE_ID
and F.FIELD_TYPE='ASSET_1' left join ASSET_ASSOCIATION AA on AA.ASSET_ID=C.ID and AA.ASSET_FIELD_ID=F.ID where AA.ASSET_ID is null /* did not already exist any relationship from this machine to location */
disclaimer: all of my posts are my own personal opinion (and sometimes even insight). I give this information to the community to support. Even though I work for Dell Kace there is no implied contract between Dell Kace and you over the support of this information. I will often refer to core features of the product that are supported by engineering and technical support but I will also often refer to information that pushes the product beyond its specification. Any technical documents that I write about supported information I will continue to do so via our infrastructure for officially supported material (e.g. knowledge base articles) which is at http://www.kace.com/support. You are welcome to contact me directly about this information but you will have the most success by responding with comments directly on my posts where I or the community can help you.
Comments