Update on Cisco Works Open Database Schema Support dbviews document ?

Answered Question
Jun 8th, 2010

Hello,


does anyone know if an update on the document below is available somewhere

http://www.cisco.com/en/US/docs/net_mgmt/ciscoworks_lan_management_solution/3.2/database_schema/guide/dbviews.html


The reason why I am asking is that I realised the document above is not accurate, for example Syslog_Today or Syslog_Lastday views in rmeng seem not be working at all, I got this error messages:

DBD::ASAny::st execute failed: Table 'Syslog_Lastday' not found (DBD: describe o
utput failed) at d:\bat\RME_ssl.pl line 43.


I've got these CW versions installed:

Name Version License Status Size
CiscoWorks Common Services 3.3.0 Licensed Not applicable
Campus Manager 5.2.0 Purchased 5000
CiscoView 6.1.9 Licensed Not applicable
CiscoWorks Assistant 1.2.0 Licensed Not applicable
Device Fault Manager 3.2.0 Purchased 5000
Internetwork Performance Monitor 4.2.0 Purchased 5000
Integration Utility 1.9.0 Licensed Not applicable
LMS Portal 1.2.0 Licensed Not applicable
Resource Manager Essentials 4.3.0 Purchased 5000


Many thanks.

Correct Answer by Joe Clarke about 6 years 8 months ago

No.  You cannot access other tables in the database.  Such access is not supported.

Correct Answer by Joe Clarke about 6 years 8 months ago

Those views should be there by default (Syslog_Today and Syslog_Lastday), but these views are owned by DBA and not lmsdatafeed.  So, you need to reference them as DBA.Syslog_Today and DBA.Syslog_Lastday.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (2 ratings)
Loading.
vladakoci Wed, 06/09/2010 - 02:48

Seems I have found the definitions of tables and views directly in Cisco Works folders on the Cisco Works server.

An example is the file dbviews.txt, see below, which is a file located in D:\Program Files\CSCOpx\databases\rmeng

( in my case CW is installed in D:\Program Files\CSCOpx )


Can anyone read form that file if SYSLOG_TODAY and SYSLOG_LASTDAY views are supposed to be available?

I can see these are 'installed' in a bit different way than the other ones, like for example NETWORK_DEVICES view.


Thanks.



