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

[jagadish85]

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.

[AndreyHS]

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

[Runals · Accepted solution]

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

[jagadish85]

Thanks to both of you ... it works

[toby_meyer]

If I could upvote this twice I would. Thanks!

[satishsdange]

You may try this -

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

[jagadish85]

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.

[jagadish85]

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.