Data collection from NMC

[MarkC_apc]

Is it possible to capture data directly to a spreadsheet from the NMC on a Smart UPS?  It would need to be in a format that could be mathmatically uitilized by the spreadsheet functions.

Thanks,

Mark

[MarkC_apc]

Hi Angela;

Guess I was looking for someone that has experience with extracting data continuously and automatically to a dedicated computer.  As you might remember, I'm into re-purposing the SURT and SUA/SMX lines for off-grid renewable energy use.  The data collected by the NMC's (AP9619 and AP9630/31) would be very useful in both monitering and actually predicting battery (external) conditions that are load and temperature dependant.  E.g. one could use the time/amperes/volts (or %watts) to estimate the total energy utilized.  I was hoping not to start from scratch.  I've used CSV data manually with Excel, and using macros can manipulate the data, but I'm thinking continuous data collection via the web interface might get complicated!

Thanks for your help,

Mark

[BillP]

Ohh, I see. Is SNMP an option? That is what came to mind where you could poll every X seconds directly into something running using SNMP and then graph it, etc and manipulate the data as you need to. That way you don't need to use the pre-defined format we choose in our onboard logs. Other than that, I don't really have any experience doing this myself but maybe someone else did.

[MarkC_apc]

Not very familiar with SNMP, although willing to learn.  If it can graph the information, then likely it can be utilized by a program such as Excel.  To program the math required to utilize data directly would be a monumental task that Excel could be "programmed" to do in seconds.

[voidstar_apc]

There's a button "view log in new window" where you can select-all, copy, and paste right into Excel.

For real-time collection, SNMP is the most flexible option. For exploring SNMP, get yourself a free copy of GetIF and the latest PowerNET MIB. For automatic collection of data, there's probably a bunch of commercial tools. I've used NetSNMP as well as python scripting for this.

[MarkC_apc]

Thanks Voidstar you're always a great help.

I've been doing the manual copy/paste/manipulate/formulate/compute with excel and it works fine for spot analysis.  "Real time" appears to be an entirely new learning curve - just the acryonyms are daunting.  Between the SMX3000 "project" and this, at least I'll learn a lot, no matter what the results.

Mark

[Terry_Kennedy_apc]

You can collect and graph the data with MRTG (it uses SNMP to query the NMC).

Or, if your system has an SNMP query package installed, you can do something like:

snmpget -v 2c -c community ups.example.com 1.3.6.1.4.1.318.1.1.1.2.2.3.0

SNMPv2-SMI::enterprises.318.1.1.1.2.2.3.0 = Timeticks: (774000) 2:09:00.00

snmpget -v 2c -c community ups.example.com 1.3.6.1.4.1.318.1.1.1.2.2.1.0

SNMPv2-SMI::enterprises.318.1.1.1.2.2.1.0 = Gauge32: 100

The first is the runtime. The second is charge percentage. If there are specific things you are looking for, post what they are and I'll dig out the OIDs for them.

[MarkC_apc]

Terry;

First, I'm not (think it's obvious) an expert in data aquisition, so I'm on a fairly steep learning curve.

What I'm attempting to do is gather real time data from an AP9619 card on a selected APC Smart UPS to use in a calculation that will moniter total energy consumption in battery backup mode.  This requires the Vout and Iout and/or %Wout.  Also needed is DC bus battery voltages (although it seems the DC bus Volts does not track local measurements very well?) and Tups-oF.  I don't currently plan on using the ambient moniter probe, but could in the future for, let's say, a high humidity shut down.

This data will be mathmatically manipulated (integrated) to match a specific battery discharge curve (system dependant) to predict the SOC (state of charge) of the battery pack.  This is quite easy to do in Excel - to both generate a specific curve and to determine the point on the curve, so this would be the software of choice to take in the data and manipulate it.  The next challenge is to alarm and shutdown the system at various SOC points.  I'll have a dedicated computer on-line to do this.

Of interest is recent data of actual operation of a SUA3000 indicated that the %Wout and the %VAout may not be a straight ratio of the units power factor, but seems to vary with the type of load (how reactive is the load).  Although not required for accuracy of the SUA unit, it could be for the SURT lines as their power factors are quite low!

I've attached recent data of real application of a SUA3000 in an "off-grid" mode with comments on loads.  This would be typical of data generated by the NMC. 

Thanks for your help,

Mark

[MarkC_apc]

Excel has a data collection function that grabs website tables.  Very easy to use and updates the "view log in new window" on a user set interval.  This can possibly provide what I need using some Excel macros.  However, there is one problem remaining.  Apparently, the NMC does not see the polling activity as user activity and logs out automatically.  The maximum interval of inactivity on my AP9630 card is 10 minutes.  I need this to essentially run continuously.  Am I missing a configuration option to stop auto-logout?

Mark

[voidstar_apc]

I had no idea Excel could do that! How does it handle records that fall off the end of the log? Or is it only a snapshot of the currently logged items?

Currently I know of no way to disable auto-logout. Part of the reason for the log-out is that because sessions are stored server-side, there's a limited number. Without auto-logout, abandoned sessions may not be closed out leaving the user unable to login after the limit is reached.

No guarantees, but I think the polling not being registered as activity will be fixed in the next release. Relying on this would be rather brittle if anything interrupts polling.

Searching around a bit, I also found people using VBA to script logging in, polling the page, and inserting it into Excel:

http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/

I don't know if this option is useful but the NMC is capable of uploading logs onto an FTP server periodically.

Anything relying on the web (or other user-facing interfaces, including, in my opinion, the CLI) may break on new NMC firmware releases so I'd be much more comfortable with a solution around SNMP which is intended for machine consumption. Apparently Excel supports SNMP, although the support isn't great.

[MarkC_apc · Accepted solution]

The Excel menu thread is Data/Get External Data/New Web Query.

With a dedicated computer connected directly to the NMC IP address, thru an Excel "window", you can actually log in, use the NMC menu to select the "table" of data that you want - in my case, the data logging in a new window.  You can select a polling "refresh" frequency and also have three options of data transfer to the spreadsheet.  The one that works with columns of formulations is the last option of "overwrite existing cells with new data, clear unused cells".  This keeps the data aligned properly with the mathmatical formulations that I desire (as the new data is polled at the "refresh" rate).  All works well until the (10 minute) logout due to inactivity of the NMC occurs, at which time, all data is overwritten with the logout text.

I believe this simplistic system will work well if the Excel "refresh" causes a register of "activity" with the NMC software - as the 10 minutes maximum inactivity would be about the polling frequency I'd want to moniter during an entire nightly (off-grid) session.  I.e., the battery conditions will not change significantly over a 10 minute period.- more over an hourly period.  10 hours (60 data points) will actually work well for me for each nightly session.  A worksheet "shell" would be loaded and started with fully charged batteries each evening and continue to run until one of two events - battery recharge cycle is started the next morning (solar panels start producing) OR an alarm that total energy exceeds a preset limit and the spreadsheet sends and alarm.  I've written a VBA script to do the alarming.  The only "hitch" is the NMC inactivity auto logout.

Any further help/suggestions is appreciated.  Is it possible to "patch" the NMC software to bypass this auto-logout?  I realize I'll likely have to do a hardware reset to relog into the NMC - as the NMC logout routine appears NOT to be available from the spreadsheet "window" (or directly from the browser either) after data logging is started. I.e., it will undoubtable give the message that someone is currently logged into the NMC.

Mark