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

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...