need to update the Machine_Label_JT and replace old Machine.ID's with new ones..
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?
Answers (1)
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.
Comments:
-
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 - SporrerS1977 4 years ago-
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?? - Hobbsy 4 years ago
-
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. - SporrerS1977 4 years ago
-
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. - chucksteel 4 years ago
