ITNM 4.2 Make ncim.managedStatus part of your networkView

Body

Your network View is typically a filter of entities from mainNodeDetails or Interfaces.   Maybe even activeevent.   Can the network view also filter from other tables in your ncim database?   The answer is yes.  And I suspect there are several ways to do this.   I'm going to show one way to do this, and part of the reason I'm using this method is that it is easy to take a slow approach in a lab if you like and make sure your table drop and create is done right.

 

For this demonstration I am going to make the 'status' field of ncim.managedStatus part of ncim.mainNodeDetails.  

This will be done with DB2, but a similar approach could be used for Oracle.

 

In our cautious approach first step is to get a count for ncim.mainNodeDetails

 

login as: itnmdb
[itnmdb@ bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to ITNM

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = ITNMDB
 Local database alias   = ITNM

db2 => select count(*) from ncim.mainNodeDetails

1
-----------
      35859

  1 record(s) selected.

db2 =>

 

In the event of a disaster in my testing I want to be able to replace ncim.mainNodeDetails if I make a mistake.  It is a 'view' and not a true 'table', so it will immediately populate if it's dropped and added again. 

 

[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $ grep mainNodeDetails create*
createPrecisionIPDb.sql:-- mainNodeDetails VIEW
createPrecisionIPDb.sql:CREATE VIEW mainNodeDetails AS

This sql contains the table create for mainNodeDetails.

 

I cut out the part I want and create a small sql file

 

[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $ cat mndgood.sql
CREATE VIEW mainNodeDetails AS
SELECT
        c.accessIPAddress   AS ipAddress,
        c.className,
        e.description,
        e.entityName,
        s.sysObjectId,
        e.entityId,
        s.sysName,
        x.classType,
        e.displayLabel,
        d.domainMgrId,
        s.sysDescr,
        s.sysLocation,
        s.sysContact,
        c.upTime            AS sysUpTime,
        c.services          AS sysServices,
        c.isIpForwarding    AS ipForwarding,
        c.vendorType        AS entPhysicalVendorType,
        e.description       AS entPhysicalDescr,
        c.serialNumber,
        g.locationId,
        g.locationDescription,
        g.latitude,
        g.longitude,
        g.altitude,
        g.timezoneOffset,
        g.altitudeUnits,
        e.manual,
        dm.domainName
FROM    entityData e
INNER JOIN physicalChassis c ON c.entityId = e.entityId
INNER JOIN snmpSystem s ON s.entityId = e.entityId
LEFT OUTER JOIN geographicLocation g ON g.entityId = e.entityId
INNER JOIN classMembers cm ON cm.entityId = e.entityId
INNER JOIN entityClass x ON x.classId = cm.classId
INNER JOIN domainMembers d ON d.entityId = e.entityId
INNER JOIN domainMgr dm ON d.domainMgrId = dm.domainMgrId
WHERE (e.entityType = 1 OR e.entityType = 8);
[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $

 

At the top I add two lines to connect to the database and to set schema to ncim

 

connect to ITNM;
set schema=ncim;
CREATE VIEW mainNodeDetails AS
SELECT
        c.accessIPAddress   AS ipAddress,
        c.className,
        e.description,
        e.entityName,
        s.sysObjectId,
        e.entityId,
        s.sysName,
        x.classType,
        e.displayLabel,
        d.domainMgrId,
        s.sysDescr,
        s.sysLocation,
        s.sysContact,
        c.upTime            AS sysUpTime,
        c.services          AS sysServices,
        c.isIpForwarding    AS ipForwarding,
        c.vendorType        AS entPhysicalVendorType,
        e.description       AS entPhysicalDescr,
        c.serialNumber,
        g.locationId,
        g.locationDescription,
        g.latitude,
        g.longitude,
        g.altitude,
        g.timezoneOffset,
        g.altitudeUnits,
        e.manual,
        dm.domainName
FROM    entityData e
INNER JOIN physicalChassis c ON c.entityId = e.entityId
INNER JOIN snmpSystem s ON s.entityId = e.entityId
LEFT OUTER JOIN geographicLocation g ON g.entityId = e.entityId
INNER JOIN classMembers cm ON cm.entityId = e.entityId
INNER JOIN entityClass x ON x.classId = cm.classId
INNER JOIN domainMembers d ON d.entityId = e.entityId
INNER JOIN domainMgr dm ON d.domainMgrId = dm.domainMgrId
WHERE (e.entityType = 1 OR e.entityType = 8);

 

So now a test so I can recover in case I do something wrong.....

 

[itnmdb@bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to ITNM

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = ITNMDB
 Local database alias   = ITNM

db2 => drop view ncim.mainNodeDetails
DB20000I  The SQL command completed successfully.
db2 => select count(*) from ncim.mainNodeDetails
SQL0204N  "NCIM.MAINNODEDETAILS" is an undefined name.  SQLSTATE=42704
db2 => quit
DB20000I  The QUIT command completed successfully.
[itnmdb@ bin]$ ./db2 -tmf /opt/IBM/netcool/core/precision/scripts/sql/db2/mndgood.sql

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = ITNMDB
 Local database alias   = ITNM

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

[itnmdb@bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to ITNM

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = ITNMDB
 Local database alias   = ITNM

db2 => select count(*) from ncim.mainNodeDetails

1
-----------
      35859

  1 record(s) selected.

db2 =>

 

 

So in the event I make a mistake I can drop the view/table and start again if I need to.

 

Here is my new sql where I make two changes to accommodate for status.

 

[netcool@ibm.com:/opt/IBM/netcool/core/precision/scripts/sql/db2] $ cat mnd1.sql
connect to ITNM;
set schema=ncim;
CREATE VIEW mainNodeDetails AS
SELECT
        c.accessIPAddress   AS ipAddress,
        c.className,
        e.description,
        e.entityName,
        s.sysObjectId,
        e.entityId,
        s.sysName,
        x.classType,
        e.displayLabel,
        d.domainMgrId,
        s.sysDescr,
        s.sysLocation,
        s.sysContact,
        c.upTime            AS sysUpTime,
        c.services          AS sysServices,
        c.isIpForwarding    AS ipForwarding,
        c.vendorType        AS entPhysicalVendorType,
        e.description       AS entPhysicalDescr,
        c.serialNumber,
        g.locationId,
        g.locationDescription,
        g.latitude,
        g.longitude,
        g.altitude,
        g.timezoneOffset,
        g.altitudeUnits,
        e.manual,
        dm.domainName,
        ms.status
FROM    entityData e
INNER JOIN physicalChassis c ON c.entityId = e.entityId
INNER JOIN snmpSystem s ON s.entityId = e.entityId
LEFT OUTER JOIN geographicLocation g ON g.entityId = e.entityId
INNER JOIN classMembers cm ON cm.entityId = e.entityId
INNER JOIN entityClass x ON x.classId = cm.classId
INNER JOIN domainMembers d ON d.entityId = e.entityId
LEFT JOIN managedstatus ms ON ms.entityId = e.entityId
INNER JOIN domainMgr dm ON d.domainMgrId = dm.domainMgrId
WHERE (e.entityType = 1 OR e.entityType = 8);

 

Two lines are new

 

ms.status    

 

and

LEFT JOIN managedstatus ms ON ms.entityId = e.entityId

 

So I drop the view/table and add my new one mnd1.sql with status information

 

db2 => drop view ncim.mainNodeDetails
DB20000I  The SQL command completed successfully.
db2 => quit
DB20000I  The QUIT command completed successfully.
[itnmdb@ bin]$ ./db2 -tmf /opt/IBM/netcool/core/precision/scripts/sql/db2/mnd1.sql

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = ITNMDB
 Local database alias   = ITNM

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

[itnmdb@ bin]$ ./db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => select count(*) from ncim.mainNodeDetails

1
-----------
      35859

  1 record(s) selected.

db2 =>

 

 

That count must still continue to match!   If not there is an error in your new sql and you should put the mndgood.sql back in!

 

At this point I'm finished with the 'core' portion of my change in the database.  So now I need to change the GUI or visual portion of this change.

 

I make a backup of my ncimMetaData.xml and I add one line in my mainNodeDetails section.  The 'status' line

 

[netcool@ibm.com:/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm] $ pwd
/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm
[netcool@ibm.com:/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm] $ less ncimMetaData.xml
[netcool@ibm.com:/opt/IBM/netcool/gui/precision_gui/profile/etc/tnm] $

 

 

        <dataField tableAlias="m" dataType="str" column="altitudeUnits"/>
        <dataField tableAlias="m" dataType="str" column="timezoneOffset"/>
        <dataField tableAlias="m" dataType="str" column="status"/>

        <fromTables>
            FROM _ncim_.mainNodeDetails m
            WHERE e.entityId = ?
        </fromTables>

 

That's it!

 

Now let's take a look in the GUI for network view creation with 'status'

 

 

 

 

 

 

 

 

 

 

 

 

 

Subscribe and follow us for all the latest information directly on your social feeds:

 

 

  

Check out all our other posts and updates:
Academy Blogs:	https://goo.gl/eZjStB
Academy Videos:	https://goo.gl/kJeFZE
Academy Google+:	https://goo.gl/HnTs0w
Academy Twitter :	https://goo.gl/DiJbvD