[INSTALL]
GRANT MEMBERSHIP IN GROUP $LMSVIEWGROUP TO DBA
CREATE SERVER CMFSA CLASS 'SAODBC' USING 'cmf'
CREATE EXTERNLOGIN DBA TO CMFSA REMOTE LOGIN $LMSVIEWUSERNAME IDENTIFIED BY $LMSVIEWPASSWD
CREATE EXISTING TABLE $LMSVIEWGROUP.NETWORK_DEVICES AT  'CMFSA.CMF.$LMSVIEWGROUP.NETWORK_DEVICES'
CREATE VIEW $LMSVIEWGROUP.DEVICE_INVENTORY AS SELECT DEVICE_ID AS DEVICE_ID,NETWORK_DEVICES.DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME,DESCRIPTION AS DEVICE_DESCRIPTION,ELEMENTLOCATION AS DEVICE_LOCATION,PRIMARYOWNERCONTACT AS DEVICE_CONTACT,DS_IMAGE.VERSION AS DEVICE_SW_VERSION ,USER_DEFINED_SERIAL_NUMBER.SERIAL_NUMBER AS USER_DEFINED_SERIAL_NUMBER FROM ( ( ( NETWORK_DEVICES INNER JOIN DM_DEV_STATE ON NETWORK_DEVICES.DEVICE_ID = DM_DEV_STATE.DCR_ID ) INNER JOIN NETWORKELEMENT ON DM_DEV_STATE.NETWORKELEMENTID = NETWORKELEMENT.NETWORKELEMENTID ) LEFT OUTER JOIN USER_DEFINED_SERIAL_NUMBER ON DM_DEV_STATE.NETWORKELEMENTID = USER_DEFINED_SERIAL_NUMBER.NETWORKELEMENTID ) INNER JOIN DS_IMAGE ON DM_DEV_STATE.NETWORKELEMENTID = DS_IMAGE.DEVICEID
CREATE VIEW $LMSVIEWGROUP.MEMORY_INVENTORY AS SELECT NETWORK_DEVICES.DEVICE_ID AS DEVICE_ID,NETWORK_DEVICES.DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME,(ALLOCATED + FREE) / (1024*1024) AS PROCESSOR_MEMORY FROM ( DM_DEV_STATE INNER JOIN NETWORK_DEVICES ON DM_DEV_STATE.DCR_ID = NETWORK_DEVICES.DEVICE_ID ) INNER JOIN MEMORYPOOL ON DM_DEV_STATE.NETWORKELEMENTID = MEMORYPOOL.NETWORKELEMENTID AND MEMORYPOOL.POOLTYPE = 2
CREATE VIEW $LMSVIEWGROUP.DEVICE_CREDENTIAL_STATUS AS SELECT NETWORK_DEVICES.DEVICE_ID AS DEVICE_ID, NETWORK_DEVICES.DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME , DM_CREDENTIAL_ERROR_STATUS.CRED_TYPE AS CREDENTIAL_TYPE , DM_CRED_TYPE_ENUM.ENUMDESCRIPTION AS CREDENTIAL_TYPE_DESCRIPTION, DM_CREDENTIAL_ERROR_STATUS.CRED_STATUS AS CREDENTIAL_STATUS, DM_CRED_STATUS_ENUM.ENUMDESCRIPTION AS CREDENTIAL_STATUS_DESCRIPTION, DM_CREDENTIAL_ERROR_STATUS.CRED_STATUS_DESCR AS CREDENTIAL_STATUS_REASON    , DM_CREDENTIAL_ERROR_STATUS.UPDATE_AT AS STATUS_UPDATE_TIME FROM DM_CREDENTIAL_ERROR_STATUS, DM_CRED_TYPE_ENUM, DM_CRED_STATUS_ENUM,DM_DEV_STATE,NETWORK_DEVICES WHERE DM_CREDENTIAL_ERROR_STATUS.CRED_TYPE = DM_CRED_TYPE_ENUM.ENUMVALUE AND DM_CREDENTIAL_ERROR_STATUS.CRED_STATUS = DM_CRED_STATUS_ENUM.ENUMVALUE AND DM_CREDENTIAL_ERROR_STATUS.NETWORKELEMENTID = DM_DEV_STATE.NETWORKELEMENTID AND DM_DEV_STATE.DCR_ID = NETWORK_DEVICES.DEVICE_ID
CREATE VIEW $LMSVIEWGROUP.PROCESSOR_INVENTORY AS SELECT NETWORK_DEVICES.DEVICE_ID AS DEVICE_ID, NETWORK_DEVICES.DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME,PROCESSOR.PRODUCTNAME AS PROCESSOR_MODEL_NAME,PROCESSOR.DESCRIPTION AS PROCESSOR_MODULE_DESCRIPTION,PROCESSOR.VENDOREQUIPMENTTYPE AS PROCESSOR_VENDOR_TYPE,PROCESSOR.MANUFACTURER AS PROCESSOR_MANUFACTURER,PROCESSOR.SERIALNUMBER FROM ( PROCESSOR INNER JOIN DM_DEV_STATE ON PROCESSOR.NETWORKELEMENTID = DM_DEV_STATE.NETWORKELEMENTID ) INNER JOIN NETWORK_DEVICES ON DM_DEV_STATE.DCR_ID = NETWORK_DEVICES.DEVICE_ID
CREATE VIEW $LMSVIEWGROUP.DEVICE_INVENTORY_COLLECTION_STATUS AS SELECT NETWORK_DEVICES.DEVICE_ID AS DEVICE_ID,DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME,INVENTORY_COLLECTION_STATUS, FAILURE_REASON,LAST_UPDATED_TIME AS INVENTORY_UPDATED_AT FROM DEV_MGMT_STATUS INNER JOIN DM_DEV_STATE ON DEV_MGMT_STATUS.NETWORKELEMENTID = DM_DEV_STATE.NETWORKELEMENTID INNER JOIN NETWORK_DEVICES ON DM_DEV_STATE.DCR_ID = NETWORK_DEVICES.DEVICE_ID
CREATE VIEW $LMSVIEWGROUP.DEVICE_CONFIG_ARCHIVE_STATUS AS SELECT DEVICE_ID AS DEVICE_ID,DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME,CONFIG_TYPE, CONFIG_FILE_TYPE,STATUS AS ARCHIVE_STATUS,UPDATEDAT AS ARCHIVE_UPDATED_AT FROM DCMA_ARCHIVE_STATUS INNER JOIN DM_DEV_STATE ON DCMA_ARCHIVE_STATUS.NETWORKELEMENTID = DM_DEV_STATE.NETWORKELEMENTID INNER JOIN NETWORK_DEVICES ON DM_DEV_STATE.DCR_ID = NETWORK_DEVICES.DEVICE_ID
CREATE VIEW $LMSVIEWGROUP.CHANGE_AUDIT_HISTORY AS SELECT NETWORK_DEVICES.DEVICE_ID AS DEVICE_ID, DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME, CREATED_AT,   USER_NAME,   APP_NAME,   CAS_LOG.HOST_NAME,   CONN_MODE,   DESCRIPTION, CAS_LOG.CATEGORY   FROM CAS_LOG INNER JOIN DM_DEV_STATE ON CAS_LOG.DEVICE_ID = DM_DEV_STATE.NETWORKELEMENTID INNER JOIN NETWORK_DEVICES ON DM_DEV_STATE.DCR_ID = NETWORK_DEVICES.DEVICE_ID
CREATE VIEW $LMSVIEWGROUP.MODULE_INVENTORY AS SELECT RME_MODULE_ATTRIBUTES.DCR_ID AS DEVICE_ID, NETWORK_DEVICES.DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME, MODULENAME AS MODULE_NAME, VENDORTYPE AS VENDOR_TYPE, SW_VERSION AS SW_VERSION, FW_VERSION AS FW_VERSION , SLOTNUM AS SLOT_NUMBER, OPERSTATUS AS OPER_STATUS, ADMINSTATUS AS ADMIN_STATUS FROM ( RME_MODULE_ATTRIBUTES INNER JOIN NETWORK_DEVICES ON RME_MODULE_ATTRIBUTES.DCR_ID = NETWORK_DEVICES.DEVICE_ID ) INNER JOIN DM_DEV_STATE ON RME_MODULE_ATTRIBUTES.DCR_ID = DM_DEV_STATE.DCR_ID
CREATE VIEW $LMSVIEWGROUP.PORT_INVENTORY AS SELECT DBA.RME_PORT_ATTRIBUTES.DCR_ID AS DEVICE_ID,NETWORK_DEVICES.DEVICE_DISPLAY_NAME AS DEVICE_DISPLAY_NAME,RME_PORT_ATTRIBUTES.PORTNAME AS PORT_NAME,RME_PORT_ATTRIBUTES.PORTDESC AS PORT_DESC,RME_PORT_ATTRIBUTES.TYPE AS PORT_TYPE,RME_PORT_ATTRIBUTES.ADMINSTATUS AS PORT_ADMIN_STATUS,RME_PORT_ATTRIBUTES.OPERSTATUS AS PORT_OPER_STATUS,RME_PORT_ATTRIBUTES.SPEED PORT_SPEED,CM_PORT_ATTRIBUTES.DUPLEXMODE AS PORT_DUPLEX_MODE,CM_PORT_ATTRIBUTES.ISLINK AS IS_LINK_PORT,CM_PORT_ATTRIBUTES.ISACCESS AS IS_ACCESS_PORT,CM_PORT_ATTRIBUTES.ISTRUNK AS IS_TRUNK_PORT, CM_PORT_ATTRIBUTES.ISCHANNEL AS IS_PORT_CHANNEL,CM_PORT_ATTRIBUTES.VLAN VLAN_NAME,CM_PORT_ATTRIBUTES.VLANID AS VLANID,CM_PORT_ATTRIBUTES.VTPDOMAIN AS VTPDOMAIN,CM_PORT_ATTRIBUTES.NEIGHBOR AS NEIGHBOUR_TYPE FROM ( RME_PORT_ATTRIBUTES INNER JOIN NETWORK_DEVICES ON RME_PORT_ATTRIBUTES.DCR_ID = NETWORK_DEVICES.DEVICE_ID ) LEFT OUTER JOIN CM_PORT_ATTRIBUTES ON RME_PORT_ATTRIBUTES.DCR_PORT_ID = CM_PORT_ATTRIBUTES.DCR_PORT_ID


