How to combine multiple rows into single row

[aditsss]

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.

[ITWhisperer]

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

| transpose 0

[aditsss]

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

[ITWhisperer]

Follow my answer https://community.splunk.com/t5/Dashboards-Visualizations/Display-the-top-3-entries-of-a-table-from-... 

[aditsss]

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

[ITWhisperer]

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

[aditsss]

@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

[ITWhisperer · Accepted solution]

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]

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

[ITWhisperer]

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.