Author Topic: GetAvgDCIValue not working?  (Read 5651 times)

Anders

  • Jr. Member
  • **
  • Posts: 60
    • View Profile
GetAvgDCIValue not working?
« on: August 22, 2012, 09:26:13 am »
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:

Code: [Select]
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

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #1 on: August 22, 2012, 06:22:26 pm »
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

  • Jr. Member
  • **
  • Posts: 60
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #2 on: August 24, 2012, 02:43:23 pm »
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

  • Jr. Member
  • **
  • Posts: 60
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #3 on: August 30, 2012, 09:48:37 am »
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

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #4 on: August 30, 2012, 03:03:57 pm »
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:

Code: [Select]
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

  • Jr. Member
  • **
  • Posts: 60
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #5 on: August 31, 2012, 07:59:01 am »
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

  • Jr. Member
  • **
  • Posts: 75
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #6 on: October 10, 2013, 05:02:59 pm »
Hi!
After one year passed, the problem still exist.
Code: [Select]
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:
Code: [Select]
*** VALUE: 2.000000

Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #7 on: October 11, 2013, 11:49:02 am »
Hi!

That's very strange. I just install MySQL 5.1 server, create new database, create dummy DCI of type "integer" with transformation script

Code: [Select]
return 22;

If I run the following script from console:

Code: [Select]
startTime = time() - 1800;
object = FindNodeObject(null,"HP8570W");
trace(0, "*** VALUE: " . GetAvgDCIValue(object, FindDCIByDescription(object,"TEST"), startTime, time()));

I got expected results:

Code: [Select]
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

  • Jr. Member
  • **
  • Posts: 75
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #8 on: October 11, 2013, 01:03:19 pm »
Code: [Select]
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
Code: [Select]
[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

  • Jr. Member
  • **
  • Posts: 75
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #9 on: October 11, 2013, 02:06:03 pm »
I've made dummy internal integer dci with random numbers and it's working fine.

Victor Kirhenshtein

  • Lead Developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 7137
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #10 on: October 11, 2013, 04:14:07 pm »
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

  • Jr. Member
  • **
  • Posts: 75
    • View Profile
Re: GetAvgDCIValue not working?
« Reply #11 on: October 11, 2013, 04:27:53 pm »
Code: [Select]
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)
Code: [Select]
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:
Code: [Select]
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:
Code: [Select]
[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
« Last Edit: October 11, 2013, 04:36:28 pm by wonderboy »