How to convert `_time` to the column and `host` as an index while using `mstats`?
| mstats avg(_value) prestats=true WHERE metric_name="cpu.*" AND index="*" AND
(host="host01.example.com" OR
host="host02.example.com" OR
host="host03.example.com" OR
host="host04.example.com" OR
host="host05.example.com" OR
host="host06.example.com"
) AND `sai_metrics_indexes` span=auto BY metric_name
| timechart avg(_value) as "Avg" span=30m by metric_name
| fillnull value=0
| foreach *[| eval "<<FIELD>>"=round('<<FIELD>>',2)]
The above results in as follows:
What is Desired:
host _time cpu.idle cpu.interrupt cpu.nice cpu.softirq cpu.steal cpu.system cpu.user cpu.wait
host01.example.com 2022-03-31 07:30:00 57.56 0.00 22.98 0.08 0.00 18.75 0.59 0.04
host01.example.com 2022-03-31 08:00:00 59.08 0.00 22.02 0.11 0.00 18.06 0.70 0.04
host01.example.com 2022-03-31 08:00:00 61.79 0.00 20.53 0.08 0.00 16.96 0.62 0.04
Any help will be uch appeciated.
## Since timechart only support group by one field, we're using this alternative implementation. Doing bucket of _time with same span that was used in timechart##
| bucket span=30m _time
## Merging two fields, so that charting can be done for two fields.
| eval temp=host."##"._time
## Running chart with merge field appearing as rows and metric_name values are column.
| chart avg(_value) as "Avg" over temp by metric_name
| fillnull value=0
| foreach cpu.* [| eval "<<FIELD>>"=round('<<FIELD>>',2)]
## Getting those merged field back.
| eval host=mvindex(split(temp,"##"),0)
| eval _time=tonumber(mvindex(split(temp,"##"),1))
| fields - temp | table host _time *
| eval host=mvindex(split(temp,"##"),0) <-- Can you please explain this ?
| eval _time=tonumber(mvindex(split(temp,"##"),1)) <-- Can you please explain this ?
| fields - temp | table host _time *
What is the meaning of using "##", does it has a special meaning here?
You haven't use host in SPL.
Try to use host filed in your SPL first either in stats or BY.
in last
|table host _time *
@dhirendra761 i tried that in the last line of search but did not list the host, it shows the host column but do not print the hostname's .. it turns blank.
Give this a try
| mstats avg(_value) prestats=true WHERE metric_name="cpu.*" AND index="*" AND
(host="host01.example.com" OR
host="host02.example.com" OR
host="host03.example.com" OR
host="host04.example.com" OR
host="host05.example.com" OR
host="host06.example.com"
) AND `sai_metrics_indexes` span=auto BY metric_name host
| bucket span=30m _time
| eval temp=host."##"._time
| chart avg(_value) as "Avg" over temp by metric_name
| fillnull value=0
| foreach cpu.* [| eval "<<FIELD>>"=round('<<FIELD>>',2)]
| eval host=mvindex(split(temp,"##"),0)
| eval _time=tonumber(mvindex(split(temp,"##"),1))
| fields - temp | table host _time *
@somesoni2 , this works can you explain the part you placed it working that will also help posterity.
## Since timechart only support group by one field, we're using this alternative implementation. Doing bucket of _time with same span that was used in timechart##
| bucket span=30m _time
## Merging two fields, so that charting can be done for two fields.
| eval temp=host."##"._time
## Running chart with merge field appearing as rows and metric_name values are column.
| chart avg(_value) as "Avg" over temp by metric_name
| fillnull value=0
| foreach cpu.* [| eval "<<FIELD>>"=round('<<FIELD>>',2)]
## Getting those merged field back.
| eval host=mvindex(split(temp,"##"),0)
| eval _time=tonumber(mvindex(split(temp,"##"),1))
| fields - temp | table host _time *