topic Re: query help in Splunk Search

query help

surekhasplunk — Wed, 30 Sep 2020 04:23:04 GMT

`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

Re: query help

kamlesh_vaghela — Mon, 02 Mar 2020 06:55:58 GMT

@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

Re: query help

surekhasplunk — Wed, 30 Sep 2020 04:23:10 GMT

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