Java tutorial
/** * Copyright (c) 2005 - 2006 Pari Networks, Inc. All Rights Reserved. * * This software is the proprietary information of Pari Networks, Inc. * */ package com.pari.nm.utils.db; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.nio.charset.Charset; import java.sql.Blob; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.Hashtable; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Random; import java.util.Set; import java.util.StringTokenizer; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.TimeUnit; import java.util.regex.Matcher; import java.util.regex.Pattern; import oracle.jdbc.OraclePreparedStatement; import org.apache.activemq.util.ByteArrayInputStream; import org.apache.commons.io.IOUtils; import org.w3c.dom.Document; import org.w3c.dom.Element; import com.pari.api.customers.CustomerFault; import com.pari.api.extdevice.ExternalDeviceRegDetails; import com.pari.api.utils.HeuristicDescriptor; import com.pari.api.utils.RegExpUtils; import com.pari.api.utils.StringOperatorType; import com.pari.api.utils.TrapActionDescriptor; import com.pari.base.so.CatOSDevice; import com.pari.base.so.CatOSSwitchModule; import com.pari.base.so.CatOSVersion; import com.pari.base.so.CiscoNetworkNodeInterface; import com.pari.base.so.DAXDevice; import com.pari.base.so.DAXVersion; import com.pari.base.so.DiscoveredDevice; import com.pari.base.so.IOSDevice; import com.pari.base.so.IOSVersion; import com.pari.base.so.IOSXRDevice; import com.pari.base.so.IOSXRVersion; import com.pari.base.so.MeruController; import com.pari.base.so.MeruControllerVersion; import com.pari.base.so.ModuleInfo; import com.pari.base.so.ModuleType; import com.pari.base.so.NetworkInterface; import com.pari.base.so.NetworkNode; import com.pari.base.so.NetworkNodeExt; import com.pari.base.so.NetworkNodeTemp; import com.pari.base.so.NxOSDevice; import com.pari.base.so.NxOSVersion; import com.pari.base.so.OSType; import com.pari.base.so.PixDevice; import com.pari.base.so.PixVersion; import com.pari.base.so.Version; import com.pari.base.so.WlcDevice; import com.pari.base.so.WlcVersion; import com.pari.base.so.defs.IPAddressBase; import com.pari.common.historyserver.HistoryManager; import com.pari.common.historyserver.HistoryRecord.Operation; import com.pari.jelly.engine.communication.ISnmpColumn; import com.pari.jelly.engine.communication.ISnmpScalar; import com.pari.logger.PariLogger; import com.pari.logger.PariLoggerFactory; import com.pari.mw.api.ServerIfProvider; import com.pari.nm.faults.FaultProfile; import com.pari.nm.faults.FaultSeverity; import com.pari.nm.faults.FaultSpec; import com.pari.nm.faults.FaultSpecFactory; import com.pari.nm.groups.rules.GroupRuleOperator; import com.pari.nm.gui.guiservices.FtpServerDetails; import com.pari.nm.gui.guiservices.PariException; import com.pari.nm.gui.guiservices.VirtualDevice; import com.pari.nm.modules.customers.Customer; import com.pari.nm.modules.customers.CustomerInstance; import com.pari.nm.modules.customers.CustomerManager; import com.pari.nm.modules.customers.CustomerWingInstanceManager; import com.pari.nm.modules.dsp.pojo.DSPCLICommandOutput; import com.pari.nm.modules.dsp.pojo.DSPNetconfCommandOutput; import com.pari.nm.modules.dsp.pojo.IDSPDeviceInfoProvider; import com.pari.nm.modules.dsp.pojo.SnmpColumn; import com.pari.nm.modules.dsp.pojo.SnmpMibData; import com.pari.nm.modules.dsp.pojo.SnmpScalar; import com.pari.nm.modules.inventory.CollectionProfile; import com.pari.nm.modules.inventory.CollectionProfileNotificationPrefs; import com.pari.nm.modules.inventory.DeviceModule; import com.pari.nm.modules.jobs.JobMgr; import com.pari.nm.modules.jobs.JobParameters; import com.pari.nm.modules.jobs.PariJobDetails; import com.pari.nm.modules.parser.config.AclElements; import com.pari.nm.modules.parser.config.CliToObjectsBuilder; import com.pari.nm.modules.parser.config.InterfaceElements; import com.pari.nm.modules.parser.config.IpRouteData; import com.pari.nm.modules.parser.config.MeruAccessPoint; import com.pari.nm.modules.parser.config.ParseShowCommand; import com.pari.nm.modules.parser.config.PixAccessListElement; import com.pari.nm.modules.parser.config.PixSSHConfElement; import com.pari.nm.modules.parser.config.PixSSHElements; import com.pari.nm.modules.parser.config.RouterVlanElements; import com.pari.nm.modules.parser.config.SwitchVlanElements; import com.pari.nm.modules.parser.xmldefs.ShowCommandGrammer; import com.pari.nm.modules.parser.xmldefs.ShowCommandGrammerBuilder; import com.pari.nm.modules.session.CredentialSetFactory; import com.pari.nm.modules.session.NetworkNodeCache; import com.pari.nm.modules.session.NmIfImpl; import com.pari.nm.modules.session.UserDetails; import com.pari.nm.modules.session.UsersFactory; import com.pari.nm.modules.snmp.MIBNodeType; import com.pari.nm.modules.syslogd.DecodedSyslogMessage; import com.pari.nm.modules.versioncontrol.ConfFileType; import com.pari.nm.modules.versioncontrol.NewVersionControlManager; import com.pari.nm.utils.ConfigIgnoreLines; import com.pari.nm.utils.Constants; import com.pari.nm.utils.MgmtReportsPaginationUtil; import com.pari.nm.utils.ServerAuditConstants; import com.pari.nm.utils.ServerAuditLog; import com.pari.reports.custom.core.NetconfOutput; import com.pari.server.LicenseValidator; import com.pari.server.ServerProperties; import com.pari.server.historyserver.CommandResult; import com.pari.swim.archive.BackupSoftwareImageRequestProcessor; import com.pari.utils.CompressionUtils; import com.pari.utils.xml.XMLUtil; public class InventoryDBHelper { private static PariLogger logger = PariLoggerFactory.getLogger("DB"); private static final Object lock = new Object(); private static final String SELECT_SYSLOG_ACTIONS = "select * from syslog_actions order by type"; private static final String SELECT_TRAP_ACTIONS = "select * from snmp_trap_actions order by type"; private static final String RESULTSET_CLOSE_ERROR = "Error while closing the resultset"; private static final String CONNECTION_ERROR = "Error while connecting"; private static final String DATASETNAME = "dataSetName"; private static final String STATUS = "status"; public static void deleteIPRouteEntries(ArrayList alist) { Connection c = null; PreparedStatement ps = null; try { ArrayList delList = new ArrayList(); Iterator it = alist.iterator(); while (it.hasNext()) { IpRouteData ipRouteData = (IpRouteData) it.next(); int device_id = ipRouteData.getDeviceId(); if (delList.contains(device_id)) { continue; } else { delList.add(device_id); } } c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.DELETE_IP_ROUTE); Iterator idel = delList.iterator(); while (idel.hasNext()) { String ipAddr = (String) idel.next(); ps.setString(1, ipAddr); ps.executeUpdate(); } } catch (Exception e) { logger.warn("Error while deleting Ip Route Entries.", e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } } /* * public static void deleteNeighborLink(int[] linkId) throws Exception { ResultSet rs = null; * * for ( int i = 0; i < linkId.length; i++ ) { try { rs = * DBHelper.executeQuery("select * from device_cdp_table where link_id=" + linkId[i]); * * if ( (rs != null) && rs.next() ) { String fromIpAddress = rs.getString("fromipaddress"); NetworkNode node = * NetworkNodeCache.getInstance().getLiveNode(fromIpAddress); * * if ( node == null ) { continue; } * * node.deleteNeighbor(linkId[i] + 1000); * * DBHelper.executeUpdate("delete from device_cdp_table where link_id=" + linkId[i]); } } catch ( Exception ee ) { * logger.warn("Error while deleting Neighbor Links.", ee); } finally { try { rs.close(); } catch ( Exception ee ) * {} } } } */ public static void deleteDevice(int devId) throws Exception { DBHelper.executeUpdate("delete from nodes where id=" + devId); } public static void deleteUnmanagedDevices() throws Exception { /*** * Added for CSCuq85761:Removing an image from Image repository hangs the wizard Just temporary solution it * should be handled in better way by tuning schema * **/ try { DBHelper.executeUpdate( "delete from software_image_devices where device_id in (select device_id from nodes where state='r')"); } catch (Exception e) { logger.error( "Failed to delete software image and node/device mapping references for unmanaged devices.", e); } DBHelper.executeUpdate("delete from nodes where state='r'"); } public static void removeOrUnManageDevice(int devId, boolean isDelete) throws Exception { if (isDelete) { DBHelper.executeUpdate("update nodes set state='r' where id=" + devId); } else { DBHelper.executeUpdate("update nodes set state='u' where id=" + devId); } } public static String getCapabilities(int device_id) { ResultSet rs = null; String caps = null; try { rs = DBHelper.executeQuery("select * from node_not_supp_caps where device_id=" + device_id); if ((rs != null) && rs.next()) { caps = rs.getString("NON_SUPP_CAP_LIST"); } } catch (Exception ee) { logger.warn("Error while getting Capabilities for :", device_id, ee); } finally { try { rs.close(); } catch (Exception ee) { } } return caps; } public static void insertMlsQosTable(Map<Integer, Integer> map, String tableName, String col1, String col2, int deviceId) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from " + tableName + " where device_id=" + deviceId); } catch (Exception ee) { logger.info("Error while deleting " + tableName + " where device_id=" + deviceId, ee); } if (map == null || map.isEmpty()) { return; } c = DBHelper.getConnection(); ps = c.prepareStatement( "INSERT INTO " + tableName + " (device_id, " + col1 + ", " + col2 + ") VALUES (?,?,?)"); for (Integer key : map.keySet()) { if (map.get(key) == null) { continue; } ps.setInt(1, deviceId); ps.setInt(2, key); ps.setInt(3, map.get(key)); ps.executeUpdate(); } } catch (Exception e) { logger.warn("Error while inserting ip data into db:", deviceId, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void insertIpDataIntoDB(ArrayList alist, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from ip_route_table where device_id=" + device_id); } catch (Exception ee) { logger.info("Error while deleting ip_route_table where device_id=" + device_id, ee); } c = DBHelper.getConnection(); Iterator it = alist.iterator(); ps = c.prepareStatement(DBHelperConstants.INSERT_IP_ROUTE); while (it.hasNext()) { IpRouteData ipRouteData = (IpRouteData) it.next(); // Bug 0004693 - Trying to insert NULL into database; if (ipRouteData == null || ipRouteData.getRouteType() == null || ipRouteData.getRoute() == null) { logger.warn("One of the required element (id or route or routetype) is null"); continue; } // Bug 0004693 - Trying to insert NULL into database; ps.setInt(1, ipRouteData.getDeviceId()); ps.setString(2, ipRouteData.getRouteType()); ps.setString(3, ipRouteData.getRoute()); ps.setString(4, ipRouteData.getPrefix()); ps.setString(5, ipRouteData.getNextHop()); ps.setString(6, ipRouteData.getIfName()); ps.executeUpdate(); } } catch (Exception e) { logger.warn("Error while inserting ip data into db:", device_id, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void insertSwitchVlanIntoDB(ArrayList pElems, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from device_show_l2_vlans where device_id=" + device_id); } catch (Exception ee) { logger.warn("Error while deleting from device_show_l2_vlans", device_id, ee); } c = DBHelper.getConnection(); c.setAutoCommit(false); ps = c.prepareStatement(DBHelperConstants.INSERT_SWITCH_VLAN_DATA); Iterator it = pElems.iterator(); while (it.hasNext()) { SwitchVlanElements elem = (SwitchVlanElements) it.next(); ps.setString(1, elem.getVlanId()); ps.setString(2, elem.getVlanName()); ps.setString(3, elem.getVlanStatus()); ps.setString(4, elem.getVlanPorts()); ps.setInt(5, device_id); try { ps.executeUpdate(); } catch (Exception e) { logger.warn("Exception while inserting switch vlan data \t" + elem.getVlanId() + "\t" + elem.getVlanName() + "\t" + elem.getVlanStatus() + "\n"); } } } catch (Exception e) { logger.warn("Exception while inserting switch vlan info into DB.", e); } finally { try { ps.close(); } catch (Exception ee) { } try { c.commit(); } catch (Exception e1) { } try { c.setAutoCommit(true); } catch (Exception ee) { } DBHelper.releaseConnection(c); } } public static void insertPixSSHIntoDB(PixSSHElements pElems, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { // device_pix_ssh_table is for access list DBHelper.executeUpdate("delete from device_pix_ssh_table where device_id=" + device_id); } catch (Exception ee) { logger.warn("Error while deleting from device_pix_ssh_table", device_id, ee); } try { // device_pix_ssh_table is for access list DBHelper.executeUpdate("delete from device_pix_ssh_state_table where device_id=" + device_id); } catch (Exception ee) { logger.warn("Error while deleting from device_pix_ssh_state_table", device_id, ee); } c = DBHelper.getConnection(); c.setAutoCommit(false); // First insert state information into device_pix_ssh_state_table. ps = c.prepareStatement(DBHelperConstants.INSERT_PIX_SSH_STATE_DATA); ps.setInt(1, device_id); ps.setString(2, pElems.getpixSshState()); ps.setString(3, pElems.getTimeout()); ps.setString(4, pElems.getVersion()); try { ps.executeUpdate(); } catch (Exception e) { logger.warn("Exception while inserting pix ssh state data\t" + pElems.getpixSshState() + "\t" + pElems.getTimeout() + "\t" + pElems.getVersion() + "\n"); } try { ps.close(); } catch (Exception e) { } // Now insert access list ps = c.prepareStatement(DBHelperConstants.INSERT_PIX_SSH_DATA); Iterator it = pElems.getpixSshConfigList().iterator(); while (it.hasNext()) { PixSSHConfElement pelem = (PixSSHConfElement) it.next(); ps.setInt(1, device_id); ps.setString(2, pelem.getConfIpAddr()); ps.setString(3, pelem.getConfNetMaskAddr()); ps.setString(4, pelem.getpixConfInOutData()); try { ps.executeUpdate(); } catch (Exception e) { logger.warn("Exception while inserting pix ssh data\t" + pelem.getConfIpAddr() + "\t" + pelem.getConfNetMaskAddr() + "\t" + pelem.getpixConfInOutData() + "\n"); } } } catch (Exception e) { logger.warn("Exception while inserting Pix SSH info into DB.", e); } finally { try { ps.close(); } catch (Exception ee) { } try { c.commit(); } catch (Exception e1) { } try { c.setAutoCommit(true); } catch (Exception ee) { } DBHelper.releaseConnection(c); } } public static void insertAclIntoDB(ArrayList alist, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from device_acl_table where device_id=" + device_id); } catch (Exception ee) { logger.warn("Error while deleting from device_acl_table", device_id, ee); } c = DBHelper.getConnection(); c.setAutoCommit(false); Iterator it = alist.iterator(); ps = c.prepareStatement(DBHelperConstants.INSERT_ACL_DATA); while (it.hasNext()) { AclElements aclElem = (AclElements) it.next(); if (aclElem.getAclId().equals("-")) { continue; } if ((aclElem != null) && (aclElem.getACLData().size() <= 0)) { ps.setInt(1, device_id); ps.setString(2, aclElem.getAclId()); if (aclElem.getAclId().trim().equalsIgnoreCase("s")) { continue; } // System.err.println("Aclid\t" + aclElem.getAclId()); ps.setString(3, "0"); ps.setString(4, "0"); ps.setString(5, aclElem.getMatches()); try { ps.executeUpdate(); } catch (Exception e) { logger.warn("Exception while inserting aclid\t" + aclElem.getAclId()); } } else { // multiple values for data Iterator datait = aclElem.getACLData().iterator(); while (datait.hasNext()) { String aclData = (String) datait.next(); ps.setInt(1, device_id); ps.setString(2, aclElem.getAclId()); // logger.warn("aclData\t"+ aclData); String num = "0"; // Fix for matches field try { if (aclData != null && aclData.length() > 0) { // Number from aclData int nNum = -1; if ((aclData.indexOf("permit") != -1) && (aclData.indexOf("Dynamic") != -1)) { nNum = aclData.indexOf("Dynamic"); num = aclData.substring(0, nNum).trim(); aclData = aclData.substring(nNum); } else if ((nNum = aclData.indexOf("permit")) != -1) { num = aclData.substring(0, nNum).trim(); aclData = aclData.substring(nNum); } if ((aclData.indexOf("deny") != -1) && (aclData.indexOf("Dynamic") != -1)) { nNum = aclData.indexOf("Dynamic"); num = aclData.substring(0, nNum).trim(); aclData = aclData.substring(nNum); } else if ((nNum = aclData.indexOf("deny")) != -1) { num = aclData.substring(0, nNum).trim(); aclData = aclData.substring(nNum); } if ((nNum = aclData.indexOf("evaluate")) != -1) { num = aclData.substring(0, nNum).trim(); aclData = aclData.substring(nNum); } ps.setString(3, num); // Matches if (aclData.indexOf("matches") != -1) { int nIdx = aclData.indexOf("("); int nIdy = aclData.indexOf("matches"); if (nIdx != -1 && nIdy != -1) { ps.setString(4, aclData.substring(0, nIdx)); ps.setString(5, aclData.substring(nIdx + 1, nIdy)); } } else { ps.setString(4, aclData.trim()); ps.setString(5, aclElem.getMatches()); } } } catch (Exception e) { logger.warn("Exception while picking matches from acldata - falling back\t" + aclData); ps.setString(3, num); ps.setString(4, aclData.trim()); ps.setString(5, aclElem.getMatches()); } // till this point try { ps.executeUpdate(); } catch (Exception e) { logger.warn("Exception while inserting aclid\t" + aclElem.getAclId() + "\n" + "\tacldata\t" + aclData, e); } } } } } catch (Exception e) { logger.warn("Exception while inserting ACLS into DB.", e); } finally { try { ps.close(); } catch (Exception ee) { } try { c.commit(); } catch (Exception e1) { } try { c.setAutoCommit(true); } catch (Exception ee) { } DBHelper.releaseConnection(c); } } public static void insertPixAclIntoDB(LinkedHashMap lmap, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from device_acl_table where device_id=" + device_id); } catch (Exception ee) { logger.warn("Error while deleting from device_acl_table", device_id, ee); } c = DBHelper.getConnection(); c.setAutoCommit(false); Iterator it = lmap.keySet().iterator(); // ps = c.prepareStatement(DBHelperConstants.INSERT_PIX_ACL_DATA); ps = c.prepareStatement(DBHelperConstants.INSERT_ACL_DATA); // System.out.println("Inside insertPixAclIntoDB\t" + // lmap.toString()); while (it.hasNext()) { String aclId = (String) it.next(); ArrayList alist = (ArrayList) lmap.get(aclId); Iterator itr = alist.iterator(); while (itr.hasNext()) { PixAccessListElement pelem = (PixAccessListElement) itr.next(); // device_id, aclId, aceData, aclData, matches ps.setInt(1, device_id); ps.setString(2, aclId); ps.setString(3, pelem.getAceData()); ps.setString(4, pelem.getAclData()); ps.setString(5, pelem.getMatches()); try { ps.executeUpdate(); } catch (Exception e) { logger.warn( "Exception while inserting pix aclid\t" + aclId + "\t for \t" + pelem.getAclData()); } } } } catch (Exception e) { logger.warn("Exception while inserting Pix ACLS into DB.", e); } finally { try { ps.close(); } catch (Exception ee) { } try { c.commit(); } catch (Exception e1) { } try { c.setAutoCommit(true); } catch (Exception ee) { } DBHelper.releaseConnection(c); } } public static void insertIntfStatsIntoDB(ArrayList alist, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from device_intf_stats where device_id=" + device_id); } catch (Exception ee) { logger.info("error while deleting from device_intf_stats where device_id=" + device_id, ee); } c = DBHelper.getConnection(); Iterator it = alist.iterator(); ps = c.prepareStatement(DBHelperConstants.INSERT_INTF_DATA); while (it.hasNext()) { InterfaceElements intfElem = (InterfaceElements) it.next(); String sTmp = intfElem.getIntfName(); if ((sTmp != null) && sTmp.equalsIgnoreCase("-")) { continue; } ps.setInt(1, device_id); ps.setString(2, intfElem.getIntfName()); ps.setString(3, intfElem.getInPackets()); ps.setString(4, intfElem.getInBytes()); ps.setString(5, intfElem.getOutPackets()); ps.setString(6, intfElem.getOutBytes()); ps.setString(7, intfElem.getInPacketsPerSec()); ps.setString(8, intfElem.getInBytesPerSec()); ps.setString(9, intfElem.getOutPacketsPerSec()); ps.setString(10, intfElem.getOutBytesPerSec()); ps.setString(11, intfElem.getInputErrors()); ps.setString(12, intfElem.getCRC()); ps.setString(13, intfElem.getOutputErrors()); ps.setString(14, intfElem.getOverRun()); ps.setString(15, intfElem.getUnderRuns()); ps.setString(16, intfElem.getRunts()); ps.setString(17, intfElem.getGiants()); ps.setString(18, intfElem.getLostCarrier()); ps.setString(19, intfElem.getNoCarrier()); ps.executeUpdate(); } } catch (Exception e) { logger.warn("Error while inserting into device_intf_stats where device_id=" + device_id, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void insertRouterVlanDataIntoDB(ArrayList alist, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from device_show_l3_vlans where device_id=" + device_id); } catch (Exception ee) { logger.info("Error while deleting from device_show_l3_vlans where device_id=" + device_id, ee); } c = DBHelper.getConnection(); Iterator it = alist.iterator(); ps = c.prepareStatement(DBHelperConstants.INSERT_ROUTER_VLAN_DATA); while (it.hasNext()) { RouterVlanElements l3Elem = (RouterVlanElements) it.next(); if (l3Elem == null) { continue; } String sId = l3Elem.getvLanId(); if ((sId != null) && (sId.indexOf("-") != -1)) { continue; } if (sId != null && sId.equalsIgnoreCase("0")) { continue; } ps.setString(1, sId); ps.setString(2, l3Elem.getvLanTrunkInterface()); ps.setString(3, l3Elem.getvLanNative()); ps.setString(4, l3Elem.getProtocol()); ps.setString(5, l3Elem.getAddress()); ps.setString(6, l3Elem.getDataRecieved()); ps.setString(7, l3Elem.getDataTransmitted()); ps.setInt(8, device_id); ps.executeUpdate(); } } catch (Exception e) { logger.warn("Error while inserting into device_show_l3_vlans where device_id='" + device_id, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void insertMeruAccessPointDataIntoDB(ArrayList alist, int device_id) { Connection c = null; PreparedStatement ps = null; try { try { DBHelper.executeUpdate("delete from lw_aps where device_id=" + device_id); } catch (Exception ee) { logger.info("Error while deleting from lw_aps where device_id=" + device_id, ee); } c = DBHelper.getConnection(); Iterator it = alist.iterator(); ps = c.prepareStatement(DBHelperConstants.INSERT_MERU_AP_DATA); // public static final String INSERT_INTO_MERU_AP_DATA = // "INSERT INTO lw_aps(device_id, ap_name, ap_id, vendor, model, mac_address, up_time, state, availability, runtime, connectivity) VALUES(?,?,?,?,?,?,?,?,?,?,?)"; while (it.hasNext()) { MeruAccessPoint map = (MeruAccessPoint) it.next(); if (map == null) { continue; } ps.setInt(1, device_id); ps.setString(2, map.getApName()); ps.setString(3, map.getApId()); ps.setString(4, "Meru Networks Inc"); // TBD ps.setString(5, map.getModel()); ps.setString(6, map.getMacAddress()); ps.setString(7, map.getUpTime()); ps.setString(8, map.getState()); ps.setString(9, map.getAvailability()); ps.setString(10, map.getRuntime()); ps.setString(11, map.getConnectvity()); ps.executeUpdate(); } } catch (Exception e) { logger.warn("Error while inserting into lw_aps where device_id='" + device_id, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static boolean vendorMatches(String input, String vendorName) { vendorName = vendorName.toLowerCase(); input = input.toLowerCase(); if (input.equals(vendorName)) { return true; } if (!input.contains(vendorName)) { return false; } if (input.matches(vendorName + " .*systems.*")) { return true; } if (input.matches(vendorName + " .*networks.*")) { return true; } return false; } public static NetworkNode insertVirtualDevice(String nodeName, String vendor, String deviceType, String versionStr, String osName, String runConf, NetworkInterface[] nfs) throws Exception { NetworkNode node = null; Version ver = null; OSType osType = OSType.getFromString(osName); if (osType == null) { throw new Exception("Unknown OS Type: " + osName); } System.out.println("Adding virtual device: " + nodeName + " OSTYpe: " + osType); switch (osType) { case CISCO_CATOS: node = new CatOSDevice(); ver = new CatOSVersion(); break; case CISCO_FWSM: node = new PixDevice(); ver = new PixVersion(); ver.setDeviceType("FWSM"); break; case CISCO_IOSXR: node = new IOSXRDevice(); ver = new IOSXRVersion(); break; case CISCO_IOS: node = new IOSDevice(); ver = new IOSVersion(); break; case CISCO_PIX: node = new PixDevice(); ver = new PixVersion(); if (deviceType.toLowerCase().contains("asa")) { ver.setDeviceType("ASA"); } break; case CISCO_NXOS: node = new NxOSDevice(); ver = new NxOSVersion(); break; case CISCO_WLC: node = new WlcDevice(); ver = new WlcVersion(); break; case MERU_CONTROLLER: node = new MeruController(); ver = new MeruControllerVersion(); break; case DAX_DEVICE: node = new DAXDevice(); ver = new DAXVersion(); break; default: throw new Exception("Devices of OS Type: " + osType.shortDisplayStr + " are not supported to be added through API right now."); } node.setVersion(ver); node.setDeviceMode(Constants.VIRTUAL_DEVICE); node.setVendorName(vendor); if (deviceType != null) { String lc = deviceType.toLowerCase(); if (lc.startsWith("router")) { deviceType = "Routers"; } else if (lc.startsWith("switch")) { deviceType = "Switches"; } else if (lc.startsWith("firewall")) { deviceType = "Security"; } else if (lc.startsWith("accesspoint")) { deviceType = "Wireless"; } } node.setProductFamily(deviceType); String uniqIp = InventoryDBHelper.getUniqIp(); node.setIpAddr(IPAddressBase.parseIPAddressString(uniqIp)); node.setNodeName(nodeName); String version = null; if ((versionStr != null) && (versionStr.length() > 0)) { version = versionStr; } else { version = InventoryDBHelper.retrieveVersionStr(runConf).trim(); } com.pari.services.def.version.IOSVersion iosVersion = com.pari.services.def.version.IOSVersion .parseString(version); if (iosVersion == null) { throw new Exception("Unable to parse version: " + version); } switch (osType) { case CISCO_CATOS: ((CatOSVersion) ver).setIosVersion(iosVersion); break; case CISCO_FWSM: case CISCO_PIX: ver.setIosVersion(iosVersion); break; case CISCO_IOSXR: ver.setIosVersion(iosVersion); break; case CISCO_IOS: ((IOSVersion) ver).setIosVersion(iosVersion); break; case CISCO_NXOS: ver.setIosVersion(iosVersion); break; case CISCO_WLC: ver.setIosVersion(iosVersion); break; default: throw new Exception("Devices of OS Type: " + osType.shortDisplayStr + " are not supported to be added through API right now."); } ver.setVersionStr(version); ver.setDeviceType(deviceType); ver.setNodeName(nodeName); node.setVersion(ver); InventoryDBHelper.retrieveNetworkInterfaces(runConf, node); NetworkNodeTemp tempNode = new NetworkNodeTemp(); tempNode.setRunConfig(runConf); InventoryDBHelper.updateDevice(node, tempNode); NetworkNodeCache.getInstance().addDevice(node); return node; } public static void updateDevice(NetworkNode device, NetworkNodeTemp tempNode) throws Exception { int updateId = -1; String deviceIp = device.getIpAddr().toString(); NetworkNode node = NetworkNodeCache.getInstance().getNode(deviceIp); if ((node != null) && (node.getIpAddr().toString() != deviceIp)) { deviceIp = node.getIpAddr().toString(); updateId = node.getNodeId(); } updateDeviceDetails(device, true, deviceIp, updateId == -1 ? false : true, updateId, false); updateIosVersionDetails(device); if (tempNode != null) { updateDeviceIfDetails(device, tempNode.getNetworkInterfaces()); updateDeviceConfig(device, tempNode.getRunConfig(), tempNode.getStartConfig()); } } public static void updateDeviceConfig(NetworkNode device, String runConf, String startupConf) { if (runConf != null) { updateConfigInternal(device, Constants.RUN_CONF, runConf); } if (startupConf != null) { updateConfigInternal(device, Constants.STARTUP_CONF, startupConf); } } private static void updateConfigInternal(NetworkNode device, int confType, String config) { String confTypeStr = (confType == Constants.RUN_CONF) ? " running " : " startup "; // logger.debug("Updating " + confTypeStr + " config for node: " + // device.getNodeId() + " IP:" + // device.getIpAddr()); if (config == null || config.isEmpty()) { logger.debug("No " + confTypeStr + " conf found for node: " + device.getNodeId() + " IP:" + device.getIpAddr() + ". Skipping"); return; } try { deleteConfig(device, confType); } catch (Exception ex) { logger.error("Exception while deleting " + confTypeStr + " config for node: " + device.getNodeId() + " IP:" + device.getIpAddr() + " can not update.", ex); // Try and insert it anyway. } // logger.debug("Inserting " + confTypeStr + " config for node: " + // device.getNodeId() + " IP:" + // device.getIpAddr()); insertConfig(device.getNodeId(), confType, config); } public static void insertConfig(Integer nodeId, int confType, String config) { PreparedStatement ps = null; Connection c = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("insert into node_current_config (id, conf_type, config) VALUES (?,?,?)"); ps.setInt(1, nodeId); ps.setInt(2, confType); byte b[] = CompressionUtils .compressInputStream(new ByteArrayInputStream(config.getBytes(Charset.forName("UTF-8")))); ps.setObject(3, b); ps.executeUpdate(); } catch (Exception ex) { logger.error("Exception while inserting " + confType + " configuration for node: " + nodeId + " can not update.", ex); } finally { try { if (ps != null) { ps.close(); } } catch (Exception ex) { } finally { if (c != null) { DBHelper.releaseConnection(c); } } } } private static void deleteConfig(NetworkNode device, int confType) throws SQLException { String confTypeStr = (confType == Constants.RUN_CONF) ? " running " : " startup "; logger.debug("Deleting " + confTypeStr + " config for node: " + device.getNodeId() + " IP:" + device.getIpAddr()); String query = "delete from node_current_config where id=" + device.getNodeId() + " and conf_type=" + confType; DBHelper.executeUpdate(query); } private static void deleteConfigOld(int nodeId, int confType) throws SQLException { String confTypeStr = (confType == Constants.RUN_CONF) ? " running " : " startup "; logger.debug("Deleting " + confTypeStr + " config for node: " + nodeId); String query = "delete from ios_run_conf where id=" + nodeId + " and conf_type=" + confType; DBHelper.executeUpdate(query); } private static void dumpFullIosVersion(NetworkNode device) { PreparedStatement ps = null; Connection c = null; if (device == null || device.getVersion() == null || device.getVersion().getShowVersion() == null || device.getVersion().getShowVersion().trim().length() == 0) { return; } try { String query = "delete from version_dump where id=" + device.getNodeId(); DBHelper.executeUpdate(query); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.IOS_VERSION_DUMP_INSERT); String versionStr = device.getVersion().getShowVersion(); int size = versionStr.length(); // TODO fix the hardcoding int noOfDBRows = size / 4000; if ((noOfDBRows * 4000) < size) { noOfDBRows += 1; } for (int i = 0; i < noOfDBRows; i++) { int startIndex = i * 4000; int endIndex = (i + 1) * 4000; if (endIndex > size) { endIndex = size; } String conf = versionStr.substring(startIndex, endIndex); ps.setInt(1, device.getNodeId()); ps.setInt(2, i); ps.setString(3, conf); ps.executeUpdate(); } } catch (Exception ee) { logger.warn("Error while inserting into version_dump where id=" + device.getNodeId(), ee); } finally { try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static Map<String, String> getComputeDeviceDetails(int deviceid) { Map<String, String> details = new HashMap<String, String>(); ResultSet rs = null; if (deviceid != 0) { try { String query = "select * from nodes_compute where id=" + deviceid; rs = DBHelper.executeQuery(query); while (rs.next()) { details.put("AvailableMemory", rs.getString("availableMemory")); details.put("MemorySpeed", rs.getString("memorySpeed")); details.put("TotalMemory", rs.getString("totalMemory")); details.put("NumOfAdaptors", rs.getString("numOfAdaptors")); details.put("NumOfCores", rs.getString("numOfCores")); details.put("NumOfCpus", rs.getString("numOfCpus")); details.put("NumOfThreads", rs.getString("numOfThreads")); } } catch (Exception d) { logger.error("Error while fetching Compute Device details ", d); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } } return details; } public static void updateComputeDetails(IDSPDeviceInfoProvider device, int deviceid) { PreparedStatement ps = null; Connection c = null; ResultSet rs = null; Map<String, Object> computeMap = device.getComputeAttributes(); logger.debug("Compute map details = " + computeMap.entrySet().toString()); try { String query = "select id,os from nodes_compute where id=" + deviceid; rs = DBHelper.executeQuery(query); if ((rs != null) && rs.next()) { logger.debug("Executing update for compute device id = " + deviceid); c = DBHelper.getConnection(); logger.debug("Statement = " + DBHelperConstants.NODE_COMPUTE_UPDATE); ps = c.prepareStatement(DBHelperConstants.NODE_COMPUTE_UPDATE); ps.setString(1, device.getIpAddress()); ps.setString(2, device.getOsName()); ps.setString(3, device.getDeviceFamily()); ps.setString(4, device.getAvailableMemory()); ps.setString(5, device.getLowVoltageMemory()); ps.setString(6, device.getMemorySpeed()); ps.setString(7, device.getName()); ps.setString(8, device.getNumOfAdaptors()); ps.setString(9, device.getNumOfCores()); ps.setString(10, device.getNumOfCoresEnabled()); ps.setString(11, device.getNumOfCpus()); ps.setString(12, device.getNumOfEthHostIfs()); ps.setString(13, device.getNumOfEthHostIfs()); ps.setString(14, device.getNumOfThreads()); ps.setString(15, device.getOperability()); ps.setString(16, device.getOriginalUuid()); ps.setString(17, device.getPartNumber()); ps.setString(18, device.getRevision()); ps.setString(19, device.getServerId()); ps.setString(20, device.getTotalMemory()); ps.setString(21, device.getFsmProgr()); ps.setTime(22, null); ps.setTime(23, null); ps.setTime(24, null); ps.setString(25, device.getUuid()); ps.setString(26, device.getVid()); ps.setInt(27, deviceid); ps.executeUpdate(); } else { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.NODE_COMPUTE_INSERT); ps.setInt(1, deviceid); ps.setString(2, device.getIpAddress()); ps.setString(3, device.getOsName()); ps.setString(4, device.getDeviceFamily()); ps.setString(5, device.getAvailableMemory()); ps.setString(6, device.getLowVoltageMemory()); ps.setString(7, device.getMemorySpeed()); ps.setString(8, device.getName()); ps.setString(9, device.getNumOfAdaptors()); ps.setString(10, device.getNumOfCores()); ps.setString(11, device.getNumOfCoresEnabled()); ps.setString(12, device.getNumOfCpus()); ps.setString(13, device.getNumOfEthHostIfs()); ps.setString(14, device.getNumOfEthHostIfs()); ps.setString(15, device.getNumOfThreads()); ps.setString(16, device.getOperability()); ps.setString(17, device.getOriginalUuid()); ps.setString(18, device.getPartNumber()); ps.setString(19, device.getRevision()); ps.setString(20, device.getServerId()); ps.setString(21, device.getTotalMemory()); ps.setString(22, device.getFsmProgr()); ps.setTime(23, null); ps.setTime(24, null); ps.setTime(25, null); ps.setString(26, device.getUuid()); ps.setString(27, device.getVid()); ps.executeUpdate(); } } catch (Exception e) { logger.error("Error while updating Compute Details ", e); } finally { try { if (ps != null) { ps.close(); DBHelper.releaseConnection(c); } if (rs != null) { rs.close(); } } catch (Exception ee) { logger.error("Error while closing connections ", ee); } } } public static boolean updateDeviceDetails(NetworkNode device, boolean manage, String deviceKnownIp, boolean updatevirtualDevice, int updateDeviceId, boolean discoveryJob) { PreparedStatement ps = null; Connection c = null; int deviceId = updateDeviceId; try { c = DBHelper.getConnection(); c.setAutoCommit(false); boolean insert = true; if (updatevirtualDevice) { insert = false; ps = c.prepareStatement(DBHelperConstants.NODE_UPDATE); } else { ps = c.prepareStatement(DBHelperConstants.NODE_INSERT); } Timestamp ts = null; // CSCtr59009 -Bug fix - Fix for timestamp issue of discovered // devices. if (device.getDiscoveredTime() > 0) { ts = new Timestamp(device.getDiscoveredTime()); } else { synchronized (lock) { // Even though synchronization here looks unnecessary, all // we are trying to do // is to gaurantee a distinct timestamp value across // multiple threads in which // updateDeviceDetails() method is invoked ts = new Timestamp(System.currentTimeMillis()); Thread.sleep(1); } } if (insert) { // logger.debug("Before inserting before device: " + // deviceKnownIp); ps.setString(1, deviceKnownIp); // ps.setInt(2, device.getVersion().getVersionType().typeInt); ps.setInt(2, device.getVersion().getOsTypeDetails().getOsId()); // ps.setString(3, device.getVersion().getDeviceType()); // Setting Device Family as family ps.setString(3, device.getVersion().getDeviceFamily()); ps.setString(4, device.getVendorName()); ps.setString(5, manage ? "m" : "u"); ps.setTimestamp(6, ts); ps.setInt(7, device.getDeviceMode()); ps.setString(8, device.getSysObjectID()); // Setting ShowFamilyAs as Product_Family ps.setString(9, device.getVersion().getShowFamilyAs()); ps.setString(10, device.getVersion().getOsTypeDetails().getOsAlias()); ps.setString(11, device.getProductId()); } else { // ps.setString(1, device.getVersion().getDeviceType()); // Setting Device Family as family ps.setString(1, device.getVersion().getDeviceFamily()); ps.setString(2, device.getVendorName()); ps.setString(3, manage ? "m" : "u"); // ps.setInt(4, device.getVersion().getVersionType().typeInt); ps.setInt(4, device.getVersion().getOsTypeDetails().getOsId()); ps.setInt(5, device.getDeviceMode()); ps.setTimestamp(6, ts); // CSCtr59009 - updating timestamp also if (device.getSysObjectID() != null && !device.getSysObjectID().isEmpty()) { ps.setString(7, device.getSysObjectID()); } else { ps.setString(7, null); } // Setting ShowFamilyAs as Product_Family ps.setString(8, device.getVersion().getShowFamilyAs()); ps.setString(9, device.getVersion().getOsTypeDetails().getOsAlias()); ps.setString(10, device.getProductId()); ps.setInt(11, deviceId); } int id = -1; if (insert) { synchronized (lock) { ps.executeUpdate(); id = getCurrentMaxNodeId(c, device); } } else { ps.executeUpdate(); } if (insert) { // logger.debug("Just inserted device: " + deviceKnownIp); if (id >= 0) { logger.debug("Setting nodeId: " + id + " for device: " + deviceKnownIp); device.setNodeId(id); /* * DeviceWeightage weightage = DeviceWeightageManager.getInstance ().getWeightage(device); * device.setWeightageObj(weightage); */ if (!discoveryJob) { BackupSoftwareImageRequestProcessor.getInstance().processRequest(id); // logger.debug("Device :"+id+" is added to Automatic Backup Job.... "); } } else { throw new Exception("Unable to get new device ID for device: " + deviceKnownIp); } /* * try { * * PreparedStatement ps1 = null; // rs = DBHelper.executeQueryNoCommit(c, * "select max(id) as id from nodes"); ps1 = c.prepareStatement(DBHelperConstants.NODE_DISOVERED); * ps1.setTimestamp(1, ts); rs = ps1.executeQuery(); * * if ((rs != null) && rs.next()) { int id = rs.getInt("ID"); * * device.setNodeId(id); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps1.close(); * } catch (Exception ex) {} } */ } else { device.setNodeId(deviceId); } c.commit(); } catch (Exception ee) { ee.printStackTrace(); logger.warn("Error while updating deviceDetails for the deviceId " + updateDeviceId, ee); try { if (c != null) { c.rollback(); } } catch (Exception e) { } return false; } finally { try { if (ps != null) { ps.close(); } } catch (Exception ee) { } try { if (c != null) { c.setAutoCommit(true); } } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } return true; } private static int getCurrentMaxNodeId(Connection c, NetworkNode device) throws Exception { ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(c, "select max(id) as id from nodes"); if ((rs != null) && rs.next()) { return rs.getInt("id"); } } catch (Exception ex) { logger.error( "Exception while getting the node id of the node just inserted (" + device.getIpAddr() + ")", ex); throw ex; } finally { if (rs != null) { try { rs.close(); } catch (Exception ex1) { } } } return -1; } public static void updateIpToIdDetails(NetworkNode device) { PreparedStatement ps = null; Connection c = null; int id = device.getNodeId(); HashMap<String, Integer> map = new HashMap<String, Integer>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from ip_to_id where id=" + id); while (rs.next()) { map.put(rs.getString("ipaddress"), rs.getInt("id")); } } catch (Exception ee) { logger.warn("Error while updating Ip To Id details for the device= " + id, ee); } try { rs.close(); } catch (Exception ee) { } try { ArrayList alist = device.getAllInterfaces(); Iterator it = alist.iterator(); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_IP_TO_ID); while (it.hasNext()) { NetworkInterface nIf = (NetworkInterface) it.next(); if (nIf.getIpAddress() == null) { continue; } String ip = nIf.getIpAddress(); try { if (rs != null) { rs.close(); } } catch (Exception ee) { } rs = DBHelper .executeQuery("select id from ip_to_id where id=" + id + " AND ipaddress='" + ip + "'"); if ((rs != null) && rs.next()) { int devId = rs.getInt("ID"); if (devId != id) { try { DBHelper.executeUpdate( "delete from ip_to_id where id=" + id + " AND ipaddress='" + ip + "'"); } catch (Exception ee) { logger.warn( "Error while deleting from ip_to_id where id=" + id + " AND ipaddress='" + ip, ee); } } else { map.remove(ip); continue; } } try { ps.setString(1, ip); ps.setInt(2, id); ps.executeUpdate(); } catch (Exception e) { logger.warn("Error while inserting into ip_to_id where id=" + id + " AND ipaddress='" + ip, e); } } // insert the device ip (in hsrp case none of the device interfaces // will have the hsrp ip address try { if (rs != null) { try { rs.close(); } catch (Exception ee) { } } rs = DBHelper.executeQuery( "select * from ip_to_id where ipaddress='" + device.getIpAddr().toString() + "'"); if ((rs == null) || !rs.next()) { try { ps.setString(1, device.getIpAddr().toString()); ps.setInt(2, id); ps.executeUpdate(); map.remove(device.getIpAddr().toString()); } catch (Exception ee) { logger.warn("Error while querying for ip to id", ee); } } else { map.remove(device.getIpAddr().toString()); } } catch (Exception ee) { logger.warn("Error in updateIpToIdDetails", ee); } for (String ip : map.keySet()) { try { DBHelper.executeUpdate("delete from ip_to_id where ipaddress='" + ip + "'"); } catch (Exception ee) { } } } catch (Exception ee) { logger.warn("Error in updateIpToIdDetails", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { rs.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void updateDeviceIfDetails(NetworkNode device, NetworkInterface[] nfs) { PreparedStatement ps = null; PreparedStatement ps1 = null; Connection c = null; try { String query = "delete from ios_if where id=" + device.getNodeId(); DBHelper.executeUpdate(query); if (device instanceof PixDevice) { query = "delete from pix_if where id=" + device.getNodeId(); DBHelper.executeUpdate(query); } if (nfs == null || nfs.length == 0) { return; } c = DBHelper.getConnection(); if (device instanceof PixDevice) { ps1 = c.prepareStatement(DBHelperConstants.PIXNODE_IF_INSERT); } ps = c.prepareStatement(DBHelperConstants.IOSNODE_IF_INSERT); // ArrayList alist = device.getAllInterfaces(); for (NetworkInterface nIf : nfs) { // // Iterator it = alist.iterator(); // // while ( it.hasNext() ) // { try { // NetworkInterface nIf = (NetworkInterface) it.next(); ps.setInt(1, device.getNodeId()); ps.setString(2, nIf.getIfName()); ps.setString(3, nIf.getInterfaceTypeName()); if (nIf.getMacAddress() != null) { ps.setString(4, nIf.getMacAddress().toDottedString()); } else { ps.setString(4, null); } ps.setInt(5, nIf.getSlot()); ps.setLong(6, nIf.getPort()); ps.setLong(7, nIf.getSubPort()); ps.setInt(8, nIf.getIfSpeed()); ps.setInt(9, nIf.getMtu()); if (nIf.getIpAddress() == null) { ps.setString(10, null); } else { ps.setString(10, nIf.getIpAddress().toString()); } if (nIf.getNetMask() == null) { ps.setString(11, null); } else { ps.setString(11, nIf.getNetMask().toString()); } String aStatus = null; // (nIf.getIfAdminStatus() == 1) ? // "up" : "down"; if (nIf.getIfAdminStatus() == 0) { aStatus = "down"; } else if (nIf.getIfAdminStatus() == 1) { aStatus = "up"; } else if (nIf.getIfAdminStatus() == 2) { aStatus = "administratively down"; } String oStatus = null; // (nIf.getIfOperStatus() == 1) ? // "up" : "down"; if (nIf.getIfOperStatus() == 0) { oStatus = "down"; } else if (nIf.getIfOperStatus() == 1) { oStatus = "up"; } else if (nIf.getIfOperStatus() == 2) { oStatus = "administratively down"; } ps.setString(12, aStatus); ps.setString(13, oStatus); ps.setString(14, nIf.getMediaType()); ps.executeUpdate(); if (ps1 != null) { ps1.setInt(1, device.getNodeId()); ps1.setString(2, nIf.getIfName()); ps1.setString(3, nIf.getIfNameAlias()); ps1.setInt(4, nIf.getSecurityLevel()); ps1.executeUpdate(); } } catch (Exception e) { } } } catch (Exception ee) { logger.warn("Error in update device if details", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { if (ps1 != null) { ps1.close(); } } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void updateIosVersionDetails(NetworkNode device) throws Exception { PreparedStatement ps = null; Connection c = null; try { c = DBHelper.getConnection(); c.setAutoCommit(false); String query = "delete from ios_version where id=" + device.getNodeId() + ""; DBHelper.executeUpdateNoCommit(c, query); ps = c.prepareStatement(DBHelperConstants.IOS_VERSION_INSERT); Version ver = device.getVersion(); ps.setInt(1, device.getNodeId()); ps.setString(2, ver.getVersionStr()); ps.setString(3, ver.getBuild()); ps.setString(4, ver.getProductId()); ps.setString(5, ver.getDeviceType()); ps.setString(6, ver.getNodeName()); ps.setString(7, ver.getFlash()); ps.setString(8, ver.getMemory()); ps.setString(9, ver.getSerialNumber()); ps.setString(10, ver.getImageFileName()); ps.executeUpdate(); c.commit(); } catch (Exception ee) { System.err.println("NODE ID:" + device.getNodeId()); ee.printStackTrace(); c.rollback(); logger.warn("Error in updateIosVersionDetails", ee); throw ee; } finally { try { c.setAutoCommit(true); } catch (Exception ex) { } try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } dumpFullIosVersion(device); } public static void updateCapabilities(int device_id, String cap) { PreparedStatement ps = null; Connection c = null; try { String query = "delete from node_not_supp_caps where device_id=" + device_id; DBHelper.executeUpdate(query); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.NODE_CAPABILITIES_INSERT); ps.setInt(1, device_id); ps.setString(2, cap); if (cap.length() > 4000) { logger.error("Size of cap for deviceID: " + device_id + " = " + cap.length()); logger.error(cap); } ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while updating the capabilities for the device=" + device_id, ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static int getNodeId(String ipAddress) { int nodeId = -1; ResultSet rs = null; try { rs = DBHelper.executeQuery("select ID from nodes where ipaddress='" + ipAddress + "'"); if (rs.next()) { nodeId = rs.getInt("ID"); } } catch (Exception ee) { logger.info("Error while selecting from nodes for the ip address: " + ipAddress, ee); try { rs.close(); } catch (Exception e) { } } finally { try { rs.close(); } catch (Exception e) { } } return nodeId; } public static boolean checkExistenceInDB(int nodeId) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from nodes where id=" + nodeId); if (rs.next()) { return true; } else { return false; } } catch (Exception ee) { logger.info("Error while checking node existence: " + nodeId, ee); try { rs.close(); } catch (Exception e) { } } finally { try { rs.close(); } catch (Exception e) { } } return false; } public static void deleteDeviceHistory(int customerId, String ipAddress) { try { DBHelper.executeUpdate("delete from unmanaged_devices where customer_id=" + customerId + " and device_ip='" + ipAddress + "'"); } catch (SQLException e) { logger.error("Failed to delete device history for unmanaged devices.", e); } } public static boolean checkUnmanagedDeviceInDB(int nodeId) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select state from nodes where id=" + nodeId); if (rs.next()) { String state = rs.getString("state"); if (state.equals("u")) { return true; } } } catch (Exception ee) { logger.info("Error while checking node existence: " + nodeId, ee); try { rs.close(); } catch (Exception e) { } } finally { try { rs.close(); } catch (Exception e) { } } return false; } public static NetworkNode[] loadDevices() { return ((new InventoryLoader()).loadDevices()); /* * NetworkNode[] nodes = null; NetworkNode node = null; ArrayList<NetworkNode> lis = new * ArrayList<NetworkNode>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from nodes"); * * while ( rs.next() ) { if ( !rs.getString("state").equals("m") ) { continue; } * * String vendor = rs.getString("vendor"); int id = rs.getInt("ID"); * * int deviceMode = rs.getInt("device_type"); * * int osType = rs.getInt("os"); OSType os = OSType.getFromInt(osType); node = VersionFactory.createDevice(os); * node.setVendorName(vendor); node.setNodeId(id); node.setDeviceMode(deviceMode); * * if (node.getVersion() == null && node.getDeviceMode() == Constants.VIRTUAL_DEVICE) { * logger.debug("Version is null..skipping loading of " +node.getNodeId()); continue; } DeviceWeightage * weightage = DeviceWeightageManager.getInstance().getWeightage(node); node.setWeightageObj(weightage); * lis.add(node); } } catch ( Exception ee ) { logger.warn("Error while loading devices", ee); } finally { try { * rs.close(); } catch ( Exception ee ) { } } * * nodes = new NetworkNode[lis.size()]; lis.toArray(nodes); * * return nodes; */ } public static NetworkNode readDevice(int nodeId, OSType deviceType) { // NetworkNode node = VersionFactory.createDevice(deviceType); NetworkNode node = new NetworkNode(); node.setNodeId(nodeId); readDevice(node); return node; } public static NetworkNode readDevice(NetworkNode node) { try { LinkedHashMap<String, String> queryTypes = node.getQueryTypes(); if (queryTypes != null) { for (String qtype : queryTypes.keySet()) { InventoryDBHelper.loadQuery(node, queryTypes.get(qtype), qtype); } } } catch (Exception ee) { logger.warn("Error in readDevice for the deviceId=" + node.getNodeId(), ee); } return node; } public static NetworkNode[] loadUnmanagedDevices() { NetworkNode[] nodes = null; NetworkNode node = null; ArrayList<NetworkNode> lis = new ArrayList<NetworkNode>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from nodes where state='u'"); while (rs.next()) { String vendor = rs.getString("vendor"); int id = rs.getInt("ID"); // if (vendor.toLowerCase().indexOf("cisco") != -1) // { node = new NetworkNode(); node.setVendorName(vendor); node.setNodeId(id); /* * DeviceWeightage weightage = DeviceWeightageManager.getInstance().getWeightage(node); * node.setWeightageObj(weightage); */ // } readDevice(node); lis.add(node); } } catch (Exception ee) { logger.warn("Error while loading unmanage devices.", ee); } finally { try { rs.close(); } catch (Exception ee) { } } nodes = new NetworkNode[lis.size()]; lis.toArray(nodes); return nodes; } /* * public static int saveDeviceNeighborDetails(int fromDeviceId, int toDeviceId, String fromPort, String toPort) * throws Exception { Connection c = null; PreparedStatement ps = null; ResultSet rs = null; int linkId = -1; * boolean entryExists = false; NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(toDeviceId); if (node * == null) { return -1; } * * String toIpAddress = node.getIpAddr().toString(); try { rs = * DBHelper.executeQuery("select * from device_cdp_table where device_id=" + fromDeviceId + " AND toIPAddress='" + * toIpAddress + "' AND fromport='" + fromPort + "' AND toport='" + toPort + "'"); * * if ( (rs != null) && rs.next() ) { entryExists = true; } } catch ( Exception ee ) { * logger.warn("Error while querying device neighbor details for device_id=" + fromDeviceId, ee ); } * * try { rs.close(); } catch ( Exception ee ) {} * * if ( entryExists ) { throw (new Exception("The Neighbor Entry already exists.")); } * * c = DBHelper.getConnection(); * * try { ps = c.prepareStatement(DBHelperConstants.INSERT_DEVICE_NEIGHBOR); ps.setInt(1, fromDeviceId); * ps.setString(2, toIpAddress); ps.setString(3, fromPort); ps.setString(4, toPort); * * ps.executeUpdate(); * * rs = DBHelper.executeQuery("select link_id from device_cdp_table where device_id=" + fromDeviceId + * " AND toIPAddress='" + toIpAddress + "' AND fromport='" + fromPort + "' AND toport='" + toPort + "'"); * * if ( (rs != null) && rs.next() ) { linkId = rs.getInt(1); * * NetworkNode device = NetworkNodeCache.getInstance().getNodeByID(fromDeviceId); * * if ( device != null ) { device.updateCDPNeighbors(linkId, fromDeviceId, toIpAddress, fromPort, toPort); } } } * catch ( Exception ee ) { logger.warn("Error while savingdevice neighbor details for device_id=" + fromDeviceId, * ee ); } finally { try { ps.close(); } catch ( Exception ee ) {} * * try { rs.close(); } catch ( Exception ee ) {} * * try { DBHelper.releaseConnection(c); } catch ( Exception ee ) {} } * * return linkId; } */ public static String retrieveVersionStr(String runConf) { try { StringTokenizer st = new StringTokenizer(runConf, "\n"); while (st.hasMoreTokens()) { String token = st.nextToken().trim().toLowerCase(); if (token.indexOf("fwsm version") != -1) { String version = token.substring("FWSM Version".length()); return version.trim(); } else if (token.startsWith("version")) { String version = token.substring("version".length()); return version.trim(); } else if (token.startsWith("pix version")) { String version = token.substring("PIX Version".length()); return version.trim(); } else if (token.startsWith("asa version")) { String version = token.substring("ASA Version".length()); return version.trim(); } else if (token.startsWith("#version")) { String version = token.substring("#version".length()); return version.trim(); } } } catch (Exception ee) { logger.warn("Error in retrieveVersionStr", ee); } return null; } public static String getVirtualDeviceTypeFromRunConf(String runConf) { try { StringTokenizer st = new StringTokenizer(runConf, "\r\n"); while (st.hasMoreTokens()) { String token = st.nextToken().trim().toLowerCase(); if (token.startsWith("pix version") || (token.startsWith("asa version"))) { return "PIX"; } if (token.startsWith("#version")) { return "CatOS"; } } } catch (Exception ee) { logger.warn("Error in getVirtualDeviceTypeFromRunConf", ee); } return null; } public static String getFirewallOsTypeFromRunConf(String runConf) { try { StringTokenizer st = new StringTokenizer(runConf, "\r\n"); while (st.hasMoreTokens()) { String token = st.nextToken().trim().toLowerCase(); if (token.startsWith("pix version")) { return "PIX"; } else if (token.startsWith("asa version")) { return "ASA"; } } } catch (Exception ee) { logger.warn("Error in getFirewallOsTypeFromRunConf", ee); } return null; } public static NetworkInterface[] retrieveNetworkInterfaces(String runConf, NetworkNode node) { if (runConf == null) { return new NetworkInterface[0]; } ShowCommandGrammer gram = ShowCommandGrammerBuilder.getInstance().getShowCommandGrammerFor("*", "parseRunConf"); ParseShowCommand parseComm = new ParseShowCommand(gram, runConf, node); try { ArrayList list = parseComm.parse(); return CliToObjectsBuilder.parseInterfaceInfo(node, list); } catch (Exception ee) { logger.warn("Error while retrieving network interfaces", ee); } return new NetworkInterface[0]; } public static NetworkInterface[] retrievePixNetworkInterfaces(String runConf, NetworkNode node) { ShowCommandGrammer gram = ShowCommandGrammerBuilder.getInstance().getShowCommandGrammerFor("*", "parsePixRunConf"); try { ParseShowCommand parseComm = new ParseShowCommand(gram, runConf, node); ArrayList list = parseComm.parse(); return CliToObjectsBuilder.parseInterfaceInfo(node, list); } catch (Exception ee) { logger.warn("Error while retrieving pix network interfaces", ee); } return new NetworkInterface[0]; } public static void updateDeviceOperationTime(int deviceId, String moduleName, String subModuleName, long timestamp) { PreparedStatement ps = null; Connection c = null; try { String query = "delete from device_operation_time where dev_id=" + deviceId + " and module='" + moduleName + "' and sub_module='" + subModuleName + "'"; DBHelper.executeUpdate(query); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.DEVICE_OPERATION_TIME_INSERT); ps.setInt(1, deviceId); ps.setString(2, moduleName); ps.setString(3, subModuleName); ps.setTimestamp(4, new Timestamp(timestamp)); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while updating device operation time for the device=" + deviceId, ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void updateDeviceMode(int deviceId, int device_type) { PreparedStatement ps = null; Connection c = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.NODE_CSPCMODE_UPDATE); ps.setInt(1, device_type); ps.setInt(2, deviceId); ps.executeUpdate(); } catch (Exception ee) { logger.error("Error while updating device mode for the device=" + deviceId, ee); } finally { try { ps.close(); } catch (Exception ee) { logger.error("Error while closing prepared statement", ee); } try { DBHelper.releaseConnection(c); } catch (Exception ee) { logger.error("Error while releasing connection ", ee); } } } public static void storeSyslogMsg(int nodeId, DecodedSyslogMessage msg) { Connection c = null; PreparedStatement ps = null; try { NetworkNode node = null; if (nodeId != -1) { node = NetworkNodeCache.getInstance().getNodeByID(nodeId); } else { node = NetworkNodeCache.getInstance().getLiveNode(msg.getSource().toString()); } if (node == null) { return; } String compare_str = msg.get_module(); if (node instanceof PixDevice) { compare_str = msg.get_mnemonic(); } int id = node.getNodeId(); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_SYSLOG_MESSAGE); ps.setInt(1, id); ps.setString(2, msg.getModule()); ps.setString(3, msg.get_mnemonic()); ps.setInt(4, msg.get_severity()); ps.setString(5, msg.message()); ps.setString(6, msg.tag()); ps.setInt(7, msg.priority()); ps.setLong(8, msg.getSeqNumber()); ps.setTimestamp(9, (new Timestamp(msg.getReceivedTime()))); ps.setString(10, compare_str); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while storing syslog message for the deviceId=" + nodeId, ee); } finally { try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void addSyslogMsg(int deviceId, DecodedSyslogMessage msg) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_SYSLOG_MESSAGE); ps.setInt(1, deviceId); ps.setString(2, msg.getModule()); ps.setString(3, msg.get_mnemonic().substring(1)); ps.setInt(4, msg.get_severity()); ps.setString(5, msg.message()); ps.setString(6, msg.tag()); ps.setInt(7, msg.priority()); ps.setLong(8, msg.getSeqNumber()); ps.setTimestamp(9, (new Timestamp(msg.getReceivedTime()))); ps.setString(10, msg.get_mnemonic()); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while storing syslog message for the deviceId=" + deviceId, ee); } finally { try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void pergeSyslogMsgPerDevice(long maxSyslogMsgsPerDev) { String selectQuery = null; String deleteQuery = null; if (isPostgres()) { selectQuery = "select distinct(device_id) from syslog_messages"; deleteQuery = "delete from syslog_messages where device_id = ? and received_at not in " + "(Select received_at from (select received_at from syslog_messages where device_id = ? " + " order by received_at desc)slm LIMIT ?) "; } else { selectQuery = "select distinct(device_id) from syslog_messages"; deleteQuery = "delete from syslog_messages where device_id = ? and received_at not in " + "(Select received_at from (select received_at from syslog_messages where device_id = ? " + " order by received_at desc) where rownum <= ?) "; } Connection c = null; PreparedStatement ps = null; ResultSet rs = null; try { rs = DBHelper.executeQuery(selectQuery); while ((rs != null) && rs.next()) { int deviceId = rs.getInt("device_id"); try { c = DBHelper.getConnection(); ps = c.prepareStatement(deleteQuery); ps.setInt(1, deviceId); ps.setInt(2, deviceId); ps.setLong(3, maxSyslogMsgsPerDev); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while perging syslog message for the deviceId=" + deviceId, ee); } finally { try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } } catch (Exception ee) { logger.warn("Error while executing getUniqIp", ee); } finally { try { rs.close(); } catch (Exception ee) { } } } public static void pergeSyslogMsgAllDevice(long maxSyslogMsgs) { String deleteQuery = null; if (isPostgres()) { deleteQuery = "delete from syslog_messages where received_at not in " + "(Select received_at from (select received_at from syslog_messages " + " order by received_at desc)slm LIMIT ?) "; } else { deleteQuery = "delete from syslog_messages where received_at not in " + "(Select received_at from (select received_at from syslog_messages " + " order by received_at desc) where rownum < ?) "; } Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(deleteQuery); ps.setLong(1, maxSyslogMsgs); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while perging syslog message for all devices", ee); } finally { try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void pergeSyslogMsgForNonExistingDevice() { String deleteQuery = "delete from syslog_messages where DEVICE_ID not in " + "(Select ID from nodes) "; try { DBHelper.executeUpdate(deleteQuery); } catch (Exception ee) { logger.warn("Error while perging syslog message for non existing devices", ee); } } public static Map<Integer, Long> getLastSyslogInfoPerDevice() { Map<Integer, Long> deviceSeqNoMap = new HashMap<Integer, Long>(); ResultSet rs = null; try { // received_at rs = DBHelper.executeQuery("select device_id, max(seq_number) from syslog_messages group by device_id"); if (rs != null) { while (rs.next()) { if (rs.getInt(1) > 0) { deviceSeqNoMap.put(rs.getInt(1), rs.getLong(2)); } } } } catch (Exception ee) { logger.warn("Error while retriving last syslog info per device", ee); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } } return deviceSeqNoMap; } public static Map<Integer, Long> getSyslogLastReceivedTimePerDevice() { Map<Integer, Long> deviceSeqNoMap = new HashMap<Integer, Long>(); ResultSet rs = null; try { rs = DBHelper .executeQuery("select device_id, max(received_at) from syslog_messages group by device_id"); if (rs != null) { while (rs.next()) { if (rs.getInt(1) > 0) { deviceSeqNoMap.put(rs.getInt(1), rs.getTimestamp(2).getTime()); } } } } catch (Exception ee) { logger.warn("Error while retriving syslog last received time per device", ee); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } } return deviceSeqNoMap; } public static int isVirtualDeviceExists(String deviceName) { String query = "select id from ios_version where hostname=?"; Connection c = null; PreparedStatement ps = null; ResultSet rs = null; int id = -1; try { c = DBHelper.getConnection(); ps = c.prepareStatement(query); ps.setString(1, deviceName); rs = ps.executeQuery(); if (rs != null && rs.next()) { id = rs.getInt("ID"); NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(id); if (node == null || node.getDeviceMode() == Constants.PHYSICAL_DEVICE) { id = -1; } } } catch (Exception ee) { logger.warn("Error while checking whether virtual device exists with the device name: " + deviceName, ee); } finally { try { rs.close(); } catch (Exception e) { } try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception e) { } } return id; } public static int isVirtualDeviceExists(String deviceName, int custId) { String query = "select DEVICE_ID from CUSTOMER_HOSTNAME where CUSTOMER_ID=? and HOSTNAME=?"; Connection c = null; PreparedStatement ps = null; ResultSet rs = null; int id = -1; try { c = DBHelper.getConnection(); ps = c.prepareStatement(query); ps.setInt(1, custId); ps.setString(2, deviceName); rs = ps.executeQuery(); if (rs != null && rs.next()) { id = rs.getInt("DEVICE_ID"); NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(id); if (node == null) { id = -1; } } } catch (Exception ee) { logger.warn("Error while checking whether virtual device exists with the device name: " + deviceName, ee); } finally { try { rs.close(); } catch (Exception e) { } try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception e) { } } return id; } public static String getUniqIp() { StringBuffer sbuf = new StringBuffer(); Random ran = new Random(); sbuf.append("0."); sbuf.append(ran.nextInt(254)).append("."); sbuf.append(ran.nextInt(254)).append("."); sbuf.append(ran.nextInt(254)); String ip = sbuf.toString(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from nodes where ipaddress='" + ip + "'"); if ((rs != null) && rs.next()) { return getUniqIp(); } } catch (Exception ee) { logger.warn("Error while executing getUniqIp", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return ip; } public static void loadQuery(NetworkNode node, String query, String queryType) { // if ( logger.isDebugEnabled() ) // { // logger.debug("loadQuery", query); // logger.debug("Query Type: " + queryType); // logger.debug("Node Id: " + node.getNodeId()); // } ResultSet rs = null; try { rs = DBHelper.executeQuery(query); if (queryType.equals("ios.runconf")) { // StringBuffer sb = new StringBuffer(); // while ( rs.next() ) // { // sb.append(rs.getString("run_conf")); // } // node.setRunningConfig(sb.toString()); } else { while (rs.next()) { node.updateObject(queryType, rs); } } } catch (Exception e) { logger.warn("Error while running loadQuery", query, e); } finally { try { rs.close(); } catch (Exception ex) { } } } public static void updateInventoryTime(int nodeId, long invUpdateTime) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_INVENTORY_TIME); ps.setTimestamp(1, (new Timestamp(invUpdateTime))); ps.setInt(2, nodeId); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while updating inventory time for the device= " + nodeId, ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } } public static void updateConfigChangeTime(int nodeId, long confUpdateTime) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_CONFIG_UPDATE_TIME); ps.setTimestamp(1, (new Timestamp(confUpdateTime))); ps.setInt(2, nodeId); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while updating config change time for the device= " + nodeId, ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } } public static CustomerInstance[] loadWingInstances() { ArrayList<CustomerInstance> lis = new ArrayList<CustomerInstance>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from wing_instance"); while (rs.next()) { CustomerInstance instance = new CustomerInstance(); int id = rs.getInt("instance_id"); instance.setInstnceId(id); String name = rs.getString("instance_name"); instance.setInstanceName(name); String contact_name = rs.getString("contact_name"); if (contact_name != null) { instance.setContactName(contact_name); } String contact_email = rs.getString("contact_email"); if (contact_email != null) { instance.setContactEmail(contact_email); } String contact_phone = rs.getString("contact_phone"); if (contact_phone != null) { instance.setContactPhoneNumber(contact_phone); } String location = rs.getString("location"); if (location != null) { instance.setLocation(location); } String description = rs.getString("description"); if (description != null) { instance.setDescription(description); } lis.add(instance); } } catch (Exception ee) { logger.error("Error while loading customers from db.", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing cursor.", ee); } } return lis.toArray(new CustomerInstance[lis.size()]); } public static Customer[] loadCustomers() { ArrayList<Customer> lis = new ArrayList<Customer>(); ResultSet rs = null; ArrayList<Customer> updateCustomers = new ArrayList<Customer>(); try { rs = DBHelper.executeQuery("select * from customers"); while (rs.next()) { Customer customer = new Customer(); int id = rs.getInt("customer_id"); customer.setId(id); String name = rs.getString("customer_name"); customer.setCustomerName(name); String contact_name = rs.getString("contact_name"); if (contact_name != null) { customer.setContactName(contact_name); } String contact_email = rs.getString("contact_email"); if (contact_email != null) { customer.setEmailAddresses(contact_email); } String contact_phone = rs.getString("contact_phone"); if (contact_phone != null) { customer.setContactPhoneNumber(contact_phone); } String wing_login = rs.getString("wing_login"); if (wing_login != null) { customer.setLogin(wing_login); } String securityKey = rs.getString("security_key"); if (securityKey != null) { customer.setSecurityKey(securityKey); } int noOfLicenses = rs.getInt("no_of_licenses"); int totLicUnits = LicenseValidator.getInstance().getNoOfDevices(); if (totLicUnits == -1) { noOfLicenses = -1; updateCustomers.add(customer); } else if (noOfLicenses > totLicUnits) { noOfLicenses = totLicUnits; updateCustomers.add(customer); } customer.setNoOfLicenses(noOfLicenses); int creatorId = rs.getInt("creator_id"); customer.setCreatorId(creatorId); String creatorName = rs.getString("creator_name"); UserDetails ud = UsersFactory.getUser(creatorId); if (ud != null) { customer.setCreaterName( ud.getName() != null ? (ud.getName() + " (" + ud.getLogin() + ")") : ud.getLogin()); } else { customer.setCreaterName(creatorName); } customer.setFaultList(getCustomerFaults(id)); lis.add(customer); } } catch (Exception ee) { logger.error("Error while loading customers from db.", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing cursor.", ee); } } if (updateCustomers.size() > 0) { for (Customer cus : updateCustomers) { insertOrUpdateCustomer(cus); } } return lis.toArray(new Customer[lis.size()]); } public synchronized static CustomerInstance insertOrUpdateWingInstance(CustomerInstance instance) { PreparedStatement ps = null; Connection c = null; try { c = DBHelper.getConnection(); boolean insert = false; if (instance.getInstanceId() == -1) { int instId = getNextWingInstanceId(c); instance.setInstnceId(instId); ps = c.prepareStatement(DBHelperConstants.WING_INSTANCE_INSERT); insert = true; } else { ps = c.prepareStatement(DBHelperConstants.WING_INSTANCE_UPDATE); } if (insert) { ps.setInt(1, instance.getInstanceId()); ps.setString(2, instance.getInstanceName()); ps.setString(3, instance.getContactName()); ps.setString(4, instance.getContactEmail()); ps.setString(5, instance.getContactPhoneNumber()); ps.setString(6, instance.getLocation()); ps.setString(7, instance.getDescription()); } else { ps.setString(1, instance.getInstanceName()); ps.setString(2, instance.getContactName()); ps.setString(3, instance.getContactEmail()); ps.setString(4, instance.getContactPhoneNumber()); ps.setString(5, instance.getLocation()); ps.setString(6, instance.getDescription()); ps.setInt(7, instance.getInstanceId()); } ps.executeUpdate(); return instance; } catch (Exception ee) { logger.warn("Error while inserting or updating the customer instance", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } return null; } public static ConcurrentHashMap<Integer, HashSet<Integer>> loadCustomerWingInstances() { ConcurrentHashMap<Integer, HashSet<Integer>> instTable = new ConcurrentHashMap<Integer, HashSet<Integer>>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from customer_wing_instance"); while (rs.next()) { int custId = rs.getInt("customer_id"); int instId = rs.getInt("instance_id"); HashSet<Integer> instances = instTable.get(custId); if (instances == null) { instances = new HashSet<Integer>(); instTable.put(custId, instances); } instances.add(instId); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { rs.close(); } catch (Exception e) { } } return instTable; } public static void deletecustomerInstanceId(int custId, int instId) throws Exception { DBHelper.executeUpdate( "delete from customer_wing_instance where customer_id=" + custId + " and instance_id=" + instId); CustomerInstance custInst = CustomerWingInstanceManager.getInstance().getCustomerInstance(instId); if (custInst != null) { DBHelper.executeUpdate("delete from wing_settings_update_details where customer_id=" + custId + " and instance_name='" + custInst.getInstanceName() + "'"); } } public static void insertCustomerWingInstance(int custId, int instId) { try { DBHelper.executeUpdate("insert into customer_wing_instance values (" + custId + "," + instId + ")"); } catch (Exception ee) { ee.printStackTrace(); } } public static Customer insertOrUpdateCustomer(Customer customer) { PreparedStatement ps = null; PreparedStatement ps1 = null; PreparedStatement ps2 = null; Connection c = null; ResultSet rs = null; try { c = DBHelper.getConnection(); boolean insert = true; if (customer.getId() != -1) { String query = "select * from customers where customer_id=" + customer.getId(); rs = DBHelper.executeQuery(query); if (rs.next()) { insert = false; } try { rs.close(); } catch (Exception ee) { } } if (!insert) { ps = c.prepareStatement(DBHelperConstants.CUSTOMER_UPDATE); } else { customer.setId(getNextCustomerId(c)); ps = c.prepareStatement(DBHelperConstants.CUSTOMER_INSERT); ps1 = c.prepareStatement(DBHelperConstants.SHADOW_CUSTOMER_INSERT); ps2 = c.prepareStatement(DBHelperConstants.INSERT_PROFILES_ALL); } if (insert) { ps.setInt(1, customer.getId()); ps.setString(2, customer.getCustomerName()); ps.setString(3, customer.getContactName()); ps.setString(4, customer.getEmailAddresses()); ps.setString(5, customer.getContactPhoneNumber()); ps.setString(6, customer.getLogin()); ps.setString(7, customer.getSecurityKey()); ps.setInt(8, customer.getCreatorId()); ps.setString(9, customer.getCreaterName()); ps.setDouble(10, customer.getNoOfLicenses()); ps1.setInt(1, customer.getId()); ps1.setInt(2, customer.getId()); ps2.setInt(1, customer.getId()); ps2.setString(2, customer.getCustomerName()); // Creating profiles FaultProfile fp = new FaultProfile(); fp.setProfId(customer.getId()); fp.setProfName(customer.getCustomerName()); FaultSpecFactory.getInstance().addFaultProfileOnly(fp); } else { ps.setString(1, customer.getCustomerName()); ps.setString(2, customer.getContactName()); ps.setString(3, customer.getEmailAddresses()); ps.setString(4, customer.getContactPhoneNumber()); ps.setString(5, customer.getLogin()); ps.setString(6, customer.getSecurityKey()); ps.setInt(7, customer.getNoOfLicenses()); ps.setDouble(8, customer.getId()); } ps.executeUpdate(); if (ps1 != null) { ps1.executeUpdate(); } if (ps2 != null) { ps2.executeUpdate(); } return customer; } catch (Exception ee) { logger.warn("Error while inserting or updating the customer", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { ps1.close(); } catch (Exception ee) { } try { ps2.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } try { rs.close(); } catch (Exception ee) { } } return null; } /** * Generarates Public/Private keypairs and stores the keystore file in database. If the keystore already exists then * it will re generate only if regenerate paramater is true * * @param customerId * Customer ID * @param instace_id * Instance ID * @param alias * Keystore alias for the key pairs * @param keystore * Keystore data in base64 encoded format * @param regenerate * If keystore already exists then true value will re-generte the keystore */ public static void insertCustomerSecurityKeystore(int customerId, int instace_id, String alias, String keystore, boolean regenerate) { if (!regenerate && getCustomerSecurityKeystore(customerId, instace_id) != null) { logger.info("Keystore already exists for customer/instance ", customerId + "/" + instace_id); return; } Connection c = null; PreparedStatement ps = null; try { DBHelper.executeUpdate("delete from customer_security_keystore where customer_id=" + customerId + " and instance_id = " + instace_id); c = DBHelper.getConnection(); ps = c.prepareStatement( "insert into customer_security_keystore (customer_id, instance_id, alias, keystore) VALUES (?,?,?,?)"); ps.setInt(1, customerId); ps.setInt(2, instace_id); ps.setString(3, alias); if (ps instanceof OraclePreparedStatement) { ((OraclePreparedStatement) ps).setClob(4, new InputStreamReader(IOUtils.toInputStream(keystore, "UTF-8"))); } else if (isPostgres()) { ps.setString(4, keystore); } else { ps.setString(4, keystore); } ps.executeUpdate(); } catch (Exception e) { logger.warn("Error while inserting key pairs for : \'" + alias + "\'", e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static String getCustomerSecurityKeystore(int customer_id, int installation_id) { String keystore = null; ResultSet rs = null; try { rs = DBHelper.executeQuery("select keystore from customer_security_keystore where customer_id = " + customer_id + " and instance_id = " + installation_id); if (rs.next()) { keystore = rs.getString("keystore"); } } catch (Exception e) { logger.warn("Error while loading keystore for : \'" + customer_id + "/" + installation_id + "\'", e); } finally { try { rs.close(); } catch (Exception ee) { } } return keystore; } public static String getCustomerSecurityKeystoreAlias(int customer_id, int installation_id) { String alias = null; ResultSet rs = null; try { rs = DBHelper.executeQuery("select alias from customer_security_keystore where customer_id = " + customer_id + " and instance_id = " + installation_id); if (rs.next()) { alias = rs.getString("alias"); } } catch (Exception e) { logger.warn("Error while loading alias for : \'" + customer_id + "/" + installation_id + "\'", e); } finally { try { rs.close(); } catch (Exception ee) { } } return alias; } private static int getNextCustomerId(Connection c) throws Exception { return DBHelper.getNextSequence(c, "customers_customer_id"); } private static int getNextWingInstanceId(Connection c) throws Exception { return DBHelper.getNextSequence(c, "wing_instance_instance_id"); } public static void deleteCustomer(int customer_id) throws Exception { try { DBHelper.executeUpdate("delete from customers where customer_id=" + customer_id); DBHelper.executeUpdate("delete from flt_profiles where profile_id=" + customer_id); FaultSpecFactory.getInstance().deleteFaultProfileOnly(customer_id); } catch (Exception ee) { logger.error("Error in Delete Customer ID :" + customer_id, ee); } } public static void loadCustomerDeviceDetails( ConcurrentHashMap<Integer, HashMap<Integer, String>> customerDevice, ConcurrentHashMap<Integer, ConcurrentHashMap<String, Integer>> customerDeviceIPList) { ResultSet rs = null; try { rs = DBHelper.executeQuery( "select * from customer_device where device_id not in (select mod_id from nodes_modules)"); while (rs.next()) { int custId = rs.getInt("customer_id"); int deviceId = rs.getInt("device_id"); NetworkNode node = NetworkNodeCache.getInstance().getNodeByID(deviceId); if (node == null) { continue; } node.setCustomerId(custId); // DeviceWeightageCache.getInstance().addCustNodeWeightageMap(custId, // node); HashMap<Integer, String> devices = customerDevice.get(custId); if (devices == null) { devices = new HashMap<Integer, String>(); customerDevice.put(custId, devices); } devices.put(deviceId, ""); ConcurrentHashMap<String, Integer> devicesIpList = customerDeviceIPList.get(custId); if (devicesIpList == null) { devicesIpList = new ConcurrentHashMap<String, Integer>(); customerDeviceIPList.put(custId, devicesIpList); } devicesIpList.put(node.getIpAddr().toString(), deviceId); /* * Removing this code because we no longer worry about duplicate devices in NCCM. CSPC takes care of * this. We only put the device management IP address in this list now. */ // ArrayList list = node.getAllInterfaces(); // Iterator it = list.iterator(); // while (it.hasNext()) // { // NetworkInterface nif = (NetworkInterface) it.next(); // if (nif.getIpAddress() != null) // { // devicesIpList.put(nif.getIpAddress(), deviceId); // } // } } } catch (Exception ee) { logger.warn("Error in loadCustomerDeviceDetails", ee); } finally { try { rs.close(); } catch (Exception ee) { } } } public static void addCustomerDeviceDetails(int custId, int[] deviceDetails) { try { for (int i = 0; i < deviceDetails.length; i++) { DBHelper.executeUpdate( "insert into customer_device VALUES (" + custId + "," + deviceDetails[i] + ")"); } } catch (Exception ee) { logger.warn("Error while setting up customer to device ids", ee); } } public static void updateCustomerDeviceDetails(int custId, int[] deviceDetails) { try { for (int i = 0; i < deviceDetails.length; i++) { DBHelper.executeUpdate( "insert into customer_device VALUES (" + custId + "," + deviceDetails[i] + ")"); } } catch (Exception ee) { logger.warn("Error while setting up customer to device ids", ee); } } public static void addCustomerDeviceDetails(int customerId, int devId) { try { DBHelper.executeUpdate("insert into customer_device VALUES (" + customerId + "," + devId + ")"); } catch (Exception ee) { logger.warn("Error adding device to the customer_device table", ee); } } public static void delCustomerDeviceDetails(int customerId, int devId) { try { DBHelper.executeUpdate( "delete from customer_device where CUSTOMER_ID=" + customerId + " AND DEVICE_ID=" + devId); } catch (Exception ee) { logger.warn("Error while deleting device ids from the customer_device.", ee); } } public static void updateCatOsDeviceDetails(NetworkNode device) { Connection c = null; PreparedStatement ps = null; try { CatOSVersion ver = (CatOSVersion) device.getVersion(); DBHelper.executeUpdate("delete from catos_version where id=" + device.getNodeId()); DBHelper.executeUpdate("delete from device_module_info where device_id=" + device.getNodeId()); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_CATOS_VERSION); ps.setInt(1, device.getNodeId()); ps.setString(2, ver.getMcpSwVersion()); ps.setString(3, ver.getHardwareVersion()); try { ps.executeUpdate(); } catch (Exception ee) { } try { ps.close(); } catch (Exception ee) { } ArrayList<CatOSSwitchModule> modules = ver.getSwitchModules(); for (CatOSSwitchModule module : modules) { try { ps = c.prepareStatement(DBHelperConstants.INSERT_CATOS_SWITCH_MODULES); ps.setInt(1, device.getNodeId()); ps.setInt(2, module.getModule()); ps.setString(3, module.getModel()); ps.setString(4, module.getPort()); ps.setString(5, module.getSerialNumber()); ps.setString(6, module.getHw()); ps.setString(7, module.getFw()); ps.setString(8, module.getFw1()); ps.setString(9, module.getSw()); ps.setString(10, module.getSw1()); ps.executeUpdate(); } catch (Exception ee) { } try { ps.close(); } catch (Exception ee) { } try { InventoryDBHelper.insertCatOsModules(c, device.getNodeId(), module.getModel(), "Module", module.getModel(), module.getSerialNumber()); } catch (Exception e) { logger.warn("Error while inserting catOs modules", e); } } } catch (Exception ee) { logger.warn("Error hwhile updating catos details", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } } public static List<String> getConsolidatedProductFamily() { ResultSet rs = null; List<String> productFamilyList = new ArrayList<>(); try { rs = DBHelper.executeQuery("select distinct product_family as ProductFamily from nodes"); while (rs.next()) { productFamilyList.add(rs.getString("ProductFamily")); } return productFamilyList; } catch (Exception ex) { logger.warn("Error while getting product family " + ex); } finally { try { rs.close(); } catch (Exception e) { } } return productFamilyList; } public static String getCustomerWingSettings(int custId, String instanceName) throws PariException { StringBuffer sbuf = new StringBuffer(); ResultSet rs = null; try { if (instanceName == null) { rs = DBHelper.executeQuery( "select * from CUSTOMER_WING_SETTINGS where CUSTOMER_ID=" + custId + " order by row_id"); } else { rs = DBHelper.executeQuery("select * from CUSTOMER_WING_SETTINGS where CUSTOMER_ID=" + custId + " AND instance_name='" + instanceName + "' order by row_id"); } while (rs.next()) { sbuf.append(rs.getString("settings")); } } catch (Exception ee) { logger.warn("Error while getting customer wing settings for the customer: " + custId, ee); } finally { try { rs.close(); } catch (Exception e) { } } return sbuf.toString(); } public static String getCustomerWingSettings(String customerName, String instanceName) throws PariException { Customer customer = CustomerManager.getInstance().getCustomer(customerName); if (customer == null) { throw (new PariException(-1, "No customer with the customerName" + customerName)); } int customerId = customer.getId(); return getCustomerWingSettings(customerId, instanceName); } // public static void insertCustomerWingSettings(String customerName, String // wingSettings) throws PariException { // Customer customer = // CustomerManager.getInstance().getCustomer(customerName); // if (customer == null) // throw (new PariException(-1, // "No customer with the customerName"+customerName)); // // int customerId = customer.getId(); // // insertCustomerWingSettings(customerId, null, wingSettings); // // } public static void insertCustomerWingSettings(int customerId, String instanceName, String wingSettings) throws PariException { int MAX_WING_SETTINGS_SIZE = 4000; Customer customer = null; if (ServerProperties.getInstance().getProductProfile().getProductShortName() .equals(Constants.PARITRA_PRODUCT)) { customer = CustomerManager.getInstance().getCustomerById(customerId); if (customer == null) { throw (new PariException(-1, "Unable to save Customer Wing Settings. \nSelected Customer may have been deleted.")); } } PreparedStatement ps = null; Connection c = null; try { c = DBHelper.getConnection(); c.setAutoCommit(false); String query = null; if (instanceName == null) { query = "delete from customer_wing_settings where customer_id=" + customerId; } else { query = "delete from customer_wing_settings where customer_id=" + customerId + " and instance_name='" + instanceName + "'"; } DBHelper.executeUpdateNoCommit(c, query); int size = wingSettings.length(); int noOfDBRows = size / MAX_WING_SETTINGS_SIZE; if ((noOfDBRows * MAX_WING_SETTINGS_SIZE) < size) { noOfDBRows += 1; } ps = c.prepareStatement(DBHelperConstants.INSERT_CUSTOMER_WING_SETTINGS); for (int i = 0; i < noOfDBRows; i++) { int startIndex = i * MAX_WING_SETTINGS_SIZE; int endIndex = (i + 1) * MAX_WING_SETTINGS_SIZE; if (endIndex > size) { endIndex = size; } String conf = wingSettings.substring(startIndex, endIndex); ps.setInt(1, customerId); ps.setString(2, instanceName); ps.setInt(3, i); ps.setString(4, conf); ps.executeUpdate(); } c.commit(); } catch (Exception ex) { ex.printStackTrace(); try { c.rollback(); } catch (Exception sqlEx) { } logger.warn("Error while trying to save wing settings for customer", ex); throw (new PariException(-1, "Error while saving Customer Wing Settings." + (customer == null ? "" : " for the customer " + customer.getCustomerName()))); } finally { if (ps != null) { try { ps.close(); } catch (Exception psex) { } } try { if (c != null) { c.setAutoCommit(true); } } catch (Exception ee) { } DBHelper.releaseConnection(c); } updateWingSettingsDetailsModifiedTime(customerId, instanceName); } private static void updateWingSettingsDetailsModifiedTime(int customerId, String wingInstance) { Connection c = null; ResultSet rs = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); rs = DBHelper.executeQuery("select * from wing_settings_update_details where customer_id = " + customerId + " and instance_name='" + wingInstance + "'"); if (rs.next()) { ps = c.prepareStatement(DBHelperConstants.UPDATE_WING_SETTINGS_UPDATE_DETAILS_MODIFIED); ps.setTimestamp(1, (new Timestamp(System.currentTimeMillis()))); ps.setInt(2, customerId); ps.setString(3, wingInstance); ps.executeUpdate(); } else { ps = c.prepareStatement(DBHelperConstants.INSERT_WING_SETTINGS_UPDATE_DETAILS_MODIFIED); ps.setInt(1, customerId); ps.setString(2, wingInstance); ps.setTimestamp(3, (new Timestamp(System.currentTimeMillis()))); ps.executeUpdate(); } } catch (Exception ee) { logger.warn("Error while trying to save wing_settings_update_details for customer", ee); } finally { try { rs.close(); } catch (Exception ee) { } try { ps.close(); } catch (Exception e) { } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception ee) { } } } public static void updateWingSettingsDetails(int customerId, String instanceName, long updateTime) { Connection c = null; PreparedStatement ps = null; ResultSet rs = null; try { c = DBHelper.getConnection(); rs = DBHelper.executeQuery("select * from wing_settings_update_details where customer_id = " + customerId + " and instance_name='" + instanceName + "'"); if (rs.next()) { ps = c.prepareStatement(DBHelperConstants.UPDATE_WING_SETTINGS_UPDATE_DETAILS_UPLOAD); ps.setTimestamp(1, (new Timestamp(System.currentTimeMillis()))); ps.setInt(2, customerId); ps.setString(3, instanceName); ps.executeUpdate(); } else { ps = c.prepareStatement(DBHelperConstants.INSERT_WING_SETTINGS_UPDATE_DETAILS_UPLOAD); ps.setInt(1, customerId); ps.setString(2, instanceName); ps.setTimestamp(3, (new Timestamp(System.currentTimeMillis()))); ps.executeUpdate(); } } catch (Exception ee) { logger.warn("Error while updating wing setting details for the customer: " + customerId, ee); } finally { try { rs.close(); } catch (Exception ee) { } try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static ArrayList<String> getIgnorableConfigStrings() { ArrayList<String> ignoreStrings = new ArrayList<String>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from config_ignore_strings"); while (rs.next()) { ignoreStrings.add(rs.getString("config_string")); } } catch (Exception ee) { logger.warn("Error while obtaining ignorable config strings", ee); } finally { try { rs.close(); } catch (Exception ex) { } } return ignoreStrings; } public static void setIgnorableConfigStrings(ArrayList<String> ignoreStrings) { NewVersionControlManager.setIgnoreConfigChanges(ignoreStrings); PreparedStatement ps = null; Connection c = null; try { DBHelper.executeUpdate("delete from config_ignore_strings"); if (ignoreStrings == null || ignoreStrings.size() == 0) { return; } c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_CONFIG_IGNORE_STRINGS); for (String conf : ignoreStrings) { ps.setString(1, conf); ps.executeUpdate(); } } catch (Exception ee) { logger.warn("Error while inserting ignorable config strings", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static ArrayList<String> getIgnorableApplyConfigStrings() { ArrayList<String> ignoreStrings = new ArrayList<String>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from apply_config_ignore_strings"); while (rs.next()) { ignoreStrings.add(rs.getString("config_string")); } } catch (Exception ee) { logger.warn("Error while obtaining ignorable apply config strings", ee); } finally { try { rs.close(); } catch (Exception ex) { } } return ignoreStrings; } public static void setIgnorableApplyConfigStrings(ArrayList<String> ignoreStrings) { ConfigIgnoreLines.getInstance().setIgnorableApplyConfigStrings(ignoreStrings); PreparedStatement ps = null; Connection c = null; try { DBHelper.executeUpdate("delete from apply_config_ignore_strings"); if (ignoreStrings == null || ignoreStrings.size() == 0) { return; } c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_APPLY_CONFIG_IGNORE_STRINGS); for (String conf : ignoreStrings) { ps.setString(1, conf); ps.executeUpdate(); } } catch (Exception ee) { logger.warn("Error while inserting ignorable apply config strings", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static String loadStartupConfig(int nodeId) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select config from node_current_config where id=" + nodeId + " AND conf_type=" + Constants.STARTUP_CONF); if (rs.next()) { byte b[] = null; if (ServerProperties.getInstance().isPostgres()) { b = rs.getBytes(1); } else { Blob blob = rs.getBlob(1); if (blob != null) { b = blob.getBytes(1, (int) blob.length()); } } if (b != null) { return CompressionUtils.getUncompressedString(b); } } } catch (Exception ex) { logger.error("Exception while loading startup config for device: " + nodeId, ex); } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } } return ""; } public static void migrateConfigs() { try { logger.debug("Migrating configurations from old database (ios_run_conf) to new database table."); Set<Integer> nodeIds = getUniqNodesFromOldConfigTable(); if (nodeIds == null || nodeIds.isEmpty()) { logger.debug("No rows found in the old configuration table ios_run_conf."); return; } for (Integer nodeId : nodeIds) { try { String config = loadRunningConfigOld(nodeId); if (config != null) { try { insertConfig(nodeId, Constants.RUN_CONF, config); deleteConfigOld(nodeId, Constants.RUN_CONF); } catch (Exception ex) { logger.error("Exception while migrating old running config for device: " + nodeId, ex); } } config = loadStartupConfigOld(nodeId); if (config != null) { try { insertConfig(nodeId, Constants.STARTUP_CONF, config); deleteConfigOld(nodeId, Constants.STARTUP_CONF); } catch (Exception ex) { logger.error("Exception while migrating old startup config for device: " + nodeId, ex); } } } catch (Exception ex) { logger.error("Exception while migrating old configs for device: " + nodeId, ex); } } logger.debug("Done. Migrating configurations from old database to new database table for " + nodeIds.size() + " rows"); } catch (Exception ex) { logger.error("Exception while migrating old configurations from old db tables", ex); } } private static Set<Integer> getUniqNodesFromOldConfigTable() throws SQLException { Set<Integer> nodeIds = new HashSet<Integer>(); String sql = "select distinct id from ios_run_conf"; ResultSet rs = null; try { rs = DBHelper.executeQuery(sql); while (rs != null && rs.next()) { nodeIds.add(rs.getInt(1)); } return nodeIds; } finally { if (rs != null) { rs.close(); } } } public static boolean isSNMPMIBMatches(int nodeId, String oid, String mib, GroupRuleOperator operator) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement( "select mib from SNMP_MIBS where device_id=" + nodeId + " and OID like '%" + oid + "%'"); rs = ps.executeQuery(); return isSNMPValueMatches(rs, operator, mib); } catch (Exception ex) { logger.error("Exception while getting SNMP MIB for device: " + nodeId, ex); } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } if (ps != null) { try { ps.close(); } catch (Exception e) { } } DBHelper.releaseConnection(c); } return false; } public static boolean isSNMPMIBNameMatches(int nodeId, String title, String mib, GroupRuleOperator operator) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement( "select * from SNMP_MIBS where device_id=" + nodeId + " and title LIKE '%" + title + "%'"); rs = ps.executeQuery(); return isSNMPValueMatches(rs, operator, mib); } catch (Exception ex) { logger.error("Exception while getting SNMP MIB for device: " + nodeId, ex); } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } if (ps != null) { try { ps.close(); } catch (Exception e) { } } DBHelper.releaseConnection(c); } return false; } public static boolean isSNMPValueMatches(ResultSet rs, GroupRuleOperator operator, String mib) throws Exception { while (rs.next()) { String mibXml = rs.getString("mib"); if (mibXml != null) { List<Element> rows = XMLUtil.getElements(mibXml, "RowList.Row"); for (Element rowElement : rows) { String value = XMLUtil.getElementText(rowElement, "Row.Column"); if (value == null || value.trim().isEmpty()) { continue; } if (operator.equals(GroupRuleOperator.CONTAINS)) { if (value.contains(mib)) { return true; } } else if (operator.equals(GroupRuleOperator.MATCHES_EXPRESSION)) { Pattern pattern = Pattern.compile(mib); Matcher matcher = pattern.matcher(value); if (matcher.find()) { return true; } } } } } return false; } public static boolean isCommandMatches(int devId, String command, String value, GroupRuleOperator operator) { String decodedOutput = getShowCli(devId, command); if (decodedOutput == null || decodedOutput.trim().isEmpty()) { return false; } if (operator.equals(GroupRuleOperator.CONTAINS)) { return decodedOutput.contains(value); } if (operator.equals(GroupRuleOperator.MATCHES_EXPRESSION)) { return RegExpUtils.isMatching(decodedOutput, value); } return false; } public static String loadRunningConfig(int nodeId) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement( "select config from node_current_config where id= ? AND conf_type=" + Constants.RUN_CONF); // rs = // DBHelper.executeQuery("select config from node_current_config where id=" // + nodeId + // " AND conf_type=" // + Constants.STARTUP_CONF); ps.setInt(1, nodeId); rs = ps.executeQuery(); if (rs.next()) { byte b[] = null; if (ServerProperties.getInstance().isPostgres()) { b = rs.getBytes(1); } else { Blob blob = rs.getBlob(1); if (blob != null) { b = blob.getBytes(1, (int) blob.length()); } } if (b != null) { return CompressionUtils.getUncompressedString(b); } } } catch (Exception ex) { logger.error("Exception while loading running config for device: " + nodeId, ex); } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } if (ps != null) { try { ps.close(); } catch (Exception e) { } } DBHelper.releaseConnection(c); } return ""; } public static String loadRunningConfigOld(int nodeId) { ResultSet rs = null; StringBuffer sbuf = new StringBuffer(); try { rs = DBHelper.executeQuery("select * from ios_run_conf where id=" + nodeId + " AND conf_type=" + Constants.RUN_CONF + " order by CONF_PART"); while (rs.next()) { sbuf.append(rs.getString("run_conf")); } } catch (Exception ee) { logger.warn("Error while loading running config", ee); } finally { // logger.info("1Time taken to load Running conf: " + // node.getNodeName() + ", " + (endTime-startTime)); try { rs.close(); } catch (Exception ee) { } } return sbuf.toString(); } public static String loadStartupConfigOld(int nodeId) { ResultSet rs = null; StringBuffer sbuf = new StringBuffer(); try { rs = DBHelper.executeQuery("select * from ios_run_conf where id=" + nodeId + " AND conf_type=" + Constants.STARTUP_CONF + " order by CONF_PART"); while (rs.next()) { sbuf.append(rs.getString("run_conf")); } } catch (Exception ee) { logger.warn("Error while loading startup config", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return sbuf.toString(); } public static String getShowVersion(int nodeId) { ResultSet rs = null; StringBuffer sbuf = new StringBuffer(); try { rs = DBHelper .executeQuery("select * from version_dump where id=" + nodeId + " order by VERSION_PART"); while (rs.next()) { sbuf.append(rs.getString("version")); } } catch (Exception ee) { logger.warn("Error while getting show version.", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return sbuf.toString(); } public static void deleteFromOldShowCommandsTable() { logger.info("Deleting show commands from old table"); try { DBHelper.executeUpdate("delete from show_commands"); logger.info("Done. Deleting show commands from old table"); } catch (Exception ex) { logger.error("Exception while deleting from table show_commands", ex); } } private static void deleteFromOldShowCommandsTable(Set<String> toBeRemoved) throws SQLException { Connection c = null; PreparedStatement ps = null; logger.debug("Deleting " + toBeRemoved.size() + " rows from old show_commands file"); try { c = DBHelper.getConnection(); ps = c.prepareStatement("delete from show_commands_new where device_id=? and cli=?"); for (String key : toBeRemoved) { int nodeId = -1; String cli = ""; try { String[] keys = key.split(":", 2); nodeId = Integer.parseInt(keys[0]); cli = keys[1]; ps.setInt(1, nodeId); ps.setString(2, cli); ps.executeUpdate(); } catch (Exception ex) { logger.error("Exception while deleteing " + nodeId + " CLI: " + cli, ex); } } } finally { try { if (ps != null) { try { ps.close(); } catch (Exception ex) { } } } finally { if (c != null) { DBHelper.releaseConnection(c); } } } } public static void migrageShowCommands() throws Exception { try { Set<String> toBeRemoved = new HashSet<String>(); boolean deleteAll = migrateShowCommandsInternal(toBeRemoved); if (deleteAll) { deleteFromOldShowCommandsTable(); } else { deleteFromOldShowCommandsTable(toBeRemoved); } } catch (Exception ex) { logger.error("Exception while migrating CLIs from old show_commands table to new tbale.", ex); } } public static boolean migrateShowCommandsInternal(Set<String> toBeRemoved) throws Exception { Connection c = null; ResultSet rs = null; PreparedStatement ps = null; int curNodeId = -1; String curCli = null; StringBuilder curOp = new StringBuilder(); // logger.info("Looking to see if we need to migrate from old show command tables to new one."); boolean deleteAll = true; try { c = DBHelper.getConnection(); ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)"); rs = DBHelper.executeQuery("select * from show_commands order by device_id,cli,output_part"); int count = 0; while (rs != null && rs.next()) { int newDev = rs.getInt("device_id"); String newCli = rs.getString("cli"); if (newDev != curNodeId || !newCli.equals(curCli)) { try { ps.setInt(1, curNodeId); ps.setString(2, curCli); ps.setObject(3, curOp.toString().getBytes()); curOp = new StringBuilder(); ps.executeUpdate(); toBeRemoved.add(curNodeId + ":" + curCli); } catch (Exception ex) { deleteAll = false; logger.trace("Exception while inserting into new table", ex); } } curOp.append(rs.getString("out_put")); curNodeId = newDev; curCli = newCli; count++; } logger.info("Done migrating from old show_commands table to new table. Migrated " + count + " rows"); } catch (Exception ee) { deleteAll = false; throw ee; } finally { try { if (rs != null) { rs.close(); } } catch (Exception e) { } finally { try { if (ps != null) { ps.close(); } } catch (Exception ex) { } finally { try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception ex) { } } } } return deleteAll; } public static String[] getAvailableShowCommands(int devId) { ArrayList<String> showCommands = new ArrayList<String>(); ResultSet rs = null; try { String whereClause = ""; if (devId != -1) { whereClause = " where device_id=" + devId; } rs = DBHelper.executeQuery("select distinct cli from show_commands_new" + whereClause); while (rs.next()) { showCommands.add(rs.getString("cli")); } } catch (Exception ee) { logger.error("Exception while getting available CLIs for device: " + devId, ee); } finally { try { rs.close(); } catch (Exception e) { } } String[] cmds = new String[showCommands.size()]; showCommands.toArray(cmds); return cmds; } public static String getShowCli(int nodeId, String cli) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; String sql = "select out_put from show_commands_new where device_id=? and cli=?"; try { c = DBHelper.getConnection(); ps = c.prepareStatement(sql); ps.setInt(1, nodeId); ps.setString(2, cli); rs = ps.executeQuery(); byte b[] = null; if (rs != null && rs.next()) { if (ServerProperties.getInstance().isPostgres()) { b = rs.getBytes(1); } else { Blob blob = rs.getBlob(1); if (blob != null) { b = blob.getBytes(1, (int) blob.length()); } } if (b != null) { return CompressionUtils.getUncompressedString(b); } } } catch (Exception ee) { logger.warn("Error while getting show cli for the node = " + nodeId + " and cli = " + cli, ee); } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } if (ps != null) { try { ps.close(); } catch (Exception ee) { } } if (c != null) { try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } } return ""; } public static String getShowSyslog(int nodeId, Map variables) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; long startTime = 0, endTime = 0; int timeGap = 0; if (variables.containsKey("syslogTimeInterval")) { timeGap = (int) variables.get("syslogTimeInterval"); if (timeGap != -1) { endTime = System.currentTimeMillis(); startTime = System.currentTimeMillis() - TimeUnit.MINUTES.toMillis(timeGap); } } else { // if syslogTimeInterval is not specified, we are expecting the input names should be endTime and startTime. // More enhancements can be done to support different names endTime = (int) variables.get("endTime"); startTime = (int) variables.get("startTime"); } String sql = "select COMPARE_STR|| ': ' || MESSAGE as message_desc from syslog_messages where device_id=?"; if (timeGap != -1) { sql += " and RECEIVED_AT " + "BETWEEN TO_TIMESTAMP(?) AND to_TIMESTAMP(?) ORDER BY RECEIVED_AT"; } try { c = DBHelper.getConnection(); ps = c.prepareStatement(sql); ps.setInt(1, nodeId); if (timeGap != -1) { ps.setTimestamp(2, new Timestamp(startTime)); ps.setTimestamp(3, new Timestamp(endTime)); } rs = ps.executeQuery(); byte b[] = null; StringBuffer ab = new StringBuffer(); while (rs != null && rs.next()) { ab.append(rs.getString("message_desc")); ab.append("\n"); } return ab.toString(); } catch (Exception ee) { logger.warn("Error while getting show cli for the node = " + nodeId, ee); } finally { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } if (ps != null) { try { ps.close(); } catch (Exception ee) { } } if (c != null) { try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } } return ""; } public static void saveClisOptimized(int nodeId, IDSPDeviceInfoProvider device, JobParameters jobParameters) { String[] clis = device.getAllClis(); if (clis == null || clis.length == 0) { return; } deleteClisOptimized(nodeId, clis); insertClisOptimized(nodeId, clis, device, jobParameters); } public static void saveNetconfResponse(int nodeId, IDSPDeviceInfoProvider device) { Set<String> colIds = device.getNetconfDatasetIds(); if (colIds == null || colIds.size() == 0) { return; } deleteNetconfCollection(nodeId, colIds); insertNetconfCollection(nodeId, colIds, device); } public static NetconfOutput getNetconfResponse(int nodeId, String datasetId) { NetconfOutput output = null; Connection con = null; try { con = DBHelper.getConnection(); String sql = "SELECT operation_name, request, response from netconf_collection where device_id = ? AND collection_id = ?"; try (PreparedStatement ps = con.prepareStatement(sql)) { ps.setInt(1, nodeId); ps.setString(2, datasetId); ResultSet rs = ps.executeQuery(); if (rs.next()) { byte[] compressedOut = rs.getBytes("response"); if (compressedOut != null) { output = new NetconfOutput(CompressionUtils.getUncompressedString(compressedOut)); } output.setOperation(rs.getString("operation_name")); output.setRequest(rs.getString("request")); } } } catch (Exception e) { logger.error("Error while retrieving netconf output from db", e); } finally { DBHelper.releaseConnection(con); } return output; } private static void insertClisOptimized(int nodeId, String[] clis, IDSPDeviceInfoProvider device, JobParameters jobParameters) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)"); for (String cli : clis) { DSPCLICommandOutput output = device.getCLI(cli); if (output != null) { String outputText = output.getOutput(); if (outputText == null) { outputText = output.getErrors(); } if (outputText == null) { continue; } if (outputText.startsWith(cli)) { outputText = outputText.substring(cli.length()); } ps.clearParameters(); ps.setInt(1, nodeId); ps.setString(2, cli); ps.setObject(3, CompressionUtils.getCompressedString(outputText)); ps.executeUpdate(); CommandResult result = new CommandResult(); result.setOperation(Operation.UPDATE); result.setOperatedBy(jobParameters.getUserName()); result.setJobId(jobParameters.getParentJobId()); result.setInventoryJobId(jobParameters.getJobId()); result.setCli(cli); result.setDeviceId((long) nodeId); result.setOutput(CompressionUtils.getCompressedString(outputText)); HistoryManager.getInstance().createRecord(result); } } } catch (Exception ex) { logger.error("Exception while saving CLIs for node: " + nodeId, ex); } finally { if (ps != null) { try { ps.close(); } catch (Throwable t) { } } if (c != null) { DBHelper.releaseConnection(c); } } } private static void insertNetconfCollection(int nodeId, Set<String> colIds, IDSPDeviceInfoProvider device) { Connection c = null; try { c = DBHelper.getConnection(); String stmt = "INSERT INTO netconf_collection (device_id, collection_id, operation_name, request, response) VALUES (?, ?, ?, ?, ?)"; try (PreparedStatement ps = c.prepareStatement(stmt)) { for (String colId : colIds) { DSPNetconfCommandOutput output = device.getNetconfOutput(colId); if (output != null) { String operation = output.getOperation(); String request = output.getRequest(); String outputText = output.getOutput(); if (outputText == null) { outputText = output.getErrors(); } if (outputText == null) { continue; } if (outputText.startsWith(colId)) { outputText = outputText.substring(colId.length()); } ps.clearParameters(); ps.setInt(1, nodeId); ps.setString(2, colId); ps.setString(3, operation); ps.setString(4, request); ps.setObject(5, CompressionUtils.getCompressedString(outputText)); ps.addBatch(); } } ps.executeBatch(); } } catch (Exception ex) { logger.error("Exception while saving netconf collection for node: " + nodeId, ex); } finally { if (c != null) { DBHelper.releaseConnection(c); } } } private static void deleteClisOptimized(int nodeId, String[] clis) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); StringBuilder str = new StringBuilder("delete from show_commands_new where device_id=? and cli in ("); boolean first = true; for (int i = 0; i < clis.length; i++) { if (!first) { str.append(","); } str.append("?"); first = false; } str.append(")"); ps = c.prepareStatement(str.toString()); ps.setInt(1, nodeId); for (int i = 0; i < clis.length; i++) { ps.setString(i + 2, clis[i]); } ps.executeUpdate(); } catch (Exception ex) { logger.error("Exception while saving show commands for node: " + nodeId, ex); } finally { try { if (ps != null) { ps.close(); } } catch (Throwable th) { } if (c != null) { DBHelper.releaseConnection(c); } } } private static void deleteNetconfCollection(int nodeId, Set<String> colIds) { Connection c = null; try { c = DBHelper.getConnection(); StringBuilder str = new StringBuilder( "DELETE FROM netconf_collection where device_id=? and collection_id in ("); boolean first = true; for (String colId : colIds) { if (!first) { str.append(","); } str.append("?"); first = false; } str.append(")"); try (PreparedStatement ps = c.prepareStatement(str.toString())) { ps.setInt(1, nodeId); int i = 1; for (String colId : colIds) { ps.setString(i + 1, colId); i++; } ps.executeUpdate(); } } catch (Exception ex) { logger.error("Exception while deleting netconf collection for node: " + nodeId, ex); } finally { if (c != null) { DBHelper.releaseConnection(c); } } } public static void saveShowCommand(int nodeId, String cli, String output) { try { String query = "delete from show_commands_new where device_id=" + nodeId + " and cli='" + cli + "'"; DBHelper.executeUpdate(query); } catch (Exception ex) { logger.error("Exception while deleting show command : " + nodeId + " and cli='" + cli + "'", ex); } PreparedStatement ps = null; Connection c = null; if (output == null || output.trim().isEmpty()) { return; } output = output.trim(); try { c = DBHelper.getConnection(); ps = c.prepareStatement("INSERT INTO show_commands_new (device_id, cli, out_put) VALUES (?,?,?)"); if (output.startsWith(cli)) { output = output.substring(cli.length()); } ps.setInt(1, nodeId); ps.setString(2, cli); ps.setObject(3, CompressionUtils.getCompressedString(output)); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while saving show cli for the node = " + nodeId + " and cli = " + cli, ee); } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static String getStartupVersion(int deviceId, String runConfVersion) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from cvs_runconf_startconf where device_id=" + deviceId + " AND runconf_version=" + runConfVersion); if (rs.next()) { return rs.getString("startconf_version"); } } catch (Exception ee) { logger.warn("Error while getting show startup version the node = " + deviceId, ee); } finally { try { rs.close(); } catch (Exception e) { } } return null; } public static void insertConfRelations(NetworkNode node) { ResultSet rs = null; try { // StringBuffer runConfFile = new // StringBuffer("devices").append(File.separator). // append(node.getDeviceFamily().toLowerCase()).append(File.separator).append(node.getNodeId()). // append(File.separator).append(Constants.CVS_FILE_TYPE_RUNCONF); // // StringBuffer startConfFile = new // StringBuffer("devices").append(File.separator). // append(node.getDeviceFamily().toLowerCase()).append(File.separator).append(node.getNodeId()). // append(File.separator).append(Constants.CVS_FILE_TYPE_STARTCONF); int latestRunConfVersion = NewVersionControlManager.getInstance().getLatestVersion(node.getNodeId(), ConfFileType.RUNNING_CONFIG.confType); int latestStartConfVersion = NewVersionControlManager.getInstance().getLatestVersion(node.getNodeId(), ConfFileType.STARTUP_CONFIG.confType); // String runLatest = // VersionControlManager.getManager().getLatestVersion(runConfFile.toString()); // String startLatest = // VersionControlManager.getManager().getLatestVersion(startConfFile.toString()); rs = DBHelper.executeQuery("select * from cvs_runconf_startconf where device_id=" + node.getNodeId() + " AND runconf_version=" + latestRunConfVersion); if (rs.next()) { DBHelper.executeUpdate("update cvs_runconf_startconf set startconf_version=" + latestStartConfVersion + " where device_id=" + node.getNodeId() + " AND runconf_version=" + latestRunConfVersion); } else { DBHelper.executeUpdate("insert into cvs_runconf_startconf values(" + node.getNodeId() + "," + latestRunConfVersion + "," + latestStartConfVersion + ")"); } } catch (Exception ee) { logger.warn("Error in insertConfRelations", ee); } finally { try { rs.close(); } catch (Exception ee) { } } } public static int getDeviceCount() { ResultSet rs = null; try { rs = DBHelper.executeQuery("select count(*) from nodes where state='m'"); if (rs.next()) { return rs.getInt(1); } } catch (Exception ee) { logger.warn("Error while getting device count", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return -1; } public static int getCummilativeDeviceCount() { ResultSet rs = null; try { rs = DBHelper.executeQuery("select max(id) from nodes"); if (rs.next()) { return rs.getInt(1); } } catch (Exception ee) { logger.warn("Error while getting cummilative device count", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return -1; } public static void insertModuleInfo(int moduleId, int parentNodeId, String slotNumber, int moduleType) { if (slotNumber == null) { return; } try { String delQuery = "delete from nodes_modules where mod_id=" + moduleId + " AND id=" + parentNodeId + " AND slot_number='" + slotNumber + "'" + " AND module_type=" + moduleType; System.err.println(delQuery); DBHelper.executeUpdate(delQuery); DBHelper.executeUpdate("insert into nodes_modules VALUES(" + moduleId + "," + parentNodeId + ",'" + slotNumber + "'," + moduleType + ")"); } catch (Exception ee) { logger.warn("Error while inserting module info for the node=" + parentNodeId + " moduleId= " + moduleId + " slot=" + slotNumber + " moduleType=" + moduleType, ee); } } public static void deleteModuleInfo(int moduleId, int parentNodeId, String slotNumber, int moduleType) { if (slotNumber == null) { return; } try { String delQuery = "delete from nodes_modules where mod_id=" + moduleId + " AND id=" + parentNodeId + " AND slot_number='" + slotNumber + "'" + " AND module_type=" + moduleType; System.err.println(delQuery); DBHelper.executeUpdate(delQuery); } catch (Exception ee) { logger.warn("Error while inserting module info for the node=" + parentNodeId + " moduleId= " + moduleId + " slot=" + slotNumber + " moduleType=" + moduleType, ee); } } public static void loadModules(NetworkNode node) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from nodes_modules where mod_id=" + node.getNodeId()); if (rs != null && rs.next()) { int parentId = rs.getInt("id"); String slotNumber = rs.getString("slot_number"); int moduleType = rs.getInt("module_type"); ModuleInfo mInfo = new ModuleInfo(); mInfo.setParentId(parentId); mInfo.setSlotNumber(slotNumber); mInfo.setType(ModuleType.getFromInt(moduleType)); node.setSupportedModuleInfo(mInfo); } } catch (Exception ee) { logger.warn("Error while loading mdoule info", ee); } finally { try { rs.close(); } catch (Exception ee) { } } } public static void deleteCustomerDiscoveredDevices(int custId, int instanceId) { try { DBHelper.executeUpdate("delete from discovered_devices where customer_id=" + custId + " and instance_id=" + instanceId); } catch (Exception ex) { logger.warn("Failed to delete discovered device list of wing instance ->" + custId + ":" + instanceId, ex); } } public static void insertDiscoveredDevice(DiscoveredDevice discDevice) { Connection c = null; PreparedStatement ps = null; try { DBHelper.executeUpdate("delete from discovered_devices where ipaddress='" + discDevice.getIpAddress() + "' and customer_id=" + discDevice.getCustomer_id() + " and instance_id=" + discDevice.getInstanceId()); c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_DISCOVERED_DEVICE); ps.setInt(1, discDevice.getCustomer_id()); ps.setInt(2, discDevice.getInstanceId()); ps.setString(3, discDevice.getIpAddress()); ps.setString(4, discDevice.getMacAddress()); ps.setString(5, discDevice.getNodeName()); ps.setString(6, discDevice.getDescription()); ps.setString(7, discDevice.getDevice_family()); ps.setString(8, discDevice.getProduct_family()); ps.setString(9, discDevice.getProduct_model()); ps.setString(10, discDevice.getOSName()); ps.setString(11, discDevice.getOSVersion()); ps.setString(12, discDevice.getVendor_name()); if (discDevice.isManaged()) { ps.setString(13, "Yes"); } else { // CSCtr61612 - Setting of 'isManaged' is based on if the // network // node is present in the same customer and instance VirtualDevice vd = new VirtualDevice(); vd.setIpAddress(discDevice.getIpAddress()); int[] nodes = NetworkNodeCache.getInstance().getMatchingNodeIds(vd, discDevice.getCustomer_id()); if (nodes != null && nodes.length > 0) { ps.setString(13, "Yes"); } else { ps.setString(13, "No"); } } ps.setString(14, discDevice.getDiscovered_from()); ps.setString(15, discDevice.getDiscovery_method()); ps.setString(16, discDevice.getDiscovery_credential()); ps.setString(17, null); ps.setTimestamp(18, discDevice.getDiscovery_time() == null ? (new Timestamp(System.currentTimeMillis())) : (new Timestamp(Long.parseLong(discDevice.getDiscovery_time())))); ps.executeUpdate(); // customer_id, ipaddress, macaddress, hostname, device_family, // product_family, product_model, vendor_name, // discovered_from, discovery_method, discovery_credential, // current_state, discovery_time } catch (Exception ee) { ee.printStackTrace(); logger.warn("Error while inserting into discovered devices", ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } private static boolean isModulesPopulated() { ResultSet rs = null; try { rs = DBHelper.executeQuery("select count(*) from module_eol_eox"); if (rs != null && rs.next()) { int count = rs.getInt(1); if (count > 0) { return true; } else { return false; } } } catch (Exception ee) { logger.warn("Error while checking to see iof modules are populated", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return false; } public static void insertModuleEolInfo(String modFilePath) { if (InventoryDBHelper.isModulesPopulated()) { return; } Connection c = null; PreparedStatement ps = null; InputStream in = null; try { if (modFilePath != null && (modFilePath.startsWith("/") || modFilePath.startsWith("\\"))) { modFilePath = modFilePath.substring(1); } in = Thread.currentThread().getContextClassLoader().getResourceAsStream(modFilePath); c = DBHelper.getConnection(); c.setAutoCommit(false); ps = c.prepareStatement(DBHelperConstants.INSERT_MODULE_EOL_DB); StringBuffer sbuf = new StringBuffer(); byte[] buf = new byte[8096]; int read = -1; while ((read = in.read(buf)) != -1) { sbuf.append(new String(buf, 0, read)); } StringTokenizer st = new StringTokenizer(sbuf.toString(), "\n"); if (st.hasMoreElements()) { st.nextElement(); // takeout the header } while (st.hasMoreElements()) { String line = st.nextToken().trim(); if (line.length() == 0) { continue; } StringTokenizer stt = new StringTokenizer(line, "\t"); String fru = null; String deviceType = null; String url = null; Timestamp aDate = null; Timestamp eosDate = null; Timestamp eolDate = null; Timestamp eocrDate = null; Timestamp eoeDate = null; if (stt.hasMoreTokens()) { fru = stt.nextToken(); } if (stt.hasMoreTokens()) { deviceType = stt.nextToken(); } if (stt.hasMoreTokens()) { url = stt.nextToken(); } if (stt.hasMoreTokens()) { aDate = getTimestamp(stt.nextToken()); } if (stt.hasMoreTokens()) { eosDate = getTimestamp(stt.nextToken()); } if (stt.hasMoreTokens()) { eolDate = getTimestamp(stt.nextToken()); } if (stt.hasMoreTokens()) { eoeDate = getTimestamp(stt.nextToken()); } if (stt.hasMoreTokens()) { eocrDate = getTimestamp(stt.nextToken()); } ps.setString(1, fru); ps.setString(2, url); ps.setString(3, deviceType); ps.setTimestamp(4, aDate); ps.setTimestamp(5, eosDate); ps.setTimestamp(6, eolDate); ps.setTimestamp(7, eocrDate); ps.setTimestamp(8, eoeDate); ps.executeUpdate(); } } catch (Exception ee) { logger.warn("Error in insertModuleEolInfo", ee); } finally { try { c.commit(); } catch (Exception ee) { } try { c.setAutoCommit(true); } catch (Exception ee) { } try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } try { if (in != null) { in.close(); } } catch (IOException ex) { logger.error("Exception in closing InputStream in InventoryDBHelper", ex); } } } private static Timestamp getTimestamp(String str) { if (str == null || str.length() == 0) { return null; } long t = getMyDate(str); if (t == -1) { return null; } else { return (new Timestamp(t)); } } public static long getMyDate(String str) { try { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date d = sdf.parse(str); return d.getTime(); } catch (Exception ee) { logger.info("Error while parsing date for the string=" + str, ee); return -1; } } public static void insertShowDiagCommand(int devId, String moduleName, String moduleDescr, String pid, String vid, String serialNum) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; try { if (serialNum != null) { rs = DBHelper.executeQuery("select * from DEVICE_MODULE_INFO where device_id=" + devId + " and SERIALNUM='" + serialNum + "'"); } boolean flag = false; if (rs != null && rs.next()) { flag = true; } try { rs.close(); } catch (Exception ex) { } if (flag) { String PID = rs.getString("PID"); if (PID != null && PID.trim().length() > 0) { return; } else if (pid != null && pid.length() > 0) { String mName = rs.getString("module_name"); String mDescr = rs.getString("module_descr"); if (mName != null && mName.length() > 0) { moduleName = mName; } if (mDescr != null && mDescr.length() > 0) { moduleDescr = mDescr; } DBHelper.executeUpdate("delete from DEVICE_MODULE_INFO where device_id=" + devId + " and SERIALNUM='" + serialNum + "'"); } else { return; } } c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_SHOW_DIAG_MODULE); ps.setInt(1, devId); ps.setString(2, moduleName); ps.setString(3, moduleDescr); ps.setString(4, pid); ps.setString(5, vid); ps.setString(6, serialNum); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while inserting show diag command for the device=" + devId, ee); } finally { try { rs.close(); } catch (Exception e) { } try { if (ps != null) { ps.close(); } } catch (Exception e) { } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { } } } public static void insertModuleData(int devId, DeviceModule module) { ResultSet rs = null; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_SHOW_MODULE); ps.setInt(1, devId); ps.setString(2, module.getName()); ps.setString(3, module.getDescr()); ps.setString(4, module.getPID()); ps.setString(5, module.getSerialNumber()); ps.setString(6, module.getModuleType().name()); ps.setInt(7, module.getParentIndex()); ps.setInt(8, module.getMyIndex()); ps.setInt(9, module.getRelPos()); ps.setString(10, module.getSlot()); ps.setString(11, module.getVendorType()); ps.setString(12, module.getHwVersion()); ps.setInt(13, module.isFruable() ? 1 : 0); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while inserting show diag command for the device=" + devId, ee); } finally { try { rs.close(); } catch (Exception e) { } try { if (ps != null) { ps.close(); } } catch (Exception e) { } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { } } } // public static void insertCatOsChassisAsModule(NetworkNode newNode) throws // Exception { public static void insertCatOsModules(Connection c, int nodeId, String model, String moduleDescr, String pid, String serialNumber) throws Exception { PreparedStatement ps = null; try { try { ps = c.prepareStatement(DBHelperConstants.INSERT_SHOW_DIAG_MODULE); ps.setInt(1, nodeId); ps.setString(2, model); ps.setString(3, moduleDescr); ps.setString(4, pid); ps.setString(5, ""); ps.setString(6, serialNumber); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while insertring catOS modules", ee); } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { } } } catch (Exception ee) { } } public static void insertModuleInfoSnmp(int nodeId, String descr, String name, String mclass, String serialnum, String modelname) { // TODO Auto-generated method stub } public static void deleteVoipPhones(int customerId, int instanceId) { try { DBHelper.executeUpdate( "delete from VOIP_PHONES where customer_id=" + customerId + " and instance_id=" + instanceId); } catch (Exception ex) { logger.warn("Failed to delete the VoIP phones associated with the customer " + customerId + ":" + instanceId); } } /** * @param profile * , creator Id */ public static int insertCollectionProfile(CollectionProfile profile, int creatorId) throws Exception { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_COLLECTION_PROFILE); ps.setString(1, profile.getProfileName()); ps.setString(2, profile.getProfileDescr()); ps.setString(3, profile.getDatasetsString()); ps.setString(4, profile.isAllDevicesSelected() ? "y" : "n"); ps.setString(5, profile.getGroupsStr()); ps.setInt(6, -1); ps.setTimestamp(7, (new Timestamp(profile.getCreatedAt()))); ps.setTimestamp(8, null); ps.setInt(9, creatorId); ps.setInt(10, -1); ps.setString(11, profile.isSystemProfile() ? "y" : "n"); ps.setString(12, profile.getCommandCLI()); ps.setInt(13, profile.isRunDiscovery() ? 1 : 0); ps.setInt(14, profile.isRunDAV() ? 1 : 0); ps.executeUpdate(); int profile_id = -1; ResultSet rs = null; try { rs = DBHelper.executeQuery( "select * from collection_profile where profile_name='" + profile.getProfileName() + "'"); if (rs.next()) { profile_id = rs.getInt("profile_id"); profile.setProfId(profile_id); } if (profile.getFtpDetails() != null) { ServerDBHelper.saveFtpServerDetails(profile.getFtpDetails(), profile.getProfileName()); } } catch (Exception ee) { logger.warn("Exception while inserting collection profile", ee); return -1; } finally { try { if (rs != null) { rs.close(); } } catch (Exception ex) { logger.warn("Exception while closing the resultset in inserting collection profile", ex); } try { if (ps != null) { ps.close(); } } catch (Exception ex) { logger.warn("Exception while closing the statement in inserting collection profile", ex); } } try { DBHelper.executeUpdate( "delete from collection_prof_devices where profile_id=" + profile.getProfId()); int size = profile.getNodesStr().length(); if (size > 0) { int count = size / 4000 + 1; String idsStr = profile.getNodesStr(); for (int i = 0; i < count; i++) { String ids = (idsStr.length() > 4000) ? idsStr.substring(0, 4000) : idsStr; if (ids.length() > 0) { DBHelper.executeUpdate("insert into collection_prof_devices values (" + profile.getProfId() + "," + i + ",'" + ids + "')"); idsStr = (idsStr.length() > 4000) ? idsStr.substring(4001) : ""; } } } } catch (Exception ee) { logger.warn("Exception while removing old device in inserting collection profile", ee); } if (profile.getScheduleDetails() != null) { System.err.println("SCHED:" + profile.getScheduleDetails().toString()); int jobId = JobMgr.getInstance().scheduleCollectionProfileJob(profile, creatorId); profile.setJobId(jobId); setCollectionProfileJobId(profile.getProfId(), jobId); } profile.setProfId(profile_id); return profile_id; } catch (Exception ee) { logger.warn("Error while inserting collection profile", ee); throw ee; } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { logger.warn("Exception while closing the statement in inserting collection profile", e); } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { logger.warn("Exception while closing the connection in inserting collection profile", e); } } } public static void modifyCollectionProfile(CollectionProfile profile, int creatorId, boolean updateSchedule) { Connection c = null; PreparedStatement ps = null; PreparedStatement ps1 = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.MODIFY_COLLECTION_PROFILE); ps.setString(1, profile.getProfileDescr()); ps.setString(2, profile.getDatasetsString()); ps.setString(3, profile.isAllDevicesSelected() ? "y" : "n"); ps.setString(4, profile.getGroupsStr()); ps.setTimestamp(5, (new Timestamp(profile.getModifiedAt()))); ps.setInt(6, creatorId); ps.setString(7, profile.getCommandCLI()); ps.setInt(8, profile.isRunDiscovery() ? 1 : 0); ps.setInt(9, profile.isRunDAV() ? 1 : 0); ps.setInt(10, profile.getProfId()); try { ps.executeUpdate(); if (profile.getFtpDetails() != null) { ServerDBHelper.saveFtpServerDetails(profile.getFtpDetails(), profile.getProfileName()); } } catch (Exception ex) { logger.warn("Unable to modify collection profile", ex); } try { DBHelper.executeUpdate( "delete from collection_prof_devices where profile_id=" + profile.getProfId()); int size = profile.getNodesStr().length(); if (size > 4000) { int count = size / 4000 + 1; String idsStr = profile.getNodesStr(); for (int i = 0; i < count; i++) { String ids = (idsStr.length() > 4000) ? idsStr.substring(0, 4000) : idsStr; if (ids.length() > 0) { DBHelper.executeUpdate("insert into collection_prof_devices VALUES (" + profile.getProfId() + "," + i + ",'" + ids + "')"); idsStr = (idsStr.length() > 4000) ? idsStr.substring(4001) : ""; } } } else if (size > 0) { DBHelper.executeUpdate("insert into collection_prof_devices VALUES (" + profile.getProfId() + "," + 0 + ",'" + profile.getNodesStr() + "')"); } } catch (Exception ee) { logger.warn("Unable to update device details in collection profile", ee); } if (updateSchedule) { if (profile.getJobId() == -1) { int jobId = JobMgr.getInstance().scheduleCollectionProfileJob(profile, creatorId); profile.setJobId(jobId); setCollectionProfileJobId(profile.getProfId(), jobId); } else { String[] jobAndGrp = ServerDBHelper.getJobAndGrpNames(profile.getJobId()); if (jobAndGrp != null && jobAndGrp.length == 2) { JobMgr.getInstance().editJob(jobAndGrp[0], jobAndGrp[1], profile.getProfileDescr(), profile.getScheduleDetails(), profile.getNotificationPrefs()); } } } } catch (Exception ee) { logger.warn("Error while inserting collection profile", ee); } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { logger.warn("Exception while closing the statement in inserting collection profile", e); } try { if (ps1 != null) { ps1.close(); } } catch (Exception e) { logger.warn("Exception while closing the statement in inserting collection profile", e); } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { logger.warn("Exception while closing the connection in inserting collection profile", e); } } } public static void setCollectionProfileJobId(int profileId, int jobId) { try { DBHelper.executeUpdate( "update collection_profile set jobid = " + jobId + " where profile_id=" + profileId); } catch (Exception ee) { logger.warn("Unable to set the Collection Profile Job Id", ee); } } public static void loadCollectionProfiles(ConcurrentHashMap<Integer, CollectionProfile> systemDefinedProfiles, ConcurrentHashMap<Integer, CollectionProfile> userDefinedProfiles) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from collection_profile"); while (rs.next()) { CollectionProfile prof = new CollectionProfile(); prof.setProfId(rs.getInt("profile_id")); prof.setProfileName(rs.getString("profile_name")); prof.setProfileDescr(rs.getString("profile_descr")); String datasetStr = rs.getString("datasets"); if (datasetStr != null) { prof.setDatasets(datasetStr); } prof.setJobId(rs.getInt("jobid")); if (prof.getJobId() != -1) { if (JobMgr.getInstance().isJobScheduled(prof.getJobId())) { PariJobDetails jDetails = JobMgr.getInstance().getJobDetail(prof.getJobId()); if (jDetails != null) { prof.setScheduleDetails(jDetails.getSchedDetails()); prof.setNotificationPrefs( (CollectionProfileNotificationPrefs) jDetails.getJobSpecificData()); } } } String allDevices = rs.getString("alldevices"); if (allDevices.equals("y")) { prof.setAllDevicesSelected(true); } else { prof.setAllDevicesSelected(false); } StringBuffer sbuf = new StringBuffer(); try { ResultSet rss = DBHelper.executeQuery("select * from collection_prof_devices where profile_id=" + prof.getProfId() + " order by sequence_id"); while (rss.next()) { sbuf.append(rss.getString("device_ids")); } try { rss.close(); } catch (Exception ee) { logger.warn("Exception while closing the resultset in inserting collection profile", ee); } } catch (Exception ee) { logger.warn("Error while loading device to collection profile", ee); } String nodeStr = sbuf.toString(); if (nodeStr != null && nodeStr.length() > 0) { String[] nodeIds = nodeStr.split(","); Integer[] nodes = new Integer[nodeIds.length]; for (int i = 0; i < nodeIds.length; i++) { try { nodes[i] = Integer.parseInt(nodeIds[i]); } catch (Exception ee) { } } prof.setNodes(nodes); } String groupStr = rs.getString("group_ids"); if (groupStr != null && groupStr.trim().length() > 0) { String[] groupIds = groupStr.split(","); Integer[] groups = new Integer[groupIds.length]; for (int i = 0; i < groupIds.length; i++) { try { groups[i] = Integer.parseInt(groupIds[i]); } catch (Exception ee) { } } prof.setGroups(groups); } Timestamp ts = rs.getTimestamp("created_at"); if (ts != null) { prof.setCreatedAt(ts.getTime()); } ts = rs.getTimestamp("modified_at"); if (ts != null) { prof.setModifiedAt(ts.getTime()); } prof.setCreatorId(rs.getInt("creator_id")); prof.setModifierId(rs.getInt("modifier_id")); String sysProfile = rs.getString("system_defined").trim(); if (sysProfile.equals("y")) { prof.setSystemProfile(true); } else { prof.setSystemProfile(false); } int runDiscovery = rs.getInt("run_discovery"); if (runDiscovery == 1) { prof.setRunDiscovery(true); } else { prof.setRunDiscovery(false); } int runDAV = rs.getInt("run_dav"); if (runDAV == 1) { prof.setRunDAV(true); } else { prof.setRunDAV(false); } String cliCommand = rs.getString("cli_command"); prof.setCommandCLI(cliCommand); if (prof.isSystemProfile()) { systemDefinedProfiles.put(prof.getProfId(), prof); } else { userDefinedProfiles.put(prof.getProfId(), prof); } FtpServerDetails ftpDetails = ServerDBHelper.getFtpServerPreferences(prof.getProfileName()); if (ftpDetails != null) { prof.setFtpDetails(ftpDetails); } } } catch (Exception ee) { logger.warn("Error while loading the collection profiles", ee); } finally { try { rs.close(); } catch (Exception ex) { logger.warn("Exception while closing the resultset in inserting collection profile", ex); } } } public static void deleteCollectionProfile(int profId) { try { DBHelper.executeUpdate("delete from collection_profile where profile_id=" + profId); } catch (Exception ee) { logger.error("Error while deleting collection profile", ee); } } public static boolean isCollectionProfileExists(String collectionProfile) { ResultSet rs = null; try { rs = DBHelper.executeQuery( "select * from collection_profile where profile_name='" + collectionProfile + "'"); if (rs.next()) { return true; } } catch (Exception ee) { logger.warn("Error while checking collection profile exists", ee); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ex) { logger.warn("Exception while closing the resultset in checking collection profile exists", ex); } } return false; } public static void deletePreferedCredentials() { try { DBHelper.executeUpdate("delete from device_prefer_credentials"); } catch (Exception ee) { ee.printStackTrace(); } CredentialSetFactory.getInstance().clearPreferedCredentials(); } public static void updateDevicePreferedCredentials(String ipaddress, int credSetId) { try { DBHelper.executeUpdate("delete from device_prefer_credentials where device_ip='" + ipaddress + "'"); } catch (Exception ee) { ee.printStackTrace(); } try { DBHelper.executeUpdate( "insert into device_prefer_credentials VALUES ('" + ipaddress + "'," + credSetId + ")"); } catch (Exception ee) { ee.printStackTrace(); } CredentialSetFactory.getInstance().loadPreferedCredentials(); } public static void insertDeviceSnmpEntMib(int devId, String sysOid, DeviceModule module) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.INSERT_SNMP_ENT_MIB); ps.setInt(1, devId); ps.setString(2, sysOid); ps.setString(3, module.getDescr()); ps.setInt(4, module.getModuleType().getclassNum()); ps.setString(5, module.getName()); ps.setString(6, module.getSerialNumber()); ps.setString(7, module.getPID()); ps.setString(8, module.getVendorType()); ps.setString(9, module.getHwVersion()); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception e) { } try { DBHelper.releaseConnection(c); } catch (Exception e) { } } } public static void insertStandByIps(int id, String[] ips) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); c.setAutoCommit(false); DBHelper.executeUpdateNoCommit(c, "delete from standby_to_id where id=" + id); ps = c.prepareStatement(DBHelperConstants.INSERT_INTO_STANDBY_IP); for (int i = 0; i < ips.length; i++) { ps.setString(1, ips[i]); ps.setInt(2, id); ps.executeUpdate(); } c.commit(); } catch (Exception ex) { ex.printStackTrace(); try { c.rollback(); } catch (Exception e) { } } finally { try { c.setAutoCommit(true); } catch (Exception ex) { } try { ps.close(); } catch (Exception ex) { } try { DBHelper.releaseConnection(c); } catch (Exception e) { } } } public static void insertDeviceSerialNumber(int id, String cli, String serialNumber) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from device_serial_numbers where id=" + id + " and serial_number='" + serialNumber + "'"); if (rs.next()) { return; } } catch (Exception ex) { ex.printStackTrace(); } finally { try { rs.close(); } catch (Exception ex) { } } if (cli == null || serialNumber == null) { logger.error("CLI and Serial numer should not be null, device id " + id + " CLI " + cli + " serial no :" + serialNumber); return; } Connection c = DBHelper.getConnection(); PreparedStatement ps = null; try { ps = c.prepareStatement("insert into device_serial_numbers values (?,?,?,?,?)"); ps.setInt(1, id); ps.setString(2, cli); ps.setInt(3, 1); ps.setString(4, "Chassis"); ps.setString(5, serialNumber); ps.executeUpdate(); // DBHelper.executeUpdate("insert into device_serial_numbers names (id, hardware_type, serial_number) values("+id+",1,'"+serialNumber+"')"); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception ex) { } DBHelper.releaseConnection(c); } } public static String getCommandForRunningConfig(String osType) { ResultSet rs = null; String command = null; try { rs = DBHelper.executeQuery("Select COMMAND from CONFIG_HEURISTICS where OS='" + osType + "'"); if (rs.next()) { command = rs.getString("COMMAND"); } } catch (Exception ee) { ee.printStackTrace(); } finally { try { rs.close(); } catch (Exception ex) { } } return command; } public static String getCommandForStartupConfig(String osType) { ResultSet rs = null; String command = null; try { rs = DBHelper.executeQuery("Select STARTUP_COMMAND from CONFIG_HEURISTICS where OS='" + osType + "'"); if (rs.next()) { command = rs.getString("STARTUP_COMMAND"); } } catch (Exception ee) { logger.error("Error while getting Startup command from Config heuristics...", ee); } finally { DBHelper.close(rs); } return command; } public static void insertRunningConfigCommand(int id, String cli) { ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from running_config_command where dev_id='" + id + "'"); boolean updateNeeded = false; if (rs.next()) { updateNeeded = true; } try { rs.close(); rs = null; } catch (Exception ex) { } finally { if (updateNeeded) { if (cli != null) { // before executing next sql statement close the // previous rs, This will make sure that previous // connection is released // before trying for new connection. DBHelper.executeUpdate( "update running_config_command set cli='" + cli + "' where dev_id='" + id + "'"); } return; } } } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ex) { } } Connection c = DBHelper.getConnection(); PreparedStatement ps = null; try { ps = c.prepareStatement("insert into running_config_command values (?,?)"); ps.setInt(1, id); // ps.setString(1, ip); ps.setString(2, cli); ps.executeUpdate(); // DBHelper.executeUpdate("insert into device_serial_numbers names (id, hardware_type, serial_number) values("+id+",1,'"+serialNumber+"')"); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception ex) { } DBHelper.releaseConnection(c); } } public static ArrayList<String> getSerialNumbers(int id) { ArrayList<String> serNumbers = new ArrayList<String>(); ResultSet rs = null; try { rs = DBHelper.executeQuery( "select serial_number from device_serial_numbers where id=" + id + " and hardware_type=1"); while (rs.next()) { serNumbers.add(rs.getString("serial_number")); } } catch (Exception ex) { } finally { try { rs.close(); } catch (Exception e) { } } return serNumbers; } public static List<HeuristicDescriptor> getHeuristics(String attribute) { ResultSet rs = null; List<HeuristicDescriptor> list = new ArrayList<HeuristicDescriptor>(); try { rs = DBHelper.executeQuery( "select * from device_heuristics where output_attr='" + attribute + "' order by id"); while ((rs != null) && rs.next()) { String in_attr = rs.getString("input_attr"); String operatorStr = rs.getString("operator"); String operand = rs.getString("operand"); String value = rs.getString("value"); int weight = rs.getInt("weight"); StringOperatorType operator = null; try { operator = StringOperatorType.valueOf(operatorStr); } catch (Exception ex) { // } if (operator == null) { logger.error("Unknown operator: " + operatorStr + " while loading heuristics for " + attribute + " ... ignoring"); } HeuristicDescriptor heu = new HeuristicDescriptor(in_attr, operator, operand, value, weight); list.add(heu); } } catch (Exception ee) { logger.warn("Error while getting Heuristics for :", attribute, ee); } finally { try { rs.close(); } catch (Exception ee) { } } return list; } public static List<HeuristicDescriptor> getConfigHeuristics(String attribute) { ResultSet rs = null; List<HeuristicDescriptor> list = new ArrayList<HeuristicDescriptor>(); try { rs = DBHelper.executeQuery("select * from config_heuristics order by id"); while ((rs != null) && rs.next()) { String os = rs.getString("OS"); String command = rs.getString("COMMAND"); String startupCommand = rs.getString("STARTUP_COMMAND"); HeuristicDescriptor heu = new HeuristicDescriptor(os, command); heu.setStartupCommand(startupCommand); list.add(heu); } } catch (Exception ee) { logger.warn("Error while getting Heuristics for :", attribute, ee); } finally { try { rs.close(); } catch (Exception ee) { } } return list; } public static void updateHeuristics(String attribute, List<HeuristicDescriptor> list) { Connection c = null; PreparedStatement ps = null; try { DBHelper.executeUpdate("delete from device_heuristics where output_attr='" + attribute + "'"); } catch (Exception ee) { logger.error("Exception while trying to delete heuristics for attribute: " + attribute, ee); } if (list == null || list.isEmpty()) { return; } try { c = DBHelper.getConnection(); int i = 0; ps = c.prepareStatement( "INSERT INTO device_heuristics (id, output_attr, input_attr, operator, operand, value, weight) VALUES (?,?,?,?,?,?,?)"); for (HeuristicDescriptor heu : list) { try { ps.setInt(1, i); ps.setString(2, attribute); ps.setString(3, heu.getDeviceAttrName()); ps.setString(4, heu.getOperator().name()); ps.setString(5, heu.getOperand()); ps.setString(6, heu.getResultVal()); if (heu.getWeight() == null) { ps.setInt(7, HeuristicDescriptor.MAX_WEIGHT); } else { ps.setInt(7, heu.getWeight()); } ps.executeUpdate(); } catch (Exception ex) { logger.error("Exception while adding heuristic for attribute: " + attribute + " operand: " + heu.getOperand(), ex); } i++; } } catch (Exception e) { logger.warn("Error while inserting heuristics for attribute: " + attribute, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } public static void updateConfigHeuristics(String attribute, List<HeuristicDescriptor> list) { Connection c = null; PreparedStatement ps = null; try { DBHelper.executeUpdate("delete from config_heuristics"); } catch (Exception ee) { logger.error("Exception while trying to delete heuristics for attribute: " + attribute, ee); } if (list == null || list.isEmpty()) { return; } try { c = DBHelper.getConnection(); int i = 0; ps = c.prepareStatement( "INSERT INTO config_heuristics (id, os, command, startup_command) VALUES (?,?,?,?)"); for (HeuristicDescriptor heu : list) { try { ps.setInt(1, i); ps.setString(2, heu.getOS()); ps.setString(3, heu.getCommand()); ps.setString(4, heu.getStartupCommand()); ps.executeUpdate(); } catch (Exception ex) { logger.error("Exception while adding heuristic for attribute: " + attribute + " operand: " + heu.getOperand(), ex); } i++; } } catch (Exception e) { logger.warn("Error while inserting heuristics for attribute: " + attribute, e); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } } /** * Save OS name and corresponding running config and/or startup config command to DB. * * @param OSDescription * @param runConfigCmd */ public static void saveConfigHeuristics(String oSName, String runConfigCmd, String startConfigCmd) throws SQLException { Connection conn = null; String selectQuery = "SELECT * from config_heuristics where os = '" + oSName + "'"; String insQuery = "INSERT INTO config_heuristics (select coalesce(max(id), 0) + 1, ?, ?, ? from config_heuristics)"; if ((runConfigCmd == null) && (startConfigCmd == null)) { return; } // String updateQuery = // "UPDATE config_heuristics SET command = ? and startup_command = ? where os = ?"; try { conn = DBHelper.getConnection(); try (ResultSet rs = DBHelper.executeQuery(selectQuery)) { if (rs.next()) { // If record already exists, update it if (startConfigCmd == null) { // If startConfigCmd is sent as null, do not set it (we // don't want to set it to null if value // already exists in DB) String updateQuery = "UPDATE config_heuristics SET command = ? where os = ?"; try (PreparedStatement ps = conn.prepareStatement(updateQuery)) { ps.setString(1, runConfigCmd); ps.setString(2, oSName); ps.executeUpdate(); } } // If runConfigCmd is sent as null, do not set it (we don't // want to set it to null if value already // exists // in DB) else if (runConfigCmd == null) { String updateQuery = "UPDATE config_heuristics SET startup_command = ? where os = ?"; try (PreparedStatement ps = conn.prepareStatement(updateQuery)) { ps.setString(1, startConfigCmd); ps.setString(2, oSName); ps.executeUpdate(); } } else { String updateQuery = "UPDATE config_heuristics SET command = ? and startup_command = ? where os = ?"; try (PreparedStatement ps = conn.prepareStatement(updateQuery)) { ps.setString(1, runConfigCmd); ps.setString(2, startConfigCmd); ps.setString(3, oSName); ps.executeUpdate(); } } } else { // Otherwise insert it try (PreparedStatement ps = conn.prepareStatement(insQuery)) { ps.setString(1, oSName); ps.setString(2, runConfigCmd); ps.setString(3, startConfigCmd); ps.executeUpdate(); } } } } catch (SQLException sqlex) { throw sqlex; } catch (Exception ex) { logger.error("Error while saving os and run config cmd to database - " + oSName + " " + runConfigCmd, ex); } finally { DBHelper.closeAll(conn, null, null); } } public static void saveCredTransportDetails(int nodeId, String transportType, String credName) { try { DBHelper.executeUpdate("delete from device_discovered_creds where id=" + nodeId); if (transportType != null && !"".equals(transportType.trim())) { DBHelper.executeUpdate("insert into device_discovered_creds values (" + nodeId + ",'" + transportType + "','" + credName + "')"); } } catch (Exception ex) { ex.printStackTrace(); } } public static void cleanModuleSerialNumbers(int nodeId) { try { DBHelper.executeUpdate("update device_module_info set SERIALNUM='' where device_id=" + nodeId + " and SERIALNUM='NAME:'"); } catch (Exception ex) { ex.printStackTrace(); } } public static int getJobId(String jobName, String jobGrp) { int jobId = -1; PreparedStatement ps = null; Connection c = null; ResultSet rs = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("select job_id from pari_job_addl_details where job_name=? and job_group=?"); ps.setString(1, jobName); ps.setString(2, jobGrp); rs = ps.executeQuery(); if (rs.next()) { jobId = rs.getInt("job_id"); } } catch (Exception ee) { logger.warn("Error while trying to get the job id", ee); } finally { try { rs.close(); } catch (Exception ex) { ex.printStackTrace(); } try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } return jobId; } public static ArrayList<Integer> getAllJobIds(String jobGrp) { int jobId = -1; PreparedStatement ps = null; Connection c = null; ResultSet rs = null; ArrayList<Integer> jobIdList = new ArrayList<Integer>(); try { c = DBHelper.getConnection(); ps = c.prepareStatement( "select job_id from pari_job_addl_details where job_group=? ORDER BY job_id DESC"); ps.setString(1, jobGrp); rs = ps.executeQuery(); while (rs.next()) { jobId = rs.getInt("job_id"); jobIdList.add(jobId); } } catch (Exception ee) { logger.warn("Error while trying to get all job ids", ee); } finally { try { rs.close(); } catch (Exception ex) { ex.printStackTrace(); } try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } return jobIdList; } public static String getLastCreatedJobNameByType(String jobType, ServerIfProvider serverIfProvider) { PreparedStatement ps = null; Connection c = null; ResultSet rs = null; String jobName = null; try { c = DBHelper.getConnection(); String query = null; String user = serverIfProvider.getUserDetails().getLogin(); if (isPostgres()) { query = "select job_name from pari_job_addl_details where job_group = '" + jobType + "' and creator_login='" + user + "' order by created_time desc limit 1;"; } else { query = "select job_name from pari_job_addl_details where job_group = '" + jobType + "' and creator_login='" + user + "' and ROWNUM <= 1 order by created_time desc;"; } ps = c.prepareStatement(query); rs = ps.executeQuery(); while (rs.next()) { jobName = rs.getString("job_name"); } } catch (Exception ee) { logger.warn("Error while getting job name", ee); } finally { try { rs.close(); } catch (Exception ex) { ex.printStackTrace(); } try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ee) { } } return jobName; } public static void saveSerialNumber(int nodeId, String saveSer) { try { DBHelper.executeUpdate("update ios_version set serial_number='" + saveSer + "' where id=" + nodeId); } catch (Exception ex) { ex.printStackTrace(); } } public static void saveProductModel(int nodeId, String pid) { try { DBHelper.executeUpdate("update ios_version set productid='" + pid + "' where id=" + nodeId); } catch (Exception ex) { ex.printStackTrace(); } } public static void insertJobCreatorDetails(String jobName, String jobGroup, int creatorId, long createdTime, String devices) throws Exception { Connection c = null; PreparedStatement ps = null; try { String loginName = ""; UserDetails ud = UsersFactory.getUser(creatorId); if (ud != null) { loginName = ud.getLogin(); } c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_CREATOR_DETAILS); ps.setString(1, jobName); ps.setString(2, jobGroup); ps.setInt(3, creatorId); ps.setString(4, loginName); ps.setTimestamp(5, (new Timestamp(createdTime))); ps.setString(6, devices); ps.executeUpdate(); insertJobCustomerDetails(jobName, jobGroup, -1); } catch (Exception ex) { logger.warn("Error while creating a job entry in db.", ex); } finally { try { ps.close(); } catch (Exception ex) { logger.debug("Error while closing prepared statement.", ex); } DBHelper.releaseConnection(c); } } public static void insertJobCreatorDetails(String jobName, String jobGroup, int creatorId, long createdTime, String devices, int cusID) throws Exception { Connection c = null; PreparedStatement ps = null; try { String loginName = ""; UserDetails ud = UsersFactory.getUser(creatorId); if (ud != null) { loginName = ud.getLogin(); } c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_CREATOR_DETAILS); ps.setString(1, jobName); ps.setString(2, jobGroup); ps.setInt(3, creatorId); ps.setString(4, loginName); ps.setTimestamp(5, (new Timestamp(createdTime))); ps.setString(6, devices); ps.executeUpdate(); insertJobCustomerDetails(jobName, jobGroup, cusID); } catch (Exception ex) { logger.warn("Error while creating a job entry in db.", ex); } finally { try { ps.close(); } catch (Exception ex) { logger.debug("Error while closing prepared statement.", ex); } DBHelper.releaseConnection(c); } } private static void insertJobCustomerDetails(String jobName, String jobGroup, int customerID) { Connection cc = null; PreparedStatement ps = null; ResultSet rs = null; int jobId = -1; int cusid = customerID; try { cc = DBHelper.getConnection(); ps = cc.prepareStatement("select job_id from pari_job_addl_details where job_name=? and job_group=?"); ps.setString(1, jobName); ps.setString(2, jobGroup); rs = ps.executeQuery(); if (rs.next()) { jobId = rs.getInt("job_id"); } rs.close(); List<Integer> deviceIdsList = NmIfImpl.getInstance().getDeviceList(jobId); StringBuilder customerQuery = new StringBuilder(); String device = null; if (!deviceIdsList.isEmpty()) { if (deviceIdsList.size() < 500) { device = deviceIdsList.toString().replace("[", "").replace("]", "").trim(); customerQuery.append("select distinct customer_id from customer_device where device_id in (" + (device) + ")"); ResultSet rsVal = DBHelper.executeQuery(customerQuery.toString()); while (rsVal.next()) { cusid = rsVal.getInt("customer_id"); ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS); ps.setInt(1, jobId); ps.setInt(2, cusid); ps.executeUpdate(); } rsVal.close(); } else { Set<Integer> customerIdset = getCustomerIdList(deviceIdsList); for (Integer customer : customerIdset) { ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS); ps.setInt(1, jobId); ps.setInt(2, customer); ps.executeUpdate(); } } } else if (cusid != -1) { ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS); ps.setInt(1, jobId); ps.setInt(2, cusid); ps.executeUpdate(); } else { List<String> customerNames = NmIfImpl.getUserDomain().getApplicableCustomerIds(); Boolean hasAccess = NmIfImpl.getUserDomain().isHasUnlimitedAccess(); if (hasAccess) { ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS); ps.setInt(1, jobId); ps.setInt(2, -1); ps.executeUpdate(); } else { for (String customer : customerNames) { ps = cc.prepareStatement(DBHelperConstants.INSERT_JOB_CUSTOMER_DETAILS); ps.setInt(1, jobId); ps.setInt(2, Integer.parseInt(customer)); ps.executeUpdate(); } } } } catch (Exception ex) { logger.warn("Error while inserting job entry in job customer details db.", ex); } finally { try { ps.close(); } catch (Exception ex) { logger.debug("Error while closing job customer details prepared statement.", ex); } DBHelper.releaseConnection(cc); } } public static Set<Integer> getCustomerIdList(List deviceIdsList) { Connection cn = null; Set<Integer> deviceset = new HashSet<Integer>(); String device = null; int cusid = -1; try { cn = DBHelper.getConnection(); int val = (deviceIdsList.size() / 500) + 1; int start = 0; int end = 500; for (int s = 1; s <= val; s++) { List<Integer> deviceIds = new ArrayList<>(); if (s == val) { deviceIds = deviceIdsList.subList(start, deviceIdsList.size()); if (!deviceIds.isEmpty()) { device = deviceIds.toString().replace("[", "").replace("]", "").trim(); ResultSet rsVal = DBHelper.executeQuery( "select distinct customer_id from customer_device where device_id in (" + (device) + ")"); while (rsVal.next()) { cusid = rsVal.getInt("customer_id"); deviceset.add(cusid); } rsVal.close(); } } else { deviceIds = deviceIdsList.subList(start, end); if (!deviceIds.isEmpty()) { device = deviceIds.toString().replace("[", "").replace("]", "").trim(); ResultSet rsVal = DBHelper.executeQuery( "select distinct customer_id from customer_device where device_id in (" + (device) + ")"); while (rsVal.next()) { cusid = rsVal.getInt("customer_id"); deviceset.add(cusid); } rsVal.close(); } start = s * 500; end = start + 500; } } } catch (Exception ex) { logger.warn("Error while creating a job entry in db.", ex); } finally { try { DBHelper.releaseConnection(cn); } catch (Exception ex) { logger.debug("Error while release connection.", ex); } } return deviceset; } public static void updateJobModifierDetails(int jobId, int modifierId, long modifiedTime) throws Exception { Connection c = null; PreparedStatement ps = null; String loginName = ""; UserDetails ud = UsersFactory.getUser(modifierId); if (ud != null) { loginName = ud.getLogin(); } try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_MODIFIER_DETAILS); ps.setInt(1, modifierId); ps.setString(2, loginName); ps.setTimestamp(3, (new Timestamp(modifiedTime))); ps.setInt(4, jobId); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception ex) { } DBHelper.releaseConnection(c); } } public static void updateJobDevicesDetails(int jobId, int noOfDevices, int noOfChanges) throws Exception { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_DEVICES_DETAILS); ps.setInt(1, noOfDevices); ps.setInt(2, noOfChanges); ps.setInt(3, jobId); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception ex) { } DBHelper.releaseConnection(c); } } public static void deleteJob(int jobId) throws Exception { DBHelper.executeUpdate("delete from pari_job_addl_details where job_id=" + jobId); DBHelper.executeUpdate("delete from job_customer_details where job_id=" + jobId); } public static ArrayList<Integer> getCustomerIDs(int jobId) { ArrayList<Integer> customerIds = new ArrayList<Integer>(); ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from job_customer_details where job_id=" + jobId); while ((rs != null) && rs.next()) { int custId = rs.getInt("customer_id"); customerIds.add(custId); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { rs.close(); } catch (Exception ex) { ex.printStackTrace(); } } return customerIds; } public static int getJobCreatorId(int jobId) { int creatorId = -1; ResultSet rs = null; try { rs = DBHelper.executeQuery("select creator_id from pari_job_addl_details where job_id=" + jobId); if (rs.next()) { creatorId = rs.getInt("creator_id"); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { rs.close(); } catch (Exception ex) { ex.printStackTrace(); } } return creatorId; } public static int getJobModifierId(int jobId) { int modifierId = -1; ResultSet rs = null; try { rs = DBHelper.executeQuery("select modifier_id from pari_job_addl_details where job_id=" + jobId); if (rs.next()) { modifierId = rs.getInt("modifier_id"); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { rs.close(); } catch (Exception ex) { ex.printStackTrace(); } } return modifierId; } // Added as part of CSCtr59009 bug fix to get discovered time of devices public static Map<String, Long> getDiscoveryTimeForAllDevices(int customerId, int instanceId) { ResultSet rs = null; Map<String, Long> map = new HashMap<String, Long>(); try { rs = DBHelper.executeQuery("select ipaddress, discovery_time from discovered_devices where customer_id=" + customerId + " and instance_id='" + instanceId + "'"); while ((rs != null) && rs.next()) { String ipaddress = rs.getString("ipaddress"); Timestamp ts = rs.getTimestamp("discovery_time"); map.put(ipaddress, ts.getTime()); } } catch (Exception ee) { logger.warn("Error while getting getDiscoveryTimeForAllDevices :", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return map; } // Added as part of bug fix - CSCtr61612 public static void updateDiscoveredDevice(String deviceIp, int customerId, int instanceId, String isManaged) { try { DBHelper.executeUpdate( "update discovered_devices set is_managed = '" + isManaged + "' where ipaddress='" + deviceIp + "' and customer_id=" + customerId + " and instance_id='" + instanceId + "'"); } catch (Exception e) { logger.error("Error while executing updateDiscoveredDevice..", e); } } public static void insertExternalDeviceDetails(int nccmId, int customerId, String ipAddress, Long externalId, String serviceName, int wingId, boolean replace) throws Exception { Connection c = DBHelper.getConnection(); PreparedStatement ps = null; try { ConcurrentHashMap<Integer, String> nodeMap = getExternalNodeInfo(serviceName, externalId); if (nodeMap != null && nodeMap.size() > 0) { if (replace) { try { String query = "delete from external_device where EXTERNAL_ID=" + externalId + " and SERVICE_NAME='" + serviceName + "'"; DBHelper.executeUpdate(query); } catch (Exception ex) { logger.error("Exception while deleting external_device: EXTERNAL_ID=" + externalId + " and SERVICE_NAME='" + serviceName + "'", ex); } } else { throw new Exception("An Entry already Exist with the same EXTERNALID=" + externalId + " and SERVICENAME='" + serviceName + "'"); } } } catch (SQLException e) { logger.error("Exception while retrieving external_device: EXTERNAL_ID=" + externalId + " and SERVICE_NAME='" + serviceName + "'", e); } try { ps = c.prepareStatement( "insert into external_device(NCCM_ID, CUSTOMER_ID, IPADDRESS, EXTERNAL_ID, SERVICE_NAME, WING_INSTANCE_ID) values (?,?,?,?,?,?)"); ps.setInt(1, nccmId); ps.setInt(2, customerId); ps.setString(3, ipAddress); ps.setLong(4, externalId); ps.setString(5, serviceName); ps.setInt(6, wingId); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception ex) { } DBHelper.releaseConnection(c); } } public static void deleteExternalDevice(String serviceName) throws SQLException { DBHelper.executeUpdate("delete from external_device where SERVICE_NAME='" + serviceName + "'"); } public static void deleteExternalDevice(String serviceName, Long externalId) throws SQLException { DBHelper.executeUpdate("delete from external_device where EXTERNAL_ID=" + externalId + "AND SERVICE_NAME='" + serviceName + "'"); } public static void deleteExternalDevice(Integer customerId, String ipAddress) throws SQLException { String delStmt = "delete from external_device where CUSTOMER_ID=" + customerId + " AND IPADDRESS='" + ipAddress + "'"; // logger.info("Delete Statement---\n"+delStmt); DBHelper.executeUpdate(delStmt); } public static HashMap<String, Long> getExtDeviceInfo(int customerId, String ipAddress) throws SQLException { String query = "select * from external_device where CUSTOMER_ID=" + customerId + " AND IPADDRESS= '" + ipAddress + "'"; Connection c = DBHelper.getConnection(); HashMap<String, Long> map = new HashMap<String, Long>(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(c, query); while (rs.next()) { String serviceName = rs.getString("SERVICE_NAME"); Long externalId = rs.getLong("EXTERNAL_ID"); if (serviceName != null && !serviceName.isEmpty() && externalId > 0) { map.put(serviceName, externalId); } } } catch (Exception e) { logger.error("Error in getExtDeviceInfo : ", e); } finally { // CSCtx91377 - Releasing the DB connection. if (rs != null) { rs.close(); } DBHelper.releaseConnection(c); } return map; } public static ConcurrentHashMap<Integer, String> getExternalNodeInfo(String serviceName, Long externalId) throws PariException { ConcurrentHashMap<Integer, String> nodeMap = new ConcurrentHashMap<Integer, String>(1); if (externalId == null || externalId.longValue() <= 0) { throw new PariException("_NCCM_SRV_DEVMGMT_EXTERNALID_MISSING", new Object[] {}); } String query = "select * from external_device where EXTERNAL_ID=" + externalId + " AND SERVICE_NAME= '" + serviceName + "'"; Connection c = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(c, query); if (rs.next()) { String ipAddress = rs.getString("IPADDRESS"); Integer nccmId = rs.getInt("NCCM_ID"); nodeMap.put(nccmId, ipAddress); return nodeMap; } } catch (Exception e) { throw new PariException("_NCCM_SRV_DEVMGMT_GET_EXTERNAL_NODE_INFO_FAILED", new Object[] {}); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (c != null) { try { DBHelper.releaseConnection(c); } catch (Exception ee) { logger.error("Failed to release connection", ee); } } } return null; } public static void updateExtDevice(String ipAddress, int customerId, int id) { ResultSet rs = null; String selectQuery = "select * from external_device where IPADDRESS ='" + ipAddress + "' AND CUSTOMER_ID=" + customerId; try { rs = DBHelper.executeQuery(selectQuery); boolean needUpdate = false; try { if (rs.next()) { needUpdate = true; } } catch (SQLException e) { logger.warn( "Exception occured while closing ResultSet in updateNccmIdFromDeviceIPandCustomerId" + e); } finally { if (rs != null) { rs.close(); rs = null; } if (needUpdate) { DBHelper.executeUpdate("update external_device set NCCM_ID=" + id + "where IPADDRESS ='" + ipAddress + "' AND CUSTOMER_ID=" + customerId); } } } catch (SQLException e) { logger.warn("Exception occured in updateNccmIdFromDeviceIPandCustomerId" + e); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { logger.warn( "Exception occured while closing ResultSet in updateNccmIdFromDeviceIPandCustomerId" + e); } } } public static Set<Long> getExternalIds(String serviceName) { String query = null; Set<Long> externalIds = new HashSet<Long>(); Connection connection = null; ResultSet rs = null; query = "select * from external_device where SERVICE_NAME='" + serviceName + "'"; connection = DBHelper.getConnection(); try { rs = DBHelper.executeQueryNoCommit(connection, query); if (rs != null && rs.next()) { externalIds.add(rs.getLong("EXTERNAL_ID")); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } DBHelper.releaseConnection(connection); } return externalIds; } public static Set<String> getNccmIds(String serviceName) { Set<String> externalIds = new HashSet<String>(); String query = "select * from external_device where SERVICE_NAME='" + serviceName + "'"; getNccmIds(query, externalIds); return externalIds; } public static Set<String> getNccmIds(String serviceName, int customerId) { Set<String> externalIds = new HashSet<String>(); String query = "select * from external_device where SERVICE_NAME='" + serviceName + "' AND CUSTOMER_ID=" + customerId; getNccmIds(query, externalIds); return externalIds; } /** * @param query * @param externalIds */ private static void getNccmIds(String query, Set<String> externalIds) { Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query); while (rs != null && rs.next()) { externalIds.add(String.valueOf(rs.getInt("NCCM_ID"))); } } catch (SQLException e) { logger.warn("Exception while executing query:" + e); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { logger.warn("Unable to close the resultset:" + e); } DBHelper.releaseConnection(connection); } } public static List<ModuleInfo> getChildModulesOrContexts(int parentNodeId, ModuleType moduleType) { ResultSet rs = null; List<ModuleInfo> devices = null; try { rs = DBHelper.executeQuery("select * from nodes_modules where id=" + parentNodeId + " AND module_type=" + moduleType.moduleType); devices = new ArrayList<ModuleInfo>(); while (rs != null && rs.next()) { int parentId = rs.getInt("id"); int modId = rs.getInt("mod_id"); String slotNumber = rs.getString("slot_number"); int modType = rs.getInt("module_type"); ModuleInfo mInfo = new ModuleInfo(); mInfo.setParentId(parentId); mInfo.setModId(modId); mInfo.setSlotNumber(slotNumber); mInfo.setType(ModuleType.getFromInt(modType)); devices.add(mInfo); } } catch (Exception ee) { logger.warn("Error while getting module/conetxt info", ee); } finally { try { rs.close(); } catch (Exception ee) { } } return devices; } public static List<ModuleInfo> getChildModulesOrContextForParent(int parentNodeId) { ResultSet rs = null; List<ModuleInfo> devices = null; try { rs = DBHelper.executeQuery("select * from nodes_modules where id=" + parentNodeId); devices = new ArrayList<ModuleInfo>(); while (rs != null && rs.next()) { int parentId = rs.getInt("id"); int modId = rs.getInt("mod_id"); String slotNumber = rs.getString("slot_number"); int modType = rs.getInt("module_type"); ModuleInfo mInfo = new ModuleInfo(); mInfo.setParentId(parentId); mInfo.setModId(modId); mInfo.setSlotNumber(slotNumber); mInfo.setType(ModuleType.getFromInt(modType)); devices.add(mInfo); } } catch (Exception ee) { logger.warn("Error while getting module/conetxt info", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.warn("Error while closing result set", ee); } } return devices; } public static DeviceModule getDeviceModuleData(int devId, String slot) { String query = null; DeviceModule devModule = null; Connection connection = null; ResultSet rs = null; query = "select * from DEVICE_MODULE_INFO_NEW where DEVICE_ID='" + devId + "'" + "AND moduletype='module' AND slot='" + slot + "'"; connection = DBHelper.getConnection(); try { rs = DBHelper.executeQueryNoCommit(connection, query); if (rs != null && rs.next()) { devModule = new DeviceModule(); devModule.setName(rs.getString("MODULE_NAME")); devModule.setDescr(rs.getString("MODULE_DESCR")); devModule.setPID(rs.getString("PID")); devModule.setSerialNumber(rs.getString("SERIALNUM")); devModule.setModuleType(rs.getString("moduleType")); devModule.setParentIndex(rs.getInt("parentIndex")); devModule.setMyIndex(rs.getInt("myIndex")); devModule.setRelPos(rs.getInt("rel_pos")); devModule.setSlot(rs.getString("slot")); devModule.setVendorType(rs.getString("vendor_type")); devModule.setHwVersion(rs.getString("hwversion")); devModule.setFruable(rs.getInt("fru") == 1 ? true : false); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } DBHelper.releaseConnection(connection); } return devModule; } public static boolean isEntityDataCollectedForDevice(int devId) { Connection connection = null; ResultSet rs = null; int count = -1; String query = "select count(*) as count from DEVICE_MODULE_INFO_NEW where DEVICE_ID='" + devId + "'"; connection = DBHelper.getConnection(); try { rs = DBHelper.executeQueryNoCommit(connection, query); if (rs != null && rs.next()) { count = rs.getInt("count"); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } DBHelper.releaseConnection(connection); } return (count >= 1); } public static List<ExternalDeviceRegDetails> getExternalDeviceInfoDetails(int customerId, String ipAddress, int nccmId, Long externalId, String serviceName, int wingId, ServerIfProvider serverIfProvider) throws Exception { ExternalDeviceRegDetails extDevRegDet = new ExternalDeviceRegDetails(); List<ExternalDeviceRegDetails> extDevicesList = new ArrayList<ExternalDeviceRegDetails>(); boolean whereExists = false; String and = " "; StringBuffer query = new StringBuffer(); query.append("SELECT * FROM external_device "); if (nccmId > 0) { if (!whereExists) { query.append(" WHERE "); whereExists = true; } query.append(and + " NCCM_ID=" + nccmId); if (whereExists) { and = " AND "; } } if (serviceName != null) { if (!whereExists) { query.append(" WHERE "); whereExists = true; } query.append(and + " SERVICE_NAME = '" + serviceName + "'"); if (whereExists) { and = " AND "; } } /* * if (nccmId > 0) { query.append("AND NCCM_ID = " + nccmId); } */ if (ipAddress != null) { if (!whereExists) { query.append(" WHERE "); whereExists = true; } query.append(and + " IPADDRESS = '" + ipAddress + "'"); if (whereExists) { and = " AND "; } } if (externalId != null && externalId > 0) { if (!whereExists) { query.append(" WHERE "); whereExists = true; } query.append(and + " EXTERNAL_ID = " + externalId); if (whereExists) { and = " AND "; } } if (customerId > 0) { if (!whereExists) { query.append(" WHERE "); whereExists = true; } query.append(and + " CUSTOMER_ID = " + customerId); if (whereExists) { and = " AND "; } } if (wingId > 0) { if (!whereExists) { query.append(" WHERE "); } query.append(and + " WING_INSTANCE_ID = " + wingId); } // logger.info("GetExternalDeviceInfo Query:" + query.toString()); Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query.toString()); while (rs != null && rs.next()) { extDevRegDet = new ExternalDeviceRegDetails(); extDevRegDet.setServiceName(rs.getString("SERVICE_NAME")); extDevRegDet.setExternalId(rs.getLong("EXTERNAL_ID")); extDevRegDet.setNccmId(rs.getInt("NCCM_ID")); extDevRegDet.setIPAddress(rs.getString("IPADDRESS")); String customerName = serverIfProvider.getCustomerIf() .getCustomerNameById(rs.getInt("CUSTOMER_ID")); if (customerName != null) { extDevRegDet.setCustomerName(customerName); } String wingInstanceName = serverIfProvider.getCustomerIf() .getWingInstanceName(rs.getInt("WING_INSTANCE_ID")); extDevRegDet.setWingInstanceName(wingInstanceName); extDevicesList.add(extDevRegDet); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } DBHelper.releaseConnection(connection); } return extDevicesList; } public static List<ExternalDeviceRegDetails> getAllExternalDeviceInfoDetails(ServerIfProvider serverIfProvider) throws Exception { ExternalDeviceRegDetails extDevRegDet = new ExternalDeviceRegDetails(); List<ExternalDeviceRegDetails> extDevicesList = new ArrayList<ExternalDeviceRegDetails>(); StringBuffer query = new StringBuffer(); query.append("SELECT * FROM external_device"); // logger.info("GetAllExternalDeviceInfo Query:" + query.toString()); Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query.toString()); while (rs != null && rs.next()) { extDevRegDet = new ExternalDeviceRegDetails(); extDevRegDet.setServiceName(rs.getString("SERVICE_NAME")); extDevRegDet.setExternalId(rs.getLong("EXTERNAL_ID")); extDevRegDet.setNccmId(rs.getInt("NCCM_ID")); extDevRegDet.setIPAddress(rs.getString("IPADDRESS")); String customerName = serverIfProvider.getCustomerIf() .getCustomerNameById(rs.getInt("CUSTOMER_ID")); if (customerName != null) { extDevRegDet.setCustomerName(customerName); } String wingInstanceName = serverIfProvider.getCustomerIf() .getWingInstanceName(rs.getInt("WING_INSTANCE_ID")); extDevRegDet.setWingInstanceName(wingInstanceName); extDevicesList.add(extDevRegDet); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } DBHelper.releaseConnection(connection); } return extDevicesList; } public static void deleteDeviceSerialNumber(int nodeId) { try { DBHelper.executeUpdate("delete from device_serial_numbers where id=" + nodeId); } catch (Exception e) { logger.error( "Exception while deleting Serial numbers for device : " + nodeId + " : " + e.getMessage()); } } public static void loadInterfacesFromDB(NetworkNode node, NetworkNodeExt networkNodeExt) { int nodeId = node.getNodeId(); String query = "select * from ios_if where id=" + nodeId + " order by if_name"; loadInterfacesFromDBInternal(query, nodeId, networkNodeExt, node); query = "select * from pix_if where id=" + nodeId + " order by if_name"; loadInterfacesFromDBInternal(query, nodeId, networkNodeExt, node); } public static void loadInterfacesFromDBInternal(String query, int nodeId, NetworkNodeExt networkNodeExt, NetworkNode node) { Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query.toString()); while (rs != null && rs.next()) { CiscoNetworkNodeInterface nIf = new CiscoNetworkNodeInterface(); nIf.updateObject(rs); networkNodeExt.addInterface(nIf, node); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { DBHelper.releaseConnection(connection); } } } public static void loadCdpNeighbors(NetworkNode node, NetworkNodeExt networkNodeExt) { String query = "select * from device_cdp_table where device_id=" + node.getNodeId(); Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query.toString()); while (rs != null && rs.next()) { int linkId = rs.getInt("link_id"); int fromDeviceId = rs.getInt("device_id"); String toIpAddress = rs.getString("toIPAddress"); String fromPort = rs.getString("fromPort"); String toPort = rs.getString("toPort"); networkNodeExt.addCDPNeighbor(linkId, fromDeviceId, node, toIpAddress, fromPort, toPort); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { DBHelper.releaseConnection(connection); } } } public static void loadCapabilitiesFromDB(NetworkNode node, NetworkNodeExt networkNodeExt) { String query = "select * from node_not_supp_caps where device_id=" + node.getNodeId(); Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query.toString()); while (rs != null && rs.next()) { String capsList = rs.getString("non_supp_cap_list"); networkNodeExt.popuateCapabilitiesFromDBString(capsList); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { DBHelper.releaseConnection(connection); } } } public static Hashtable<String, String> loadStandByIpsFromDB(NetworkNode node) { Hashtable<String, String> standByIps = new Hashtable<String, String>(1); String query = "select * from standby_to_id where id=" + node.getNodeId(); Connection connection = DBHelper.getConnection(); ResultSet rs = null; try { rs = DBHelper.executeQueryNoCommit(connection, query.toString()); while (rs != null && rs.next()) { String stdIP = rs.getString("standby_ip"); standByIps.put(stdIP, ""); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } finally { DBHelper.releaseConnection(connection); } } return standByIps; } public static String[] getAvailableShowCommands() { return getAvailableShowCommands(-1); } public static List<SyslogPreferences> loadSyslogPatternFromDB() { List<SyslogPreferences> syslogPrefLists = new ArrayList<SyslogPreferences>(); SyslogPreferences syslogPrefObj = null; ResultSet resultSet = null; try { try { resultSet = DBHelper.executeQuery(SELECT_SYSLOG_ACTIONS); while (resultSet.next()) { syslogPrefObj = new SyslogPreferences(); syslogPrefObj.setID(resultSet.getInt("ID")); syslogPrefObj.setTitle(resultSet.getString("TITLE")); syslogPrefObj.setOperator(resultSet.getString("OPERATOR")); syslogPrefObj.setOperand(resultSet.getString("OPERAND")); int dataSetId = resultSet.getInt("DATASETID"); if (dataSetId != -1) { Map<String, String> dataSetInfoMap = getDataSetNameAndStatus(dataSetId); syslogPrefObj.setDataSet(dataSetInfoMap.get(DATASETNAME)); syslogPrefObj.setStatus(Boolean.parseBoolean(dataSetInfoMap.get(STATUS))); getSyslogHeuristicsCommands(dataSetId, syslogPrefObj.getOsCommandMap()); } syslogPrefLists.add(syslogPrefObj); } } catch (Exception e) { logger.debug("Error while executing the query to retrieve syslog patterns", e); } finally { if (resultSet != null) { try { resultSet.close(); resultSet = null; } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } } } } catch (Exception e) { logger.debug(CONNECTION_ERROR, e); } return syslogPrefLists; } public static List<HeuristicDescriptor> getSyslogHeuristics(String attribute) { List<HeuristicDescriptor> syslogList = new ArrayList<HeuristicDescriptor>(); ResultSet resultSet = null; try { try { resultSet = DBHelper.executeQuery(SELECT_SYSLOG_ACTIONS); while (resultSet.next()) { int id = resultSet.getInt("ID"); String title = resultSet.getString("TITLE"); String desc = resultSet.getString("DESCRIPTION"); String operator = resultSet.getString("OPERATOR"); String operand = resultSet.getString("OPERAND"); String type = resultSet.getString("TYPE"); HeuristicDescriptor heu = new HeuristicDescriptor(id, title, desc, operator, operand, type); int dataSetId = resultSet.getInt("DATASETID"); if (dataSetId != -1) { Map<String, String> dataSetInfoMap = getDataSetNameAndStatus(dataSetId); heu.setDataSet(dataSetInfoMap.get(DATASETNAME)); heu.setStatus(Boolean.parseBoolean(dataSetInfoMap.get(STATUS))); getSyslogHeuristicsCommands(dataSetId, heu.getOsCommandMap()); } syslogList.add(heu); } } catch (Exception e) { logger.warn("Error while getting Heuristics for :", attribute, e); } finally { if (resultSet != null) { try { resultSet.close(); resultSet = null; } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } } } } catch (Exception e) { logger.debug(CONNECTION_ERROR, e); } return syslogList; } public static Map<String, String> getDataSetNameAndStatus(int dataSetId) { ResultSet resultSet = null; Map<String, String> dataSetInfoMap = new HashMap<String, String>(); try { try { resultSet = DBHelper .executeQuery("select datasetname,status from syslog_datasets where id=" + dataSetId); while (resultSet.next()) { dataSetInfoMap.put(DATASETNAME, resultSet.getString(DATASETNAME)); dataSetInfoMap.put(STATUS, String.valueOf(resultSet.getBoolean(STATUS))); } } catch (Exception e) { logger.warn("Error while getting dataset details for Syslog Heuristics", e); } finally { if (resultSet != null) { try { resultSet.close(); resultSet = null; } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } } } } catch (Exception e) { logger.debug(CONNECTION_ERROR, e); } return dataSetInfoMap; } public static void getSyslogHeuristicsCommands(int dataSetId, Map<String, String> osCommandMap) { ResultSet resultSet = null; try { try { resultSet = DBHelper.executeQuery("select * from syslog_commands where id=" + dataSetId); while (resultSet.next()) { String os = resultSet.getString("OS"); String command = resultSet.getString("COMMAND"); osCommandMap.put(os, command); } } catch (Exception e) { logger.warn("Error while getting commands for Syslog Heuristics", e); e.printStackTrace(); } finally { if (resultSet != null) { try { resultSet.close(); resultSet = null; } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } } } } catch (Exception e) { logger.debug(CONNECTION_ERROR, e); } } public static List<TrapActionDescriptor> getTrapActionHeuristics() { List<TrapActionDescriptor> trapActionList = new ArrayList<TrapActionDescriptor>(); ResultSet resultSet = null; try { try { resultSet = DBHelper.executeQuery(SELECT_TRAP_ACTIONS); while (resultSet.next()) { int id = resultSet.getInt("ID"); String title = resultSet.getString("TITLE"); String objectId = resultSet.getString("OBJECT_ID"); String type = resultSet.getString("TYPE"); TrapActionDescriptor heu = new TrapActionDescriptor(id, title, objectId, type); int dataSetId = resultSet.getInt("DATASETID"); if (dataSetId != -1) { Map<String, String> dataSetInfoMap = getDataSetNameAndStatus(dataSetId); heu.setDataSet(dataSetInfoMap.get(DATASETNAME)); heu.setStatus(Boolean.parseBoolean(dataSetInfoMap.get(STATUS))); getSyslogHeuristicsCommands(dataSetId, heu.getOsCommandMap()); } trapActionList.add(heu); } } catch (Exception e) { logger.warn("Error while getting Heuristics for :", "TrapAction", e); } finally { if (resultSet != null) { try { resultSet.close(); resultSet = null; } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } } } } catch (Exception e) { logger.debug(CONNECTION_ERROR, e); } return trapActionList; } public static void updateSyslogHeuristics(String attribute, List<HeuristicDescriptor> list) { logger.info("Updating Heuristics for " + attribute); for (HeuristicDescriptor heu : list) { if ("Add".equals(heu.getAction())) { insertSyslogHeuristics(heu); } if ("Modify".equals(heu.getAction())) { modifySyslogHeuristics(heu); } if ("Delete".equals(heu.getAction())) { deleteSyslogHeuristics(heu.getId()); } } } public static void updateTrapActionHeuristics(String attribute, List<TrapActionDescriptor> list) { logger.info("Updating SNMP Trap ActionsHeuristics for " + attribute); for (TrapActionDescriptor heu : list) { if ("Add".endsWith(heu.getAction())) { insertTrapActionHeuristics(heu); } if ("Modify".equals(heu.getAction())) { modifyTrapActionheuristics(heu); } if ("Delete".equals(heu.getAction())) { deleteTrapActionHeuristics(heu.getId()); } } } private static void deleteTrapActionHeuristics(int id) { try { removeDataSet(id, "snmp_trap_actions"); DBHelper.executeUpdate("delete from snmp_trap_actions where id = " + id); ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT, ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "SNMP Trap Action Deleted", -1, -1); } catch (Exception ee) { logger.error("Exception while trying to delete from snmp_trap_actions : ", ee); } } private static void modifyTrapActionheuristics(TrapActionDescriptor heu) { try { int dataSetId = -1; String dataSetName = heu.getDataSet(); if (dataSetName == null || dataSetName.equals("")) { removeDataSet(heu.getId(), "snmp_trap_actions"); } else { dataSetId = getDataSetId(dataSetName); if (dataSetId == -1) { dataSetId = createDataSet(dataSetName, true); } updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction()); } StringBuffer sbf = new StringBuffer(); sbf.append("update snmp_trap_actions set "); sbf.append("title = '").append(heu.getTrapActionTitle()).append("'"); sbf.append(" , object_id = '").append(heu.getOID()).append("'"); sbf.append(" , datasetid = ").append(dataSetId); sbf.append(" where id = ").append(heu.getId()); logger.info("SNMP Trap Action Update query" + sbf.toString()); DBHelper.executeUpdate(sbf.toString()); ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT, ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "SNMP Trap Action Modified", -1, -1); } catch (Exception e) { logger.debug("Exception while Modifying SNMP TrapAction", e); } } private static void insertSyslogHeuristics(HeuristicDescriptor heu) { try { String dataSetName = heu.getDataSet(); int dataSetId = -1; if (dataSetName != null && !dataSetName.equals("")) { dataSetId = createDataSet(dataSetName, false); updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction()); } StringBuffer sbf = new StringBuffer(); sbf.append("insert into syslog_actions (title, description, operator, operand, dataSetId) values ("); sbf.append("'").append(heu.getSyslogTitle()).append("'"); if (heu.getDescription() != null) { sbf.append(",'").append(heu.getDescription()).append("'"); } else { sbf.append(",' '"); } sbf.append(",'").append(heu.getSyslogOperator()).append("'"); sbf.append(",'").append(heu.getSyslogOperand()).append("'"); sbf.append(",'").append(dataSetId).append("')"); logger.info("Syslog Insert query" + sbf.toString()); DBHelper.executeUpdate(sbf.toString()); ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT, ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "Syslog Pattern Added", -1, -1); } catch (Exception e) { logger.debug("Error while inserting into syslog heuristics", e); } } private static int getDataSetId(String dataSetName) { int dataSetId = -1; PreparedStatement ps = null; Connection c = null; ResultSet rs = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("select id from syslog_datasets where datasetname='" + dataSetName + "'"); rs = ps.executeQuery(); if (rs.next()) { dataSetId = rs.getInt("id"); } } catch (Exception ee) { logger.warn("Error while trying to get dataset id", ee); } finally { try { rs.close(); } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } try { ps.close(); } catch (Exception ee) { logger.error("Error while closing ps", ee); } DBHelper.releaseConnection(c); } return dataSetId; } private static int createDataSet(String dataSetName, boolean status) throws SQLException { int dataSetId = -1; DBHelper.executeUpdate( "insert into syslog_datasets (datasetname,status) values('" + dataSetName + "'," + status + ")"); dataSetId = getDataSetId(dataSetName); return dataSetId; } private static int removeDataSet(int syslogActionId, String parentTable) { int dataSetId = -1; PreparedStatement ps = null; Connection c = null; ResultSet rs = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("select datasetid from " + parentTable + " where id=" + syslogActionId); rs = ps.executeQuery(); if (rs.next()) { dataSetId = rs.getInt("datasetid"); } if (dataSetId != -1) { DBHelper.executeUpdate("delete from syslog_datasets where id = " + dataSetId); } } catch (Exception ee) { logger.warn("Error while trying to get dataset id", ee); } finally { try { rs.close(); } catch (Exception ex) { logger.debug(RESULTSET_CLOSE_ERROR, ex); } try { ps.close(); } catch (Exception ee) { logger.error("Error while closing ps", ee); } DBHelper.releaseConnection(c); } return dataSetId; } private static void updateSyslogActionsCommand(int dataSetId, Map<String, String> osCommandMap, String action) { if ("Modify".equals(action)) { try { DBHelper.executeUpdate("delete from syslog_commands where id = " + dataSetId); } catch (SQLException e) { logger.error("Exception while trying to delete syslog commands", e); } } Iterator<String> itr = osCommandMap.keySet().iterator(); while (itr.hasNext()) { String os = itr.next(); String command = osCommandMap.get(os); try { DBHelper.executeUpdate("insert into syslog_commands (id,os,command) values (" + dataSetId + ",'" + os + "','" + command + "')"); } catch (SQLException e) { logger.error("Exception while trying to insert syslog commands", e); } } } private static void insertTrapActionHeuristics(TrapActionDescriptor heu) { try { String dataSetName = heu.getDataSet(); int dataSetId = -1; if (dataSetName != null && !dataSetName.equals("")) { dataSetId = createDataSet(dataSetName, true); updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction()); } StringBuffer sbf = new StringBuffer(); sbf.append("insert into snmp_trap_actions (title,object_id,type,datasetid) values ("); sbf.append("'").append(heu.getTrapActionTitle()).append("'"); sbf.append(",'").append(heu.getOID()).append("'"); sbf.append(",'").append("User_Defined").append("'"); sbf.append(",'").append(dataSetId).append("')"); logger.info("Trap Action Insert query" + sbf.toString()); DBHelper.executeUpdate(sbf.toString()); ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT, ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "SNMP Trap Action Added", -1, -1); } catch (Exception e) { logger.debug("Exception while inserting into SNMP Trap Actions heuristics", e); } } public static void deleteSyslogHeuristics(int id) { try { removeDataSet(id, "syslog_actions"); DBHelper.executeUpdate("delete from syslog_actions where id = " + id); ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT, ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "Syslog Pattern Deleted", -1, -1); } catch (Exception ee) { logger.error("Exception while trying to delete from syslog_actions : ", ee); } } public static void modifySyslogHeuristics(HeuristicDescriptor heu) { try { int dataSetId = -1; String dataSetName = heu.getDataSet(); if (dataSetName == null || dataSetName.equals("")) { removeDataSet(heu.getId(), "syslog_actions"); } else { dataSetId = getDataSetId(dataSetName); if (dataSetId == -1) { dataSetId = createDataSet(dataSetName, false); } updateSyslogActionsCommand(dataSetId, heu.getOsCommandMap(), heu.getAction()); } StringBuffer sbf = new StringBuffer(); sbf.append("update syslog_actions set "); sbf.append("title = '").append(heu.getSyslogTitle()).append("'"); if (heu.getDescription() != null) { sbf.append(" , description = '").append(heu.getDescription()).append("'"); } else { sbf.append(" , description = ' '"); } sbf.append(" , operator = '").append(heu.getSyslogOperator()).append("'"); sbf.append(" , operand = '").append(heu.getSyslogOperand()).append("'"); sbf.append(" , datasetid = ").append(dataSetId); sbf.append(" where id = ").append(heu.getId()); logger.info("Syslog Update query" + sbf.toString()); DBHelper.executeUpdate(sbf.toString()); ServerAuditLog.getInstance().logAudit(NmIfImpl.getLogin(), ServerAuditConstants.SERVER_MANAGEMENT, ServerAuditConstants.DEVICE_MANAGEMENT_INVENTORY, "Syslog Pattern Modified", -1, -1); } catch (Exception e) { logger.debug("Exception while Modifying Syslog", e); } } public static void deleteSnmpMib(int deviceId) { String delQuery = DBHelperConstants.DELETE_SNMP_MIB_QUERY; Connection con = null; PreparedStatement ps = null; try { con = DBHelper.getConnection(); ps = con.prepareStatement(delQuery); ps.setInt(1, deviceId); ps.executeUpdate(); } catch (Exception ex) { logger.error("Error while deleting snmp data for device = " + deviceId, ex); } finally { try { ps.close(); } catch (SQLException ex) { logger.error("Error while closing ps", ex); } DBHelper.releaseConnection(con); } } public static void insertSnmpMib(int deviceId, String oid, String tableOid, String mib, String type) { String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY; Connection con = null; PreparedStatement ps = null; try { con = DBHelper.getConnection(); ps = con.prepareStatement(insQuery); ps.setInt(1, deviceId); ps.setString(2, oid); ps.setString(3, tableOid); // TODO: Do we need to compress data before storing? if (ps instanceof OraclePreparedStatement) { ((OraclePreparedStatement) ps).setStringForClob(4, mib); } else { logger.debug("PS is not OraclePreparedStatement, inserting as regular string"); ps.setString(4, mib); } ps.setString(5, type); ps.executeUpdate(); } catch (Exception ex) { logger.error("Error while inserting snmp data", ex); } finally { try { ps.close(); } catch (SQLException ex) { logger.error("Error while closing ps", ex); } DBHelper.releaseConnection(con); } } public static void insertSnmpColsInBatch(int deviceId, Map<String, ISnmpColumn> snmpCols, String type) { String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY; Connection con = null; PreparedStatement ps = null; try { con = DBHelper.getConnection(); ps = con.prepareStatement(insQuery); con.setAutoCommit(false); for (String colOid : snmpCols.keySet()) { // e.g. if colOid is .1.3.6.1.2.1.47.1.1.1.1.5.2, then entryOid // is .1.3.6.1.2.1.47.1.1.1.1.5 String entryOid = colOid.substring(0, colOid.lastIndexOf(".")); // and tableOid is .1.3.6.1.2.1.47.1.1.1.1 String tableOid = entryOid.substring(0, entryOid.lastIndexOf(".")); ISnmpColumn snmpCol = snmpCols.get(colOid); String colXml = snmpCol.toXml(); ps.setInt(1, deviceId); ps.setString(2, colOid); ps.setString(3, tableOid); ps.setString(6, snmpCol.getTitle()); // TODO: Do we need to compress data before storing? if (ps instanceof OraclePreparedStatement) { ((OraclePreparedStatement) ps).setStringForClob(4, colXml); } else { logger.debug("PS is not OraclePreparedStatement, inserting as regular string"); ps.setString(4, colXml); } ps.setString(5, type); ps.addBatch(); } ps.executeBatch(); con.commit(); } catch (SQLException sqlex) { logger.error("Error while inserting rows to database", sqlex); try { if (con != null) { con.rollback(); } } catch (SQLException ex) { logger.error("Error while calling rollback on db conn", ex); } } catch (Exception ex) { logger.error("Error while inserting snmp data in batch", ex); } finally { try { if (con != null) { con.setAutoCommit(true); } } catch (SQLException sqlex) { logger.error("Error while calling setAutoCommit", sqlex); } try { ps.close(); } catch (SQLException sqlex) { logger.error("Error while closing ps", sqlex); } DBHelper.releaseConnection(con); } } public static void insertSnmpScalarsInBatch(int deviceId, Map<String, ISnmpScalar> snmpScalars, String type) { String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY; Connection con = null; PreparedStatement ps = null; try { con = DBHelper.getConnection(); ps = con.prepareStatement(insQuery); con.setAutoCommit(false); for (String oid : snmpScalars.keySet()) { ISnmpScalar value = snmpScalars.get(oid); String xmlValue = value.toXml(); ps.setInt(1, deviceId); ps.setString(2, oid); // There's no table oid for scalar values ps.setString(3, null); ps.setString(6, null); // TODO: Do we need to compress data before storing? if (ps instanceof OraclePreparedStatement) { ((OraclePreparedStatement) ps).setStringForClob(4, xmlValue); } else { logger.debug("PS is not OraclePreparedStatement, inserting as regular string"); ps.setString(4, xmlValue); } ps.setString(5, type); ps.addBatch(); } ps.executeBatch(); con.commit(); } catch (SQLException sqlex) { logger.error("Error while inserting rows to database", sqlex); try { if (con != null) { con.rollback(); } } catch (SQLException ex) { logger.error("Error while calling rollback on db conn", ex); } } catch (Exception ex) { logger.error("Error while inserting snmp data in batch", ex); } finally { try { if (con != null) { con.setAutoCommit(true); } } catch (SQLException sqlex) { logger.error("Error while calling setAutoCommit", sqlex); } try { ps.close(); } catch (SQLException sqlex) { logger.error("Error while closing ps", sqlex); } DBHelper.releaseConnection(con); } } public static List<SnmpMibData> getSnmpByDevice(String deviceId) { String selQuery = DBHelperConstants.GET_SNMP_MIB_BY_DEVICE_QUERY; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; String colXml = null; List<SnmpMibData> snmpMibList = new ArrayList<>(); try { con = DBHelper.getConnection(); ps = con.prepareStatement(selQuery); ps.setInt(1, Integer.parseInt(deviceId)); rs = ps.executeQuery(); while (rs.next()) { SnmpMibData snmpMib = new SnmpMibData(); snmpMib.setDeviceId(deviceId); snmpMib.setMib(rs.getString("mib")); snmpMib.setOid(rs.getString("oid")); snmpMib.setTitle(rs.getString("title")); snmpMibList.add(snmpMib); } } catch (Exception ex) { logger.error("Exception while quering snmp mibs", ex); } finally { if (rs != null) { try { rs.close(); } catch (SQLException ex) { logger.error("Error while closing resultset", ex); } } if (ps != null) { try { ps.close(); } catch (SQLException ex) { logger.error("Error while closing ps", ex); } } DBHelper.releaseConnection(con); } return snmpMibList; } public static ISnmpColumn getSnmpColumn(String deviceId, String oid) { String selQuery = DBHelperConstants.GET_SNMP_MIB_QUERY; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; String colXml = null; ISnmpColumn snmpCol = null; try { con = DBHelper.getConnection(); ps = con.prepareStatement(selQuery); ps.setString(1, deviceId); ps.setString(2, oid); rs = ps.executeQuery(); if (rs.next()) { String mibType = rs.getString("type"); if (mibType != null && mibType.equals(MIBNodeType.TABLE.toString())) { colXml = rs.getString("mib"); if (colXml != null) { Document doc = XMLUtil.parseXMLDocument(colXml); snmpCol = new SnmpColumn(oid); snmpCol.load(doc.getDocumentElement()); } } } } catch (Exception ex) { logger.error("Exception while quering snmp mibs", ex); } finally { if (rs != null) { try { rs.close(); } catch (SQLException ex) { logger.error("Error while closing resultset", ex); } } if (ps != null) { try { ps.close(); } catch (SQLException ex) { logger.error("Error while closing ps", ex); } } DBHelper.releaseConnection(con); } return snmpCol; } public static ISnmpScalar getSnmpScalar(String deviceId, String oid) { String selQuery = DBHelperConstants.GET_SNMP_MIB_QUERY; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; String scalarXml = null; SnmpScalar scalar = null; try { con = DBHelper.getConnection(); ps = con.prepareStatement(selQuery); if (isPostgres()) { ps.setInt(1, Integer.valueOf(deviceId)); } else { ps.setString(1, deviceId); } ps.setString(2, oid); rs = ps.executeQuery(); if (rs.next()) { String mibType = rs.getString("type"); if (mibType != null && mibType.equals(MIBNodeType.SCALAR.toString())) { scalarXml = rs.getString("mib"); if (scalarXml != null) { Document doc = XMLUtil.parseXMLDocument(scalarXml); scalar = new SnmpScalar(0, ""); scalar.load(doc.getDocumentElement()); } } } } catch (Exception ex) { logger.error("Exception while quering snmp mibs", ex); } finally { if (rs != null) { try { rs.close(); } catch (SQLException ex) { logger.error("Error while closing resultset", ex); } } if (ps != null) { try { ps.close(); } catch (SQLException ex) { logger.error("Error while closing ps", ex); } } DBHelper.releaseConnection(con); } return scalar; } public static List<Integer> getRunConfId(String os, String model, String version) { ResultSet rs = null; List<Integer> NodeIds = new ArrayList<Integer>(); try { if (ServerProperties.getInstance().isPostgres()) { rs = DBHelper.executeQuery("select device_id from inventory_changes where os_name ~* '" + os + "'" + " and product_model ~* '" + model + "'" + " and os_version ~* '" + version + "'"); } else { rs = DBHelper.executeQuery("select device_id from inventory_changes where ((REGEXP_LIKE (os_name,'" + os + "', 'i'))" + " and (REGEXP_LIKE (product_model,'" + model + "', 'i'))" + " and (REGEXP_LIKE (os_version,'" + version + "', 'i')))"); } if (rs != null) { while (rs.next()) { NodeIds.add(rs.getInt("device_id")); } return NodeIds; } } catch (Exception e) { logger.warn("Error while getting the Node Ids ", e); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } } return null; } public static List<Integer> getStartUpIds(String os, String model, String version) { ResultSet rs = null; List<Integer> NodeIds = new ArrayList<Integer>(); try { rs = DBHelper.executeQuery("select device_id from inventory_changes where ((REGEXP_LIKE (os_name,'" + os + "', 'i'))" + " and (REGEXP_LIKE (product_model,'" + model + "', 'i'))" + "and (REGEXP_LIKE (os_version,'" + version + "', 'i')))"); if (rs != null) { while (rs.next()) { NodeIds.add(rs.getInt("device_id")); } return NodeIds; } } catch (Exception e) { logger.warn("Error while getting the Node Ids ", e); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } } return null; } public static void updatePCEInputs(int nodeId, IDSPDeviceInfoProvider device) { String wlcMaxAPSupported = device.getAttribute("WlcMaxAPSupported"); String smuNames = device.getAttribute("SMUNames"); String processorRev = device.getAttribute("ProcessorRev"); // Check if entry for node id already exists boolean update = false; ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from pce_input_data where device_id =" + nodeId); if (rs != null && rs.next()) { update = true; } } catch (Exception e) { } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } } String query = null; if (wlcMaxAPSupported != null) { try { if (update) { query = "update pce_input_data set WLC_MAX_AP_SUPPORTED = " + wlcMaxAPSupported + "where device_id = " + nodeId; } else { query = "insert into pce_input_data (device_id, WLC_MAX_AP_SUPPORTED) values (" + nodeId + "," + wlcMaxAPSupported + ")"; } DBHelper.executeUpdate(query); } catch (Exception e) { logger.error("Error while updating wlcMaxAPSupported..", e); } } if (smuNames != null) { try { if (update) { query = "update pce_input_data set iosxr_smu_names = '" + smuNames + "' where device_id = " + nodeId; } else { query = "insert into pce_input_data (device_id, iosxr_smu_names) values (" + nodeId + ",'" + smuNames + "')"; } DBHelper.executeUpdate(query); } catch (Exception e) { logger.error("Error while updating iosxr_smu_names..", e); } } if (processorRev != null) { try { if (update) { query = "update pce_input_data set processor_rev = " + processorRev + "where device_id = " + nodeId; } else { query = "insert into pce_input_data (device_id, processor_rev) values (" + nodeId + ",'" + processorRev + "')"; } DBHelper.executeUpdate(query); } catch (Exception e) { logger.error("Error while updating processorRev..", e); } } } public static int getCustomersCount(HashMap<String, Object> filterMap) { return PolicyDBHelper.getRecordCount("customers", filterMap); } public static List<Customer> loadCustomers(int start, int pageLength, HashMap<String, Object> filterMap) { ArrayList<Customer> lis = new ArrayList<Customer>(); ResultSet rs = null; ArrayList<Customer> updateCustomers = new ArrayList<Customer>(); try { StringBuffer sb = MgmtReportsPaginationUtil.getQuery(start, pageLength, "customers", filterMap); rs = DBHelper.executeQuery(sb.toString()); while (rs.next()) { Customer customer = new Customer(); int id = rs.getInt("customer_id"); customer.setId(id); String name = rs.getString("customer_name"); customer.setCustomerName(name); String contact_name = rs.getString("contact_name"); if (contact_name != null) { customer.setContactName(contact_name); } String contact_email = rs.getString("contact_email"); if (contact_email != null) { customer.setEmailAddresses(contact_email); } String contact_phone = rs.getString("contact_phone"); if (contact_phone != null) { customer.setContactPhoneNumber(contact_phone); } String wing_login = rs.getString("wing_login"); if (wing_login != null) { customer.setLogin(wing_login); } String securityKey = rs.getString("security_key"); if (securityKey != null) { customer.setSecurityKey(securityKey); } int noOfLicenses = rs.getInt("no_of_licenses"); int totLicUnits = LicenseValidator.getInstance().getNoOfDevices(); if (totLicUnits == -1) { noOfLicenses = -1; updateCustomers.add(customer); } else if (noOfLicenses > totLicUnits) { noOfLicenses = totLicUnits; updateCustomers.add(customer); } customer.setNoOfLicenses(noOfLicenses); int creatorId = rs.getInt("creator_id"); customer.setCreatorId(creatorId); String creatorName = rs.getString("creator_name"); UserDetails ud = UsersFactory.getUser(creatorId); if (ud != null) { customer.setCreaterName( ud.getName() != null ? (ud.getName() + " (" + ud.getLogin() + ")") : ud.getLogin()); } else { customer.setCreaterName(creatorName); } customer.setNoOfDevices(getCustomerDeviceCount(id)); lis.add(customer); } } catch (Exception ee) { logger.error("Error while loading customers from db.", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing cursor.", ee); } } if (updateCustomers.size() > 0) { for (Customer cus : updateCustomers) { insertOrUpdateCustomer(cus); } } return lis; } private static int getCustomerDeviceCount(int id) { int count = 0; ResultSet rs = null; try { String query = "Select count(device_id) as device_count from customer_device where customer_id=" + id + " and device_id not in (select mod_id from nodes_modules)"; rs = DBHelper.executeQuery(query); while (rs.next()) { count = rs.getInt("device_count"); } } catch (Exception ee) { logger.error("Error while getting device count associated with customer from db.", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing cursor.", ee); } } return count; } /** * (1)To find any pas job's status as Running, if new pas job schedules as run now then job id passes as zero or * negative(as it checks before creation of job and no job id). (2)Any scheduled pas job comes to start, it should * be confirmed no other pas job started or running other than this scheduled pas job. So this case job id(greater * than zero) of scheduled job need to be passed to separate this job's run state from other pas job. * * @param jobId * - job id * @return */ public static boolean findAnyPasJobRunning(int jobId) { boolean running = false; ResultSet rs = null; String query = null; try { if (jobId <= 0) { query = "SELECT JOB_ID, JOB_NAME, JOB_GROUP FROM pari_job_addl_details WHERE (JOB_GROUP = 'PasProfilingJobGroup' OR JOB_GROUP = 'PasImportJobGroup') AND JOB_ID IN (SELECT jobid from pari_job_runs where job_run_state = 'Running')"; } else { query = "SELECT JOB_ID, JOB_NAME, JOB_GROUP FROM pari_job_addl_details WHERE (JOB_GROUP = 'PasProfilingJobGroup' OR JOB_GROUP = 'PasImportJobGroup') AND JOB_ID IN (SELECT jobid from pari_job_runs where jobid !=" + jobId + " and job_run_state = 'Running')"; } logger.debug("isPasJobRunning query check : " + query); rs = DBHelper.executeQuery(query); while (rs.next()) { running = true; logger.debug("Currently running PAS job details given below :"); logger.debug("JOB_ID : " + rs.getInt("JOB_ID")); logger.debug("JOB_NAME : " + rs.getString("JOB_NAME")); logger.debug("JOB_GROUP : " + rs.getString("JOB_GROUP")); } } catch (Exception ee) { logger.error("exception caught while executing findAnyPasJobRunning!", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.error("Error while closing cursor.[findAnyPasJobRunning]", ee); } } return running; } public static List<Integer> getConfigNodeId(String os, String model, String version) { ResultSet rs = null; List<Integer> NodeIds = new ArrayList<Integer>(); try { String query = getQueryForSearchId(os, model, version); rs = DBHelper.executeQuery(query); if (rs != null) { while (rs.next()) { NodeIds.add(rs.getInt("device_id")); } return NodeIds; } } catch (Exception e) { logger.warn("Error while getting the Node Ids ", e); } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } } return null; } private static String getQueryForSearchId(String os, String model, String version) { StringBuffer query = new StringBuffer(); StringBuffer osQuery = null; query = query.append(" select device_id from inventory_changes where ("); if (os != null) { osQuery = new StringBuffer(); osQuery.append(" (os_name IN (" + os + ") )"); } if (model != null) { if (osQuery != null) { query.append(osQuery).append(" and ").append("(product_model IN(" + model + ")) and "); } else { query.append("(product_model IN(" + model + ")) and "); } } else if (os != null) { query.append(osQuery).append(" and "); } if (isPostgres()) { version = version.replace("*", "%"); query.append("(LIKE (os_version,'" + version + "'))"); } else { query.append("(REGEXP_LIKE (os_version,'" + version + "', 'i'))"); } query.append(")"); return query.toString(); } public static String[] retrieveCustomerSpecificEmails(int customerId) { String[] emails = new String[0]; ResultSet rs = null; try { rs = DBHelper.executeQuery("select * from customers where customer_id=" + customerId); if ((rs != null) && rs.next()) { String emailStr = rs.getString("contact_email"); if (!(emailStr == null || "".equals(emailStr))) { emails = emailStr.split(","); } } } catch (Exception e) { logger.error("Exception while getting customer related emails", e); } finally { try { rs.close(); } catch (Exception e) { logger.error("Exception while closing the result set", e); } } return emails; } public static boolean retrieveMailNotificationOptionForFaults(String fltCondition) { boolean isSendMail = false; ResultSet rs = null; try { rs = DBHelper .executeQuery("select NOTIFICATION_ENABLED from fault_notification_prefs where FLT_CONDITION='" + fltCondition + "'"); if ((rs != null) && rs.next()) { isSendMail = rs.getBoolean("NOTIFICATION_ENABLED"); } } catch (Exception ex) { logger.error("Exception while getting email notification option", ex); } finally { try { rs.close(); } catch (Exception ee) { logger.error("Exception while closing the result set", ee); } } return isSendMail; } public static String[] getEmailListFromSMTPSettings() { String[] emails = new String[0]; ResultSet rs = null; try { rs = DBHelper.executeQuery("select emails from smtp_settings"); if ((rs != null) && rs.next()) { String mailList = rs.getString("emails"); if ((mailList != null) && (mailList.trim().length() > 0)) { emails = mailList.split(","); } } } catch (Exception ex) { logger.error("Exception while getting email addresses", ex); } finally { try { rs.close(); } catch (Exception ee) { logger.error("Exception while closing the result set", ee); } } return emails; } public static int getCustomerIdUsingFaultSubModule(String profileName) { int customerId = -1; ResultSet rs = null; try { rs = DBHelper .executeQuery("select customer_id from policy_profiles where prof_name='" + profileName + "'"); if ((rs != null) && rs.next()) { customerId = rs.getInt("customer_id"); } } catch (Exception ex) { logger.error("Exception while getting customer id", ex); } finally { try { rs.close(); } catch (Exception ee) { logger.error("Exception while closing the result set", ee); } } return customerId; } public static void insertOrUpdateCustomerFaults(Customer customer) { Connection c = DBHelper.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { boolean insert = true; String query = "select * from customer_fault where customer_id=" + customer.getId(); rs = DBHelper.executeQuery(query); if (rs.next()) { insert = false; } List<CustomerFault> faultList = customer.getFaultList(); if (faultList != null && faultList.size() != 0) { if (!insert) { ps = c.prepareStatement(DBHelperConstants.CUSTOMER_FAULT_DELETE); ps.setInt(1, customer.getId()); ps.executeUpdate(); // FaultSpecFactory.getInstance().deleteFaultProfileOnly(customer.getId()); } for (CustomerFault fault : faultList) { ps = c.prepareStatement(DBHelperConstants.CUSTOMER_FAULT_INSERT); ps.setInt(1, customer.getId()); ps.setString(2, fault.getName()); ps.setString(3, fault.getDescription()); ps.setString(4, fault.getSeverity()); ps.setInt(5, fault.getSendMail()); ps.executeUpdate(); } } else if ((faultList == null || faultList.size() == 0) && !insert) { ps = c.prepareStatement(DBHelperConstants.CUSTOMER_FAULT_DELETE); ps.setInt(1, customer.getId()); ps.executeUpdate(); // FaultSpecFactory.getInstance().deleteFaultProfileOnly(customer.getId()); } } catch (Exception ee) { logger.warn("Error while inserting or updating the customer faults", ee); } finally { try { ps.close(); } catch (Exception ee) { logger.error("Error while closing statement for updating customer faults", ee); } try { DBHelper.releaseConnection(c); } catch (Exception ee) { logger.error("Error while closing connection for updating customer faults", ee); } try { rs.close(); } catch (Exception ee) { logger.error("Error while closing resultset for updating customer faults", ee); } } } public static void loadCustomerFaultNotificationSettings() { logger.debug("Loading Customer Specific Faults"); ResultSet rs = null; try { rs = DBHelper.executeQuery(DBHelperConstants.READ_CUSTOMER_FAULT); FaultSpec cusSpec; while (rs.next()) { int profId = rs.getInt("CUSTOMER_ID"); String cusfltCondition = rs.getString("NAME"); String cusfltSeverity = rs.getString("SEVERITY"); int enabled = rs.getInt("SENDMAIL"); FaultProfile fp = FaultSpecFactory.getInstance().getFaultProfile(profId); if (fp == null) { continue; } fp.enableMailNotification(cusfltCondition, (enabled == 1)); fp.addFaultCondition(cusfltCondition, cusfltSeverity); cusSpec = fp.getFaultSpec(cusfltCondition); if (cusSpec == null) { FaultSpec spe = FaultSpecFactory.getInstance().getFaultSpec(cusfltCondition); cusSpec = new FaultSpec(spe); fp.addFautlSpec(cusfltCondition, cusSpec); FaultSpecFactory.getInstance().updateSpecToProfile(cusfltCondition, fp.getProfId()); } cusSpec.setSeverity(FaultSeverity.getByName(cusfltSeverity)); FaultProfile[] prf = FaultSpecFactory.getInstance().getAllProfiles(); } } catch (Exception ee) { ee.printStackTrace(); } finally { try { rs.close(); } catch (Exception ee) { } } } public static List<CustomerFault> getCustomerFaults(int customerId) { Connection c = DBHelper.getConnection(); ResultSet rs = null; List<CustomerFault> faultList = null; try { faultList = new LinkedList<CustomerFault>(); String query = "select * from customer_fault where customer_id=" + customerId; rs = DBHelper.executeQuery(query); if (rs != null) { while (rs.next()) { String name = rs.getString("name"); String description = rs.getString("description"); String severity = rs.getString("severity"); int sendMail = rs.getInt("sendmail"); CustomerFault fault = new CustomerFault(name, description, severity, sendMail); faultList.add(fault); } } } catch (Exception ee) { logger.warn("Error while getting the customer faults", ee); } finally { try { DBHelper.releaseConnection(c); } catch (Exception ee) { logger.error("Error while releasing connection for customer faults", ee); } try { rs.close(); } catch (Exception ee) { logger.error("Error while releasing resultset for customer faults", ee); } } return faultList; } public static Integer getNextTerminalGatewaySessionId() throws Exception { Connection c = DBHelper.getConnection(); try { return DBHelper.getNextSequence(c, "terminal_gateway_session_id"); } catch (Exception e) { logger.warn("Unable to create terminal gateway session id - " + e.getMessage()); throw e; } finally { DBHelper.releaseConnection(c); } } public static void createTerminalGatewaySessionEntry(Integer connectionId, int user_id, int customerId, String deviceIp, String deviceName, long startTime, String protocol) { logger.debug("Creating tg session entry for customerId:" + customerId + ", deviceIp:" + deviceIp); PreparedStatement ps = null; Connection c = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement( "insert into terminal_gateway_sessions (SESSION_ID, CREATOR_ID, CUSTOMER_ID, HOST_IP, HOST_NAME, START_TIME, PROTOCOL) VALUES (?,?,?,?,?,?,?)"); ps.setInt(1, connectionId); ps.setInt(2, user_id); ps.setInt(3, customerId); ps.setString(4, deviceIp); ps.setString(5, deviceName); ps.setTimestamp(6, new Timestamp(startTime)); ps.setString(7, protocol); ps.executeUpdate(); } catch (Exception ex) { logger.error("Exception while inserting terminal gateway session for node: " + deviceIp, ex); } finally { DBHelper.closeAll(c, ps, null); } } public static void storeTerminalGatewaySessionData(Integer connectionId, String data) { if (data == null || data.isEmpty()) { return; } ResultSet rs = null; String oldData = null; try { rs = DBHelper.executeQuery( "select SESION_DATA from terminal_gateway_sessions where SESSION_ID=" + connectionId); if (rs != null) { if (rs.next()) { oldData = rs.getString(1); } } } catch (Exception e) { logger.warn("Error loading terminal session data - " + e.getMessage(), e); } finally { DBHelper.close(rs); } if (oldData != null) { oldData = new StringBuffer(oldData).append(data).toString(); } else { oldData = data; } PreparedStatement ps = null; Connection c = null; c = DBHelper.getConnection(); try { ps = c.prepareStatement( "update terminal_gateway_sessions SET SESION_DATA=? where SESSION_ID=" + connectionId); if (ps instanceof OraclePreparedStatement) { ((OraclePreparedStatement) ps).setClob(1, new InputStreamReader(IOUtils.toInputStream(oldData, "UTF-8"))); } else { ps.setString(1, oldData); } ps.executeUpdate(); } catch (Exception e) { logger.warn("Error with updating terminals session data - " + e.getMessage(), e); } finally { DBHelper.closeAll(c, ps, null); } } public static String getTerminalGatewaySessionConfig(int sessionId) { String selectQuery = "SELECT SESION_DATA FROM TERMINAL_GATEWAY_SESSIONS WHERE SESSION_ID =" + sessionId + ""; ResultSet rs = null; String configChanges = null; try { rs = DBHelper.executeQuery(selectQuery); if (rs != null) { if (rs.next()) { configChanges = rs.getString("SESION_DATA"); } } } catch (SQLException e) { logger.warn("Error with retriving terminals session data - " + e.getMessage(), e); } finally { DBHelper.close(rs); } return configChanges; } public static void endTerminalGatewaySession(int id) { PreparedStatement ps = null; Connection c = null; c = DBHelper.getConnection(); try { ps = c.prepareStatement("update terminal_gateway_sessions SET END_TIME=? where SESSION_ID =" + id); ps.setTimestamp(1, new Timestamp(System.currentTimeMillis())); ps.executeUpdate(); } catch (Exception e) { logger.warn("Error with updating terminals session end time - " + e.getMessage(), e); } finally { DBHelper.closeAll(c, ps, null); } } public static String[] getCustomerShowCommands(String customer) { logger.info("Inside getCustomerShowCommands method"); ArrayList<String> showCommands = new ArrayList<String>(); PreparedStatement ps = null; Connection c = null; c = DBHelper.getConnection(); ResultSet rs = null; try { ps = c.prepareStatement( "select distinct cli from show_commands_new where device_id in (select distinct a.device_id from customer_device a,customers b where a.customer_id = b.customer_id and b.customer_name = ? )"); ps.setString(1, customer); rs = ps.executeQuery(); while (rs.next()) { showCommands.add(rs.getString("cli")); } } catch (Exception ee) { logger.error("Exception while getting available CLIs for device: " + customer, ee); } finally { try { DBHelper.closeAll(c, ps, null); } catch (Exception e) { } } String[] cmds = new String[showCommands.size()]; showCommands.toArray(cmds); return cmds; } public static boolean isPostgres() { return ServerProperties.getInstance().isPostgres(); } public static void updateJobDevicesDetails(int jobId, String devStr) { Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement("UPDATE pari_job_addl_details set devices=? where job_id=?"); ps.setString(1, devStr); ps.setInt(2, jobId); ps.executeUpdate(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { ps.close(); } catch (Exception ex) { } DBHelper.releaseConnection(c); } } public static boolean isJobRunning(int jobId) { boolean isJobInRunning = false; ResultSet rs = null; try { rs = DBHelper .executeQuery("select * from PARI_JOB_RUNS where JOB_RUN_STATE='Running' and JOBID=" + jobId); if ((rs != null) && rs.next()) { isJobInRunning = true; } } catch (Exception ee) { logger.debug("Error while checking if a job is running: ", jobId, ee); } finally { try { rs.close(); } catch (Exception ee) { } } return isJobInRunning; } public static Set<String> getDeviceShowRunCommands(List<Integer> selectedDevices) { ResultSet rs = null; ResultSet rs1 = null; Set<String> running_config_command = new HashSet<String>(); Set<Integer> dbDevices = new HashSet<Integer>(); try { rs = DBHelper.executeQuery("SELECT DISTINCT dev_id FROM running_config_command"); while (rs.next()) { int dev_id = rs.getInt("dev_id"); if (selectedDevices.contains(dev_id)) { dbDevices.add(dev_id); int index = selectedDevices.indexOf(dev_id); selectedDevices.remove(index); } } if (!dbDevices.isEmpty()) { StringBuilder queryBuilder = new StringBuilder( "SELECT DISTINCT CLI FROM running_config_command where dev_id IN ("); boolean isRemoveLastComma = false; for (Integer deviceId : dbDevices) { queryBuilder.append(deviceId + ","); isRemoveLastComma = true; } if (isRemoveLastComma) { queryBuilder.deleteCharAt(queryBuilder.length() - 1); } queryBuilder.append(")"); rs1 = DBHelper.executeQuery(queryBuilder.toString()); while (rs1.next()) { running_config_command.add(rs1.getString("CLI")); } } // get show run command by mapping of OS type for devices which is not found in running_config_command table // incase CP is not collected in NCCM side. for (Integer deviceId : selectedDevices) { NetworkNode nodeByID = NetworkNodeCache.getInstance().getNodeByID(deviceId); if (nodeByID != null) { String command = getCommandForRunningConfig( nodeByID.getVersion().getOsTypeDetails().getShortDesc()); if (command != null && !command.isEmpty()) { running_config_command.add(command); } else { running_config_command.add("show running-config"); } } } } catch (Exception ee) { logger.debug("Error while getting running config Command Of Devices", ee); } finally { DBHelper.close(rs); DBHelper.close(rs1); } return running_config_command; } }