Is there a way to get the end-to-end network routes from the ETL Export database?

[shlomo1332]

I want to automatically export all Network management tab in the IT Advisor client to csv file every week

what is the best way to export?

I try to export from ETL Export DB but I can not find the table with the full network route

If anyone can help me with this

Thank you

[gsterling]

Hello, the ETL export data does include the network connection information.

The information in the export database is defined per point to point connection so there's no single row you can query which describes a multiple hop connection.

That being said, the tables and info you probably want to focus on are the following

configuration_item to identify the starting point (server, network device, patch panel, .... ) to get asset ci_id's

configuration_item_relation where cir_type = 'network_connection' has a list of all the ci_id's which are connected to each other.

relation_endpoint_property contains the network connection details like port_numbers, port_type, module_name and so on.

Hope this helps get you pointed in the right direction.

Regards

Greg Sterling

[shlomo1332]

Do you have an idea for a simple query how to get the ROUTE from end to end?

Thank you very much for your help

[gsterling]

Creating a query for the connections is not hard. The below query should list the network connections and many of the connection properties... tracing routes is much more complicated as then you need to check each endpoint check if its a patch panel if it is, using the rear port as the next connection if not then the route ends. The below query would work for devices to switches ... it will not continue connections through patch panels.

select distinct configuration_item.ci_id,
coalesce(configuration_item.ci_name,configuration_item.ci_logic_name) as ci_name,
configuration_item.ci_location,
configuration_item.ci_room_label as fromroom,
configuration_item.ci_rack_label as rackname,
from_portnum.rep_value as fromport,
from_porttype.rep_value as fromporttype,
from_portmodule.rep_value as fromportmodule,
todeviceinfo.ci_logic_name as todevicename,
todeviceinfo.ci_room_label as todeviceroom,
todeviceinfo.ci_rack_label as todevicerack,
to_portnum.rep_value as toport,
to_porttype.rep_value as toporttype,
to_portmodule.rep_value as toportmodule

from configuration_item

left outer join
( select cir_id, ci_id_a, ci_id_b from configuration_item_relation where cir_type = 'network_connection' ) cir on ( configuration_item.ci_id = cir.ci_id_a )

left outer join
( select ci_id, ci_room_label, ci_rack_label, ci_type, ci_logic_name from configuration_item ) todeviceinfo on ( cir.ci_id_b = todeviceinfo.ci_id )

left outer join
( select ci_id, cir_endpoint, rep_name, cir_id, rep_value from relation_endpoint_property
where rep_name = 'port_number' ) from_portnum on ( from_portnum.cir_id = cir.cir_id and from_portnum.ci_id = cir.ci_id_a )

left outer join
( select ci_id, cir_endpoint, rep_name, cir_id, rep_value from relation_endpoint_property
where rep_name = 'port_type' ) from_porttype on ( from_porttype.cir_id = cir.cir_id and from_porttype.ci_id = cir.ci_id_a )

left outer join
( select ci_id, cir_endpoint, rep_name, cir_id, rep_value from relation_endpoint_property
where rep_name = 'port_module_name' ) from_portmodule on ( from_portmodule.cir_id = cir.cir_id and from_portmodule.ci_id = cir.ci_id_a )

left outer join
( select ci_id, cir_endpoint, rep_name, cir_id, rep_value from relation_endpoint_property
where rep_name = 'port_number' ) to_portnum on ( to_portnum.cir_id = cir.cir_id and to_portnum.ci_id = cir.ci_id_b )

left outer join
( select ci_id, cir_endpoint, rep_name, cir_id, rep_value from relation_endpoint_property
where rep_name = 'port_type' ) to_porttype on ( to_porttype.cir_id = cir.cir_id and to_porttype.ci_id = cir.ci_id_b )

left outer join
( select ci_id, cir_endpoint, rep_name, cir_id, rep_value from relation_endpoint_property
where rep_name = 'port_module_name' ) to_portmodule on ( to_portmodule.cir_id = cir.cir_id and to_portmodule.ci_id = cir.ci_id_b )

where configuration_item.ci_type in ('PatchPanel','Layer3NetworkGear','Layer1NetworkGear','SwitchModule','Layer2NetworkGear','SwitchEnclosure')
and configuration_item.ci_location is not null
order by configuration_item.ci_id;

Regards

Greg Sterling