CTM - 15454 reporting report utilization reports needed

gsjones79 · ‎11-01-2010

I have many Cisco ONS 15454's deployed, and I need a way (I presume from CTM) to

run reports to show

1 - Circuit utilization (how many STS-1's and for which clients are provisioned on all spans)

2 - Capacity report (how much capactiy in STS-1's are available on all spans)

It is too time consuming to manually export circuits provisioned from each span when the

network changes constantly, and the number of nodes too. This leads to constantly out

dated spreadsheets and inacurate data on capacity, utilization.

Basically I need to run reports against all things circuits on the ONS boxes.

I would be willing to use 3rd party software too, which would talk with CTM I presume?

Who else has this issue?

--Scott

Alexei Kiritchenko · ‎11-04-2010

Hello Scott,

You may automate this by directly querying and parsing Oracle CTM database. I don’t have a ready to use script but here is an idea.

For example link utilization is kept in Link_Utilization_View and links are kept in Link_Table.

Database Schema Tables

Database Schema Views

Here is how to get this information is html formatted file:

su – oracle

sqlplus ctmanager/ctm123!

SQL> spool /tmp/links.html

SQL> set markup html on

SQL> select * from link_table;

SQL> select * from Link_Utilization_View;

SQL> set markup html off

SQL> spool off

Now the information is in /tmp/links.html which you can parse.

En example from my net. let’s take a span between Node 14 port 6/1 and node13 port13/1:

bash-3.00# su – oracle

Blowfish% sqlplus ctmanager/ctm123!

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Nov 4 14:33:51 2010

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>select LINKID from Link_Table where LINKNAME='Node14:6/1 - Node13:13/1';

LINKID

----------

35

SQL> select CKTTYPE,CKTSIZE,CKTUNIQUEID,CKTNAME from Link_Utilization_View where LINKID='35';

CKTTYPE CKTSIZE CKTUNIQUEID CKTNAME

---------- ---------- ----------- ----------------------------------------------------------------

1 1 11 BRT

1 1 114 Link2

======================

CKTTYPE 1 is STS_CIRCUIT and CKTSIZE 1 is STS1 (from Database Schema Tables). Hence i have 2 STS1 circuits on that span.

Regards,

/A