Combine two rows from a search into one?

[dzenn]

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:

index = projectonline | dedup ProjectName | search Importance!=NULL | stats count by Importance | eval Importance_slice = Importance + ", " + count | fields Importance_slice, count

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!

[sundareshr]

Add this before your stats command.

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

[dzenn]

thank you for the suggestion!

so after using search string:

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

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 😞

[richgalloway]

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

[dzenn]

http://imgur.com/Y1UgrmQ

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

[richgalloway · Accepted solution]

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