org.opennms.provisiond.utils.CsvRequisitionParser.java Source code

Java tutorial

Introduction

Here is the source code for org.opennms.provisiond.utils.CsvRequisitionParser.java

Source

/*******************************************************************************
 * This file is part of OpenNMS(R).
 *
 * Copyright (C) 2010-2014 The OpenNMS Group, Inc.
 * OpenNMS(R) is Copyright (C) 1999-2014 The OpenNMS Group, Inc.
 *
 * OpenNMS(R) is a registered trademark of The OpenNMS Group, Inc.
 *
 * OpenNMS(R) is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published
 * by the Free Software Foundation, either version 3 of the License,
 * or (at your option) any later version.
 *
 * OpenNMS(R) is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with OpenNMS(R).  If not, see:
 *      http://www.gnu.org/licenses/
 *
 * For more information contact:
 *     OpenNMS(R) Licensing <license@opennms.org>
 *     http://www.opennms.org/
 *     http://www.opennms.com/
 *******************************************************************************/

package org.opennms.provisiond.utils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.dbcp.PoolingConnection;
import org.opennms.netmgt.model.PrimaryType;
import org.opennms.netmgt.provision.persist.FilesystemForeignSourceRepository;
import org.opennms.netmgt.provision.persist.requisition.Requisition;
import org.opennms.netmgt.provision.persist.requisition.RequisitionCategory;
import org.opennms.netmgt.provision.persist.requisition.RequisitionCategoryCollection;
import org.opennms.netmgt.provision.persist.requisition.RequisitionInterface;
import org.opennms.netmgt.provision.persist.requisition.RequisitionInterfaceCollection;
import org.opennms.netmgt.provision.persist.requisition.RequisitionMonitoredService;
import org.opennms.netmgt.provision.persist.requisition.RequisitionMonitoredServiceCollection;
import org.opennms.netmgt.provision.persist.requisition.RequisitionNode;

public class CsvRequisitionParser {

    private static final String PROPERTY_FS_REPO_PATH = "fs.repo.path";
    private static final String PROPERTY_CSV_FILE = "csv.file";
    private static final String PROPERTY_FOREIGN_SOURCE = "foreign.source";
    private static final String PROPERTY_RESOLVE_IPS = "resolve.ips";
    private static final String PROPERTY_PARSE_DB = "parse.db";
    private static final String PROPERTY_IPLIKE_QUERY = "iplike.query";
    private static final String PROPERTY_DB_SVR = "db.server";
    private static final String PROPERTY_DB_NAME = "db.name";
    private static final String PROPERTY_DB_USER = "db.user";
    private static final String PROPERTY_DB_PW = "db.password";
    private static final String PROPERTY_USE_NODE_ID = "use.nodeid";
    private static final String PROPERTY_CATEGORY_LIST = "category.list";
    private static final String PROPERTY_CATEGORY_ADD_EXISTING = "category.add.existing";
    private static final String PROPERTY_SERVICE_LIST = "service.list";
    private static final String PROPERTY_ADD_ONLY = "add.only";

    private static FilesystemForeignSourceRepository m_fsr = null;
    private static File m_csvFile = new File("/tmp/nodes.csv");
    private static File m_repoPath = new File("/opt/opennms/imports");
    private static String m_foreignSource = "default";
    private static Boolean m_resolveIps = false;
    private static Boolean m_parseDb = false;
    private static String m_iplikeQuery = "*.*.*.*";
    private static String m_dbSvr = "127.0.0.1";
    private static String m_dbName = "opennms";
    private static String m_dbUser = "opennms";
    private static String m_dbPass = "opennms";
    private static Boolean m_useNodeId = false;
    private static List<String> m_categoryList = new ArrayList<String>();
    private static Boolean m_categoryAddExisting = true;
    private static List<String> m_serviceList = new ArrayList<String>();
    private static Boolean m_addOnly = true;

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Runtime.getRuntime().addShutdownHook(createShutdownHook());

