GetSumDCIValue and 64bits integer

[Jmp_3f8h]

Hi!

As per subject, I have a problem with the GetSumDCIValue when the result is greater than a 32bit integer.
This is my transformation:

return int64(GetSumDCIValue($node, FindDCIByDescription($node, "WAN: delta in (bytes)"), time()-86400, time()));

It returns 0 when the result cannot be handled by a 32 bits integer.

I have another version of that DCI that uses a different approach to do the same computations and it works properly:

last = int64(GetCustomAttribute($node, "WANCumulativeIn"));
if (last == null)
	last = 0;
SetCustomAttribute($node, "WANCumulativeIn", 0);
return last;

The data type assigned to both DCIs is "Unsigned Integer 64 bit" (NB in the last example the custom attribute is updated by another DCI).

Any hints?

Best regards,
Marco

[victor]

Hi!

Could you check what is returned by underlying SQL query? Implementation of this function tries to parse value read from database as double, so there should be no limitation on 32bit integer size.

Actual query is

SELECT avg(coalesce(idata_value,0)) FROM idata_<node_id_here> WHERE item_id=<dci_id_here> AND (idata_timestamp BETWEEN <time1> AND <time2>);

Best regards,
Victor

[Jmp_3f8h]

@victor said in GetSumDCIValue and 64bits integer:

Hi!

Could you check what is returned by underlying SQL query? Implementation of this function tries to parse value read from database as double, so there should be no limitation on 32bit integer size.

Actual query is

SELECT avg(coalesce(idata_value,0)) FROM idata_<node_id_here> WHERE item_id=<dci_id_here> AND (idata_timestamp BETWEEN <time1> AND <time2>);

Best regards,
Victor

I get "Arithmetic overflow error converting expression to data type int". I propose to apply the same suggestion as per this thread:

https://www.netxms.org/forum/configuration/getxxxdcivalue-(avgmaxmin)-fails-on-floating-numbers/msg13724/#msg13724

adding

cast(idata_value as float),0)

inside the coalesce function.

Best regards,
Marco

[victor]

Yes, that change already applied - so after upgrade to 1.2.14 you'll get this problem fixed as well.

Best regards,
Victor

[Jmp_3f8h]

@victor said in GetSumDCIValue and 64bits integer:

Yes, that change already applied - so after upgrade to 1.2.14 you'll get this problem fixed as well.

Best regards,
Victor

Is there a reason to get always null in return from this function:

GetMaxDCIValue($node, $dci->id, time()-86400, time());

I tried to run manually the query (as per the SQL syntax in this thread) and it works fine, but I'm not able to have the function working in my transformation script.

Best regards,
Marco

[victor]

Hi!

Actually there is an error in query used in function. Query looks like

SELECT max(coalesce(cast(idata_value as float),0)) FROM idata_%u
WHERE item_id=? AND (idata_timestamp BETWEEN ? AND ?) AND isnumeric(idata_value)

but isnumeric returns int, not boolean, so it should be

isnumeric(idata_value)=1

I've fixed it. PLease let me know if you need intermediate server build with this fix.

Best regards,
Victor

[Jmp_3f8h]

@victor said in GetSumDCIValue and 64bits integer:

I've fixed it. PLease let me know if you need intermediate server build with this fix.

Best regards,
Victor

It could be nice to have it, I'm using the x64 Windows version.

Best regards,
Marco

[victor]

Hi!

I'm finally built an intermediate release. It is available here: https://www.netxms.org/download/1.2.15-M1/.

Best regards,
Victor

[Jmp_3f8h]

@victor said in GetSumDCIValue and 64bits integer:

Hi!

I'm finally built an intermediate release. It is available here: https://www.netxms.org/download/1.2.15-M1/.

Best regards,
Victor

Thank you very much for your effort, I'm downloading it, I plan to install it tonight.

Best regards,
Marco

[Jmp_3f8h]

@victor said in GetSumDCIValue and 64bits integer:

Hi!

I'm finally built an intermediate release. It is available here: https://www.netxms.org/download/1.2.15-M1/.

Best regards,
Victor

Is it possible to have the WebUI? You changed the protocol and the previous version is no more able to connect.

Best regards,
Marco