topic Re: stats command help to convert a row to column in Splunk Search

stats command help to convert a row to column

dhavamanis — Tue, 29 Sep 2020 06:43:39 GMT

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

_time, metric_dimensions, MemoryUsed, CPUUtilization, MemoryAvailable

Re: stats command help to convert a row to column

somesoni2 — Thu, 16 Jul 2015 21:20:55 GMT

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

Re: stats command help to convert a row to column

woodcock — Thu, 16 Jul 2015 21:22:52 GMT

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

Re: stats command help to convert a row to column

Roopaul — Tue, 29 Sep 2020 10:37:51 GMT

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.

Output:
field4_1 field4_2 field1 field2 field3

Re: stats command help to convert a row to column

sundareshr — Fri, 12 Aug 2016 23:44:32 GMT

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

Re: stats command help to convert a row to column

Roopaul — Fri, 12 Aug 2016 23:46:27 GMT

field 4 is dynamically generated.

Re: stats command help to convert a row to column

sundareshr — Sat, 13 Aug 2016 00:03:52 GMT

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

Re: stats command help to convert a row to column

Roopaul — Sat, 13 Aug 2016 00:15:54 GMT

The first one works well. Thanks a lot. 🙂

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