stats command help to convert a row to column

[dhavamanis]

Need your help,

In the below query, we want to convert metric_name as column with values of avg_average, Can you please help us,

(index="aws-cloudwatch" ) ("i-2" OR "i-3" OR "i-4" OR "i-5" OR "i-6" OR "i-7" OR "i-8" OR "i-9" OR "i-11" OR "i-12" OR "i-13" OR "i-14" OR "i-15" OR "i-16" OR "i-17" OR "i-18" OR "i-19" OR "i-20" ) (metric_name=MemoryUsed OR metric_name=CPUUtilization OR metric_name=MemoryAvailable) | bin span=5m _time | eval Average=if(metric_name='MemoryAvailable',Average/1024,Average) | stats  avg(Average) as "avg_average"  by _time,  metric_dimensions,metric_name 

from

_time, metric_dimensions,metric_name, avg_average

to

_time, metric_dimensions, MemoryUsed, CPUUtilization, MemoryAvailable

[woodcock]

Like this:

index="aws-cloudwatch" ("i-2" OR "i-3" OR "i-4" OR "i-5" OR "i-6" OR "i-7" OR "i-8" OR "i-9" OR "i-11" OR "i-12" OR "i-13" OR "i-14" OR "i-15" OR "i-16" OR "i-17" OR "i-18" OR "i-19" OR "i-20" ) (metric_name=MemoryUsed OR metric_name=CPUUtilization OR metric_name=MemoryAvailable) | bin span=5m _time | eval MemoryUsed=if(metric_name="MemoryUsed",Average,0) | eval CPUUtilization=if(metric_name="CPUUtilization,Average,0) | eval MemoryAvailable=if(metric_name='MemoryAvailable',Average/1024,0) | stats avg(MemoryUsed) AS MemoryUsed avg(CPUUtilization) AS CPUUtilization avg(MemoryAvailable) ASMemoryAvailable BY _time, metric_dimensions

[somesoni2 · Accepted solution]

Give this a try

(index="aws-cloudwatch" ) ("i-2" OR "i-3" OR "i-4" OR "i-5" OR "i-6" OR "i-7" OR "i-8" OR "i-9" OR "i-11" OR "i-12" OR "i-13" OR "i-14" OR "i-15" OR "i-16" OR "i-17" OR "i-18" OR "i-19" OR "i-20" ) (metric_name=MemoryUsed OR metric_name=CPUUtilization OR metric_name=MemoryAvailable) | bin span=5m _time | eval Average=if(metric_name='MemoryAvailable',Average/1024,Average) | eval metric_dimensions=_time."#".metric_dimensions | chart avg(Average) as "avg_average"  over  metric_dimensions by metric_name | rex field=metric_dimensions "(?<_time>.*)#(?<metric_dimensions>.*)"

[Roopaul]

I had a similar situation. The query work well for me except the output field positions.

I am getting
MemoryUsed, CPUUtilization, MemoryAvailable,_time, metric_dimensions

Can you plz check and help.

This is my query
index=abc | eval field=field1." | ".field2." | ".field3| chart count over field by field4 | rex field5=field "(?.)|(?.)|(?.*)" | fields - field

Output:
field4_1 field4_2 field1 field2 field3

[sundareshr]

@Roopaul, just replace fields - field with table field4_1 field4_2 field1 field2 field3

[Roopaul]

field 4 is dynamically generated.

[sundareshr]

If the value in field4 are know before hand, try this

index=abc | eval field=field1." | ".field2." | ".field3 | eval field4="00".field4 | chart count over field by field4 | rex field5=field "(?.)\|(?.)\|(?.*)" | table 00* field 1 field2 field3 | rename 00* AS *

*else*

index=abc | eval field=field1." | ".field2." | ".field3 | eval field4="00".field4 | chart count over field by field4 | rex field5=field "(?.)\|(?.)\|(?.*)" | table field4_1 field4_2 field 1 field2 field3

[Roopaul]

The first one works well. Thanks a lot. 🙂

P.S. > 1st to be used if values is unknown and 2nd if value is known.