Difference between revisions of "Script to Run PSQL"

Latest revision as of 13:03, 9 October 2023

var devices=["172.16.1.104","172.16.1.100","176.16.1.101","172.16.1.97"]
var hours=3;
var ProcessBuilder = Java.type('java.lang.ProcessBuilder');
var BufferedReader = Java.type('java.io.BufferedReader');
var InputStreamReader= Java.type('java.io.InputStreamReader');
var ProcessBuilder= Java.type('java.lang.ProcessBuilder');
var utils = Java.type('ros.CROSUtils');
var timestamp  = new Date().getTime()
var thishour=timestamp
thishour=thishour-(thishour%(3600000))-((hours*3600000)-3600000); //Untested
timestamp=timestamp-(timestamp%(hours*3600000)-3600000); //Untested
if (thishour===timestamp){ //check to only run once every 3 hours untested!!
for (var i in devices){

log.warn(timestamp +" "+devices[i]) log.warn("select * from netflow where device = "+utils.inet_atob(devices[i])+" and firstseen>="+ (timestamp-(hours*3600000*2)) +" and firstseen< "+ (timestamp-(hours*3600000)) )

//set PGPASSWORD=myipfix&& psql -A -F , -o c:\temp\flows_device_2.csv -U myipfix -h 127.0.0.1 -d sentry1 -c "select * from netflow where firstseen>gettimeimmutable('1 hour') limit 10;" runCommand("c:/temp/flows_"+devices[i]+"_"+(timestamp-(hours*3600000*2))+".csv","select * from netflow where device = "+utils.inet_atob(devices[i])+" and firstseen>="+ (timestamp-(hours*3600000*2)) +" and firstseen< "+ (timestamp-(hours*3600000)) ) }

}else{
log.warn("Wrong hour thishour:"+thishour+" timestamp:"+timestamp);
}
function runCommand(filename,sqlcommand){
try {
  // Use a ProcessBuilder
  //var pb = new ProcessBuilder("c:/Gigaflow/Flow/resources/psql/psql.exe","-A","-F ,","-o "+filename,"-U myipfix","-h 127.0.0.1","-d sentry1","-c \""+sqlcommand+"\"");
  var pb = new ProcessBuilder("c:/Gigaflow/Flow/resources/psql/psql.exe","-U","myipfix","-o",filename,"-h","127.0.0.1","-d","sentry1","-c",sqlcommand);
  //var pb = new ProcessBuilder("c:/Gigaflow/Flow/resources/psql/psql.exe");
  var env = pb.environment();
  env.put("PGPASSWORD", "myipfix");
  log.warn("runCommand:"+filename+" "+sqlcommand)
     log.warn("mid1")
  var p = pb.start();
        log.warn("mid2")
  var is = p.getInputStream();
  log.warn("mid3")
  var br = new BufferedReader(new InputStreamReader(is));
  var line = null;
  log.warn("mid4")
  while ((line = br.readLine()) != null) {
    log.warn(line)
  }
     log.warn("mid5")

log.warn(pb.toString())

  var r = p.waitFor(); // Let the process finish.
  if (r == 0) { // No error
     // run cmd2.
  }
} catch ( e) {
  log.warn(e.printStackTrace());
}
log.warn("end")
}