Hey all, I was attempting to figure out how to provide a whole bunch of trend logs to a CxA out of Niagara, and didn’t want to spend 8 years adding export sources and transforms to accomplish it, so I brute forced a program object that will dump filtered trends into a CSV file and stuff them in a timestamped folder under the following path:
I am not a dev, it isn’t perfect, I had to put chatgpt through its paces to figure out half of it. Empty search terms will pull all records in the station, so tread lightly.
Wondering if anyone has a better way of doing this?
Hey, someone’s already done this. They used a BQL query to search for histories.
Here’s their code:
public void onStart() throws Exception {
// start up code here
}
// This example runs under N4
// This example demonstrates executing a program object task on a separate
// thread. Since actions are run on the system thread by default, any long-
// running task should be completed on a separate thread in order to avoid
// watchdog timeout problems.
//
// In this example, the execute method creates a new thread, and starts it.
// Since program objects implement Runnable, you can implement the run() method
// and allow the program object to be the target of the new thread.
//
// This technique is good when the program object will not be executed at a high
// rate. Since a new thread is created for each execution, it is possible this
// approach could consume to many system resources.
//
public void onExecute()
throws Exception {
if (running) return; // only allow one execution at a time
// Create a new thread, with the starting point set as the current program
// object.
Thread thread = new Thread(this, getComponent().getName());
// Start the thread
thread.start();
}
public void onCancel()
throws Exception {
running = false;
}
public void run() {
// This task will take a long time
try {
BComponent base = getComponent();
running = true;
BOrdList exclude = getHistoriesToExclude();
progress("Started exporting history records");
BHistoryService service = (BHistoryService) Sys.getService(BHistoryService.TYPE);
BHistoryDatabase db = service.getDatabase();
if (db == null) {
progress("Failed - Could not find history database");
return;
}
int count = 0;
BHistoryDevice[] devices = db.listDevices();
for (int i = 0; i < devices.length; i++) {
if (!running) { // Check if canceled
progress("Operation canceled.");
return;
}
if (contains(devices[i], exclude)) continue; // skip if in excluded list
BIHistory[] histories = db.listHistories(devices[i]);
//add a sub folder for storing the histories if necessary
File basePath = Sys.getStationHome();
File ePath = null;
File stationFolder = null;
if (new File(basePath, "historyExports").exists() != true) {
ePath = new File(basePath, "historyExports");
ePath.mkdir();
} else {
ePath = new File(basePath, "historyExports");
}
String stationName = "";
String historyName = "";
for (int j = 0; j < histories.length; j++) {
if (!running) { // Check if canceled
progress("Operation canceled.");
return;
}
if (contains(histories[j], exclude)) continue; // skip if in excluded list
progress(" Exporting records for " + histories[j].getId());
stationName = histories[j].getId().getDeviceName();
historyName = histories[j].getId().getHistoryName();
if (new File(ePath, stationName).exists() != true) {
//need to make the folder
stationFolder = new File(ePath, stationName);
stationFolder.mkdir();
} else {
stationFolder = new File(ePath, stationName);
}
//PrintStream out = new PrintStream(new FileOutputStream(baseEPath + stationName + "" + historyName + ".csv", true));
PrintStream out = new PrintStream(new FileOutputStream(new File(stationFolder, historyName + ".csv"), true));
//Changed below to only output the timestamp and value.
OrdTarget table = BOrd.make("history:" + histories[j].getId().toString() + "|bql:select timestamp, value").resolve(base);
out.println(exportToCsv(table));
out.close();
count++;
}
}
progress("Completed exporting " + count + " histories ");
} catch (Exception e) {
progress("Error during export of histories " + e);
} finally {
running = false;
}
}
/**
* Run the CSV exporter against the specified table to build an
* in memory representation of the table as a CSV file.
*/
private String exportToCsv(OrdTarget table)
throws Exception {
// create instance of ITableToCsv exporter
BExporter exporter = (BExporter) Sys.getType("file:ITableToCsv").getInstance();
// run the CSV exporter to export to memory based byte array
ByteArrayOutputStream out = new ByteArrayOutputStream();
ExportOp op = ExportOp.make(table, out);
exporter.export(op);
// return as string (this works because we String will use the default
// encoding, which should match encoding ITableToCsv exporter used to
// create a PrintWriter from a raw OutputStream)
return new String(out.toByteArray());
}
public static boolean contains(BINavNode obj, BOrdList list) {
BOrd navOrd = obj.getNavOrd().relativizeToSession();
for (int i = 0; i < list.size(); i++) {
if (navOrd.equals(list.get(i).relativizeToSession())) return true;
}
return false;
}
public void progress(String progress) {
System.out.println(progress);
setStatus(progress);
}
public static boolean running = false;
//String basePath = "d:\\niagara\\Niagara-3.5.25\\stations" + (Sys.getStation().getStationName().toString()) + "";
//String baseEPath = "d:\\niagara\\Niagara-3.5.25\\stations" + (Sys.getStation().getStationName().toString()) + "\\historyExport";
public void onStop() throws Exception {
// shutdown code here
}
When you say group. Do you mean using history grouping? Interested to see how yours does this. I’ve built one that groups histories based on folder structure.
Just to add to my request, I have gone through the code from Buidling Systems and can add a time interval to the BOrd statement using the same options you get when pulling history in Workbench e.g. period=today which helps limit the amount of data being pulled. But I have a project with about 300 buildings and over 100,000 points that I need to filter.