How can I get the result ?

[flzhang132]

How can I get the result ? thanks ！

[flzhang132]

[somesoni2]

How frequently do you collect these metrics for each host?

[flzhang132]

yes , there are many hosts ,and each hosts have vary of device metrics

[renjith_nair · Accepted solution]

@flzhang132,

Try this and let's know

index="your index" "other search terms"
|eval time=strftime(_time,"%d/%m/%Y")
|fields time,type,host,device,metric_value| fillnull value="NA"
|stats avg(eval(if(type="cpu",metric_value,null()))) as cpu,
  avg(eval(if(type="mem",metric_value,null()))) as mem,
  avg(eval(if(device="/dev/vda1",metric_value,null()))) as diskusage1,
  avg(eval(if(device="/dev/vdb",metric_value,null()))) as diskusage2,
  values(host) as host,values(time) as time by type,device
|fields - type,device|replace NA with ""

Sorry not tested.

[flzhang132]

device="/dev/vda1 or device="/dev/vdb2 or device="/dev/sdb1 ...... and so on
so can not used to if(device="/dev/vda1",metric_value,null())
I want to find rank...over() like oracle

[renjith_nair]

The above is exactly to match your requirement. The easy solution is

 index="your index" "other search terms"
 |eval time=strftime(_time,"%d/%m/%Y")
 |fields time,type,host,device,metric_value| fillnull value="NA"
 |stats avg(metric_value) as metric_value , values(host) as host,values(time) as time by type,device
 |replace NA with ""

and you could use chart func(value) over something by this