How to apply a custom property via the ETL Import database

[wesburhoe]

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.

[gsterling]

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

[wesburhoe · Accepted solution]

-- 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

[Peter89]

Hello,

I profoundly like your dedicated abilities as the post you distributed has some incredible data which is very valuable for me.