/** * Return the number of events for this node and the given acknowledgment type. * * @param nodeId a int. * @param ackType a {@link org.opennms.web.event.AcknowledgeType} object. * @return a int. * @throws java.sql.SQLException if any. */ public static int getEventCountForNode(int nodeId, AcknowledgeType ackType) throws SQLException { if (ackType == null) { throw new IllegalArgumentException("Cannot take null parameters."); } int eventCount = 0; Connection conn = Vault.getDbConnection(); try { StringBuffer select = new StringBuffer("SELECT COUNT(EVENTID) AS EVENTCOUNT FROM EVENTS WHERE "); select.append(getAcknowledgeTypeClause(ackType)); select.append(" AND NODEID=?"); select.append(" AND EVENTDISPLAY='Y' "); PreparedStatement stmt = conn.prepareStatement(select.toString()); stmt.setInt(1, nodeId); ResultSet rs = stmt.executeQuery(); if (rs.next()) { eventCount = rs.getInt("EVENTCOUNT"); } rs.close(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } return eventCount; }
/** * Return all events (optionally only unacknowledged events) sorted by time that have the given * distributed poller. * * @param poller a {@link java.lang.String} object. * @param includeAcknowledged a boolean. * @return an array of {@link org.opennms.web.event.Event} objects. * @throws java.sql.SQLException if any. */ public static Event[] getEventsForPoller(String poller, boolean includeAcknowledged) throws SQLException { if (poller == null) { throw new IllegalArgumentException("Cannot take null parameters."); } Event[] events = null; Connection conn = Vault.getDbConnection(); try { StringBuffer select = new StringBuffer("SELECT * FROM EVENTS WHERE EVENTDPNAME=?"); if (!includeAcknowledged) { select.append(" AND EVENTACKUSER IS NULL"); } select.append(" AND EVENTDISPLAY='Y' "); select.append(" ORDER BY EVENTID DESC"); PreparedStatement stmt = conn.prepareStatement(select.toString()); stmt.setString(1, poller); ResultSet rs = stmt.executeQuery(); events = rs2Events(rs); rs.close(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } return events; }
/** * Return a specific event. * * @param eventId a int. * @return a {@link org.opennms.web.event.Event} object. * @throws java.sql.SQLException if any. */ public static Event getEvent(int eventId) throws SQLException { Event event = null; Connection conn = Vault.getDbConnection(); try { PreparedStatement stmt = conn.prepareStatement( "SELECT EVENTS.*, NODE.NODELABEL, SERVICE.SERVICENAME FROM EVENTS LEFT OUTER JOIN NODE USING (NODEID) LEFT OUTER JOIN SERVICE USING (SERVICEID) WHERE EVENTID=? "); stmt.setInt(1, eventId); ResultSet rs = stmt.executeQuery(); Event[] events = rs2Events(rs); // what do I do if this actually returns more than one service? if (events.length > 0) { event = events[0]; } rs.close(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } return event; }
/** * Unacknowledge a list of events. * * @param eventIds an array of int. * @throws java.sql.SQLException if any. */ public static void unacknowledge(int[] eventIds) throws SQLException { if (eventIds == null) { throw new IllegalArgumentException("Cannot take null parameters."); } if (eventIds.length > 0) { StringBuffer update = new StringBuffer("UPDATE EVENTS SET EVENTACKUSER=NULL, EVENTACKTIME=NULL"); update.append(" WHERE EVENTID IN ("); update.append(eventIds[0]); for (int i = 1; i < eventIds.length; i++) { update.append(","); update.append(eventIds[i]); } update.append(")"); Connection conn = Vault.getDbConnection(); try { PreparedStatement stmt = conn.prepareStatement(update.toString()); stmt.executeUpdate(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } } }
/** * Unacknowledge events that match the given filter criteria. * * @param filters an array of org$opennms$web$filter$Filter objects. * @throws java.sql.SQLException if any. */ public static void unacknowledge(Filter[] filters) throws SQLException { if (filters == null) { throw new IllegalArgumentException("Cannot take null parameters."); } StringBuffer update = new StringBuffer("UPDATE EVENTS SET EVENTACKUSER=NULL, EVENTACKTIME=NULL WHERE"); update.append(getAcknowledgeTypeClause(AcknowledgeType.ACKNOWLEDGED)); for (Filter filter : filters) { update.append(" AND"); update.append(filter.getParamSql()); } Connection conn = Vault.getDbConnection(); try { PreparedStatement stmt = conn.prepareStatement(update.toString()); int parameterIndex = 1; for (Filter filter : filters) { parameterIndex += filter.bindParam(stmt, parameterIndex); } stmt.executeUpdate(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } }
/** * Unacknowledge all acknowledged events. * * @throws java.sql.SQLException if any. */ public static void unacknowledgeAll() throws SQLException { Connection conn = Vault.getDbConnection(); try { PreparedStatement stmt = conn.prepareStatement( "UPDATE EVENTS SET EVENTACKUSER=NULL, EVENTACKTIME=NULL WHERE EVENTACKUSER IS NOT NULL"); stmt.executeUpdate(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } }
/** * Return the count of current outages. * * <p>Note: This method has been optimized for the simplest query. * * @return a int. * @throws java.sql.SQLException if any. */ public static int getOutageCount() throws SQLException { int outageCount = 0; final Connection conn = Vault.getDbConnection(); final DBUtils d = new DBUtils(OutageFactory.class, conn); try { final Statement stmt = conn.createStatement(); d.watch(stmt); final ResultSet rs = stmt.executeQuery( "SELECT COUNT(OUTAGEID) AS OUTAGECOUNT FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID " + "WHERE IFREGAINEDSERVICE IS NULL " + "AND (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') "); d.watch(rs); if (rs.next()) { outageCount = rs.getInt("OUTAGECOUNT"); } } finally { d.cleanUp(); } return outageCount; }
/** * getOutage * * @param outageId a int. * @return a {@link org.opennms.web.outage.Outage} object. * @throws java.sql.SQLException if any. */ public static Outage getOutage(int outageId) throws SQLException { Outage outage = null; final Connection conn = Vault.getDbConnection(); final DBUtils d = new DBUtils(OutageFactory.class, conn); try { final PreparedStatement stmt = conn.prepareStatement( "SELECT OUTAGES.*, NODE.NODELABEL, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "LEFT OUTER JOIN SERVICE USING(SERVICEID) " + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID " + "WHERE OUTAGEID=?"); d.watch(stmt); stmt.setInt(1, outageId); final ResultSet rs = stmt.executeQuery(); d.watch(rs); Outage[] outages = rs2Outages(rs); if (outages != null && outages.length > 0) { outage = outages[0]; } } finally { d.cleanUp(); } return outage; }
/** * Count the number of events for a given acknowledgement type. * * @return An array of event counts. Each index of the array corresponds to the event severity for * the counts (indeterminate is 1, critical is 7, etc). * @param ackType a {@link org.opennms.web.event.AcknowledgeType} object. * @param filters an array of org$opennms$web$filter$Filter objects. * @throws java.sql.SQLException if any. */ public static int[] getEventCountBySeverity(AcknowledgeType ackType, Filter[] filters) throws SQLException { if (ackType == null || filters == null) { throw new IllegalArgumentException("Cannot take null parameters."); } int[] eventCounts = new int[8]; Connection conn = Vault.getDbConnection(); try { StringBuffer select = new StringBuffer( "SELECT EVENTSEVERITY, COUNT(*) AS EVENTCOUNT FROM EVENTS LEFT OUTER JOIN NODE USING (NODEID) LEFT OUTER JOIN SERVICE USING (SERVICEID) WHERE "); select.append(getAcknowledgeTypeClause(ackType)); for (Filter filter : filters) { select.append(" AND"); select.append(filter.getParamSql()); } select.append(" AND EVENTDISPLAY='Y'"); select.append(" GROUP BY EVENTSEVERITY"); PreparedStatement stmt = conn.prepareStatement(select.toString()); int parameterIndex = 1; for (Filter filter : filters) { parameterIndex += filter.bindParam(stmt, parameterIndex); } ResultSet rs = stmt.executeQuery(); while (rs.next()) { int severity = rs.getInt("EVENTSEVERITY"); int eventCount = rs.getInt("EVENTCOUNT"); eventCounts[severity] = eventCount; } rs.close(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } return eventCounts; }
/** * Acknowledge all unacknowledged events with the given username and the given time. * * @param user a {@link java.lang.String} object. * @param time a java$util$Date object. * @throws java.sql.SQLException if any. */ public static void acknowledgeAll(String user, Date time) throws SQLException { if (user == null || time == null) { throw new IllegalArgumentException("Cannot take null parameters."); } Connection conn = Vault.getDbConnection(); try { PreparedStatement stmt = conn.prepareStatement( "UPDATE EVENTS SET EVENTACKUSER=?, EVENTACKTIME=? WHERE EVENTACKUSER IS NULL"); stmt.setString(1, user); stmt.setTimestamp(2, new Timestamp(time.getTime())); stmt.executeUpdate(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } }
/** * Count the number of outages for a given outage type. * * @param outageType a {@link org.opennms.web.outage.OutageType} object. * @param filters an array of org$opennms$web$filter$Filter objects. * @return a int. * @throws java.sql.SQLException if any. */ public static int getOutageCount(OutageType outageType, Filter[] filters) throws SQLException { if (outageType == null || filters == null) { throw new IllegalArgumentException("Cannot take null parameters."); } int outageCount = 0; final Connection conn = Vault.getDbConnection(); final DBUtils d = new DBUtils(OutageFactory.class, conn); try { StringBuffer select = new StringBuffer( "SELECT COUNT(OUTAGEID) AS OUTAGECOUNT FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID " + "LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID " + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.NOTIFYID " + "WHERE (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') " + "AND "); select.append(outageType.getClause()); for (Filter filter : filters) { select.append(" AND "); select.append(filter.getParamSql()); } final PreparedStatement stmt = conn.prepareStatement(select.toString()); d.watch(stmt); int parameterIndex = 1; for (Filter filter : filters) { parameterIndex += filter.bindParam(stmt, parameterIndex); } final ResultSet rs = stmt.executeQuery(); d.watch(rs); if (rs.next()) { outageCount = rs.getInt("OUTAGECOUNT"); } } finally { d.cleanUp(); } return outageCount; }
/** * Return all events (optionally only unacknowledged events) sorted by the given sort style. * * <p><strong>Note: </strong> This limit/offset code is <em>Postgres specific!</em> Per <a * href="mailto:[email protected]">Shane </a>, this is okay for now until we can come up with an * Oracle alternative too. * * @param limit if -1 or zero, no limit or offset is used * @param offset if -1, no limit or offset if used * @param sortStyle a {@link org.opennms.web.event.SortStyle} object. * @param ackType a {@link org.opennms.web.event.AcknowledgeType} object. * @param filters an array of org$opennms$web$filter$Filter objects. * @return an array of {@link org.opennms.web.event.Event} objects. * @throws java.sql.SQLException if any. */ public static Event[] getEvents( SortStyle sortStyle, AcknowledgeType ackType, Filter[] filters, int limit, int offset) throws SQLException { if (sortStyle == null || ackType == null || filters == null) { throw new IllegalArgumentException("Cannot take null parameters."); } boolean useLimits = false; if (limit > 0 && offset > -1) { useLimits = true; } Event[] events = null; Connection conn = Vault.getDbConnection(); try { /* StringBuffer select = new StringBuffer("" + " SELECT EVENTS.*, NODE.NODELABEL, SERVICE.SERVICENAME " + " FROM EVENTS " + "LEFT OUTER JOIN NODE USING(NODEID) " + "LEFT OUTER JOIN SERVICE USING(SERVICEID) WHERE"); */ StringBuffer select = new StringBuffer( "" + " SELECT events.*, node.nodelabel, service.servicename " + " FROM node " + "RIGHT OUTER JOIN events " + " ON (events.nodeid = node.nodeid) " + " LEFT OUTER JOIN service " + " ON (service.serviceid = events.serviceid) " + " WHERE "); select.append(getAcknowledgeTypeClause(ackType)); for (Filter filter : filters) { select.append(" AND"); select.append(filter.getParamSql()); } select.append(" AND EVENTDISPLAY='Y' "); select.append(getOrderByClause(sortStyle)); if (useLimits) { select.append(" LIMIT "); select.append(limit); select.append(" OFFSET "); select.append(offset); } PreparedStatement stmt = conn.prepareStatement(select.toString()); int parameterIndex = 1; for (Filter filter : filters) { parameterIndex += filter.bindParam(stmt, parameterIndex); } ResultSet rs = stmt.executeQuery(); events = rs2Events(rs); rs.close(); stmt.close(); } finally { Vault.releaseDbConnection(conn); } return events; }
/** * Return all notices (optionally only unacknowledged notices) sorted by the given sort style. * * <p><strong>Note: </strong> This limit/offset code is <em>Postgres specific!</em> Per <a * href="mailto:[email protected]">Shane </a>, this is okay for now until we can come up with an * Oracle alternative too. * * @param limit if -1 or zero, no limit or offset is used * @param offset if -1, no limit or offset if used * @param sortStyle a {@link org.opennms.web.outage.SortStyle} object. * @param outType a {@link org.opennms.web.outage.OutageType} object. * @param filters an array of org$opennms$web$filter$Filter objects. * @return an array of {@link org.opennms.web.outage.Outage} objects. * @throws java.sql.SQLException if any. */ public static Outage[] getOutages( SortStyle sortStyle, OutageType outType, Filter[] filters, int limit, int offset) throws SQLException { if (sortStyle == null || outType == null || filters == null) { throw new IllegalArgumentException("Cannot take null parameters."); } boolean useLimits = false; if (limit > 0 && offset > -1) { useLimits = true; } Outage[] outages = null; final Connection conn = Vault.getDbConnection(); final DBUtils d = new DBUtils(OutageFactory.class, conn); try { StringBuffer select = new StringBuffer( "SELECT OUTAGES.*, NODE.NODELABEL, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID " + "LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID " + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID " + "WHERE (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') " + "AND "); select.append(outType.getClause()); for (Filter filter : filters) { select.append(" AND "); select.append(filter.getParamSql()); } select.append(sortStyle.getOrderByClause()); if (useLimits) { select.append(" LIMIT "); select.append(limit); select.append(" OFFSET "); select.append(offset); } log.debug(select.toString()); final PreparedStatement stmt = conn.prepareStatement(select.toString()); d.watch(stmt); int parameterIndex = 1; for (Filter filter : filters) { parameterIndex += filter.bindParam(stmt, parameterIndex); } final ResultSet rs = stmt.executeQuery(); d.watch(rs); outages = rs2Outages(rs); } finally { d.cleanUp(); } return outages; }