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

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity