topic Re: How to combine duplicate multiple rows into one row in a table? in Splunk Search

How to combine duplicate multiple rows into one row in a table?

wanda619 — Wed, 03 Aug 2022 14:34:46 GMT

Hi Community,

I have a data as follows -

Customer	Error Code	Result
Abc	1111	2
Abc	1111	3
Abc	1222	4
Abc	Total	4
Abc	Total	5

I want to combine the Total into single row Total showing the result column as - Total : 9.

My code now -

| stats count as Result by Customer, ErrorCode
| eval PercentOfTotal=100
| append
[search index=sourcetype= abc: source= */ABC/* ErrorCode!=0
| stats count as Result by Customer
| eval ErrorCode="Total", PercentOfTotal=100]
| lookup xyz ErrorCode OUTPUT Description
| lookup pqr Customer OUTPUT Customer_Name
| eval Customer_Name=coalesce(Customer_Name,Customer)
| eval Error=if(ErrorCode!="Total", ErrorCode+" ("+coalesce(Description,"Description Missing - Update xyz")+")", ErrorCode)
| fields CustomerName, Error, Result

Re: How to combine duplicate multiple rows into one row in a table?

martinpu — Wed, 03 Aug 2022 14:36:57 GMT

| stats count as Result by Customer, ErrorCode
| eval PercentOfTotal=100
| addcoltotals Result
|fillnull 'Error Code' value="Total"

You can sum up your counts with the addcoltotals command as shown above.

Re: How to combine duplicate multiple rows into one row in a table?

somesoni2 — Wed, 03 Aug 2022 14:41:05 GMT

I'm sure there will be a better option than this but without knowing your data, it would be difficult to suggest that. Try to add following to your current search(at the end):

Your current search | fields CustomerName, Error, Result | stats sum(Result) as Result by CustomerName, Error