Splunk Search

merge rows to one row (a little more complex)

exocore123
Path Finder
 Date          Category            Type         Count
 5              car               sedan          300
 5              car               suv           400
 5              car               van           400
 5              plane             com           300
 5              plane             priv           300
 6              booking           test           500
 6              booking           test1           500               
 6              booking           test2           500

I want from above to below

Date          Category            Type         Count
 5              car               sedan          300
                                   suv           400
                                   van           400
                plane             com            300
                                  priv           300
 6              booking           test           500
                                  test1           500               
                                  test2           500

And possibly more merging than just 4 columns 🙂
Possibly using table also?

0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="5,car,sedan,300
5,car,suv,400
5,car,van,400
5,plane,com,300
5,plane,priv,300
6,booking,test,500
6,booking,test1,500
6,booking,test2,500"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Date>[^,]+),(?<Category>[^,]+),(?<Type>[^,]+),(?<Count>.*)$"

| rename COMMENT AS "Everything above creates sample event data; everything above is your solution"

| stats sum(Count) AS Count by Date Category Type
| stats list(Type) list(Count) BY Date Category
0 Karma

maciep
Champion

I think this is a good use case for autoregress, which will look at the previous row and let you do some comparisons. So, assuming your results are already sorted by date, categories and type...

... 
| autoregress Category as prev_Category
| eval Category = if(Category=prev_Category,"",Category)
| fields Date, Category, Type, Count
| stats list(Category) as Category, list(Type) as Type, list(Count) as Count by _time

So if the previous row's values match this rows value, then set this row's value to nothing. And as noted above, once you do this, you really can't interact with the panel any more. The panel will look pretty, but those values are gone and so you won't be able to click on the column headers to sort data while keeping the pretty looking format.

Hope that helps.

EDIT: Just realized what you mean bey wanting this in one rows. I think that can be done with multi-value magic...

EDIT 2: Ok, maybe just need stats list at this point. I updated the search.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Okay, assuming that you are ONLY trying to change the presentation, and never intend to do any more calculation on the data, then you can do this...

(your search with | stats etc that produces)
| table field1 field2 field3  field4 field5...

| rename COMMENT as "Do this streamstats for each field you want to suppress reprints" 
| streamstats current=f last(field1) as old_field1, last(field2) as old_field2,last(field3) as old_field3
| fillnull value="((null))" old_*

| rename COMMENT as "A field can only be blank if all higher levels on the same line are blank."
| eval field1=if(field1!=old_field1,field1,"((blank))")
| eval field2=case(field1!="((blank))",field2,field2=old_field2,"((blank))",true(),field2)
| eval field3=case(field2!="((blank))",field3,field3=old_field3,"((blank))",true(),field3)

| rename COMMENT as "Now you can get rid of the ((blank)) flags and all the work (old_*) fields."
| replace "((blank))" WITH "" IN field1 field2 field3
| table field1 field2 field3  field4 field5...
0 Karma

exocore123
Path Finder

With this, it does stop repeating the values... wish it could merge it into 1 row tho... this is what I have now

  5                                sedan          300
  5              car               suv           400
  5                               van           400
  5              plane             com           300
  5                                 priv           300
  6                                  test           500
  6              booking           test1           500               
  6                                  test2           500

There are still rows being displayed on the table

Actually, I think I did it wrong, gimme a min

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Okay, this really is starting to feel like you are stuck on attempting to commit spreadsheet.

What is your reason for wanting to present the data in this manner? Who is the user, and what specifically will they be using the report for?

If it is just for a display/report, then why does it matter that there are multiple actual lines? They represent multiple detail lines, so they SHOULD BE multiple lines.

In other words, use case, please?

0 Karma

exocore123
Path Finder

business reviews for shareholders and execs

0 Karma

cmerriman
Super Champion

you can add |eval typeCount=Type+"-"+Count|fields - Type Count|nomv typeCount
or i suppose if the count is always the same, you could just do |nomv Type|nomv Count

0 Karma

exocore123
Path Finder

if I was to try nomv, it comes after stats?

0 Karma

cmerriman
Super Champion

yup, just tack it on to the end of your query that got you that table. it will take a multi-value list and make it one value, you could also try |mvcombine delim="," typeCount before the nomv command to get a comma between them.

0 Karma

exocore123
Path Finder

Oh no, I meant I want to get that table from repetitive fields. I'll update what I have. I tacked nomv after my table date category type count, did not do anything

0 Karma

cmerriman
Super Champion

alright i understand now that you posted more.

what you need is |eval typeCount=Type+"-"+Count|stats values(typeCount) by date category

you can do some more evaling to split out typeCount to separate columns using mvindex or rex if you want.

0 Karma

exocore123
Path Finder

Ah, I see... with the eval typeCount.. we are basically just combining the columns... if we were to do rex to split the columns, wouldn't it just go back to what I originally had? haha

0 Karma

cmerriman
Super Champion

it wouldn't go back to what you originally had because your date and category columns would still be condensed, which i believe is what you're looking for.

0 Karma

exocore123
Path Finder

Alright, i'll attempt and keep posted. This solution is much harder for what I am trying to implement so it'll be a while (trying to use an input field to basically make a new column as a new filter every time a new parameter is checked)

0 Karma

exocore123
Path Finder

I have looked at stats list(...) AS ... by Date, but I want to do more than just one merge. And I've tried doing separate stats or new search, not sure what to do now

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 ...