        if (args.length > 0) {
            try {
                usageReport();
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.exit(0);
        }

        try {
            if (!validateProperties()) {
                usageReport();
                System.exit(-1);
            }
        } catch (IOException e) {
            e.printStackTrace();
            System.exit(1);
        }

        if (System.getProperty("parse.db") != null) {
            try {
                migrateDbNodes();
            } catch (UnknownHostException e) {
                e.printStackTrace();
            }
        } else {
            try {
                parseCsv(m_csvFile, m_repoPath);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        System.out.println("Finished.");
    }

    private static void migrateDbNodes() throws SQLException, UnknownHostException, ClassNotFoundException {

        String distinctNodesQueryStr = "  " + "SELECT nodeId AS \"nodeid\"," + "       nodeLabel AS \"nodelabel\","
                + "       foreignSource AS \"foreignsource\"," + "       foreignId AS \"foreignid\" "
                + "  FROM node " + " WHERE nodeid in (" + "  SELECT " + "DISTINCT nodeid " + "    FROM ipinterface "
                + "   WHERE iplike(ipaddr, '" + m_iplikeQuery + "')) " + "ORDER BY nodeid";

        if (m_addOnly) {
            distinctNodesQueryStr = "  " + "SELECT nodeId AS \"nodeid\"," + "       nodeLabel AS \"nodelabel\","
                    + "       foreignSource AS \"foreignsource\"," + "       foreignId AS \"foreignid\" "
                    + "  FROM node " + " WHERE nodeid in (" + "  SELECT " + "DISTINCT nodeid "
                    + "    FROM ipinterface " + "   WHERE iplike(ipaddr, '" + m_iplikeQuery + "')) "
                    + "  AND foreignsource is NULL " + "ORDER BY nodeid";
        }

        Connection connection = null;
        Statement distinctNodesStatement = null;
        PoolingConnection pool = null;
        connection = createConnection();
        connection.setAutoCommit(false);
        pool = new PoolingConnection(connection);
        distinctNodesStatement = pool.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        ResultSet distinctNodesResultSet = null;
        int rowsFound = 0;
        distinctNodesResultSet = distinctNodesStatement.executeQuery(distinctNodesQueryStr);
        distinctNodesResultSet.last();
        rowsFound = distinctNodesResultSet.getRow();
        distinctNodesResultSet.beforeFirst();

        System.out.println(rowsFound + " nodes found.");

        int nodesMigrated = 0;
        while (distinctNodesResultSet.next()) {
            System.out.println("Processing row: " + distinctNodesResultSet.getRow() + "...");

            int nodeId = distinctNodesResultSet.getInt("nodeid");
            String queryStr = "" + "  SELECT ipaddr " + "    FROM ipinterface " + "   WHERE nodeid = " + nodeId
                    + " " + "     AND issnmpprimary = 'P' " + "ORDER BY inet(ipaddr)" + "   LIMIT 1";

            Statement findPrimaryStatement = pool.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            System.out.println("Querying DB for SNMP Primary interface for node: " + nodeId + "...");
            ResultSet findPrimaryResultSet = findPrimaryStatement.executeQuery(queryStr);

            String primaryIp = null;

            if (findPrimaryResultSet.next()) {
                primaryIp = findPrimaryResultSet.getString("ipaddr");
                System.out.println("SNMP Primary found: " + primaryIp);
            }

            findPrimaryResultSet.close();
            findPrimaryStatement.close();

            if (primaryIp == null) {
                System.out.println("SNMP Primary not found.  Determining lowest numbered IP to set as Primary...");
                queryStr = "" + "  SELECT ipaddr " + "    FROM ipinterface " + "   WHERE nodeid = " + nodeId + " "
                        + "ORDER BY inet(ipaddr)" + "   LIMIT 1";
                findPrimaryStatement = pool.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                findPrimaryResultSet = findPrimaryStatement.executeQuery(queryStr);
            }

            if (primaryIp == null && findPrimaryResultSet.next()) {
                primaryIp = findPrimaryResultSet.getString("ipaddr");
                System.out.println("SNMP Primary found: " + primaryIp);
            }

            findPrimaryResultSet.close();
            findPrimaryStatement.close();

            if (primaryIp == null) {
                System.out.println(
                        "SNMP Primary not found.  Skipping node.  (This should never happen since it is the iplike query that finds the distinct nodes :( )");
                continue;
            }

            String foreignId = null;
            if (m_useNodeId) {
                foreignId = String.valueOf(nodeId);
            } else {
                foreignId = String.valueOf(System.currentTimeMillis());
            }

            String label = distinctNodesResultSet.getString("nodelabel");
            distinctNodesResultSet.updateString("foreignsource", m_foreignSource);
            distinctNodesResultSet.updateString("foreignId", foreignId);

            System.out.println("Updating node (" + nodeId + ":" + label + ") with foreignsource:" + m_foreignSource
                    + " and foreignId:" + foreignId);
            distinctNodesResultSet.updateRow();
            System.out.println("Node updated.");

            RequisitionData rd = new RequisitionData(label, primaryIp, m_foreignSource, foreignId);

            if (m_categoryAddExisting) {
                String categoriesQueryString = "" + "SELECT c.categoryname as \"categoryname\" "
                        + "  FROM categories c " + "  JOIN category_node cn "
                        + "    ON cn.categoryid = c.categoryid " + "  JOIN node n on n.nodeid = cn.nodeid "
                        + " WHERE n.nodeid = " + nodeId;
                Statement categoriesStatement = pool.createStatement();

                ResultSet crs = categoriesStatement.executeQuery(categoriesQueryString);

                Set<String> categories = new LinkedHashSet<String>();
                while (crs.next()) {
                    categories.add(crs.getString("categoryname"));
                }

                crs.close();
                categoriesStatement.close();
                rd.setCategories(categories);
            }

            System.out.println("Updating requistion...");
            createOrUpdateRequistion(rd);
            System.out.println("Requistion updated!  Next...\n");
            nodesMigrated++;
        }

        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            connection.rollback();
        }

        distinctNodesResultSet.close();
        distinctNodesStatement.close();
        pool.close();
        connection.close();

        System.out.println(nodesMigrated + " Nodes migrated to foreign source " + m_foreignSource);

    }

    private static Connection createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        Connection connection = DriverManager.getConnection("jdbc:postgresql://" + m_dbSvr + ":5432/" + m_dbName,
                m_dbUser, m_dbPass);
        return connection;
    }

