Using mstats to create separate columns per metrics

[kutsyy]

I know that I can combine multiple metrics using mstats as:

| mstats avg(_value) AS "Average" WHERE metric_name=metric_name* span=1m by metric_name, host

this would create a row per metric_name*. Instead of this I want to have 1 column per metric_name*, something along the lines:

| mstats avg(_value of metric_name1) AS "Average1" avg(_value of metric_name2) AS "Average2" WHERE metric_name=metric_name* span=1m by host

How could I do this?

Thank you,

Vadim

[thaggie_splunk · Accepted solution]

I think this should do the trick, though you have to hand code the metrics, no metric_name*:

| mstats avg(_value) as metric_name1 WHERE metric_name="metric_name1" by host span=1m
| join type=left _time [
  | mstats avg(_value) as metric_name2 WHERE metric_name="metric_name2" by host span=1m
]

Without the host column you can do:

| mstats avg(_value) WHERE metric_name="metric_name*" by metric_name span=1m
| timechart first(avg(_value)) by metric_name span=1m

[peiffer]

Have there been any updates on methodologies for extacting multiple metrics in a single mstats call?  I can do the work with a join across _time and dimensions held in common, but after about 2 metrics, the method gets a bit tedious.

[MuS]

Hi kutsyy,

if I got your question right, you can get the expect result by adding a chart to your mstats search like this:

| mstats avg(_value) AS avg_v max(_value) AS max_v min(_value) AS min_v WHERE index="meh-tricks" AND source="http:collectd" AND metric_name=cpu.percent* span=1m by host
| eval idle=if(metric_name=="cpu.percent.idle.value", max_v, null()), user=if(metric_name=="cpu.percent.user.value", max_v, null()) 
| chart values(idle) AS idle values(user) AS user over _time by host

This will get you a chart view with column contains the values for different metric, like the screenshot below:

Please adjust to your needs, because I'm pretty sure you will not have an index called meh-tricks 😉

Hope this helps ...

cheers, MuS