Splunk Search

stats command help to convert a row to column

dhavamanis
Builder

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

Tags (3)
0 Karma
1 Solution

somesoni2
Revered Legend

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

woodcock
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

somesoni2
Revered Legend

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
Explorer

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

0 Karma

sundareshr
Legend

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

0 Karma

Roopaul
Explorer

field 4 is dynamically generated.

0 Karma

sundareshr
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

Roopaul
Explorer

The first one works well. Thanks a lot. :slightly_smiling_face:

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...