MySql vars @myvar:=table.host

[2006 IR Open Dicussion]

By: Chris Collins - crusty_collins

MySql vars @myvar:=table.host

2006-05-10 11:59

Is there a way to use the mysql variables? I run a report that gives cpu util for sets of machines and I would like be able to use a params to set the machines returned.

 

<code>

select @nodes:=node_ref_table.ref from node_ref_table where

node_ref_table.node like 'adv';

 

SELECT date_format(from_unixtime(cpu_table.timestamp), '%Y-%m-%d') as date,

round(avg(cpu_table.value)) as value,

round(max(cpu_table.value)) as Maxvalue,

round(min(cpu_table.value)) as Minvalue,

count(cpu_table.value) as count,

node_ref_table.node,

var_ref_table.varname

FROM cpu_table

JOIN node_ref_table ON ( node_ref_table.ref = cpu_table.node_ref AND cpu_table.node_ref =

( select node_ref_table.ref from node_ref_table

where

node_ref_table.node in @nodes

)

)

JOIN var_ref_table ON (var_ref_table.ref = cpu_table.var_ref AND cpu_table.var_ref =

(SELECT var_ref_table.ref FROM var_ref_table

WHERE

var_ref_table.varname = '%hardware.cpuutilisation.value' LIMIT 0,1

)

)

WHERE UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY)) <= cpu_table.timestamp GROUP BY 1;

</code>

 

 

 

 

By: Chris Collins - crusty_collins

RE: MySql vars @myvar:=table.host

2006-05-10 12:02

Mistake in sql

<code>

JOIN node_ref_table ON ( node_ref_table.ref = cpu_table.node_ref AND cpu_table.node_ref in

( select node_ref_table.ref from node_ref_table

where

node_ref_table.node in @nodes

)

</code>