topic Re: Combine two rows from a search into one? in Splunk Search

Combine two rows from a search into one?

dzenn — Tue, 29 Sep 2020 10:00:01 GMT

Hello!

I'm doing a search for some project information, specifically for a count of projects based on their Importance, a created field in our Project Online instance, using this string:

It's working pretty nicely but when we created the Importance field and look up table originally we used values High, Medium and Low then switched to 1 - High, 2 - Medium and 3 - Low. This, I believe, has thrown my search a little bit and it returns this:

Now naturally what I'd like to do is combing the High and 1 - High rows, the Medium and 2 - Medium, and the Low and 3 - Low rows. This will be for a piechart dasboard panel, so maybe sections of a piechart can be combined in the XML as a way to attack it from a different angle. Anyways, can this be done in the search or XML? I've been struggling with addtotals and the evals for this. Any and all advice would be most welcome!

Thank you!

Re: Combine two rows from a search into one?

richgalloway — Thu, 23 Jun 2016 17:00:30 GMT

Perhaps this will help. Use sed to normalize the Importance values.

index = projectonline | dedup ProjectName | search Importance!=NULL | rex field=Importance mode=sed "s/\d - (\w+)/\1/g" | stats count by Importance | eval Importance_slice = Importance + ", " + count | fields Importance_slice, count

Re: Combine two rows from a search into one?

sundareshr — Thu, 23 Jun 2016 17:00:59 GMT

Add this before your stats command.

... | rex field=Importance mode=sed "s/(\d\s-\s)(\w+)/$2/g" | stats ...

Re: Combine two rows from a search into one?

dzenn — Tue, 29 Sep 2020 10:00:04 GMT

thank you for the suggestion!

so after using search string:

The results seem to be combined but the totals are off:

I've found that whether or not "rex field=Importance mode=sed "s/(\d\s-\s)(\w+)/$2/g" " is in the search or not the results are the same.

I've never used sed or rex so unfortunately I'm rather ignorant of how they work 😞

Re: Combine two rows from a search into one?

dzenn — Thu, 23 Jun 2016 17:11:36 GMT

http://imgur.com/Y1UgrmQ

this is the image of the new results, for some reason you can't upload an image in the comments.

Re: Combine two rows from a search into one?

richgalloway — Thu, 23 Jun 2016 17:25:28 GMT

You don't need two rex commands in your search. One is enough.