/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Automatically Assign Asset Location Based on the Assigned User Location

05/18/2020 505 views

Hello,

Our users are automatically pulled in from AD and the user location is populated from the "Office" field in AD.

I was wondering if there was a way to assign the asset (computer) location based on the assets assigned users location.


Thanks

Bob


0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

2

po0naQQzstZvLLSEXQdforrnyH8BBKpwP2i9VK9qXkASwbPVz1tBW4mkYyArw4UyJpTnYIof8DX41AZM6HENoAAAAASUVORK5CYII=

So you might want to try this as a potential solution. 

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

Answered 05/19/2020 by: Hobbsy
Red Belt

All Answers

0

You would need to build a ticket rule to update the fields appropriately. The challenge you will have is the join will be via the user, so the location will need to be the location in the assigned user, if that makes sense?

You may also need to think about what will happen if there is no location assigned to a user.

Answered 05/18/2020 by: Hobbsy
Red Belt

  • Unfortunately, we are not using the ticketing piece of Kace, only the inventory and software management areas. Would it still be possible?
    • Ticket rules have the rights to update every table of the KACE database, use the force wisely young Jedi ;o)
    • Are you licensed for the service desk module? If so, then you can create rules that can make changes in other parts of the DB.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