Uploaded image for project: 'OpenNMS'
  1. OpenNMS
  2. NMS-6868

"Interface Availability Report" & "Snmp Interface Availability Report" of Database Reports are not working

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.13.0
    • Fix Version/s: 14.0.0
    • Component/s: Reports / Statsd
    • Security Level: Default (Default Security Scheme)
    • Labels:
    • Environment:
      attaching the report.log and the psql DB.

      Description

      In the Reports->Database Reports->List Reports page, I ran "Interface Availability Report" & "Snmp Interface Availability Report". But empty report gets displayed even though there are outages on the interfaces. Following are the details :

      Note: I found this relevant old pr. NMS-5848 : Interface Availability Report - Is always blank

      I enabled the debug mode for report.log and observed that the result of the sql query is empty::

      1) Following is the query for "Interface Availability Report" ::
      SELECT interface_outages.nodeid, interface_outages.nodelabel
      FROM( SELECT
      outages_scope.ipaddr,
      outages_scope.eventuei,
      outages_scope.nodeid,
      outages_scope.nodelabel

      FROM
      (SELECT
      outages.nodeid,
      outages.iflostservice,
      outages.ifregainedservice,
      outages.ipaddr,
      events.eventuei,
      node.nodelabel
      FROM
      outages, service, events, node
      WHERE
      (iflostservice, ifregainedservice) OVERLAPS ('2013-09-15 00:00:00 +0000'::TIMESTAMP, '2013-09-15 00:00:00 +0000'::TIMESTAMP + '22 Days'::INTERVAL) AND
      outages.serviceid = service.serviceid AND
      outages.svclosteventid = events.eventid AND
      events.eventuei = 'uei.opennms.org/nodes/interfaceDown' AND
      node.nodeid = outages.nodeid)
      AS
      outages_scope)
      AS
      interface_outages

      GROUP BY
      interface_outages.nodeid,
      interface_outages.nodelabel;

      2) Following is the query executed for "Snmp Interface Availability Report" ::

      SELECT
      interface_outages.nodeid,
      n.nodelabel,
      s.snmpifdescr,
      s.snmpifname,
      interface_outages.ifindex,
      SUM(interface_outages.outage_counter) as outages_count,
      SUM(interface_outages.duration) as duration_tally,
      AVG(interface_outages.duration) as average_duration,
      100 - SUM(EXTRACT (epoch from interface_outages.duration)) * 100 / interface_outages.avail_total AS avail_percent,
      SUM(EXTRACT (epoch from interface_outages.duration)) * 100 / interface_outages.avail_total AS outages_percent,
      AVG( EXTRACT( epoch from interface_outages.duration)) as average_duration_seconds,
      SUM(EXTRACT (epoch from interface_outages.duration)) AS outages_seconds
      FROM
      (SELECT
      outage_scope.nodeid,
      outage_scope.ifindex,
      EXTRACT (epoch from ('2013-09-15 00:00:00 +0000'::TIMESTAMP + '22 Days'::INTERVAL) - '2013-09-15 00:00:00 +0000'::TIMESTAMP) AS avail_total,
      outage_scope.outage_end - outage_scope.outage_start as duration,
      CAST('1' AS INTEGER) as outage_counter
      FROM
      (SELECT
      outages.nodeid,
      outages.ifindex,
      greatest('2013-09-15 00:00:00 +0000'::TIMESTAMP, outages.iflostservice) as outage_start,
      case when outages.ifregainedservice is null then '2013-09-15 00:00:00 +0000'::TIMESTAMP + '22 Days'::INTERVAL else least('2013-09-15 00:00:00 +0000'::TIMESTAMP + '22 Days'::INTERVAL, outages.ifregainedservice) end as outage_end
      FROM
      (SELECT
      d.nodeid,
      d.ifindex,
      d.eventuei,
      d.eventtime as iflostservice,
      u.eventuei,
      min(u.eventtime) as ifregainedservice,
      age(min(u.eventtime), d.eventtime) as outage_total
      FROM
      events d
      JOIN
      events u
      ON
      (d.nodeid = u.nodeid AND d.ifindex = u.ifindex AND u.eventtime > d.eventtime)
      WHERE
      d.eventuei = 'uei.opennms.org/nodes/snmp/interfaceOperDown' AND
      u.eventuei = 'uei.opennms.org/nodes/snmp/interfaceOperUp'
      GROUP BY
      d.eventuei,
      d.eventtime,
      u.eventuei,
      d.nodeid,
      d.ifindex) AS outages
      WHERE
      (outages.iflostservice, outages.ifregainedservice) OVERLAPS ('2013-09-15 00:00:00 +0000'::TIMESTAMP, '2013-09-15 00:00:00 +0000'::TIMESTAMP + '22 Days'::INTERVAL))
      AS outage_scope)
      AS interface_outages
      JOIN
      node n
      ON
      (n.nodeid = interface_outages.nodeid)
      JOIN
      snmpinterface s
      ON
      (s.nodeid = interface_outages.nodeid AND s.snmpifindex = interface_outages.ifindex)

      GROUP BY
      interface_outages.nodeid,
      interface_outages.ifindex,
      interface_outages.avail_total,
      n.nodelabel,
      s.snmpifdescr,
      s.snmpifname;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                mvr Markus von RĂ¼den
                Reporter:
                jkishore jai kishore
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: