GetAvgDCIValue not working?
-
Hi Victor,
I've tried to get an average value for the last 3600 seconds (1 hour) of my temperature sensor, but it doesn't seem like the GetAvgDCIValue is returning the value that I'm expecting. I've more or less copied the example from the wiki and made a few adjustments.
I've made a dummy DCI with the following transformation script that runs every 60 seconds:
sub main() { startTime = time() - 3600; return GetAvgDCIValue(FindObject("TEMPNODE01"), 176, startTime, time()); }The average value that GetAvgDCIValue is returning is between: 1 and 2, while the temperature DCI (integer) has values between 80 and 200 during that hour, which in my world also should return an average value in that range.
I appreciate any help you can give on this.
// Anders
-
Hi!
What database you are using? Basically all that GetAvgDCIValue does is SQL query like this:
SELECT avg(coalesce(idata_value,0)) FROM idata_<node_id> WHERE item_id=<dci_id> and idata_timestamp between <time_from> and <time_to>
If possible, try to run such query by hand and check the output for correctness.
Best regards,
Victor -
-
-
Hi!
I test this on Oracle and PostgreSQL - everything works as expected. Now I want to try this on MySQL.
You can try to create script in script library which will print received values to log:startTime = time() - 3600; trace(0, "*** VALUE: " . GetAvgDCIValue(FindObject("TEMPNODE01"), 176, startTime, time()));and run it from server's console with exec command. What value it will show in log?
Best regards,
Victor -
-
Hi!
After one year passed, the problem still exist.startTime = time() - 1800; object = FindNodeObject(null,"TERM1"); trace(0, "*** VALUE: " . GetAvgDCIValue(FindNodeObject(null,"TERM1"), FindDCIByDescription(FindNodeObject(null,"TERM1"),"npThermoValue"), startTime, time()));All DCI values in history are 22 (integer).
And the script output is:
*** VALUE: 2.000000 -
Hi!
That's very strange. I just install MySQL 5.1 server, create new database, create dummy DCI of type "integer" with transformation script
return 22;If I run the following script from console:
startTime = time() - 1800; object = FindNodeObject(null,"HP8570W"); trace(0, "*** VALUE: " . GetAvgDCIValue(object, FindDCIByDescription(object,"TEST"), startTime, time()));I got expected results:
netxmsd: exec avg [11-Oct-2013 11:46:17.797] [INFO ] *** VALUE: 22.000000 INFO: Script finished with rc=0Can you re-test it with some another DCI?
Best regards,
Victor -
startTime = time() - 3600; trace(0, "*** TIME: " . startTime); object = FindNodeObject(null,"log1"); trace(0, "*** VALUE: " . GetAvgDCIValue(object, FindDCIByDescription(object,"Total number of threads"), startTime, time())); trace(0, "*** MAX: " . GetMaxDCIValue(object, FindDCIByDescription(object,"Total number of threads"), startTime, time())); trace(0, "*** MIN: " . GetMinDCIValue(object, FindDCIByDescription(object,"Total number of threads"), startTime, time()));It's interested to note that MAX and MIN are showing correct numbers, however GetAvg still results to strange
[11-Oct-2013 14:00:55.172] [INFO ] *** TIME: 1381482055 [11-Oct-2013 14:00:55.173] [INFO ] *** VALUE: 4.000000 [11-Oct-2013 14:00:55.173] [INFO ] *** MAX: 369.000000 [11-Oct-2013 14:00:55.174] [INFO ] *** MIN: 359.000000PS. I did direct Mysql query like you provided above and result was good value.
-
-
So strange... Can you please post actual results from SQL query and some plain values (with all spaces, etc. - I suppose that maybe results are parsed incorrectly)?
-
mysql> SELECT * FROM idata_134 WHERE item_id=26; +---------+-----------------+-------------+ | item_id | idata_timestamp | idata_value | +---------+-----------------+-------------+ | 26 | 1381494998 | 22 | | 26 | 1381495238 | 22 | | 26 | 1381495478 | 22 | | 26 | 1381495718 | 22 | | 26 | 1381495958 | 22 | | 26 | 1381496198 | 22 | | 26 | 1381496438 | 22 | | 26 | 1381496678 | 22 | | 26 | 1381496918 | 22 | | 26 | 1381497158 | 22 | | 26 | 1381497398 | 22 | | 26 | 1381497638 | 22 | | 26 | 1381497878 | 22 | | 26 | 1381498118 | 22 | +---------+-----------------+-------------+ 14 rows in set (0.00 sec)mysql> SELECT avg(coalesce(idata_value,0)) FROM idata_134 WHERE item_id=26 and idata_timestamp between 1381494998 and 1381497638; +------------------------------+ | avg(coalesce(idata_value,0)) | +------------------------------+ | 22 | +------------------------------+ 1 row in set (0.00 sec)Script:
startTime = 1381494998; stopTime = 1381497638; object = FindNodeObject(null,"TERM1"); trace(0, "*** VALUE: " . GetAvgDCIValue(object, FindDCIByDescription(object,"npThermoValue"), startTime, stopTime)); trace(0, "*** MAX: " . GetMaxDCIValue(object, FindDCIByDescription(object,"npThermoValue"), startTime, stopTime)); trace(0, "*** MIN: " . GetMinDCIValue(object, FindDCIByDescription(object,"npThermoValue"), startTime, stopTime));Result:
[11-Oct-2013 17:34:24.413] [INFO ] *** VALUE: 2.000000 [11-Oct-2013 17:34:24.414] [INFO ] *** MAX: 22.000000 [11-Oct-2013 17:34:24.414] [INFO ] *** MIN: 22.000000
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login