/build/static/layout/Breadcrumb_cap_w.png

K1000 Custom Ticket Rule to Update Asset Locations

So here's the overarching view of what I'm trying to accomplish.

Computer Assets have a location field that is joined to the Location assets.   To automate some of the work for my department I'd like to create a custom ticket rule that runs daily to check for computer assets that don't have a location set, then set it depending on the name of the machine.  At this point, I'm trying to get a script that sets the location properly.  Here is the sql:

 select A.ID, A.NAME, AA.ASSET_FIELD_ID, AA.ASSET_ID, AA.ASSOCIATED_ASSET_ID, ASassoc.ID AS Associated_ID, ASassoc.NAME  AS Associated_Name,
    ASassoc.ASSET_TYPE_ID AS Associated_Type_ID, ASSET_TYPE.NAME AS Associated_Type_Name

from ASSET_DATA_5 AD5
join ASSET A on A.ASSET_DATA_ID = AD5.ID  and A.ASSET_TYPE_ID=5
JOIN MACHINE M ON M.ID = A.MAPPED_ID
left join ASSET_ASSOCIATION AA on AA.ASSET_ID and AA.ASSET_ID = A.ID
left join ASSET ASassoc on ASassoc.ID = AA.ASSOCIATED_ASSET_ID
left join ASSET_DATA_5 DSassoc on DSassoc.ID = ASassoc.ASSET_DATA_ID
left join ASSET_TYPE on ASSET_TYPE.ID = ASassoc.ASSET_TYPE_ID


WHERE /*AA.ASSET_FIELD_ID IS NULL
AND */A.ASSET_TYPE_ID = '5'
AND AA.ASSET_FIELD_ID = '23'
AND A.NAME LIKE 'DO-%'

 

What happens at the present is that it scans the machines and grabs the rows that meets the criteria in the where statement.  Then the rule runs the following update query:

 UPDATE ASSET AS A, ASSET_ASSOCIATION AS AA, ASSET AS ASassoc
SET AA.ASSOCIATED_ASSET_ID = '10'
WHERE A.ASSET_TYPE_ID = '5' AND AA.ASSET_FIELD_ID = '23' AND A.NAME LIKE 'DO-%' AND(A.ID in (<TICKET_IDS>))

The update query then updates all rows that have a location set instead of just the rows that meet the naming criteria.  Any suggestions?


0 Comments   [ + ] Show comments

Answers (0)

Be the first to answer this question

Don't be a Stranger!

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

Sign up! or login

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