How to ignore certain source data coming in IntegrationHub ETL?

[Subhashree Sub2]

Hi Team,

We have a requirement to ignore data transform to 'cmdb_ci_printer' table for the condition "Lifecycle status is Empty".

We have ETL configured for this transform however, I am unable to restrict the empty lifecycle status data.

The target table 'cmdb_ci_printer' has Default value set for column "Lifecycle Status" as '1' (Installed). My requirement is not to transform any BLANK lifecycle status to the target table.

Please share your suggestion on this.

Thanks and Regards,

Subhashree

[AJ-TechTrek]

Hi @Subhashree Sub2 ,'

You can always modify the integration hub action to filter out this data from the data pull. .Other than that you can refer to this post for an additional option of adding a before script.

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.

Please appreciate the efforts of community contributors by marking appropriate response as Mark my Answer Helpful or Accept Solution this may help other community users to follow correct solution in future.

Thanks

AJ

Linkedin Profile:- https://www.linkedin.com/in/ajay-kumar-66a91385/

ServiceNow Community Rising Star 2024

[Subhashree Sub2]

Thank you very much Ajay for your suggestions. It was really helpful to debug the issue.

However, I am still not able to override the Default value of "Lifecycle status" field through ETL.
I am using the GlideAjax method that you have suggested and it works fine in checking what value is coming from the source side but it is not working for ignoring the data transform. Based on the script include return value as 'true' I am doing the field mapping which is correct but, when Source has empty data for 'Lifecycle status' field and script include returns 'false', in this case our requirement is IGNORE the mapping to target table (cmdb_ci_printer) which is not happening.

The mapping script in the ETL considers this empty value coming from source table and inserts into the target table by setting a default value 1 (Installed).

Secondly, I also tried the reference article you provided which is for having an onBefore script. Strangely, onbefore and onAfter script is not at all running for my ETL. The simple logs that I have put just to check script execution is not printing. I wonder why the onBefore script is not getting called by the ETL when I try to insert data to staging table.

I appreciate your help on providing this helpful suggestion. If you could review my concern mentioned above and provide your input, that would be very much helpful.

Thanks & Regards,

Subhashree

[James Chun]

Hey @Subhashree Sub2,

So if the Lifecycle status field of a source record is BLANK, do you skip the entire record or just that particular field?

Cheers

[Subhashree Sub2]

Yeah my requirement is to skip that particular record having Blank "Lifecycle status" coming from source. I tried to restrict through onBefore script of ETL but it is not at all executing.
If you have any suggestion, please let me know.