/build/static/layout/Breadcrumb_cap_w.png

Dell K1000 - Service Desk - Ticketrule SQL update help

Hello,
I want to update the room from my assets in the childticket - so I create a ticketrule where they selected my ticket with custom_field_value0 is not null and custom_field_value1 is 1 - this works. Now I want to update the asset in my customfields..

I have the follow SQL update:
 UPDATE ORG1.ASSET_ASSOCIATION
SET ASSOCIATED_ASSET_ID = HD_TICKET.ASSET_ID
WHERE ASSOCIATED_ASSET_ID =
(
SELECT ASSOCIATED_ASSET_ID FROM ORG1.ASSET_ASSOCIATION
WHERE ASSET_FIELD_ID = 88 'ID for PC'
AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = HD_TICKET.CUSTOM_FIELD_VALUE0)
OR ASSET_FIELD_ID = 888 'ID for monitor'
AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = HD_TICKET.CUSTOM_FIELD_VALUE0)
OR ASSET_FIELD_ID = 8888 'ID for printer'
AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = HD_TICKET.CUSTOM_FIELD_VALUE0)
)
  where
        (HD_TICKET_ID in ())
I get this error back:

dM8ePX.png

Hope someone can help, thanks!

(Note: In the CUSTOM_FIELD_VALUE0 is only the asset name - the asset in the childticket is the room where the asset in the customfield move to)
Asked 8 years ago 1673 views
0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 8 years ago
Red Belt
1
You are updating the ASSET_ASSOCIATION table but referring to a column that doesn't exist in the table (HD_TICKET_ID). You need to instead reference something in the ASSET_ASSOCIATION table.

