EcoStruxure IT forum
Schneider Electric support forum about installation and configuration for DCIM including EcoStruxure IT Expert, IT Advisor, Data Center Expert, and NetBotz
Link copied. Please paste this link to share this article on your social media post.
Posted: 2021-11-08 08:56 AM
Hello Forum followers... Has anyone tried importing custom properties using the ETL Import database process? We tested the XLS manual import process via the desktop client, but that requires an asset NOT already EXIST. Seems we can set a custom property if we are also creating the asset at the same time... but that is not our use case.
We have defined a new custom property from the desktop client called "legacy_id". The use case... historically, we were modeling receptacles at the cage level, but have decided to destroy those CI's... and instead, recreate them at the rack level. In doing this, our integrations with external system require us to set a custom property on the new receptacle called "legacy_id" and fill it with the old ci_id value.
Hoping someone has encountered a similar use case... otherwise, we're aware of the RESTapi call option to set this value. Doing this data work via SQL insert statements is preferred.
Link copied. Please paste this link to share this article on your social media post.
Link copied. Please paste this link to share this article on your social media post.
Posted: 2021-11-08 12:42 PM
-- Step 1 : Showing insertion of the ci into the etl import DB.
-- Must be present to perform auto association steps
INSERT INTO public.configuration_item
(ci_id, ci_barcode, ci_description, ci_fingerprint, ci_ip_address, ci_location, ci_manufacturer, ci_model_name, ci_model_number, ci_name, ci_serial_number, state_flag, state_last_synchronized, state_last_updated, cit_id)
VALUES(
'38b4439a-fe7d-4a27-b41c-9b4bb0c263bc',
'barcode',
'description',
'fingerprint',
'ip',
'location',
'manufacturer',
'model',
'model number',
'name',
'serial',
'new',
0, 0, 0);
-- Step 2 : Run the following SQL before and after triggering ETL job to run
-- Expect to see state_flag change from new to synced
select ci_id, state_flag from configuration_item ci where ci_id = '38b4439a-fe7d-4a27-b41c-9b4bb0c263bc';
-- Step 3 : Now insert the custom property.
-- Example shown setting custom property legacy_id
INSERT INTO public.configuration_item_property (ci_id, cip_name, state_flag, state_last_synchronized, state_last_updated, cip_value, pt_id) VALUES('38b4439a-fe7d-4a27-b41c-9b4bb0c263bc','legacy_id', 'new', 0, 0, '38b4439a-fe7d-4a27-b41c-9b4bb0c263bc', 2);
-- Step 4 : Run the following SQL before and after triggering ETL job to run
-- Expect to see state_flag change from new to synced
select * from configuration_item_property cip
where cip_name = 'legacy_id' and ci_id = '38b4439a-fe7d-4a27-b41c-9b4bb0c263bc';
-- Step 5 : Trigger the ETL Export job
-- Verify custom property now present in the export
Link copied. Please paste this link to share this article on your social media post.
Link copied. Please paste this link to share this article on your social media post.
Posted: 2021-11-08 11:32 AM
Have been responding to the customer directly.
I have found via an ETL import database it is possible to add custom properties to receptacles. I have been able to do this successfully.
Looking into whether this causes any unforseen caveats.
Greg Sterling
Link copied. Please paste this link to share this article on your social media post.
Link copied. Please paste this link to share this article on your social media post.
Posted: 2021-11-08 12:42 PM
-- Step 1 : Showing insertion of the ci into the etl import DB.
-- Must be present to perform auto association steps
INSERT INTO public.configuration_item
(ci_id, ci_barcode, ci_description, ci_fingerprint, ci_ip_address, ci_location, ci_manufacturer, ci_model_name, ci_model_number, ci_name, ci_serial_number, state_flag, state_last_synchronized, state_last_updated, cit_id)
VALUES(
'38b4439a-fe7d-4a27-b41c-9b4bb0c263bc',
'barcode',
'description',
'fingerprint',
'ip',
'location',
'manufacturer',
'model',
'model number',
'name',
'serial',
'new',
0, 0, 0);
-- Step 2 : Run the following SQL before and after triggering ETL job to run
-- Expect to see state_flag change from new to synced
select ci_id, state_flag from configuration_item ci where ci_id = '38b4439a-fe7d-4a27-b41c-9b4bb0c263bc';
-- Step 3 : Now insert the custom property.
-- Example shown setting custom property legacy_id
INSERT INTO public.configuration_item_property (ci_id, cip_name, state_flag, state_last_synchronized, state_last_updated, cip_value, pt_id) VALUES('38b4439a-fe7d-4a27-b41c-9b4bb0c263bc','legacy_id', 'new', 0, 0, '38b4439a-fe7d-4a27-b41c-9b4bb0c263bc', 2);
-- Step 4 : Run the following SQL before and after triggering ETL job to run
-- Expect to see state_flag change from new to synced
select * from configuration_item_property cip
where cip_name = 'legacy_id' and ci_id = '38b4439a-fe7d-4a27-b41c-9b4bb0c263bc';
-- Step 5 : Trigger the ETL Export job
-- Verify custom property now present in the export
Link copied. Please paste this link to share this article on your social media post.
Link copied. Please paste this link to share this article on your social media post.
Posted: 2021-11-11 01:18 AM
Hello,
I profoundly like your dedicated abilities as the post you distributed has some incredible data which is very valuable for me.
Link copied. Please paste this link to share this article on your social media post.
Create your free account or log in to subscribe to the board - and gain access to more than 10,000+ support articles along with insights from experts and peers.