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
