[SOLVED] KACE K1000 - SQL Query Asset Values
Hi all,
i have a Asset called "Transponder" with these fields
i have a Asset called "Transponder" with these fields
- Name as Text
- Transponder as Single Select with values 123, 456, 789, 1010, 1123
- Date as Date
- Description as Text
So far so good.
Now I want to select the selectable values (123, 456, 789, 1010, 1123) via query, so i can select these in a ticket queue by custom field with single select.
For me I only have to change/add new transponder-ids in one table and not in the asset settings and in the queue setting.
Is that possible? I tried already with a lot of differnt SQL query, but didn't find the data i searched for.
Thanks in advance.
BR
Christian
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
StepBaer
8 years ago
Hi,
thanks for your answer - but I already tried this.
I will explain my problem a little more detailed.
This is the asset type, with the field "Transponder" and the highlighted data/values I want to select in a query
But this is only the already existing assets with the selected value, not all transponder values.
Maybe no it is more clarfied.
Thanks
BR
Christian
thanks for your answer - but I already tried this.
I will explain my problem a little more detailed.
This is the asset type, with the field "Transponder" and the highlighted data/values I want to select in a query
This is the result of
SELECT * FROM ASSET_DATA_#But this is only the already existing assets with the selected value, not all transponder values.
Maybe no it is more clarfied.
Thanks
BR
Christian
Comments:
-
Yes, that helps. The data you are looking to query is in the ASSET_FIELD_DEFINITION table. If you query that table you'll find a row that shows your values in the FIELD_VALUES column where the FIELD_NAME is "Transponder". The problem with using that table is that when you use the service desk custom fields with the query syntax I believe it is looking for rows to be returned, but you would be returning one row with a set of comma delimited values. You can try a query like SELECT FIELD_VALUES from ASSET_FIELD_DEFINITION where FIELD_NAME = "Transponder" but I'm not sure if it will work.
You could create another asset type called Transponders and use your transponder values as the names of those assets. If you need to track the data in the current Transponder asset type then it can reference the Transponders asset type, too. That way both the service desk and the Transponder asset type both reference the same data. When you need to add more transponders to the list you just create the new asset. - chucksteel 8 years ago-
Hi, sorry for my late reply. Your answer helped me and I created a new asset called "Transponder Values". No I can select them by
SELECT ASSET.Name FROM ASSET WHERE ASSET_TYPE_ID=<ID>
Thanks for your Support! :) - StepBaer 8 years ago
Posted by:
chucksteel
8 years ago
You will need to look at the database in order to know the names of the table associated with your asset type. The data should be stored in ASSET_DATA_# where # is the ID of the Transponder asset type. I recommend setting up MySQL Workbench to look at the database.
If the name of the transponder is the number, then the select statement should be (assuming the asset type ID is 10):
SELECT NAME FROM ASSET WHERE ASSET_TYPE_ID = 10
If the transponder number is only stored in the Transponder field then the select query will also need to pull from the appropriate ASSET_DATA table.
Comments:
-
Please see my answer below :) - StepBaer 8 years ago