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…..