How to convert `_time` to the column and `host` as an index while using `mstats`?

[microsac]

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.

[microsac]

@somesoni2  

| 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?

[dhirendra761]

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 *

[microsac]

@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.

[somesoni2]

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 *

[microsac]

@somesoni2 , this works can you explain the part you placed it working that will also help posterity.

[somesoni2 · Accepted solution]

## 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 *