In this Blog we discuss how you may want to apply the saved location from your SMA User import from AD, to any asset that is assigned to that user as an owner.
So if the user location is “London”, any asset assigned to them within the KACE SMA is then also associated with “London”
The theory of this is that you are copying the ID for the location assigned to the User, into the Asset table location ID for any Device (asset type 5)
To do this create a ticket rule, so go to Service Desk > Configuration
Select Rules, then Choose Action – New SQL
Give the rule a title
Enter a description of what the rule does, if you like?
Paste the following SQL into the Select Statement box
SELECT ASSET.ID, USER.LOCATION_ID as location_ID
FROM ASSET ASSET
INNER JOIN USER USER ON (ASSET.OWNER_ID = USER.ID)
WHERE (ASSET.ASSET_TYPE_ID = 5)
Tick “Run Update Query and Paste the following SQL into the Update Statement Box
UPDATE ASSET ASSET
INNER JOIN USER USER ON (ASSET.OWNER_ID = USER.ID)
set ASSET.LOCATION_ID = USER.LOCATION_ID
where (ASSET.ID in (<TICKET_IDS>))
and when you run the rule, if an owner is assigned to a Device the location of the owner will be applied to the Device Asset Record.
If you want to test this on a single asset first, you will need to do the following steps:
1, Identify a device in Inventory, preferably without a location defined
2, Assign an Owner in the Inventory record
3, Scroll down to the Asset section and display the Asset data
4, Click to Edit the Asset
5, Record the Unique Asset ID for the device at the top of the page.
6, Go back and edit your ticket rule and alter the final line to say
WHERE (ASSET.ID = 5) but change the 5 to your recorded Asset ID from step 5
7, Go to the User record for the User you Assigned the Device to and set them a fresh location.
8, Save the User record and then go back an run your ticket rule
Hopefully you should see a single record as updated, and when you go back to the device in inventory it should now have the Owner’s location showing as the Devices Location.
NB Don’t forget to set your ticket rule select statement back to the original text and maybe set the rule to run once a day.
I hope that works…..