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.
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
| streamstats count by Subsystem | eval Subsystem=if(count==1,Subsystem,"")
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
Thanks to both of you ... it works
If I could upvote this twice I would. Thanks!
You may try this -
index=xxx | stats count by ServiceName, Subsystem | stats list(ServiceName) , list(Subsystem)
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.
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.