KACE populates the TICKET_IDS variable with the values returned in the ID column, so if you want to update assets or another table return the values using that column. Alternatively you can use a sub select statement inside of the WHERE clause to find the correct data based on the ticket ID.
Comments:
  • Thanks chucksteel for your response,

    I have change the SQL-update in:
    UPDATE ORG1.ASSET_ASSOCIATION
    SET ASSOCIATED_ASSET_ID =
    (
    SELECT ASSET_ID FROM ORG1.HD_TICKET
    WHERE ID = (<TICKET_IDS>)
    )
    WHERE ASSOCIATED_ASSET_ID =
    (
    SELECT ASSOCIATED_ASSET_ID FROM ORG1.ASSET_ASSOCIATION
    WHERE ASSET_FIELD_ID = 88
    AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = (SELECT CUSTOM_FIELD_VALUE0 FROM ORG1.HD_TICKET WHERE ID = (<TICKET_IDS>)))
    OR ASSET_FIELD_ID = 888
    AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = (SELECT CUSTOM_FIELD_VALUE0 FROM ORG1.HD_TICKET WHERE ID = (<TICKET_IDS>)))
    OR ASSET_FIELD_ID = 8888
    AND ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = (SELECT CUSTOM_FIELD_VALUE0 FROM ORG1.HD_TICKET WHERE ID = (<TICKET_IDS>)))
    )

    But I get another error message - I think, I must use a JOIN but I'm not good in mySQL, what I do is asking and googling - in this case I don't get it... - svmay 8 years ago
    • Error message;
      mysqli error: [1093: Table 'ASSET_ASSOCIATION' is specified twice, both as a target for 'UPDATE' and as a separate source for data] - svmay 8 years ago
    • I'm still not entirely sure what you are trying to accomplish. Can you give a better description? It looks like you are trying to update the asset associations which would change which assets are associated with other assets. Is that really what you want to accomplish? - chucksteel 8 years ago
      • Ok, I try. My english is not so good, but I hope you understand me.

        I have in the child ticket my custom field and next to it one checkbox - I have also an asset in the child ticket (in this case - a room asset). In the ticket rule I select the status from the custom field and the status from the checkbox. When the custom field is filled and the checkbox is true then the asset in the custom field should change is associated asset (room) to the asset in the child asset field. So I have a automatically room movement with help from the ticket and ticketrules. - svmay 8 years ago
      • That's good. Just to be clear that I understand how your assets are setup, things look like this right now in the ASSET_ASSOCIATION table:
        ASSET_ID = 1
        ASSET_FIELD_ID = 2
        ASSOCIATED_ASSET_ID = 3

        This translates to room 1 has the computer (field 2) set to asset number 3. Is that correct?

        If so, then you want to change it to look like this after the move:
        ASSET_ID = 5
        ASSET_FIELD_ID = 2
        ASSOCIATED_ASSET_ID = 3

        So now room 5 has computer 3. Still correct?

        If so, then your update query should look like this:
        UPDATE ASSET_ASSOCIATION
        SET ASSET_ID = (select ASSET_ID from HD_TICKET where ID = <TICKET_IDS>)
        WHERE
        ASSOCIATED_ASSET_ID = (select CUSTOM_FIELD_VALUE0 from HD_TICKET
        where ID = <TICKET_IDS>)

        I might have the asset association backwards but I'm not positive. - chucksteel 8 years ago
      • ASSET_ID = for example unique ID from PC
        ASSET_FIELD_ID = ID type, for example from asset PC list
        ASSOCIATED_ASSET_ID = Room ID - svmay 8 years ago
      • For Example:
        ASSET_ID = 12345 (unique ID for PC123)
        ASSET_FIELD_ID = 88 (ID type for PC)
        ASSOCIATED_ASSET_ID = 5432 (ID for Room 8.OG.88)

        the child ticket have the ID from the room where to move for example the ID is 6666 (Room 6.OG.123

        After the move it looks like this
        ASSET_ID = 12345 (unique ID for PC123)
        ASSET_FIELD_ID = 88 (ID type for PC)
        ASSOCIATED_ASSET_ID = 6666 (ID for Room 6.OG.123) - svmay 8 years ago
      • I've tried with a JOIN function but mysql is not my area.. I always get an error message back - svmay 8 years ago
      • Alright, I had the association backwards. Try this for the update statement:
        UPDATE ASSET_ASSOCIATION
        SET ASSOCIATED_ASSET_ID = (select CUSTOM_FIELD_VALUE0 from HD_TICKET
        where ID = <TICKET_IDS>)
        WHERE
        ASSET_ID = (select ASSET_ID from HD_TICKET where ID = <TICKET_IDS>) - chucksteel 8 years ago
      • Thanks chucksteel,

        I have now the following problem; I've update more assets that it should. I must query the ASSET_FIELD_ID and the ASSET_ID in the table ASSET_ASSOCIATION.. but I don't know how..

        When I use the SQL-update from the top of the comments - I get the error message

        .. table asset_association is specified twice, both as a target for update and as separate source for data .. - svmay 8 years ago
      • To include the ASSET_FIELD_ID you need to add it to the where clause of the update statement, like this:

        UPDATE ASSET_ASSOCIATION
        SET ASSOCIATED_ASSET_ID = (select CUSTOM_FIELD_VALUE0 from HD_TICKET
        where ID = <TICKET_IDS>)
        WHERE
        ASSET_ID = (select ASSET_ID from HD_TICKET where ID = <TICKET_IDS>)
        AND ASSET_FIELD_ID = 88 - chucksteel 8 years ago
      • Next problem: In the custom_field_value0 is only the name from the asset - I have edit my custom ticketrules (with concat) so I have now name and id from the pc (or printer or monitor) - now I have the follow sql update:

        UPDATE ASSET_ASSOCIATION
        SET ASSOCIATED_ASSET_ID = (SELECT CUSTOM_FIELD_VALUE0, SUBSTRING(CUSTOM_FIELD_VALUE0, 17, 35) from ORG1.HD_TICKET
        WHERE ID = <TICKET_IDS>)
        WHERE
        ASSET_ID = (SELECT ASSET_ID FROM HD_TICKET WHERE ID = <TICKET_IDS>)
        AND ASSET_FIELD_ID = 88

        and here I get an error message back ..

        mysqli error: [1241: Operand should contain 1 column(s)]

        (note: At sign 17 till ... is the ID) - svmay 8 years ago
      • Since you only want the substring then remove the SELECT CUSTOM_FIELD_VALUE0, from the sub select.

        UPDATE ASSET_ASSOCIATION
        SET ASSOCIATED_ASSET_ID = (SELECT SUBSTRING(CUSTOM_FIELD_VALUE0, 17, 35) from ORG1.HD_TICKET
        WHERE ID = <TICKET_IDS>)
        WHERE
        ASSET_ID = (SELECT ASSET_ID FROM HD_TICKET WHERE ID = <TICKET_IDS>)
        AND ASSET_FIELD_ID = 88 - chucksteel 8 years ago
      • oh sorry, my fault..
        I had tested, no error, but also no update
        > selected 1 rows
        > 0 rows are updated - svmay 8 years ago
      • I've found a way that works! I post it later - svmay 8 years ago
Posted by: svmay 8 years ago
Red Belt
1

Top Answer

Solution:
I created 15 new customfields (CUSTOM_FIELD_VALUE30 till 44) and set it to 'hidden' - the name for the customfield is ASSET_FIELD_ID - Move (01) till (15).

I use this new customfields as a kind of chache..

For example;
in CUSTOM_FIELD_VALUE0 is an asset that I want to move to
in CUSTOM_FIELD_VALUE1 is the checkbox, wich is needed for moving
in CUSTOM_FIELD_VALUE30 is the ASSET_FIELD_ID from the asset in CUSTOM_FIELD_VALUE0

I have 3 ticketrules for this (probably would be one ticketrule enough - whatever - I made 3)
In the SQL-select, I look for the name, because every PC has 'PC' in his name (the same for printer and monitor)

SQL-update looks like this:
update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE30 = '88'
  where
        (HD_TICKET.ID in (<TICKET_IDS>))
The ASSET_FIELD_ID for PC is in my case 88, for monitor 888 and for printer 8888.

For the asset room move, I need 15 ticketrules (for every customfield for moving), looks like this:
SQL-update:
 UPDATE ORG1.ASSET_ASSOCIATION
SET ASSOCIATED_ASSET_ID =
(
SELECT ASSET_ID FROM HD_TICKET WHERE ID = <TICKET_IDS>
)
WHERE
ASSET_ID =
(
SELECT SUBSTRING(CUSTOM_FIELD_VALUE0, 17, 35) FROM HD_TICKET WHERE ID = <TICKET_IDS>
)
AND ASSET_FIELD_ID = (SELECT CUSTOM_FIELD_VALUE30 FROM HD_TICKET WHERE ID = <TICKET_IDS>
)
I have SELECT SUBSTRING in my sql-command, because in the custom_field is the asset name and the id (concat).

When I confirm the checkbox from the asset that should move, save that, after that the asset get a new associated room asset !


Thanks for the help chucksteel!!
Comments:
  • Glad you got it working. - chucksteel 8 years ago
    • YES :D Thank you chuck! - svmay 8 years ago
 
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