    //need to do some better exception handling here
    private static boolean validateProperties()
            throws IOException, FileNotFoundException, IllegalArgumentException {

        System.out.println("\n\nProperties:\n");
        m_foreignSource = System.getProperty(PROPERTY_FOREIGN_SOURCE, m_foreignSource);
        System.out.println("\t" + PROPERTY_FOREIGN_SOURCE + ":" + m_foreignSource);

        m_parseDb = Boolean.valueOf(System.getProperty(PROPERTY_PARSE_DB, m_parseDb.toString()));
        System.out.println("\t" + PROPERTY_PARSE_DB + ":" + m_parseDb);

        if (!m_parseDb.booleanValue()) {

            String csvFileName = System.getProperty(PROPERTY_CSV_FILE, m_csvFile.getCanonicalPath());
            System.out.println("\t" + PROPERTY_CSV_FILE + ":" + m_csvFile);

            m_csvFile = new File(csvFileName);
            if (!m_csvFile.exists()) {
                throw new FileNotFoundException("CSV Input File: " + csvFileName + "; Not Found!");
            }
        } else {
            m_dbSvr = System.getProperty(PROPERTY_DB_SVR, m_dbSvr);
            System.out.println("\t" + PROPERTY_DB_SVR + ":" + m_dbSvr);

            m_dbName = System.getProperty(PROPERTY_DB_NAME, m_dbName);
            System.out.println("\t" + PROPERTY_DB_NAME + ":" + m_dbName);

            m_dbUser = System.getProperty(PROPERTY_DB_USER, m_dbUser);
            System.out.println("\t" + PROPERTY_DB_USER + ":" + m_dbUser);

            m_dbPass = System.getProperty(PROPERTY_DB_PW, m_dbPass);
            System.out.println("\t" + PROPERTY_DB_PW + ":" + m_dbPass);

            m_iplikeQuery = System.getProperty(PROPERTY_IPLIKE_QUERY, m_iplikeQuery);
            System.out.println("\t" + PROPERTY_IPLIKE_QUERY + ":" + m_iplikeQuery);

            m_useNodeId = Boolean.valueOf(System.getProperty(PROPERTY_USE_NODE_ID, m_useNodeId.toString()));
            System.out.println("\t" + PROPERTY_USE_NODE_ID + ":" + m_useNodeId);

            m_addOnly = Boolean.valueOf(System.getProperty(PROPERTY_ADD_ONLY, m_addOnly.toString()));
            System.out.println("\t" + PROPERTY_ADD_ONLY + ":" + m_addOnly);

            m_categoryAddExisting = Boolean
                    .valueOf(System.getProperty(PROPERTY_CATEGORY_ADD_EXISTING, m_categoryAddExisting.toString()));
            System.out.println("\t" + PROPERTY_CATEGORY_ADD_EXISTING + ":" + m_categoryAddExisting);

        }

        String fsRepo = System.getProperty(PROPERTY_FS_REPO_PATH, m_repoPath.getCanonicalPath());
        System.out.println("\t" + PROPERTY_FS_REPO_PATH + ":" + fsRepo);

        m_repoPath = new File(fsRepo);

        if (!m_repoPath.exists() || !m_repoPath.isDirectory() || !m_repoPath.canWrite()) {
            throw new IllegalArgumentException(
                    "The specified " + PROPERTY_FS_REPO_PATH + ": " + m_repoPath.getCanonicalPath()
                            + ", either doesn't exist, isn't writable, or isn't a directory.");
        } else {
            m_fsr = new FilesystemForeignSourceRepository();
            m_fsr.setRequisitionPath(m_repoPath.getCanonicalPath());
        }

        m_resolveIps = Boolean.valueOf(System.getProperty(PROPERTY_RESOLVE_IPS, m_resolveIps.toString()));
        System.out.println("\t" + PROPERTY_RESOLVE_IPS + ":" + m_resolveIps.toString());

        String categories = System.getProperty(PROPERTY_CATEGORY_LIST);
        if (categories != null) {
            System.out.println("\t" + PROPERTY_CATEGORY_LIST + ":" + categories);
            String[] cats = categories.split(",");
            for (String cat : cats) {
                m_categoryList.add(cat);
            }
        }

        String services = System.getProperty(PROPERTY_SERVICE_LIST);
        if (services != null) {
            System.out.println("\t" + PROPERTY_SERVICE_LIST + ":" + services);
            String[] srvs = services.split(",");
            for (String srv : srvs) {
                m_serviceList.add(srv);
            }
        }

        System.out.println("\n");
        return true;
    }

