Groovy web console

Subscribe to this site

DBtoCSV

Published 6 months ago by test1 with tags test1

//EXPORT_SANXTOUTAGE_REPORT_TO_CSV
//content
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import java.sql.DriverManager;
import java.sql.SQLException;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;

import au.com.bytecode.opencsv.CSVWriter;

import java.util.Calendar;


def     summary        =     "Export to CSV";
def     condition     =     "good";
def     severity    =     "good";
def     detail        =    "";
def     detailDebug    =     "";
 
def     connString    =     "jdbc:oracle:thin:@//runbookdb-kn-p1.systems.private:1521/runbk51p";//INPUTS["DBCONN_STRING"];
def     username    =     INPUTS["USERNAME"];
def     password    =     "R3s0lv3#";//INPUTS["PASSWORD"];

// def      fileName    =   INPUTS["FILENAME"];


def     calendar     =     Calendar.getInstance(); 
calendar.add(Calendar.DAY_OF_MONTH, -1);
def        currentDate    =    calendar.getTime()

def     prevDay     =     new SimpleDateFormat("yyyy-MM-dd").format(currentDate);


detail += "\nPrevious Date: " + prevDay;


def     sqlQuery    =     "SELECT * FROM sanxt_notifications PARTITION FOR (date '${prevDay}')   ORDER BY u_notification_time ASC";



def DB_DRIVER = "";
def dbType       = "$PROPERTY{DB_TYPE}";

     if( "Oracle".equals(dbType)  ) {        DB_DRIVER = "oracle.jdbc.OracleDriver";        }     
else if( "MariaDB".equals(dbType) ) {        DB_DRIVER = "org.mariadb.jdbc.Driver";         }

boolean             errorStatus     =     true;

Connection             conn             =     null;
Statement              stmt             =     null;
ResultSet             rs                =    null;
FileOutputStream     outputStream    =   null;
CSVWriter             csvWriter         =     null;

try {
    
    Class.forName(DB_DRIVER);
    
    detail+="\n Loading of Driver -- Success ";
    
    
    // Establish a connection with the given URL    
    conn = DriverManager.getConnection(connString,username, password);
    
    if(conn != null) {
        
        detailDebug+="connected\n"; 
        
        stmt     =     conn.createStatement();
        
        if(sqlQuery != null) {
            
            detail     += "\nQuery to run..."+ sqlQuery;
            
            rs       =     stmt.executeQuery(sqlQuery);
            
            detail  +="\nQuery executed successfully";                

            def     fileName = (prevDay != null && prevDay.length() > 7) ? prevDay.substring(0, 7) : calendar.get(Calendar.YEAR) + "-" + calendar.get(Calendar.MONTH) + 1;
            String     filePath = "/app/resolve/tomcat/webapps/sanxtoutagereports/"+ fileName+ ".csv";
            
            
            outputStream = new FileOutputStream(filePath, false);
            
            csvWriter = new CSVWriter(new OutputStreamWriter(outputStream));


            csvWriter.writeAll(rs, true);
            
            detail += "\nExport to CSV done: File saved at: " + filePath;
            
            errorStatus = false;
     
    
        }
    }
    
    
}
catch (ClassNotFoundException e)
{
     detail += "\nFailed to load Oracle database driver:" + e.getMessage();
     errorStatus =true;    
     
} 
catch (SQLException e)
{
     detail += "\nFailed to execute SQL Queries: " + e.getMessage();
     errorStatus = true;
     
}
catch (IOException e)
{
     detail += "\nEncounted IO issues: " + e.getMessage();
     errorStatus =true;    
     
}
catch (Exception e)
{
     detail += "\nException at : " + e.getMessage();
     errorStatus = true;    
     
}
finally
{
    try {

        if(stmt != null) {
            stmt.close();
        }
    } catch(SQLException e) { }
    

    try{
        if(rs != null) {
            rs.close();
        }
    } catch(SQLException e) { }
    
    try{
        if(outputStream != null) {
            
            outputStream.flush();
            outputStream.close();
        }
    } catch(Exception e) { }
    
    try {
        if(conn != null) {
            conn.close();
        }
    } catch(SQLException e) { }
    
    try {
        if(csvWriter != null) {
        
            csvWriter.flush();
            csvWriter.close();
        }
    } catch(Exception e) { }    
}


boolean isNullorEmpty(Object object) { 
    if(object == null || object == "") {
        return true;
    }
    return false;
}

INPUTS["DETAIL"]         =     detail;
INPUTS["ERRORSTATUS"]     =     errorStatus;

return;

//assess
import groovy.json.JsonOutput;


def     summary        =     "Export SANXT Outage Notifications to CSV";
def     condition     =     "good";
def     severity    =     "good";
def     detail        =    INPUTS["DETAIL"];
def     detailDebug    =     "";
 
def     errorStatus =     INPUTS["ERRORSTATUS"];

detail += "\nRAW Output: " + RAW;

def status_point     =     null;
def outermap        =    [:];

if(errorStatus==true){
    
    condition        =     "bad";
     severity        =     "critical";
     
    outermap.put("success",false);
    
    status_point    =    "FAIL-EXPORT TO CSV"; 
}
else{
    outermap.put("success",true);
    
    status_point    =    "SUCCESS-EXPORT TO CSV"
}


def jsonoutput = JsonOutput.prettyPrint(JsonOutput.toJson(outermap));

OUTPUTS["JSON_OUTPUT"]  = jsonoutput;
OUTPUTS["STATUS_POINT"] = status_point;

detailDebug += "\nQUERY RESULT"+outermap;


RESULT.condition     =     condition;
RESULT.severity     =     severity;
RESULT.summary         =     summary;
if (PARAMS["DEBUG_THIS_RUNBOOK"] == "yes" || FLOWS["DEBUG_ALL"] == "yes")
{
    RESULT.detail     = detail + detailDebug;
}
else
{
    RESULT.detail     = detail;
}