Hi Guys,

I have a number of machines that belong to a specific department and I want to assign them to the department. Is there a way I can automate this, because I know when a machine checks in with a certain name it belongs to that department as an asset.

Thanks,

Mike
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
You could have a ticket rule that does this but it would be specific to your kbox because the field meta data for assets is unique to every customer. This might help your plight:
http://www.kace.com/support/kb/index.php?action=artikel&cat=64&id=943&artlang=en
http://www.kace.com/support/kb/index.php?action=artikel&cat=3&id=718&artlang=en


It wouldn't be automatic but if there is a name pattern you could probably create a spreadsheet quickly to update it (csv import).

A custom asset field can be a bit tricky. Assets are stored in the database in tables that are numbered. It is not obvious which table represents which asset.

The way to find out is to go to your asset type list and mouseover the asset that you are interested in. You will see the ID for the asset. (e.g how_to_detect_id_for_an_asset.png)

When you have that number then you know that the details of an asset is stored in a table called.

ASSET_DATA_X where X is the number

e.g from my screenshot the table would be ASSET_DATA_12

The list of assets is actually stored in the table ASSET. But this table only contains the names and the asset IDs. From my example, to get a list of all the assets that are of type customer company the query would be
select NAME from ASSET WHERE ASSET_TYPE_ID=12

To get all the details for these assets the query would be:
SELECT * FROM ASSET CUSTOMER_COMPANY, ASSET_DATA_12 CUSTOMER_COMPANY_DETAIL
WHERE CUSTOMER_COMPANY.ASSET_TYPE_ID=12 and
CUSTOMER_COMPANY.ASSET_DATA_ID=CUSTOMER_COMPANY_DETAIL.ID


The columns in the ASSET_DATA_X tables contain the detail. These columns are also numbered. They will have names like FIELD_X where X is an integer.

The defintions for these columns are contained in the table ASSET_FIELD_DEFINITION. If you query this table you can see which column is which.

In the picture asset_type_detail.png you can see that I have 5 fields associated with this asset type. If I do a query on ASSET_FIELD_DEFINITION I can get a mapping of the field numbers to the columns.

In this case the query is run in mysql query browser (a great tool for testing). As shown in results_of_field_definition_query.png the query is:
select * from ASSET_FIELD_DEFINITION WHERE ASSET_TYPE_ID=12

the result shows that the column "Domain Name" is mapped to id 25. So I know that field_25 in the table ASSET_DATA_12 is the column for domain.

So a query to get this information would be:
SELECT CUSTOMER_COMPANY.NAME, FIELD_25 CUSTOMERS_DOMAIN FROM ASSET CUSTOMER_COMPANY, ASSET_DATA_12 CUSTOMER_COMPANY_DETAIL
WHERE CUSTOMER_COMPANY.ASSET_TYPE_ID=12 and
CUSTOMER_COMPANY.ASSET_DATA_ID=CUSTOMER_COMPANY_DETAIL.ID


To further complicate this is the situation when assets point to other assets. Any assets that are associated to each other are done via the ASSET_ASSOCIATION table.

Here is a sample query where the computer asset refers to the location asset. The computer asset it type 5 and the location asset is type 1. The field that stores the relationship between them(in this example) is 81. In this case the value for 81 is actually an asset so NAME is used instead of FIELD_81. Field_81 doesn't exist but there is an id 81 in the asset_association table to link the two assets. field_76 and field_77 are
Traditional asset fields (and are being aliased to useful names in the query for clarity)

select
NAME, NAME AS LOCATION, FIELD_76 AS NOTES,FIELD_77 AS STATUS
from ASSET COMPUTER JOIN ASSET_DATA_5 AD ON ASSET_DATA_ID=ID and ASSET_TYPE_ID=5
LEFT JOIN ASSET_ASSOCIATION AA ON ASSET_ID=ID and ASSET_FIELD_ID=81
LEFT JOIN ASSET LOCATION ON ID=ASSOCIATED_ASSET_ID and ASSET_TYPE_ID=1
LEFT JOIN ASSET_DATA_1 AD1 ON ASSET_DATA_ID=ID
LEFT JOIN MACHINE ON MAPPED_ID=ID


I hope that this sheds some light on how to query information from assets.



Answered 03/09/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
the other two pix



Answered 03/09/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
... when a machine checks in with a certain name ...
Other possibilities are smart labels along the lines of "System Name begins with xyz...", LDAP labels for users or LDAP labels for computers if you are using Active Directory.
Answered 03/09/2012 by: RichB
Third Degree Green Belt

Please log in to comment
0
That would definitely work to label it, but it wouldn't update the asset record portion.
Answered 03/09/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Yes that's true Gerald but I'm not sure what the intent was. I was thinking they just wanted to be able to automatically see all the computers belonging to a department and in that case a smart label is the way to go.
Answered 03/09/2012 by: RichB
Third Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity