Dashboards & Visualizations

How to combine multiple rows into single row

aditsss
Motivator

Hi Everyone,

I have one requirement.

Below is my query:

index=abc ns=ab ("NullPointerException" OR "IllegalStateException" OR "IllegalArgumentException" OR "RuntimeException" OR "NumberFormatException" OR "NoSuchMethodException" OR "ClassCastException" OR "ParseException" OR "InvocationTargetException" OR "OutOfMemoryError")| rex "message=(?<ExceptionMessage>[^\n]+)"|eval _time = strftime(_time,"%Y-%m-%d %H:%M:%S.%3N")|cluster showcount=t t=0.6|table app_name, ExceptionMessage,cluster_count,_time, environment, pod_name,ns|dedup ExceptionMessage|rename app_name as APP_NAME, _time as Time, environment as Environment, pod_name as Pod_Name,cluster_count as Count

 

I am getting multiple rows and column names are app_name, ExceptionMessage,cluster_count,_time, environment, pod_name,ns

 

I want them to be in one row. 

All the app_name should be in one row, all excepton messages should be in one row

Can soemone guide me on this.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Stats values will give you all the unique values in multi-value fields in sorted order. If you want the order they are in in the pipeline, you should use stats list(*) as *

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

If you just want columns to be rows and rows to be columns, use transpose

| transpose 0
0 Karma

aditsss
Motivator

@ITWhisperer 

I dont want columns to be rows and rows to be columns.

Currently my search results is coming like below:

App_Name          ExceptionMessage          Count

one                         nullpointer                                  38

one3                      IllegalArgument                        45

There are coming in different rows .

I want them to be in single row so that I can send them via Incident in a single row.

Can you guide me on this.

0 Karma

aditsss
Motivator

@ITWhisperer 

I want like in this way:

APP_NAME                   ExceptionMessage                    Count                    Env

aon1                                nullpointer                                        1                                  E1

buy2                                IllegalArgument                               23                               E1

............................

Then both APP_NAME aon1 and buy2 should be in one row        

similar way ExceptionMessage  nullpointer   and IllegalArgument  both should be in same row

similar for count both 1 and 23 should be in same row

And same  for Environment.

Is this correct:

index=abc ns=ab ("NullPointerException" OR "IllegalStateException" OR "IllegalArgumentException" OR "RuntimeException" OR "NumberFormatException" OR "NoSuchMethodException" OR "ClassCastException" OR "ParseException" OR "InvocationTargetException" OR "OutOfMemoryError")| rex "message=(?<ExceptionMessage>[^\n]+)"|eval _time = strftime(_time,"%Y-%m-%d %H:%M:%S.%3N")|cluster showcount=t t=0.6|table app_name, ExceptionMessage,cluster_count,_time, environment, pod_name,ns|dedup ExceptionMessage|rename app_name as APP_NAME, _time as Time, environment as Environment, pod_name as Pod_Name,cluster_count as Count| stats values(*) as *

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Yes, the stats will collect all the unique values for each column into a multi-value field for each column all in one row.

0 Karma

aditsss
Motivator

@ITWhisperer 

This is fine But the problem I am facing is 

For below query:

APP_NAME                   ExceptionMessage                    Count                    Env

aon1                                nullpointer                                        1                                  E1

buy2                                IllegalArgument                               162                             E1

when I am combining this with | stats values(*) as * in my search query

its combining like below:

APP_NAME                   ExceptionMessage                    Count                    Env

aon1                                nullpointer                                        162                                E1

buy2                               IllegalArgument                                   1                                     E1

The counts are not coming proper 162 count is for   IllegalArgument not for null pointer

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Stats values will give you all the unique values in multi-value fields in sorted order. If you want the order they are in in the pipeline, you should use stats list(*) as *

aditsss
Motivator

@ITWhisperer 

I don't have only one and two rows .

It could be 10 rows as well .

I want how can I combine them from my query:

My current query:

index=abc ns=ab ("NullPointerException" OR "IllegalStateException" OR "IllegalArgumentException" OR "RuntimeException" OR "NumberFormatException" OR "NoSuchMethodException" OR "ClassCastException" OR "ParseException" OR "InvocationTargetException" OR "OutOfMemoryError")| rex "message=(?<ExceptionMessage>[^\n]+)"|eval _time = strftime(_time,"%Y-%m-%d %H:%M:%S.%3N")|cluster showcount=t t=0.6|table app_name, ExceptionMessage,cluster_count,_time, environment, pod_name,ns|dedup ExceptionMessage|rename app_name as APP_NAME, _time as Time, environment as Environment, pod_name as Pod_Name,cluster_count as Count

My Result 

APP_NAME                   ExceptionMessage                    Count                    Env

aon1                                nullpointer                                        1                                  E1

buy2                                IllegalArgument                               23                               E1

............................

And there  can be several rows as well.

I want like APP_NAME aon1 and buy2 should come in one row instead of two rows,

Can you guide me how to achieve this.

 

 

 

 

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I am not clear whether you want all 10 rows rearranged into one row (which the solution I pointed at should do), or just the two rows you mentioned have their values combined leaving the other rows as they are.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...