Splunk Search

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

jagadish85
Path Finder

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.

Tags (4)
1 Solution

Runals
Motivator

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

View solution in original post

AndreyHS
New Member

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

0 Karma

Runals
Motivator

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

View solution in original post

jagadish85
Path Finder

Thanks to both of you ... it works

0 Karma

toby_meyer
Engager

If I could upvote this twice I would. Thanks!

satishsdange
Builder

You may try this -

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

jagadish85
Path Finder

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.

0 Karma

jagadish85
Path Finder

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!