GetAvgDCIValue not working?

[victor]

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

[Anders]

Hi Victor,

Sorry for the delay. I'm running a MySQL 5.1 server. I tried to execute the SQL query that you suggested and the result is: 104.7 which seems to be about the result that I'm expecting.

Regards

Anders

[Anders]

Hi Victor,

Sorry for nagging, I'm just curious to know if you can see any solution to my problem, or if I can assist with some additional data / information to solve this problem?

Regards

Anders

[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

[Anders]

Hi Victor,

It seems like there is some sort of issue between NetXMS and MySQL, the output I get after executing the script is:

*** VALUE: 1.000000

Regards

Anders

[wonderboy]

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

[victor]

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=0

Can you re-test it with some another DCI?

Best regards,
Victor

[wonderboy]

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.000000

PS. I did direct Mysql query like you provided above and result was good value.

[wonderboy]

I've made dummy internal integer dci with random numbers and it's working fine.

[victor]

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)?

[wonderboy]

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