topic Re: How to merge rows in a table column if the value is repeating? in Splunk Search

How to merge rows in a table column if the value is repeating?

jagadish85 — Thu, 30 Apr 2015 09:37:12 GMT

I need to merge rows in a column if the value is repeating.

My search output gives me a table containing Subsystem, ServiceName and its count. It will show as below:

Subsystem     ServiceName      count
A             booking           300
A             checkin            20
A             seatassignment       3
B             booking            10
B             AAA                12
B             BBB                34
B             CCC                54

But I want the output as:

Subsystem      ServiceName    count
A             booking          300
               checkin          20
               seatassignment     3
B             booking           10
               AAA              12
               BBB                34
               CCC              54

I tried using this search: search...| stats values(ServiceName), count by Subsystem, but it gives me wrong subsystem count. I want the count of each of the services used by subsystem.

Re: How to merge rows in a table column if the value is repeating?

jagadish85 — Thu, 30 Apr 2015 09:39:59 GMT

To be clear: Like Merge Centre in Excel , I need to merge Subsytem values and the count should be the count of services called by subsystem.

Re: How to merge rows in a table column if the value is repeating?

satishsdange — Thu, 30 Apr 2015 10:05:10 GMT

You may try this -

index=xxx | stats count by ServiceName, Subsystem | stats list(ServiceName) , list(Subsystem)

Re: How to merge rows in a table column if the value is repeating?

jagadish85 — Thu, 30 Apr 2015 11:06:44 GMT

It is not working. Not showing me the count. Also it gives me a single row with all the ServiceName in one column and all the subsystem in another.

Re: How to merge rows in a table column if the value is repeating?

Runals — Thu, 30 Apr 2015 11:13:16 GMT

satishsdange is close I think. I will include what I expect your existing search is that is giving you your current output.

sourcetype=foo | stats count by Subsystem ServiceName | stats list(ServiceName) as ServiceName list(count) as Count by Subsystem

Because of how list works (shows values as it sees them) any sorting you want to do will need to be done before the second stats command. For example

sourcetype=foo | stats count by Subsystem ServiceName | sort -count | stats list(ServiceName) as ServiceName list(count) as Count by Subsystem

Re: How to merge rows in a table column if the value is repeating?

jagadish85 — Thu, 30 Apr 2015 11:55:35 GMT

Thanks to both of you ... it works

Re: How to merge rows in a table column if the value is repeating?

toby_meyer — Thu, 30 Jul 2015 05:10:49 GMT

If I could upvote this twice I would. Thanks!

Re: How to merge rows in a table column if the value is repeating?

AndreyHS — Thu, 20 Jul 2017 02:51:42 GMT

| streamstats count by Subsystem | eval Subsystem=if(count==1,Subsystem,"")