topic How to combine multiple rows into single row in Dashboards & Visualizations

How to combine multiple rows into single row

aditsss — Fri, 02 Apr 2021 06:14:53 GMT

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.

Re: How to combine multiple rows into single row

ITWhisperer — Fri, 02 Apr 2021 06:37:00 GMT

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

| transpose 0

Re: How to combine multiple rows into single row

aditsss — Sun, 04 Apr 2021 11:23:05 GMT

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

Re: How to combine multiple rows into single row

ITWhisperer — Sun, 04 Apr 2021 14:28:58 GMT

Follow my answer https://community.splunk.com/t5/Dashboards-Visualizations/Display-the-top-3-entries-of-a-table-from-one-panel-to-our-block/m-p/546507#M37562

Re: How to combine multiple rows into single row

aditsss — Sun, 04 Apr 2021 15:57:52 GMT

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

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.

Re: How to combine multiple rows into single row

ITWhisperer — Sun, 04 Apr 2021 16:45:03 GMT

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.

Re: How to combine multiple rows into single row

aditsss — Sun, 04 Apr 2021 16:57:23 GMT

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

Re: How to combine multiple rows into single row

ITWhisperer — Sun, 04 Apr 2021 17:04:48 GMT

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

Re: How to combine multiple rows into single row

aditsss — Sun, 04 Apr 2021 17:14:51 GMT

@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

Re: How to combine multiple rows into single row

ITWhisperer — Sun, 04 Apr 2021 17:24:05 GMT

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 *