    private static void usageReport() throws IOException {
        System.err.println("Usage: java CsvRequistionParser [<Property>...]\n" + "\n" + "Supported Properties:\n"
                + "\t" + PROPERTY_CSV_FILE + ": default:" + m_csvFile.getCanonicalPath() + "\n" + "\t"
                + PROPERTY_FS_REPO_PATH + ": default:" + m_repoPath.getCanonicalPath() + "\n" + "\t"
                + PROPERTY_FOREIGN_SOURCE + ": default:" + m_foreignSource + "\n" + "\t" + PROPERTY_RESOLVE_IPS
                + ": default:" + m_foreignSource + "\n" + "\t" + PROPERTY_PARSE_DB + ": default:" + m_parseDb + "\n"
                + "\t" + PROPERTY_DB_SVR + ": default:" + m_dbSvr + "\n" + "\t" + PROPERTY_DB_NAME + ": default:"
                + m_dbName + "\n" + "\t" + PROPERTY_DB_USER + ": default:" + m_dbUser + "\n" + "\t" + PROPERTY_DB_PW
                + ": default:" + m_dbPass + "\n" + "\t" + PROPERTY_IPLIKE_QUERY + ": default:" + m_iplikeQuery
                + "\n" + "\t" + PROPERTY_USE_NODE_ID + ": default:" + m_useNodeId + "\n" + "\t"
                + PROPERTY_CATEGORY_LIST + ": default:null" + "\n" + "\t" + PROPERTY_CATEGORY_ADD_EXISTING
                + ":default:" + m_categoryAddExisting + "\n" + "\t" + PROPERTY_SERVICE_LIST + ": default:null"
                + "\n" + "\t" + PROPERTY_ADD_ONLY + ": default:" + m_addOnly + "\n" + "\n" + "\n" + "Example:\n"
                + "\t java -D" + PROPERTY_CSV_FILE + "=/tmp/mynodes.csv \\\n" + "\t\t-D" + PROPERTY_FS_REPO_PATH
                + "= /opt/opennms/etc/imports \\\n" + "\t\t-D" + PROPERTY_FOREIGN_SOURCE + "=default \\\n"
                + "\t\t-D" + PROPERTY_RESOLVE_IPS + "=false \\\n" + "\t\t-D" + PROPERTY_PARSE_DB + "=false \\\n"
                + "\t\t-D" + PROPERTY_DB_SVR + "=localhost \\\n" + "\t\t-D" + PROPERTY_DB_NAME + "=opennms \\\n"
                + "\t\t-D" + PROPERTY_DB_USER + "=opennms \\\n" + "\t\t-D" + PROPERTY_DB_PW + "=opennms \\\n"
                + "\t\t-D" + PROPERTY_IPLIKE_QUERY + "=\"*.*.*.*\" \\\n" + "\t\t-D" + PROPERTY_USE_NODE_ID
                + "=false \\\n" + "\t\t-D" + PROPERTY_CATEGORY_LIST + "=Production,Router \\\n" + "\t\t-D"
                + PROPERTY_CATEGORY_ADD_EXISTING + "=true \\\n" + "\t\t-D" + PROPERTY_SERVICE_LIST
                + "=ICMP,SNMP \\\n" + "\t\t-D" + PROPERTY_ADD_ONLY + "=false \\\n"
                + "\t\t-jar opennms-csv-requisition-1.13.0-SNAPSHOT-jar-with-dependencies.jar" + "\n" + "\n"
                + "FYI: This application expects the csv file to have 2 elements, nodelabel and IP address.  Example:"
                + "\n\n" + "#nodelabel,ipaddress\n" + "www.opennms.com,64.146.64.212\n"
                + "github.com,204.232.175.90\n" + "www.juniper.net,2600:1406:1f:195::720\n\n");
    }

