Sure,
In your ETL you're going to create a New Transform and choose Use Source Column
From there you're going to choose Script Operation, and you're going to need to pass in one of the columns you're bringing in, such as 'device name' so that you have a value to do a query with against the CMDB. You'll give it an Output Column name that you'll use in the ETL Mapping.
Then in your script you're going to call a Script Include that you'll need to create that will take the 'device name' and do a lookup in the CMDB to determine the device status. With the status you can return a value of 'true' if it's retired, otherwise return 'false' from the script include. Your script should look something similar to this.
Hi @Joe S1,
Our use case is that we have to create a new CI if it is not existing on the table and update an existing one, we should be able to update the fields including CI name. In our dev instance we have a field called CI ID wherein it auto generates a number as a unique field for each CIs (see below screenshot). Now I have a class named Workspace wherein our unique identifier and identification rule is the Name attribute, thus we have a requirement wherein we can also update the Name of the workspace CI along with the other attributes. To achieve this I did the below steps:
1. Created a reconciliation rule for Workspace class (Software class in PDI) and added the attributes I needed to be updated with no filter conditions.
2. Created a script operation named u_ci_id_script for where it returns true if CI ID matches the CI ID on the template.
3. Added this script operation on the step 3 mapping class.
4. Mapped the template column u_ci_id to its respective field (noticed that if I don't mapped this then it'll create a new record instead of updating an existing record)
After doing so we can update the attributes including the CI Name however we can also update the CI ID which we should not as it should be a unique field. Note that I did not add the CI ID attribute on the reconciliation rule that I created for the mentioned class.
I did the similiar configuration to PDI using the Software class as it also has the Name attribute in its Identification rule and used the Serial Number field as its unique field.
CI ID field/attribute
Reconciliation Rule:
Hi @Joe S1 ,
I think I got it working with your method above. I have not considered the scenario that if its a new record then there will be no CI ID to input on template thus it should be blank. The operation script would return true if the column is empty/blank thus would still create a new CI if name does not exist on the system and would not update an existing record if CI ID does not match in the system.
For future reference see below:
Operation Script:
I do have a follow-up question though, I have noticed that some of the attributes that are mapped on the ETL can still be updated even though I have only set a few attributes on the reconciliation rule (as per the screenshot I have shared on my previous reply). Where can I check why this is happening or how can I make sure that only the attributes set on the reconciliation rule are updated through the ETL?
Thank you for your assistance!
