Splunk Search

Can I merge few rows into 1 row depending on the value of a field?

surekhasplunk
Communicator

Hi,

I have a lookup file and I am using below query to show results in statistics table in my dashboard which is working fine.

|inputlookup file1.csv |search bla bla |chart count over "Delivery Area" by "Sourcing Status" 

Am getting results as:

Delivery Area | field2 | field 3 
asdfdf-ABD-ffdfsdf | 3 |4
asdff-ABD-erewr | 5 | 6
adffs-SD-223 |4 |4
213-SD-dffh |5 |7
adf-SD-dff |7 |8

But now for the Delivery Area I need them to be merged into one row if for ex:
when I have 3 rows like below they should be merged into a new row called

All-SD | 16 | 19
adffs-SD-223
213-SD-dffh
adf-SD-dff

How do I achieve this?

Tags (3)
0 Karma

elliotproebstel
Champion

I'm assuming the following:
1. You want to join all items where Delivery Area matches on the middle section (part between two hyphens) if there exist more than one item with the same middle section.
2. If there is only one item in the list with at particular middle section, you want to identify it by name.
3. You want to sum the values of field1 and field2 for merged items and preserve the count for non-merged items.
If all of that is correct, I think this should do what you're looking for:

[ your current search]
| rex field="Delivery Area" "[^-]-(?<mergeable>[A-Z]+)-" 
| eventstats count by mergeable 
| eval title=if(count>1,"All-".mergeable, 'Delivery Area') 
| stats sum(f*) AS f* BY title
| rename title AS "Delivery Area"
0 Karma

elliotproebstel
Champion

It looks like you're merging all the rows that contain SD. Would you also be merging the first two rows into: All-ABD | 8 | 10?

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...