    protected static void parseCsv(File csv, File m_repo) throws IOException {
        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(csv)));

        String line = null;
        int lineNum = 0;
        while ((line = br.readLine()) != null) {
            lineNum++;
            if (line != null && line.startsWith("#")) {
                continue;
            }

            String[] fields = line.split(",", 2);
            int fieldCount = fields.length;
            if (fieldCount != 2) {
                System.err.println("Error on line: " + Integer.toString(lineNum) + ". Found "
                        + Integer.toString(fieldCount) + " fields and expected 2.");
                continue;
            }

            RequisitionData rd = new RequisitionData(fields[0], fields[1], m_foreignSource, null);
            System.out.println("Line " + Integer.toString(lineNum) + ":" + rd.toString());

            createOrUpdateRequistion(rd);
        }
        br.close();
    }

    private static void createOrUpdateRequistion(RequisitionData rd) throws UnknownHostException {
        Requisition r = null;
        RequisitionNode rn = new RequisitionNode();
        String foreignSource = rd.getForeignSource();

        r = m_fsr.getRequisition(foreignSource);

        if (r == null) {
            r = new Requisition(foreignSource);
        }

        System.err.println("Creating/Updating requistion: " + foreignSource);

        r.updateDateStamp();

        RequisitionMonitoredServiceCollection services = new RequisitionMonitoredServiceCollection();
        for (String svc : m_serviceList) {
            services.add(new RequisitionMonitoredService(svc));
        }

        RequisitionInterface iface = new RequisitionInterface();
        iface.setDescr("mgmt-if");
        iface.setIpAddr(rd.getPrimaryIp());
        iface.setManaged(true);
        iface.setSnmpPrimary(PrimaryType.PRIMARY);
        iface.setStatus(Integer.valueOf(1));
        iface.setMonitoredServices(services.getObjects());

        RequisitionInterfaceCollection ric = new RequisitionInterfaceCollection();
        ric.add(iface);

        //add categories requisition level categories
        RequisitionCategoryCollection rcc = new RequisitionCategoryCollection();
        if (m_categoryList != null && m_categoryList.size() > 0) {
            for (String cat : m_categoryList) {
                rcc.add(new RequisitionCategory(cat));
            }
        }

        //add categories already on the node to the requisition
        if (rd.getCategories() != null) {
            for (String cat : rd.getCategories()) {
                rcc.add(new RequisitionCategory(cat));
            }
        }

        rn.setBuilding(foreignSource);

        if (rcc.size() >= 1) {
            rn.setCategories(rcc.getObjects());
        }

        rn.setForeignId(rd.getForeignId());
        rn.setInterfaces(ric.getObjects());

        String nodeLabel = rd.getNodeLabel();
        if (m_resolveIps) {
            InetAddress addr = InetAddress.getByName(rd.getPrimaryIp());
            nodeLabel = addr.getCanonicalHostName();
        }

        rn.setNodeLabel(nodeLabel);

        //r.insertNode(rn);
        r.putNode(rn);
        m_fsr.save(r);
    }

    public static Thread createShutdownHook() {
        Thread t = new Thread() {
            @Override
            public void run() {
                System.out.println("\nHave a nice day! :)");
                Runtime.getRuntime().halt(0);
            }
        };
        return t;
    }

}