Splunk Search
Highlighted

stats command help to convert a row to column

Builder

Need your help,

In the below query, we want to convert metricname as column with values of avgaverage, 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, metricdimensions,metricname, avgaverage

to

time, metricdimensions, MemoryUsed, CPUUtilization, MemoryAvailable

Tags (3)
0 Karma
Highlighted

Re: stats command help to convert a row to column

SplunkTrust
SplunkTrust

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>.*)"

View solution in original post

Highlighted

Re: stats command help to convert a row to column

Explorer

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

I am getting
MemoryUsed, CPUUtilization, MemoryAvailable,time, metricdimensions

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:
field41 field42 field1 field2 field3

0 Karma
Highlighted

Re: stats command help to convert a row to column

Legend

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

0 Karma
Highlighted

Re: stats command help to convert a row to column

Explorer

field 4 is dynamically generated.

0 Karma
Highlighted

Re: stats command help to convert a row to column

Legend

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
Highlighted

Re: stats command help to convert a row to column

Explorer

The first one works well. Thanks a lot. 🙂

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

0 Karma
Highlighted

Re: stats command help to convert a row to column

Esteemed Legend

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
0 Karma