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

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

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

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...