[collectd] Queries with `sum(something) as this' don't work on dbi plugin (SQLServer)

[Daniel Hilst]

On 02-04-2013 16:31, Daniel Hilst wrote:
> On 02-04-2013 12:19, Daniel Hilst Selli wrote:
>> I'm facing this error
>>
>> [2013-04-02 12:13:21] [error] dbi plugin: Column `log_reads': Don't know
>> how to handle source type 4.
>> [2013-04-02 12:13:21] [error] dbi plugin: cdbi_read_database_query
>> (MSSQLServer, teste_query): cdbi_result_get_field (1) failed.
>>
>> While using this dbi block
>> <Plugin dbi>
>>          <Query "teste_query">
>>                  Statement "select sum(total_logical_reads) as log_reads
>> from sys.dm_exec_query_stats"
>>                  <Result>
>>                          Type "gauge"
>>                          ValuesFrom "log_reads"
>>                  </Result>
>>          </Query>
>>
>>          <Database "MSSQLServer">
>>                  Driver "freetds"
>>                  Hostname "orisnet.verzani.com.br"
>>                  DriverOption "host" "192.168.5.18"
>>                  DriverOption "username" "SA"
>>                  DriverOption "password" "Paxazx01"
>>                  Query "teste_query"
>>          </Database>
>> </Plugin>
>>
>> The problem is with things like this `sum(something) as this' I just
>> can't get it working.. The query works fine on SSMS and retruns only one
>> line and one column,  Any idea?
>>
>> PS: I'm trying to monitor the reads/writes and worker time on MS
>> SQLServer..
>>
>> Regards,
>>
>
>
> I track the problem...
> This message
> 'dbi plugin: Column `log_reads': Don't know how to handle source type 4'
> Comes from here [1]. Seems that libdbi is returning something unexpected
> by dbi plugin.
>
> The type for stands for DBI_TYPE_BINARY here [2] line 114
>
> [1] https://github.com/collectd/collectd/blob/master/src/dbi.c#L132
> [2]
> http://libdbi.cvs.sourceforge.net/viewvc/libdbi/libdbi/include/dbi/dbi.h.in?revision=1.13&view=markup
> line 114
>
>
> So, I think, SUM() would return integer types, but for libdbi, don't,
> I'm digging deeper on this..
>
> :-)
>
Yeap, the problem is on SQLServer returning binary for SUM().. so.. the 
solution is use CAST(), urgh..

select cast(sum(total_logical_reads) as int) as log_reads from 
sys.dm_exec_query_stats

Cheers,

-- 
Follow the white rabbit!