/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


need to update the Machine_Label_JT and replace old Machine.ID's with new ones..

09/21/2020 144 views

I'm currently working on a challenging task. I'd like to set a replacement System in Assetmangement to replace an older one.

I'v already managed to set the older device's  "Device Status","Replacement Asset",

I also managed to set the replacement Asset's "Owner","Location", "Patchlevel", "CostCenter" to the Values of the "old"-replaced- System's Values.

The next step would be to update the Machine_Label_JT to replace the Old Systems Machine ID (therefore removing the Static-Label while assigning the Softwarepackages to the replacement-System) 

I created the following querys:

Detect Old System's Machine ID (to simplify this I set another SQL Statement to add the Machine ID to the AD5 Table)

Select

  ASSET_DATA_52.FIELD_10081 As `OLD Machine ID`

From

  ASSET AS2 Inner Join

  ASSET_ASSOCIATION AA1

    On AA1.ASSET_ID = AS2.ID Inner Join

  ASSET AS1

    On AS1.ID = AA1.ASSOCIATED_ASSET_ID Inner Join

  ASSET_DATA_5 ASSET_DATA_52

    On ASSET_DATA_52.ID = AS2.ASSET_DATA_ID Inner Join

  ASSET_DATA_5 ASSET_DATA_51

    On ASSET_DATA_51.ID = AS1.ASSET_DATA_ID

Where

  AA1.ASSET_FIELD_ID = 10080


I did the same for the new Asset

Select

  AD54.FIELD_10081 As `New Machine ID`

From

  ASSET AS3 Inner Join

  ASSET_ASSOCIATION AA2

    On AA2.ASSET_ID = AS3.ID Inner Join

  ASSET AS4

    On AS4.ID = AA2.ASSOCIATED_ASSET_ID Inner Join

  ASSET_DATA_5 AD53

    On AD53.ID = AS3.ASSET_DATA_ID Inner Join

  ASSET_DATA_5 AD54

    On AD54.ID = AS4.ASSET_DATA_ID

Where

  AA2.ASSET_FIELD_ID = 10080



These SQL Statements should be used in an Update-Statement for the MACHINE_LABEL_JT

Update  MACHINE_LABEL_JT

Set MACHINE_LABEL_JT.MACHINE_ID = (Select AD54.FIELD_10081 As `New Machine ID`

From

  ASSET AS3 Inner Join

  ASSET_ASSOCIATION AA2

    On AA2.ASSET_ID = AS3.ID Inner Join

  ASSET AS4

    On AS4.ID = AA2.ASSOCIATED_ASSET_ID Inner Join

  ASSET_DATA_5 AD53

    On AD53.ID = AS3.ASSET_DATA_ID Inner Join

  ASSET_DATA_5 AD54

    On AD54.ID = AS4.ASSET_DATA_ID

Where

  AA2.ASSET_FIELD_ID = 10080

)

Where

  MACHINE_LABEL_JT.MACHINE_ID = (Select ASSET_DATA_52.FIELD_10081 As `OLD Machine ID`

From

  ASSET AS2 Inner Join

  ASSET_ASSOCIATION AA1

    On AA1.ASSET_ID = AS2.ID Inner Join

  ASSET AS1

    On AS1.ID = AA1.ASSOCIATED_ASSET_ID Inner Join

  ASSET_DATA_5 ASSET_DATA_52

    On ASSET_DATA_52.ID = AS2.ASSET_DATA_ID Inner Join

  ASSET_DATA_5 ASSET_DATA_51

    On ASSET_DATA_51.ID = AS1.ASSET_DATA_ID

Where

  AA1.ASSET_FIELD_ID = 10080

)  


This Update fails as there is no unique primary Key -in fact the Key is used several times (depending on how many Labels are assigned to the Machine)

Now my Question: Do you (the Community brain) have an idea how this could be solved?


0 Comments   [ + ] Show comments

Comments


All Answers

0

I'm not sure I understand why you would want to do this. From your description it sounds like you want to make sure that the new machine receives the same software as the old machine, based on labels assigned to the old machine, but are you only using labels to push software? Are all of those labels manual labels? Can you be certain that there aren't any other labels on the old machine that should not be applied to the new machine?

This sounds like a level of automation that is potentially dangerous and possibly unnecessary.

Answered 09/22/2020 by: chucksteel
Red Belt

  • Hi Chucksteel,
    This is indeed what We'd like to do. We have all Software deployed based on role specific Labels.
    For example we have one Label/Role called "Standard Software" with this role we assign all Standard Software to each client. This is followed by a department specific Label for example "Marketing". All software specific for the Marketing department is assigned via this role.
    User specific Software is deployed via the Download-Portal in Servicedesk.
    For a rollout where we replace existing systems we would like to have as few manual labour as possible. So we just want to choose (in assetmangement) that a specific system gets replaced by a new system.
    Everything else should be done automatically..

    Kind Regards
    Stephan
    • Stephan, I agree with Chuck btw, but just a thought, if you had an asset field that you use to signify say the department, and the department value signifies the software the machine should get, surely setting up the new machine, add the department value and then labels and MI's will take over the automation for you??
      • Hi Hobbsy, Hi Chucksteel,

        I agree with both of you that this level of automation is not suitable for every customer.
        But especially for rollouts where in principle "only" the hardware is exchanged and the frame parameters like
        -who is responsible for this device?
        -To which cost center is this device assigned?
        -to which patch level is the device assigned?
        and which labels are assigned to the device?
        it would be necessary to automate this process to eliminate the human factor as a source of error


        This is information we generally want to take over from the old device when replacing the hardware.
        In addition, we also set a new status and the note that the old device will be replaced by a new one.
        For this I have created a set of ticket rules.

        If the field "this device replaces" on an asset is set to a reference to another computer asset
        a) The new Asset will be linked to the old one via the field "is replaced by"
        b) The Asset Status of the Asset to be replaced is changed to withdrawn
        c) on the new asset, the person responsible for is set the sam as at the asset to be replaced
        d) at the new asset the location of the asset to be replaced is taken over
        e) on the new Asset, the Patchlevel of the Asset to be replaced is taken
        f) the cost center of the asset to be replaced is transferred to the new asset
        g) the new device is assigned to the same labels as the old device

        Except for the point"g" we have already realized everything.

        I am now considering whether it is possible to do this with an "insert" command in the Machine_Label_JT.
        I would then have to use select in Machine_Label_JT to determine which labels are connected to the machine.
        Then I would have to connect this select with the insert into command - but I'm afraid that I will have the same problem with the multiple used primary keys.
    • I think that an insert statement will be easier to get working, in particular, I would use an insert...select:
      https://mariadb.com/kb/en/insert-select/

      Based on what you shared about your setup, you should be able to perform the select part of the query based on the values stored in your ticket.
 
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