| Project | OpenNMS |
| Version | OpenNMS 090807 |
| Version Date | 2009-08-07 |
| Column | Data Type | Nullable | Default | PK / Index |
| alarmid | integer | null |
index alarm_attributes_aan_idx index alarm_attributes_idx |
|
| attributename | varchar(63) | null |
index alarm_attributes_aan_idx |
|
| attributevalue | varchar(255) | null |
| Foreign Key | Column | Referenced Column |
| alarms (public) | alarmid | alarmid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| alarmid | integer | not null |
PK pk_alarmid index alarm_reduction2_idx |
|
| eventuei | varchar(256) | not null |
index alarm_reduction2_idx index alarm_uei_idx |
|
| dpname | varchar(12) | not null |
index alarm_reduction2_idx |
|
| nodeid | integer | null |
index alarm_nodeid_idx index alarm_reduction2_idx |
|
| ipaddr | varchar(16) | null | ||
| serviceid | integer | null |
index alarm_reduction2_idx |
|
| reductionkey | varchar(256) | null |
index alarm_reductionkey_idx index alarm_reduction2_idx |
|
| alarmtype | integer | null | ||
| counter | integer | not null | ||
| severity | integer | not null | ||
| lasteventid | integer | null |
index alarm_eventid_idx |
|
| firsteventtime | timestamp with time zone | null | ||
| lasteventtime | timestamp with time zone | null | ||
| firstautomationtime | timestamp with time zone | null | ||
| lastautomationtime | timestamp with time zone | null | ||
| description | varchar(4000) | null | ||
| logmsg | varchar(256) | null | ||
| operinstruct | varchar(1024) | null | ||
| tticketid | varchar(128) | null | ||
| tticketstate | integer | null | ||
| mouseovertext | varchar(64) | null | ||
| suppresseduntil | timestamp with time zone | null | ||
| suppresseduser | varchar(256) | null | ||
| suppressedtime | timestamp with time zone | null | ||
| alarmackuser | varchar(256) | null | ||
| alarmacktime | timestamp with time zone | null | ||
| clearuei | varchar(256) | null | ||
| managedobjectinstance | varchar(512) | null | ||
| managedobjecttype | varchar(512) | null | ||
| applicationdn | varchar(512) | null |
index alarm_app_dn |
|
| ossprimarykey | varchar(512) | null |
index alarm_oss_primary_key |
|
| x733alarmtype | varchar(31) | null | ||
| x733probablecause | integer | not null | 0 | |
| qosalarmstate | varchar(31) | null | ||
| clearkey | varchar(256) | null |
index alarm_clearkey_idx |
| Foreign Key | Column | Referenced Column |
| events (public) | lasteventid | eventid |
| Detail Table | Column | Referencing Column |
| alarm_attributes (public) | alarmid | alarmid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| appid | integer | null |
index appid_ifserviceid_idex index appid_idx |
|
| ifserviceid | integer | null |
index appid_ifserviceid_idex index ifserviceid_idx |
| Foreign Key | Column | Referenced Column |
| applications (public) | appid | id |
| ifservices (public) | ifserviceid | id |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null |
PK applications_pkey |
|
| name | varchar(32) | not null |
index applications_name_idx |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| application_service_map (public) | id | appid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK pk_assetid |
| nodeid | integer | null |
index assets_nodeid_idx |
|
| category | varchar(64) | not null | ||
| manufacturer | varchar(64) | null | ||
| vendor | varchar(64) | null | ||
| modelnumber | varchar(64) | null | ||
| serialnumber | varchar(64) | null | ||
| description | varchar(128) | null | ||
| circuitid | varchar(64) | null | ||
| assetnumber | varchar(64) | null |
index assets_an_idx |
|
| operatingsystem | varchar(64) | null | ||
| rack | varchar(64) | null | ||
| slot | varchar(64) | null | ||
| port | varchar(64) | null | ||
| region | varchar(64) | null | ||
| division | varchar(64) | null | ||
| department | varchar(64) | null | ||
| address1 | varchar(256) | null | ||
| address2 | varchar(256) | null | ||
| city | varchar(64) | null | ||
| state | varchar(64) | null | ||
| zip | varchar(64) | null | ||
| building | varchar(64) | null | ||
| floor | varchar(64) | null | ||
| room | varchar(64) | null | ||
| vendorphone | varchar(64) | null | ||
| vendorfax | varchar(64) | null | ||
| vendorassetnumber | varchar(64) | null | ||
| userlastmodified | char(20) | not null | ||
| lastmodifieddate | timestamp with time zone | not null | ||
| dateinstalled | varchar(64) | null | ||
| lease | varchar(64) | null | ||
| leaseexpires | varchar(64) | null | ||
| supportphone | varchar(64) | null | ||
| maintcontract | varchar(64) | null | ||
| maintcontractexpires | varchar(64) | null | ||
| displaycategory | varchar(64) | null | ||
| notifycategory | varchar(64) | null | ||
| pollercategory | varchar(64) | null | ||
| thresholdcategory | varchar(64) | null | ||
| comment | text | null | ||
| managedobjectinstance | varchar(512) | null | ||
| managedobjecttype | varchar(512) | null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) | |
| nodeid | integer | not null |
PK pk_atinterface index atinterface_nodeid_idx index atinterface_node_ipaddr_idx |
|
| ipaddr | varchar(16) | not null |
PK pk_atinterface index atinterface_node_ipaddr_idx |
|
| atphysaddr | varchar(12) | not null |
PK pk_atinterface index atinterface_atphysaddr_idx |
|
| status | char(1) | not null | ||
| sourcenodeid | integer | not null | ||
| ifindex | integer | not null | ||
| lastpolltime | timestamp without time zone | not null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| categoryid | integer | not null |
PK category_pkey |
|
| categoryname | varchar(64) | not null |
index category_idx |
|
| categorydescription | varchar(256) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| category_node (public) | categoryid | categoryid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| categoryid | integer | null |
index catenode_unique_idx index catid_idx |
|
| nodeid | integer | null |
index catenode_unique_idx index catnode_idx |
| Foreign Key | Column | Referenced Column |
| categories (public) | categoryid | categoryid |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
PK pk_datalinkinterface index dlint_node_idx |
|
| ifindex | integer | not null |
PK pk_datalinkinterface |
|
| nodeparentid | integer | not null |
index dlint_nodeparent_idx index dlint_nodeparent_paifindex_idx |
|
| parentifindex | integer | not null |
index dlint_nodeparent_paifindex_idx |
|
| status | char(1) | not null | ||
| lastpolltime | timestamp without time zone | not null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| node (public) | nodeparentid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null |
PK demandpoll_pkey |
|
| requesttime | timestamp with time zone | null |
index demandpoll_request_time |
|
| username | varchar(32) | null | ||
| description | varchar(128) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| pollresults (public) | id | pollid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| dpname | varchar(12) | not null |
PK pk_dpname |
|
| dpip | varchar(16) | not null | ||
| dpcomment | varchar(256) | null | ||
| dpdisclimit | numeric(5, 2) | null | ||
| dplastnodepull | timestamp with time zone | null | ||
| dplasteventpull | timestamp with time zone | null | ||
| dplastpackagepush | timestamp with time zone | null | ||
| dpadminstate | integer | null | ||
| dprunstate | integer | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| node (public) | dpname | dpname |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| mapid | integer | not null |
PK pk_element index element_mapid_elementid |
|
| elementid | integer | not null |
PK pk_element index element_mapid_elementid |
|
| elementtype | char(1) | not null |
PK pk_element |
|
| elementlabel | varchar(256) | not null | ||
| elementicon | varchar(256) | null | ||
| elementx | integer | null | ||
| elementy | integer | null |
| Foreign Key | Column | Referenced Column |
| map (public) | mapid | mapid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| eventid | integer | not null |
PK pk_eventid |
|
| eventuei | varchar(256) | not null |
index events_uei_idx |
|
| nodeid | integer | null |
index events_nodeid_idx index events_nodeid_display_ackuser |
|
| eventtime | timestamp with time zone | not null |
index events_time_idx |
|
| eventhost | varchar(256) | null | ||
| eventsource | varchar(128) | not null | ||
| ipaddr | varchar(16) | null |
index events_ipaddr_idx |
|
| eventdpname | varchar(12) | not null | ||
| eventsnmphost | varchar(256) | null | ||
| serviceid | integer | null |
index events_serviceid_idx |
|
| eventsnmp | varchar(256) | null | ||
| eventparms | text | null | ||
| eventcreatetime | timestamp with time zone | not null | ||
| eventdescr | varchar(4000) | null | ||
| eventloggroup | varchar(32) | null | ||
| eventlogmsg | varchar(256) | null | ||
| eventseverity | integer | not null |
index events_severity_idx |
|
| eventpathoutage | varchar(1024) | null | ||
| eventcorrelation | varchar(1024) | null | ||
| eventsuppressedcount | integer | null | ||
| eventoperinstruct | varchar(1024) | null | ||
| eventautoaction | varchar(256) | null | ||
| eventoperaction | varchar(256) | null | ||
| eventoperactionmenutext | varchar(64) | null | ||
| eventnotification | varchar(128) | null | ||
| eventtticket | varchar(128) | null | ||
| eventtticketstate | integer | null | ||
| eventforward | varchar(256) | null | ||
| eventmouseovertext | varchar(64) | null | ||
| eventlog | char(1) | not null |
index events_log_idx |
|
| eventdisplay | char(1) | not null |
index events_nodeid_display_ackuser index events_display_idx |
|
| eventackuser | varchar(256) | null |
index events_nodeid_display_ackuser index events_ackuser_idx |
|
| eventacktime | timestamp with time zone | null |
index events_acktime_idx |
|
| alarmid | integer | null |
index events_alarmid_idx |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| outages (public) | eventid | svclosteventid |
| outages (public) | eventid | svcregainedeventid |
| notifications (public) | eventid | eventid |
| alarms (public) | eventid | lasteventid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK ifservices_pkey |
| nodeid | integer | not null |
index ifservices_nodeid_ipaddr_svc_unique index ifservices_nodeid_ipaddr_status index ifservices_nodeid_status index ifservices_nodeid_idx index ifservices_nodeid_serviceid_idx |
|
| ipaddr | varchar(16) | not null |
index ifservices_nodeid_ipaddr_svc_unique index ifservices_nodeid_ipaddr_status |
|
| ifindex | integer | null | ||
| serviceid | integer | not null |
index ifservices_nodeid_ipaddr_svc_unique index ifservices_serviceid_idx index ifservices_nodeid_serviceid_idx |
|
| lastgood | timestamp with time zone | null | ||
| lastfail | timestamp with time zone | null | ||
| qualifier | char(16) | null | ||
| status | char(1) | null |
index ifservices_nodeid_ipaddr_status index ifservices_nodeid_status |
|
| source | char(1) | null | ||
| notify | char(1) | null | ||
| ipinterfaceid | integer | not null |
index ifservicves_ipinterfaceid_idx |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| service (public) | serviceid | serviceid |
| ipinterface (public) | ipinterfaceid | id |
| Detail Table | Column | Referencing Column |
| outages (public) | nodeid | nodeid |
| outages (public) | nodeid | nodeid |
| outages (public) | nodeid | nodeid |
| outages (public) | id | ifserviceid |
| application_service_map (public) | id | ifserviceid |
| location_specific_status_changes (public) | id | ifserviceid |
| Triggers | |
| setipinterfacekeysoninserttrigger | ROW BEFORE INSERT |
| setipinterfacekeysonupdatetrigger | ROW BEFORE UPDATE |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
index inventory_nodeid_idx index inventory_nodeid_name_idx |
|
| name | varchar(30) | not null |
index inventory_nodeid_name_idx |
|
| createtime | timestamp without time zone | not null | ||
| lastpolltime | timestamp without time zone | not null |
index inventory_lastpolltime_idx |
|
| pathtofile | varchar(256) | not null | ||
| status | char(1) | not null |
index inventory_status_idx |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK ipinterface_pkey |
| nodeid | integer | not null |
index ipinterface_nodeid_ipaddr_notzero_idx index ipinterface_nodeid_idx index ipinterface_nodeid_ipaddr_ismanaged_idx |
|
| ipaddr | varchar(16) | not null |
index ipinterface_nodeid_ipaddr_notzero_idx index ipinterface_nodeid_ismanaged_idx index ipinterface_ipaddr_idx index ipinterface_ipaddr_ismanaged_idx index ipinterface_nodeid_ipaddr_ismanaged_idx |
|
| ifindex | integer | null | ||
| iphostname | varchar(256) | null | ||
| ismanaged | char(1) | null |
index ipinterface_ipaddr_ismanaged_idx index ipinterface_nodeid_ipaddr_ismanaged_idx |
|
| ipstatus | integer | null | ||
| iplastcapsdpoll | timestamp with time zone | null | ||
| issnmpprimary | char(1) | null | ||
| snmpinterfaceid | integer | null |
index ipinterface_snmpinterfaceid_idx |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| snmpinterface (public) | snmpinterfaceid | id |
| Detail Table | Column | Referencing Column |
| ifservices (public) | id | ipinterfaceid |
| Triggers | |
| setsnmpinterfacekeysoninserttrigger | ROW BEFORE INSERT |
| setsnmpinterfacekeysonupdatetrigger | ROW BEFORE UPDATE |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
PK pk_iprouteinterface index iprouteinterface_node_ifdex_idx index iprouteinterface_nodeid_idx |
|
| routedest | varchar(16) | not null |
PK pk_iprouteinterface |
|
| routemask | varchar(16) | not null | ||
| routenexthop | varchar(16) | not null |
index iprouteinterface_rnh_idx |
|
| routeifindex | integer | not null |
index iprouteinterface_node_ifdex_idx |
|
| routemetric1 | integer | null | ||
| routemetric2 | integer | null | ||
| routemetric3 | integer | null | ||
| routemetric4 | integer | null | ||
| routemetric5 | integer | null | ||
| routetype | integer | null | ||
| routeproto | integer | null | ||
| status | char(1) | not null | ||
| lastpolltime | timestamp without time zone | not null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| locationmonitorid | integer | not null |
index location_monitor_details_id_property index location_monitor_details_id |
|
| property | varchar(255) | not null |
index location_monitor_details_id_property |
|
| propertyvalue | varchar(255) | null |
| Foreign Key | Column | Referenced Column |
| location_monitors (public) | locationmonitorid | id |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null |
PK location_monitors_pkey |
|
| status | varchar(31) | not null | ||
| lastcheckintime | timestamp with time zone | null | ||
| definitionname | varchar(31) | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| location_monitor_details (public) | id | locationmonitorid |
| location_specific_status_changes (public) | id | locationmonitorid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null |
PK location_specific_status_changes_pkey |
|
| locationmonitorid | integer | not null |
index location_specific_status_changes_locationmonitorid_ifserviceid index location_specific_status_changes_locationmonitorid index location_specific_status_changes_locationmonitorid_loc_if_time |
|
| ifserviceid | integer | not null |
index location_specific_status_changes_locationmonitorid_ifserviceid index location_specific_status_changes_ifserviceid index location_specific_status_changes_locationmonitorid_loc_if_time |
|
| statuscode | integer | not null | ||
| statustime | timestamp with time zone | not null |
index location_specific_status_changes_statustime index location_specific_status_changes_locationmonitorid_loc_if_time |
|
| statusreason | varchar(255) | null | ||
| responsetime | double precision | null |
| Foreign Key | Column | Referenced Column |
| ifservices (public) | ifserviceid | id |
| location_monitors (public) | locationmonitorid | id |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| mapid | integer | not null |
PK pk_mapid |
|
| mapname | varchar(40) | not null | ||
| mapbackground | varchar(256) | null | ||
| mapowner | varchar(64) | not null | ||
| mapcreatetime | timestamp without time zone | not null | ||
| mapaccess | char(6) | not null | ||
| userlastmodifies | varchar(64) | not null | ||
| lastmodifiedtime | timestamp without time zone | not null | ||
| mapscale | double precision | null | ||
| mapxoffset | integer | null | ||
| mapyoffset | integer | null | ||
| maptype | char(1) | null | ||
| mapwidth | integer | not null | ||
| mapheight | integer | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| element (public) | mapid | mapid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
PK pk_nodeid index node_id_type_idx |
|
| dpname | varchar(12) | null |
index node_dpname_idx |
|
| nodecreatetime | timestamp with time zone | not null | ||
| nodeparentid | integer | null | ||
| nodetype | char(1) | null |
index node_id_type_idx |
|
| nodesysoid | varchar(256) | null | ||
| nodesysname | varchar(256) | null | ||
| nodesysdescription | varchar(256) | null | ||
| nodesyslocation | varchar(256) | null | ||
| nodesyscontact | varchar(256) | null | ||
| nodelabel | varchar(256) | null |
index node_label_idx |
|
| nodelabelsource | char(1) | null | ||
| nodenetbiosname | varchar(16) | null | ||
| nodedomainname | varchar(16) | null | ||
| operatingsystem | varchar(64) | null | ||
| lastcapsdpoll | timestamp with time zone | null | ||
| foreignsource | varchar(64) | null |
index node_foreign_unique_idx |
|
| foreignid | varchar(64) | null |
index node_foreign_unique_idx |
| Foreign Key | Column | Referenced Column |
| distpoller (public) | dpname | dpname |
| Detail Table | Column | Referencing Column |
| atinterface (public) | nodeid | nodeid |
| stpnode (public) | nodeid | nodeid |
| stpinterface (public) | nodeid | nodeid |
| iprouteinterface (public) | nodeid | nodeid |
| datalinkinterface (public) | nodeid | nodeid |
| datalinkinterface (public) | nodeid | nodeparentid |
| inventory (public) | nodeid | nodeid |
| vlan (public) | nodeid | nodeid |
| ipinterface (public) | nodeid | nodeid |
| snmpinterface (public) | nodeid | nodeid |
| ifservices (public) | nodeid | nodeid |
| outages (public) | nodeid | nodeid |
| assets (public) | nodeid | nodeid |
| notifications (public) | nodeid | nodeid |
| pathoutage (public) | nodeid | nodeid |
| category_node (public) | nodeid | nodeid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| textmsg | varchar(4000) | not null | ||
| subject | varchar(256) | null | ||
| numericmsg | varchar(256) | null | ||
| notifyid | integer | not null |
PK pk_notifyid |
|
| pagetime | timestamp with time zone | null | ||
| respondtime | timestamp with time zone | null |
index notifications_respondtime_idx |
|
| answeredby | varchar(256) | null |
index notifications_answeredby_idx |
|
| nodeid | integer | null |
index notifications_nodeid_idx |
|
| interfaceid | varchar(16) | null |
index notifications_ipaddr_idx |
|
| serviceid | integer | null |
index notifications_serviceid_idx |
|
| queueid | varchar(256) | null | ||
| eventid | integer | null |
index notifications_eventid_idx |
|
| eventuei | varchar(256) | not null |
index notifications_eventuei_idx |
|
| notifconfigname | varchar(63) | null |
| Foreign Key | Column | Referenced Column |
| events (public) | eventid | eventid |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| usersnotified (public) | notifyid | notifyid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| outageid | integer | not null |
PK pk_outageid |
|
| svclosteventid | integer | null |
index outages_svclostid_idx |
|
| svcregainedeventid | integer | null |
index outages_svcregainedid_idx |
|
| nodeid | integer | not null |
index outages_nodeid_ipaddr_svc_idx index outages_nodeid_idx |
|
| ipaddr | varchar(16) | not null |
index outages_nodeid_ipaddr_svc_idx index outages_ipaddr_idx |
|
| serviceid | integer | not null |
index outages_serviceid_idx index outages_nodeid_ipaddr_svc_idx |
|
| iflostservice | timestamp with time zone | not null | ||
| ifregainedservice | timestamp with time zone | null |
index outages_regainedservice_idx |
|
| suppresstime | timestamp with time zone | null | ||
| suppressedby | varchar(256) | null | ||
| ifserviceid | integer | not null |
index outages_ifservivceid_idx |
| Foreign Key | Column | Referenced Column |
| events (public) | svclosteventid | eventid |
| events (public) | svcregainedeventid | eventid |
| node (public) | nodeid | nodeid |
| service (public) | serviceid | serviceid |
| ifservices (public) | nodeid | nodeid |
| ifservices (public) | ifserviceid | id |
| Detail Table | Column | Referencing Column |
| Triggers | |
| setifservicekeysoninserttrigger | ROW BEFORE INSERT |
| setifservicekeysonupdatetrigger | ROW BEFORE UPDATE |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | null |
index pathoutage_nodeid |
|
| criticalpathip | varchar(16) | not null |
index pathoutage_criticalpathip |
|
| criticalpathservicename | varchar(32) | null |
index pathoutage_criticalpathservicename_idx |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null |
PK pollresult_pkey |
|
| pollid | integer | null |
index pollresults_poll_id |
|
| nodeid | integer | null |
index pollresults_service |
|
| ipaddr | varchar(16) | null |
index pollresults_service |
|
| ifindex | integer | null |
index pollresults_service |
|
| serviceid | integer | null |
index pollresults_service |
|
| statuscode | integer | null | ||
| statusname | varchar(32) | null | ||
| reason | varchar(128) | null |
| Foreign Key | Column | Referenced Column |
| demandpolls (public) | pollid | id |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| trigger_name | varchar(80) | not null |
PK pk_qrtz_blob_triggers |
|
| trigger_group | varchar(80) | not null |
PK pk_qrtz_blob_triggers |
|
| blob_data | bytea | null |
| Foreign Key | Column | Referenced Column |
| qrtz_triggers (public) | trigger_name | trigger_name |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| calendar_name | varchar(80) | not null |
PK pk_qrtz_calendars |
|
| calendar | bytea | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| trigger_name | varchar(80) | not null |
PK pk_qrtz_cron_triggers |
|
| trigger_group | varchar(80) | not null |
PK pk_qrtz_cron_triggers |
|
| cron_expression | varchar(80) | not null | ||
| time_zone_id | varchar(80) | null |
| Foreign Key | Column | Referenced Column |
| qrtz_triggers (public) | trigger_name | trigger_name |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| entry_id | varchar(95) | not null |
PK pk_qrtz_fired_triggers |
|
| trigger_name | varchar(80) | not null | ||
| trigger_group | varchar(80) | not null | ||
| is_volatile | boolean | not null | ||
| instance_name | varchar(80) | not null | ||
| fired_time | bigint | not null | ||
| state | varchar(16) | not null | ||
| job_name | varchar(80) | null | ||
| job_group | varchar(80) | null | ||
| is_stateful | boolean | null | ||
| requests_recovery | boolean | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| job_name | varchar(80) | not null |
PK qrtz_job_details_pkey |
|
| job_group | varchar(80) | not null |
PK qrtz_job_details_pkey |
|
| description | varchar(120) | null | ||
| job_class_name | varchar(128) | not null | ||
| is_durable | boolean | not null | ||
| is_volatile | boolean | not null | ||
| is_stateful | boolean | not null | ||
| requests_recovery | boolean | not null | ||
| job_data | bytea | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| qrtz_job_listeners (public) | job_name | job_name |
| qrtz_job_listeners (public) | job_name | job_name |
| qrtz_triggers (public) | job_name | job_name |
| qrtz_triggers (public) | job_name | job_name |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| job_name | varchar(80) | not null |
PK pk_qrtz_job_listeners |
|
| job_group | varchar(80) | not null |
PK pk_qrtz_job_listeners |
|
| job_listener | varchar(80) | not null |
PK pk_qrtz_job_listeners |
| Foreign Key | Column | Referenced Column |
| qrtz_job_details (public) | job_name | job_name |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| lock_name | varchar(40) | not null |
PK pk_qrtz_locks |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| trigger_group | varchar(80) | not null |
PK pk_qrtz_paused_trigger_grps |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| instance_name | varchar(80) | not null |
PK pk_qrtz_scheduler_state |
|
| last_checkin_time | bigint | not null | ||
| checkin_interval | bigint | not null | ||
| recoverer | varchar(80) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| trigger_name | varchar(80) | not null |
PK pk_qrtz_simple_triggers |
|
| trigger_group | varchar(80) | not null |
PK pk_qrtz_simple_triggers |
|
| repeat_count | bigint | not null | ||
| repeat_interval | bigint | not null | ||
| times_triggered | bigint | not null |
| Foreign Key | Column | Referenced Column |
| qrtz_triggers (public) | trigger_name | trigger_name |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| trigger_name | varchar(80) | not null |
PK pk_qrtz_trigger_listeners |
|
| trigger_group | varchar(80) | not null |
PK pk_qrtz_trigger_listeners |
|
| trigger_listener | varchar(80) | not null |
PK pk_qrtz_trigger_listeners |
| Foreign Key | Column | Referenced Column |
| qrtz_triggers (public) | trigger_name | trigger_name |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| trigger_name | varchar(80) | not null |
PK pk_qrtz_triggers |
|
| trigger_group | varchar(80) | not null |
PK pk_qrtz_triggers |
|
| job_name | varchar(80) | not null | ||
| job_group | varchar(80) | not null | ||
| is_volatile | boolean | not null | ||
| description | varchar(120) | null | ||
| next_fire_time | bigint | null | ||
| prev_fire_time | bigint | null | ||
| trigger_state | varchar(16) | not null | ||
| trigger_type | varchar(8) | not null | ||
| start_time | bigint | not null | ||
| end_time | bigint | null | ||
| calendar_name | varchar(80) | null | ||
| misfire_instr | smallint | null | ||
| job_data | bytea | null |
| Foreign Key | Column | Referenced Column |
| qrtz_job_details (public) | job_name | job_name |
| Detail Table | Column | Referencing Column |
| qrtz_blob_triggers (public) | trigger_name | trigger_name |
| qrtz_blob_triggers (public) | trigger_name | trigger_name |
| qrtz_cron_triggers (public) | trigger_name | trigger_name |
| qrtz_cron_triggers (public) | trigger_name | trigger_name |
| qrtz_simple_triggers (public) | trigger_name | trigger_name |
| qrtz_simple_triggers (public) | trigger_name | trigger_name |
| qrtz_trigger_listeners (public) | trigger_name | trigger_name |
| qrtz_trigger_listeners (public) | trigger_name | trigger_name |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| reportid | integer | not null | ||
| reportcategory | varchar(256) | not null | ||
| reportdate | timestamp with time zone | not null | ||
| reportformat | varchar(256) | not null | ||
| reporttype | varchar(256) | not null | ||
| reportlocation | varchar(256) | not null | ||
| reportavailable | boolean | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK pk_resourcereference_id |
| resourceid | varchar(255) | not null |
index resourcereference_resourceid |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| statisticsreportdata (public) | id | resourceid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| ipaddr | varchar(16) | not null | ||
| servername | varchar(64) | not null |
index server_name_idx |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| serviceid | integer | not null |
PK pk_serviceid |
|
| servicename | varchar(32) | not null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| ifservices (public) | serviceid | serviceid |
| outages (public) | serviceid | serviceid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| ipaddr | varchar(16) | not null |
index servicemap_ipaddr_idx |
|
| servicemapname | varchar(32) | not null |
index servicemap_name_idx |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK snmpinterface_pkey |
| nodeid | integer | not null |
index snmpinterface_nodeid_ifindex_unique_idx index snmpinterface_nodeid_idx |
|
| ipaddr | varchar(16) | not null |
index snmpinterface_ipaddr_idx |
|
| snmpipadentnetmask | varchar(16) | null | ||
| snmpphysaddr | char(12) | null | ||
| snmpifindex | integer | not null |
index snmpinterface_nodeid_ifindex_unique_idx |
|
| snmpifdescr | varchar(256) | null | ||
| snmpiftype | integer | null | ||
| snmpifname | varchar(96) | null | ||
| snmpifspeed | bigint | null | ||
| snmpifadminstatus | integer | null | ||
| snmpifoperstatus | integer | null | ||
| snmpifalias | varchar(256) | null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| ipinterface (public) | id | snmpinterfaceid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK pk_statisticsreport_id |
| startdate | timestamp with time zone | not null |
index statisticsreport_startdate |
|
| enddate | timestamp with time zone | not null | ||
| name | varchar(63) | not null |
index statisticsreport_name |
|
| description | varchar(255) | not null | ||
| jobstarteddate | timestamp with time zone | not null | ||
| jobcompleteddate | timestamp with time zone | not null | ||
| purgedate | timestamp with time zone | not null |
index statisticsreport_purgedate |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| statisticsreportdata (public) | id | reportid |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('opennmsnxtid'::regclass) |
PK pk_statsdata_id |
| reportid | integer | not null |
index statsdata_unique |
|
| resourceid | integer | not null |
index statsdata_unique |
|
| value | double precision | not null |
| Foreign Key | Column | Referenced Column |
| statisticsreport (public) | reportid | id |
| resourcereference (public) | resourceid | id |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
PK pk_stpinterface index stpinterface_node_idx index stpinterface_node_ifindex_idx |
|
| bridgeport | integer | not null |
PK pk_stpinterface |
|
| ifindex | integer | not null |
index stpinterface_node_ifindex_idx |
|
| stpportstate | integer | null | ||
| stpportpathcost | integer | null | ||
| stpportdesignatedroot | varchar(16) | null | ||
| stpportdesignatedcost | integer | null | ||
| stpportdesignatedbridge | varchar(16) | null |
index stpinterface_stpdesbridge_idx |
|
| stpportdesignatedport | varchar(4) | null | ||
| status | char(1) | not null | ||
| lastpolltime | timestamp without time zone | not null | ||
| stpvlan | integer | not null |
PK pk_stpinterface index stpinterface_stpvlan_idx |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
PK pk_stpnode index stpnode_nodeid_idx |
|
| basebridgeaddress | varchar(12) | not null |
index stpnode_basebridgeaddress_idx |
|
| basenumports | integer | null | ||
| basetype | integer | null | ||
| stpprotocolspecification | integer | null | ||
| stppriority | integer | null | ||
| stpdesignatedroot | varchar(16) | null |
index stpnode_stpdesignatedroot_idx |
|
| stprootcost | integer | null | ||
| stprootport | integer | null | ||
| status | char(1) | not null | ||
| lastpolltime | timestamp without time zone | not null | ||
| basevlan | integer | not null |
PK pk_stpnode |
|
| basevlanname | varchar(32) | null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null | nextval('testdatatypes_id_seq'::regclass) | |
| num52 | numeric(5, 2) | null | ||
| num63 | numeric(6, 3) | null | ||
| num84 | numeric(8, 4) | null | ||
| realvalue | real | null | ||
| dec52 | numeric(5, 2) | null | ||
| dec63 | numeric(6, 3) | null | ||
| dec84 | numeric(8, 4) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| id | integer | not null |
PK pk_usernotificationid |
|
| userid | varchar(256) | not null |
index userid_notifyid_idx |
|
| notifyid | integer | null |
index userid_notifyid_idx |
|
| notifytime | timestamp with time zone | null | ||
| media | varchar(32) | null | ||
| contactinfo | varchar(64) | null | ||
| autonotify | char(1) | null |
| Foreign Key | Column | Referenced Column |
| notifications (public) | notifyid | notifyid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| nodeid | integer | not null |
PK pk_vlan |
|
| vlanid | integer | not null |
PK pk_vlan |
|
| vlanname | varchar(64) | not null |
index vlan_vlanname_idx |
|
| vlantype | integer | null | ||
| vlanstatus | integer | null | ||
| status | char(1) | not null | ||
| lastpolltime | timestamp without time zone | not null |
| Foreign Key | Column | Referenced Column |
| node (public) | nodeid | nodeid |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| vulnerabilityid | integer | not null |
PK pk_vulnerabilityid |
|
| nodeid | integer | null |
index vulnerabilities_nodeid_idx |
|
| ipaddr | varchar(16) | null |
index vulnerabilities_ipaddr_idx |
|
| serviceid | integer | null | ||
| creationtime | timestamp with time zone | not null | ||
| lastattempttime | timestamp with time zone | not null | ||
| lastscantime | timestamp with time zone | not null | ||
| resolvedtime | timestamp with time zone | null | ||
| severity | integer | not null |
index vulnerabilities_severity_idx |
|
| pluginid | integer | not null | ||
| pluginsubid | integer | not null | ||
| logmsg | varchar(256) | null | ||
| descr | text | null | ||
| port | integer | null |
index vulnerabilities_port_idx |
|
| protocol | varchar(32) | null |
index vulnerabilities_protocol_idx |
|
| cveentry | varchar(255) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
| Column | Data Type | Nullable | Default | PK / Index |
| pluginid | integer | not null |
index vulnplugins_plugin_idx |
|
| pluginsubid | integer | not null |
index vulnplugins_plugin_idx |
|
| name | varchar(128) | null | ||
| category | varchar(32) | null | ||
| copyright | varchar(128) | null | ||
| descr | text | null | ||
| summary | varchar(256) | null | ||
| family | varchar(32) | null | ||
| version | varchar(32) | null | ||
| cveentry | varchar(255) | null | ||
| md5 | varchar(32) | null |
| Foreign Key | Column | Referenced Column |
| Detail Table | Column | Referencing Column |
| Triggers |
CREATE OR REPLACE FUNCTION public.getmanagedoutageforintfinwindow(integer, character varying, timestamp without time zone, timestamp without time zone) RETURNS double precision LANGUAGE plpgsql AS $function$ DECLARE nid ALIAS FOR $1; ipid ALIAS FOR $2; xtime ALIAS FOR $3; ytime ALIAS FOR $4; downtime float8 := 0.0; orec RECORD; BEGIN FOR orec IN SELECT distinct ifservices.nodeid, ifservices.ipaddr, ifservices.serviceid FROM ipinterface, ifservices where ifservices.nodeid = nid AND ifservices.ipaddr = ipid AND ipinterface.nodeid = nid AND ipinterface.ipaddr = ipid AND ipinterface.ismanaged = 'M' AND ifservices.status = 'A' LOOP BEGIN downtime := downtime + getOutageTimeInWindow( orec.nodeid, orec.ipaddr, orec.serviceid, xtime, ytime); END; END LOOP; RETURN downtime; END; $function$
CREATE OR REPLACE FUNCTION public.getmanagedoutagefornodeinwindow(integer, timestamp without time zone, timestamp without time zone) RETURNS double precision LANGUAGE plpgsql AS $function$ DECLARE nid ALIAS FOR $1; xtime ALIAS FOR $2; ytime ALIAS FOR $3; downtime float8 := 0.0; orec RECORD; BEGIN FOR orec IN SELECT distinct ifservices.nodeid, ifservices.ipaddr, ifservices.serviceid FROM ipinterface, ifservices, node where ifservices.nodeid = nid AND ipinterface.nodeid = nid AND ipinterface.ismanaged = 'M' AND ifservices.ipaddr = ipinterface.ipaddr AND ifservices.status = 'A' AND node.nodeid = nid and node.nodetype = 'A' LOOP BEGIN downtime := downtime + getOutageTimeInWindow( orec.nodeid, orec.ipaddr, orec.serviceid, xtime, ytime); END; END LOOP; RETURN downtime; END; $function$
CREATE OR REPLACE FUNCTION public.getmanagedservicecountforintf(integer, character varying) RETURNS double precision LANGUAGE plpgsql AS $function$ DECLARE nid ALIAS FOR $1; ipid ALIAS FOR $2; orec RECORD; counter float8; BEGIN counter = 0; FOR orec IN SELECT DISTINCT ifservices.nodeid, ifservices.ipaddr, ifservices.serviceid FROM ipinterface, ifservices WHERE ifservices.nodeid = nid AND ifservices.ipaddr = ipid AND ipinterface.nodeid = nid AND ipinterface.ipaddr = ipid AND ipinterface.ismanaged = 'M' AND ifservices.status = 'A' LOOP BEGIN counter := counter + 1; END; END LOOP; RETURN counter; END; $function$
CREATE OR REPLACE FUNCTION public.getmanagedservicecountfornode(integer)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
nid ALIAS FOR $1;
orec RECORD;
counter float8;
BEGIN
counter = 0;
FOR orec IN SELECT distinct ifservices.nodeid, ifservices.serviceid, ifservices.ipaddr
FROM ipinterface, ifservices
WHERE ifservices.nodeid = nid
AND ipinterface.nodeid = nid
AND ipinterface.ismanaged = 'M'
AND ifservices.ipaddr = ipinterface.ipaddr
AND ifservices.status = 'A'
LOOP
BEGIN
counter := counter + 1;
END;
END LOOP;
RETURN counter;
END;
$function$
CREATE OR REPLACE FUNCTION public.getmanagepercentavailintfwindow(integer, character varying, timestamp without time zone, timestamp without time zone)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
nid ALIAS FOR $1;
ipid ALIAS FOR $2;
xtime ALIAS FOR $3;
ytime ALIAS FOR $4;
downtime float8 := 0.0;
count integer := 0;
rollingWindow float := 0;
totalServiceTime float := 0;
BEGIN
IF xtime < ytime THEN
rollingWindow := EXTRACT (EPOCH FROM (ytime - xtime));
downtime := getManagedOutageForIntfInWindow(nid, ipid, ytime, xtime)/1000;
ELSE
rollingWindow := EXTRACT (EPOCH FROM (xtime - ytime));
downtime := getManagedOutageForIntfInWindow(nid, ipid, xtime, ytime)/1000;
END IF;
count := getManagedServiceCountForIntf(nid, ipid);
totalServiceTime := count * rollingWindow;
IF totalServiceTime > 0 THEN
RETURN 100 * (1 - (downtime / totalServiceTime));
ELSE
IF totalServiceTime = 0 THEN
RETURN 100;
ELSE
RETURN -1;
END IF;
END IF;
END;
$function$
CREATE OR REPLACE FUNCTION public.getmanagepercentavailnodewindow(integer, timestamp without time zone, timestamp without time zone)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
nid ALIAS FOR $1;
xtime ALIAS FOR $2;
ytime ALIAS FOR $3;
downtime float8 := 0.0;
count integer := 0;
rollingWindow float := 0;
totalServiceTime float := 0;
BEGIN
IF xtime < ytime THEN
rollingWindow := EXTRACT (EPOCH FROM (ytime - xtime));
downtime := getManagedOutageForNodeInWindow(nid, ytime, xtime)/1000;
ELSE
rollingWindow := EXTRACT (EPOCH FROM (xtime - ytime));
downtime := getManagedOutageForNodeInWindow(nid, xtime, ytime)/1000;
END IF;
count := getManagedServiceCountForNode(nid);
totalServiceTime := count * rollingWindow;
IF totalServiceTime > 0 THEN
RETURN 100 * (1 - (downtime / totalServiceTime));
ELSE
IF totalServiceTime = 0 THEN
RETURN 100;
ELSE
RETURN -1;
END IF;
END IF;
END;
$function$
CREATE OR REPLACE FUNCTION public.getoutagetimeinwindow(integer, character varying, integer, timestamp without time zone, timestamp without time zone)
RETURNS double precision
LANGUAGE plpgsql
AS $function$
DECLARE
nid ALIAS FOR $1;
ipid ALIAS FOR $2;
sid ALIAS FOR $3;
xtime ALIAS FOR $4;
ytime ALIAS FOR $5;
orec RECORD;
lostTime timestamp without time zone;
gainTime timestamp without time zone;
downtime float8;
zero CONSTANT float8 := 0.0;
epochTime CONSTANT timestamp without time zone := to_timestamp('01 Jan 1970 00:00:00', 'DD Mon YYYY HH24:MI:SS');
BEGIN
downtime = zero;
FOR orec IN SELECT ifLostService,ifRegainedService
FROM outages WHERE (nodeID = nid AND ipAddr = ipid AND serviceID = sid AND
((ifRegainedService IS NULL AND ifLostService <= xtime) OR
(ifRegainedService > ytime)))
LOOP
BEGIN
gainTime := epochTime;
lostTime := orec.ifLostService;
IF orec.ifRegainedService IS NOT NULL THEN
gainTime := orec.ifRegainedService;
END IF;
--
-- Find the appropriate records
--
IF xtime > lostTime THEN
--
-- for any outage to be in window of
-- opportunity the lost time must ALWAYS be
-- less that the x time.
--
IF gainTime = epochTime THEN
--
-- if the gain time is epochTime then the outage
-- does not have an uptime.
--
IF ytime > lostTime THEN
downtime := downtime + EXTRACT(EPOCH FROM (xtime - ytime));
ELSE
downtime := downtime + EXTRACT(EPOCH FROM (xtime - lostTime));
END IF;
ELSE
IF xtime > gainTime AND gainTime > ytime THEN
--
-- regain time between x & y
--
IF ytime > lostTime THEN
downtime := downtime + EXTRACT (EPOCH FROM (gainTime - ytime));
ELSE
downtime := downtime + EXTRACT (EPOCH FROM (gainTime - lostTime));
END IF;
ELSE
IF gainTime > xtime THEN
--
-- regain time greater than x, lost less that x
--
IF ytime > lostTime THEN
downtime := downtime + EXTRACT (EPOCH FROM (xtime - ytime));
ELSE
downtime := downtime + EXTRACT (EPOCH FROM (xtime - lostTime));
END IF;
-- end gainTime > xtime
END IF;
-- end xtime > gainTime AND gainTime > ytime
END IF;
-- end gaintime == epochTime
END IF;
-- end xtime > lostTime
END IF;
END;
END LOOP;
RETURN downtime*1000.0;
END;
$function$
CREATE OR REPLACE FUNCTION public.getpercentavailabilityinwindow(integer, character varying, integer, timestamp without time zone, timestamp without time zone) RETURNS double precision LANGUAGE plpgsql AS $function$ DECLARE nid ALIAS FOR $1; ipid ALIAS FOR $2; sid ALIAS FOR $3; xtime ALIAS FOR $4; ytime ALIAS FOR $5; downtime float8; BEGIN downtime := getOutageTimeInWindow(nid, ipid, sid, xtime, ytime); IF xtime > ytime THEN RETURN 100 * (1 - (downtime / (EXTRACT(EPOCH FROM (xtime - ytime))* 1000))); ELSE RETURN 100 * (1 - (downtime / (EXTRACT(EPOCH FROM (ytime - xtime))* 1000))); END IF; END; $function$
CREATE OR REPLACE FUNCTION public.setifservicekeysoninsert()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
--
-- (Used with Trigger Insert with old style foreign key)
-- This condition keeps the ifServiceID inSync with the composite foreign key of nodeid, ipaddr, serviceid
-- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
-- code has no knowledge of the new keys
--
IF NEW.ifServiceId IS NULL
THEN
SELECT ifsvc.id INTO NEW.ifserviceid
FROM ifservices ifsvc
WHERE (ifsvc.nodeid = NEW.nodeid AND ifsvc.ipAddr = NEW.ipAddr AND ifsvc.serviceid = NEW.serviceid);
IF NOT FOUND
THEN
RAISE EXCEPTION 'Outages Trigger Exception, Condition 1: No service found for... nodeid: % ipaddr: % serviceid: %', NEW.nodeid, NEW.ipAddr, NEW.serviceid;
END IF;
--
-- (Used with Trigger Insert with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, serviceid inSync with the ifserviceid
-- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
-- the composite key columns
--
ELSIF NEW.ifServiceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL OR NEW.serviceId IS NULL)
THEN
SELECT ifsvc.nodeId, ifsvc.ipAddr, ifsvc.serviceId INTO NEW.nodeId, NEW.ipAddr, NEW.serviceId
FROM ifservices ifsvc
WHERE (ifsvc.id = NEW.ifServiceId);
IF NOT FOUND THEN
RAISE EXCEPTION 'Outages Trigger Exception, Condition 2: No service found for serviceID: %', NEW.ifServiceId;
END IF;
END IF;
RETURN NEW;
END;
$function$
CREATE OR REPLACE FUNCTION public.setifservicekeysonupdate()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
--
-- (Update with old style foreign key)
-- This condition keeps ifserviceid inSync with the composite foreign key of nodeid, ipaddr, serviceid
-- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
-- one or more of the composite key values, the ifServiceId needs to be updated
--
IF (NEW.ifserviceID = OLD.ifServiceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr OR NEW.serviceId != OLD.serviceID)
THEN
SELECT ifsvc.id INTO NEW.ifserviceid
FROM ifservices ifsvc
WHERE (ifsvc.nodeid = NEW.nodeid AND ifsvc.ipAddr = NEW.ipAddr AND ifsvc.serviceid = NEW.serviceid);
IF NOT FOUND THEN
RAISE EXCEPTION 'Outages Trigger Exception, Condition 3: No service found for... nodeid: % ipaddr: % serviceid: %', NEW.nodeid, NEW.ipAddr, NEW.serviceid;
END IF;
--
-- (Update with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, serviceid inSync with the ifserviceid
-- This usually happens with the Hibernate DAOs decide to change the ifserviceid (MonitoredService) represented
-- by the outage.
--
ELSIF NEW.ifServiceId != OLD.ifServiceId
THEN
SELECT ifsvc.nodeId, ifsvc.ipAddr, ifsvc.serviceId INTO NEW.nodeId, NEW.ipAddr, NEW.serviceId
FROM ifservices ifsvc
WHERE (ifsvc.id = NEW.ifServiceId);
IF NOT FOUND THEN
RAISE EXCEPTION 'Outages Trigger Exception, Condition 4: No service found for serviceID: %', NEW.ifServiceId;
END IF;
END IF;
RETURN NEW;
END;
$function$
CREATE OR REPLACE FUNCTION public.setipinterfacekeysoninsert()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
-- ifServices must have an IP address that is *not* 0.0.0.0
IF NEW.ipAddr IS NOT NULL AND NEW.ipAddr = '0.0.0.0'
THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 0: ipAddr of 0.0.0.0 is not allowed in ifServices table';
END IF;
--
-- (Insert with old style foreign key)
-- This condition keeps the ipInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
-- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
-- code has no knowledge of the new keys
--
IF NEW.ipInterfaceId IS NULL
THEN
SELECT ipif.id INTO NEW.ipInterfaceId
FROM ipinterface ipif
WHERE (ipif.nodeid = NEW.nodeid AND ipif.ipAddr = NEW.ipAddr AND ipif.ipAddr != '0.0.0.0');
IF NOT FOUND
THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 1: No IpInterface found for... nodeid: % ipaddr: %', NEW.nodeid, NEW.ipAddr;
END IF;
--
-- (Insert with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipInterfaceId
-- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
-- the composite key columns
--
ELSIF NEW.ipInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ipAddr IS NULL)
THEN
SELECT ipif.nodeid, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeid, NEW.ipAddr, NEW.ifIndex
FROM ipinterface ipif
WHERE (ipif.id = NEW.ipInterfaceId);
IF NOT FOUND
THEN
RAISE EXCEPTION 'IfServices Trigger Exception: No ipinterface found for ipInterfaceId: %', NEW.ipInterfaceId;
END IF;
IF NEW.ipAddr = '0.0.0.0'
THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr', NEW.ipInterfaceId;
END IF;
END IF;
RETURN NEW;
END;
$function$
CREATE OR REPLACE FUNCTION public.setipinterfacekeysonupdate()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
-- ifServices must have an IP address that is *not* 0.0.0.0
IF NEW.ipAddr IS NOT NULL AND NEW.ipAddr = '0.0.0.0'
THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 0: ipAddr of 0.0.0.0 is not allowed in ifServices table';
END IF;
--
-- (Used with Trigger Update with old style foreign key)
-- This condition keeps ipinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
-- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
-- one or more of the composite key values, the ipInterfaceId needs to be updated
--
IF (NEW.ipInterfaceId = OLD.ipInterfaceId) AND (NEW.nodeId != OLD.nodeId OR NEW.ipAddr != OLD.ipAddr)
THEN
SELECT ipif.id INTO NEW.ipInterfaceId
FROM ipinterface ipif
WHERE (ipif.nodeid = NEW.nodeid AND ipif.ipAddr = NEW.ipAddr AND ipif.ipAddr != '0.0.0.0');
IF NOT FOUND THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 3: No IpInterface found for... nodeid: % ipaddr: % ', NEW.nodeid, NEW.ipAddr;
END IF;
--
-- (Used with Trigger Update with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the ipinterfaceid
-- This usually happens with the Hibernate DAOs decide to change the ipinterfaceid represented
-- by the ifservices.
--
ELSIF NEW.ipInterfaceId != OLD.ipInterfaceId
THEN
SELECT ipif.nodeId, ipif.ipAddr, ipif.ifIndex INTO NEW.nodeId, NEW.ipAddr, NEW.ifIndex
FROM ipinterface ipif
WHERE (ipif.id = NEW.ipInterfaceId);
IF NOT FOUND THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 4: No IpInterface found for ipInterfaceId: %', NEW.ipInterfaceId;
END IF;
IF NEW.ipAddr = '0.0.0.0'
THEN
RAISE EXCEPTION 'IfServices Trigger Exception, Condition 5: IpInterface found for ipInterfaceId: % has 0.0.0.0 ipAddr', NEW.ipInterfaceId;
END IF;
END IF;
RETURN NEW;
END;
$function$
CREATE OR REPLACE FUNCTION public.setsnmpinterfacekeysoninsert()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
--
-- (Used for Trigger insert with old style foreign key)
-- This condition keeps the snmpInterfaceId inSync with the composite foreign key of nodeid, ipaddr, ifindex
-- This usually happens when a new record is written by our JDBC code (non-Hibernate DAO) for the old JDBC style
-- code has no knowledge of the new keys
--
IF NEW.snmpInterfaceId IS NULL
THEN
IF NEW.ifIndex IS NOT NULL
THEN
SELECT snmpif.id INTO NEW.snmpInterfaceId
FROM snmpinterface snmpif
WHERE (snmpif.nodeid = NEW.nodeid AND snmpif.snmpIfIndex = NEW.ifIndex);
IF NOT FOUND
THEN
RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 1: No SnmpInterface found for... nodeid: % ifindex: %', NEW.nodeid, NEW.ifIndex;
END IF;
END IF;
--
-- (Used for Insert with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the SnmpInterfaceId
-- This usually happens when a new record is written by our Hibernate DAOs... these DAOs have no knowledge of
-- the composite key columns
--
ELSIF NEW.snmpInterfaceId IS NOT NULL AND (NEW.nodeId IS NULL OR NEW.ifIndex IS NULL)
THEN
SELECT snmpif.nodeid, snmpif.snmpIfIndex INTO NEW.nodeid, NEW.ifIndex
FROM snmpinterface snmpif
WHERE (snmpif.id = NEW.snmpInterfaceId);
IF NOT FOUND
THEN
RAISE EXCEPTION 'IpInterface Trigger Notice: No SnmpInterface found for snmpInterfaceId: %', NEW.snmpInterfaceId;
END IF;
END IF;
RETURN NEW;
END;
$function$
CREATE OR REPLACE FUNCTION public.setsnmpinterfacekeysonupdate()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
--
-- (Used for Trigger update with old style foreign key)
-- This condition keeps snmpinterfaceid inSync with the composite foreign key of nodeid, ipaddr, ifindex
-- This usually happens when a record is being updated by old JDBC code (non-Hibernate DAOs) and has changed
-- one or more of the composite key values, the snmpInterfaceId needs to be updated
--
IF ((NEW.snmpInterfaceId = OLD.snmpInterfaceId OR (NEW.snmpInterfaceId IS NULL AND OLD.snmpInterfaceId IS NULL)) AND
(NEW.nodeId != OLD.nodeId OR NEW.ifIndex != OLD.ifIndex OR (NEW.ifIndex IS NULL AND OLD.ifIndex IS NOT NULL) OR (NEW.ifIndex IS NOT NULL AND OLD.ifIndex IS NULL)))
THEN
IF NEW.ifIndex IS NULL AND NEW.snmpInterfaceId IS NOT NULL
THEN
SELECT NULL INTO NEW.snmpInterfaceId;
ELSIF NEW.ifIndex IS NOT NULL
THEN
SELECT snmpif.id INTO NEW.snmpInterfaceId
FROM snmpinterface snmpif
WHERE (snmpif.nodeid = NEW.nodeid AND snmpif.snmpIfIndex = NEW.ifIndex);
IF NOT FOUND THEN
RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 3: No SnmpInterface found for... nodeid: % ifindex: %', NEW.nodeid, NEW.ifIndex;
END IF;
END IF;
--
-- (Used for Trigger update with new style foreign key)
-- This condition keeps the composite foreign key of nodeid, ipaddr, ifindex inSync with the snmpinterfaceid
-- This usually happens with the Hibernate DAOs decide to change the snmpinterfaceid represented
-- by the ipinterface.
--
-- We dont match on the case where NEW.snmpInterfaceId IS NULL, because we use it in the WHERE clause.
--
ELSIF (NEW.snmpInterfaceId != OLD.snmpInterfaceId OR (NEW.snmpInterfaceId IS NOT NULL AND OLD.snmpInterfaceId IS NULL))
THEN
SELECT snmpif.nodeId, snmpif.snmpIfIndex INTO NEW.nodeId, NEW.ifIndex
FROM snmpinterface snmpif
WHERE (snmpif.id = NEW.snmpInterfaceId);
IF NOT FOUND THEN
RAISE EXCEPTION 'IpInterface Trigger Notice, Condition 4: No SnmpInterface found for snmpInterfaceId: %', NEW.snmpInterfaceId;
END IF;
END IF;
RETURN NEW;
END;
$function$
CREATE SEQUENCE public.alarmsnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.catnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.demandpollnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.eventsnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.mapnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.nodenxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.notifynxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.opennmsnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.outagenxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.pollresultnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.reportnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.servicenxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.testdatatypes_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.usernotifnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE
CREATE SEQUENCE public.vulnnxtid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE