query help

[surekhasplunk]

`myquery` | table Site Device Interface metric_name *

returns values like this :

Site    Device  Interface   metric_name full_metric_name    values  _time
Ams-P   xyz123  vni-0/1.0   vni-0/1_0   vni-0/1_0_in_usage  0.72    2020-03-02
Ams-P   xyz123  vni-0/1.0   vni-0/1_0   vni-0/1_0_out_usage 1.61    2020-03-02
Ams-S   xyz678  vni-0/1.0   vni-0/1_0   vni-0/1_0_in_usage  0.62    2020-03-02
Ams-S   xyz678  vni-0/1.0   vni-0/1_0   vni-0/1_0_out_usage 1.20    2020-03-02

Now i want to device the in_usage and out_usage into two different columns and show the output like below :

Site Device Interface in_usage out_usage _time
Ams-P xyz123 vni-0/1.0 0.72 1.61 2020-03-02
Ams-S xyz678 vni-0/1.0 0.62 1.20 2020-03-02

[kamlesh_vaghela · Accepted solution]

@surekhasplunk

Try this.

YOUR_SEARCH  | stats values(eval(if(full_metric_name="vni-0/1_0_in_usage",value,null()))) as in_usage values(eval(if(full_metric_name="vni-0/1_0_out_usage",value,null()))) as out_usage by Site Device Interface _time
 | table Site Device Interface in_usage out_usage _time

Sample Search

| makeresults | eval _raw=" Site    Device    Interface    metric_name    full_metric_name    value    _time
 Ams-P    xyz123    vni-0/1.0    vni-0/1_0    vni-0/1_0_in_usage    0.72    2020-03-02
 Ams-P    xyz123    vni-0/1.0    vni-0/1_0    vni-0/1_0_out_usage    1.61    2020-03-02
 Ams-S    xyz678    vni-0/1.0    vni-0/1_0    vni-0/1_0_in_usage    0.62    2020-03-02
 Ams-S    xyz678    vni-0/1.0    vni-0/1_0    vni-0/1_0_out_usage    1.20    2020-03-02"
 | multikv forceheader=1
 | stats values(eval(if(full_metric_name="vni-0/1_0_in_usage",value,null()))) as in_usage values(eval(if(full_metric_name="vni-0/1_0_out_usage",value,null()))) as out_usage by Site Device Interface _time
 | table Site Device Interface in_usage out_usage _time

[surekhasplunk]

Thanks a lot for your quick help @kamlesh_vaghela,

It worked i just tweaked a little as the interface names vary from device to device.

| multikv forceheader=1|eval in_metric=metric_name."_in_usage" |eval out_metric=metric_name."_out_usage" | stats values(eval(if(full_metric_name=in_metric,value,null()))) as in_usage values(eval(if(full_metric_name=out_metric,value,null()))) as out_usage by Site Device Interface _time
| table Site Device Interface in_usage out_usage _time