GetAvgDCIValue not working?

Started by Anders, August 22, 2012, 09:26:13 AM

Previous topic - Next topic

Anders

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

Victor Kirhenshtein

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 Kirhenshtein

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:

Quote*** 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 Kirhenshtein

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 Kirhenshtein

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

#11

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