Convert a table to a percent table

[DavidHourani]

Hello,

I have a table that looks like this :

And I wish to convert all the values in the table to percent of the line total. Anybody has any idea how that can be done ? What I'm looking for is a table with percentages only, no real values.

Thanks splunkers.

Regards,
David

[Runals · Accepted solution]

I'd do something like

... | addtotals | foreach * [ eval perc<<FIELD>> = round(<<FIELD>>/Total*100,1) ] | table _time perc* | sort -_time | fields - percTotal

This will dynamically account for any of your display fields. It also didn't seem like you wanted the total events field in the results but you could easily add that within the table command. A couple things to keep in mind:

• If you add the % sign in the eval you wont be able to sort the results numerically by each column. As you have a time element that is perhaps not desired. However if you want to sort prior to the results being displayed you could adjust the sort field and then after that add another foreach command ie

  | foreach perc* [ eval <> = <> . " %"

• If your fields have any special characters in them the foreach command breaks as written (which drives me crazy). You would need to add single quotes around the FIELD bit like round('<>' /Total ...

• Note in my foreach eval I'm making new fields that are like percBIND and percOTHER. You could just as easily not make new fields by changing perc<> to just [ eval <> =

[Runals]

Bleh I'm tried of monkeying with the formatting to have the bullet list items show up correctly 😃

[DavidHourani]

pretty neat trick, thank you Runals ^^ any good links that explain that foreach command ?

[Runals]

Here is a link to all search commands which I have bookmarked. Once there you can navigate to each command easily enough.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/ListOfSearchCommands

I have often wished foreach would work with the fieldformat command which would allow you to add something like your % sign but keep the results an int under the covers but it "only" works with eval. Obviously eval is very powerful. The only other real issue I've run into is if the fields have a special character like "." or ":" which is why I included the bit about adding the single quotes in my answer (which solves for that issue). Being able to list fields makes it run faster than a wildcard pulling in all fields but sometimes I have to use that as there isn't a way to have it run over all fields EXCEPT this field, that field, etc.

At any rate as with all commands you can over use foreach but it is incredibly handy.

[DavidHourani]

Thank you for your time and your detailed explanation!

[richgalloway]

This should get you started.

<your current search> | eval TOTAL=BIND+CONNEXION+NULL+OTHER | eval BIND=round((BIND*100)/TOTAL,2)."%" | eval CONNEXION=round((CONNEXION*100)/TOTAL,2)."%" | eval NULL=round((NULL*100)/TOTAL,2)."%" | eval OTHER=round((OTHER*100)/TOTAL,2)."%" | ...

[DavidHourani]

Yeah that should work! thanks mate 🙂

[richgalloway]

If it does, please accept the answer.

[DavidHourani]

It works quite well. Only problem is that in my real table I have way more columns and I would like to automate the eval process so I won't have to write down everything column by column and repeat the eval several times. Any idea how that can be done ? Is the only way to do this by manually including all the column names ?

[richgalloway]

The addcoltotals or addtotals command may be able to calculate TOTAL automatically, but you'll still have to convert the columns to percentages manually.

[DavidHourani]

yeah i figured as much.. thank you Rich 🙂