call syslog_views()


GRANT SELECT ON $LMSVIEWGROUP.NETWORK_DEVICES TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.DEVICE_INVENTORY TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.MEMORY_INVENTORY TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.DEVICE_CREDENTIAL_STATUS TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.PROCESSOR_INVENTORY TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.DEVICE_INVENTORY_COLLECTION_STATUS TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.DEVICE_CONFIG_ARCHIVE_STATUS TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.CHANGE_AUDIT_HISTORY TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.MODULE_INVENTORY TO $LMSVIEWUSERNAME
GRANT SELECT ON $LMSVIEWGROUP.PORT_INVENTORY TO $LMSVIEWUSERNAME
GRANT SELECT ON DBA.SYSLOG_TODAY TO $LMSVIEWUSERNAME
GRANT SELECT ON DBA.SYSLOG_LASTDAY TO $LMSVIEWUSERNAME


[CLEAN]
DROP VIEW  $LMSVIEWGROUP.DEVICE_INVENTORY
DROP VIEW  $LMSVIEWGROUP.MEMORY_INVENTORY
DROP VIEW  $LMSVIEWGROUP.DEVICE_CREDENTIAL_STATUS
DROP VIEW  $LMSVIEWGROUP.PROCESSOR_INVENTORY
DROP VIEW  $LMSVIEWGROUP.DEVICE_INVENTORY_COLLECTION_STATUS
DROP VIEW  $LMSVIEWGROUP.DEVICE_CONFIG_ARCHIVE_STATUS
DROP VIEW  $LMSVIEWGROUP.CHANGE_AUDIT_HISTORY
DROP VIEW  $LMSVIEWGROUP.MODULE_INVENTORY
DROP VIEW  $LMSVIEWGROUP.PORT_INVENTORY
DROP VIEW  DBA.SYSLOG_TODAY
DROP VIEW  DBA.SYSLOG_LASTDAY
DROP TABLE NETWORK_DEVICES
DROP EXTERNLOGIN DBA TO CMFSA
DROP SERVER CMFSA

Correct Answer
Joe Clarke Wed, 06/09/2010 - 22:29

Those views should be there by default (Syslog_Today and Syslog_Lastday), but these views are owned by DBA and not lmsdatafeed.  So, you need to reference them as DBA.Syslog_Today and DBA.Syslog_Lastday.

vladakoci Thu, 06/10/2010 - 00:34

Thank you.


Yes, in the meantime I accessed the DBs through SQL and ODBC and was able to dig the data also from Syslog_Today and Syslog_Lastday.


Can see that in the databases there are more tables available.

What would happen if I

* shut the CW down

* edit dbviews.txt say in D:\Program Files\CSCOpx\databases\rmeng and try to add more tables/views

* start the CW up

* start the DB access (dbaccess.pl install )

would that allow me to get access to more tables/views?

Looking into dbaccess.pl and seeing that it works with dbviews.txt it might be worth trying.


Thank you.

Correct Answer
Joe Clarke Sat, 06/12/2010 - 15:57

No.  You cannot access other tables in the database.  Such access is not supported.

Actions

This Discussion

Related Content