ITA ETL Import Questions

[thombrooks]

We recently upgraded a LOT of network switches. All of the device names were kept the same, and all network connections were preserved, port-for-port. Each new device was 'racked' 1U above or below the old one, and the old one was renamed to have an 'X-' in front of the name in ITA. We want to bulk-modify the network connections. Since the API doesn't currently support network connections, we want to do this with an ETA transform... sort of. (More like by just putting new or updated rows directly into the Postgres 'staging_whatever' import DB, and having ITA pick up the changes.)

1. What exactly has to happen during or after an ETL import process in order for the ITA system to 'notice' that there are NEW/CHANGED flags, a newer value in 'state_last_updated', etc? Does the transform specifically have to call out which table(s) might have changed? Or is ITA always reviewing the tables in a staging_ database for changes?
2. If we know the uuid of each row in the existing isxo DB's network_connection table that needs updating, can we just create an import ETL with the 'No transformation v1.0.0' and add rows directly to its Postgres 'staging_' db, which will get picked up by ITA and affect the existing network_connection rows?
3. If so, would we do this by either:
   1. Copying the existing row (with the old, original switch) but flagging it 'deleted' and then inserting a new row with the corrected information (the new switch) and a later-modified timestamp, or
   2. Replicating the existing row, but flagging it 'changed' and changing the 'from_asset_item_uuid' value from the old switch to the new one that is replacing it? Or,
   3. If we just added the relationship between the new switch and an existing connection panel / port, can ITA be told to delete / overwrite any existing network_connection that is in conflict with the new one?

The article, "Keeping track of information state" has what seem to be contradictory statements. It first says, "If the external system decides to remove a piece of information it should be moved to the DELETED state. IT Advisor will subsequently handle cleanup of the information inside IT Advisor and report back the state SYNC_DELETED."

But then it goes on to say, "Only data/information in the NEW or CHANGED state are expected to contain information that is not already known by IT Advisor, so only those are read and synchronized.

Note: Information in the [...] DELETED states will not be read by IT Advisor, and subsequently changes to the information will not be updated into IT Advisor."

Is there is an unspoken, underlying assumption that says, "we will know if an entity originated from a third party outside of ITA when its state is 'new', so that is how we'll notice if its record goes missing and delete our internal record to match"? If the DELETED state is truly not 'noticed' during the sync process, then how can a third-party system use the ETL process to tell ITA to delete info that was originally created within ITA?

Thank you! - TB

[ATrabert]

Hi,

I apologize for someone not replying to this question sooner.  Do you still need assistance with how to update network cables via ETL in ITA?  We have an existing feature request for this use case I can add you too and there may be some API enhancements coming later this year/early next year that would make this easier in the future.

Regarding some of your questions
1.What exactly has to happen during or after an ETL import process in order for the ITA system to 'notice' that there are NEW/CHANGED flags
- when an etl job is either ran manually or scheduled, ITA is looking for tables/rows with the state flag set to either NEW, CHANGED, DELETED.  Your transformation or SQL insert statement will need update those fields and ITA will state last updated time when it processes it.

2. Can we just create an import ETL with the 'No transformation v1.0.0' and add rows directly to its Postgres 'staging_' db,
- so this is the more challenging part of this.  We have an example of how to import network connections via ETL and the staging_db/transformation requires importing both the ports and the cables.  This example shows all of the relations and tables used for it https://community.se.com/t5/ETL-examples-troubleshooting-and/ETL-integration-examples/ta-p/458032

Specifically the configuration_item_relation and relation_endpoint_property, in theory the ci_id_a(or ci_id_b) column /ci_id columns would need to be updated.

You can use an ETL export DB to get this information out of ITA, however not sure if ITA will let you update/change the cable destination or not or if it will try to create a new cable.  In that scenario, you would have to remove the cables first then create new ones.

Although this isn't a very clean method, it would almost be easier for you to
- leave the existing switches in place

- do either an ETL export or equipment browser export of the existing equipment make/model/sn

- then use ETL/API to update the make/model/serial information and keep all of the cables intact. 

- using the existing device UUID would allow you to update these fields 

I hope the above helps.  Happy to continue discussing this and planned on doing some additional testing on trying to update the cable endpoints myself.

[thombrooks]

Yes, please add us to the list for beta testing the API for C/R/U/D cables. This would be a huge help.

Since it had been a while since I posted this request, I did already try doing an export of the cables to try re-importing them with the new connection data... But during this process I realized there seems to be an even larger problem with our installation:

When we tried using the ETL Staging database to add new cables between existing ports and switches, at some point it just stopped accepting any new rows. We tried adding them two or three different ways - with and without the to/from UUIDs of existing asset items, or instead filling all of the location / asset name, rack, room, etc. instead.

When I found the error messages, they said that either the port did not exist, or the cable already did. I promise you the ports already existed and the cables did not (nor were there any conflicts); we went so far as to do a Postgres db dump of ISXO and looked directly at the data on a dev machine to confirm it.

Does it sound like we need to open a support case to look into this before we try attempting any of the suggestions above?

Thank you - TB 

[ATrabert]

Yes, please add us to the list for beta testing the API for C/R/U/D cables. This would be a huge help.
-Will do

When I found the error messages, they said that either the port did not exist, or the cable already did. I promise you the ports already existed and the cables did not (nor were there any conflicts); we went so far as to do a Postgres db dump of ISXO and looked directly at the data on a dev machine to confirm it
- In my experience, when I've used our existing ETL network import job linked above, the staging database requires both the ports on the switch be imported as well as the connections between the ports.  This requires that ports are not pre-configured on the genome/asset in ITA.  This only works with new devices/cables and doesn't allow you to update them with this method.  I think the staging import DB may be working as designed vs a tech support issue since it will try to create a new cable and see that the port is already in use.

As I said in my post, I haven't tested trying to updating cables via ETL, but i almost think you'd have to clear the existing ones and re-import them vs just update the endpoints.

Which is why I suggested just updating the asset information of the switch to match the new ones vs updating the cable connections.

[thombrooks]

I'm sorry if I wasn't clear enough with my last post. What I was trying to explain was that after we had difficulty trying to 'migrate' the existing cables, we kept simplifying what we were trying to do, i.e. 'cut the problem in half' until we figured out what was going wrong.

We were down to a level of trying to use the Staging import database to create cables between existing patch panel ports and switches, but not ones that previously had any connections. You could go into the ITA client and see the 'green dot' view of the rack devices and ports, and there would be NO existing green dots. We were trying to make brand new-cable connections. Yes, the switches and patch panels are pre-existing; they have been in the system for years already, but have no current connections.

If you are implying that the ETL import method cannot create a cable between two existing assets with no previous connections, then the ETL method seems pretty unsuited to the needs of our organization.