EcoStruxure IT forum
A support forum for Data Center Operation, Data Center Expert, and EcoStruxure IT product users to share knowledge on installation, configuration, and general product use.
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.
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
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
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
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.
Create your free account or log in to subscribe to the forum - and gain access to more than 10,000+ support articles along with insights from experts and peers.