topic Re: How to convert `_time` to the column and `host` as an index while using `mstats`? in Splunk Search

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

microsac — Thu, 31 Mar 2022 12:10:04 GMT

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.

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

somesoni2 — Thu, 31 Mar 2022 14:45:19 GMT

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 *

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

dhirendra761 — Thu, 31 Mar 2022 14:51:52 GMT

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 *

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

microsac — Thu, 31 Mar 2022 15:06:28 GMT

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

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

microsac — Thu, 31 Mar 2022 15:23:44 GMT

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

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

somesoni2 — Thu, 31 Mar 2022 18:43:41 GMT

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

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

microsac — Fri, 01 Apr 2022 03:23:27 GMT

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