topic Re: Convert a table to a percent table in Splunk Search

Convert a table to a percent table

DavidHourani — Fri, 06 Nov 2015 13:31:16 GMT

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

Re: Convert a table to a percent table

richgalloway — Fri, 06 Nov 2015 13:57:34 GMT

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)."%" | ...

Re: Convert a table to a percent table

DavidHourani — Fri, 06 Nov 2015 14:01:26 GMT

Yeah that should work! thanks mate 🙂

Re: Convert a table to a percent table

richgalloway — Fri, 06 Nov 2015 14:04:37 GMT

If it does, please accept the answer.

Re: Convert a table to a percent table

DavidHourani — Fri, 06 Nov 2015 14:11:37 GMT

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 ?

Re: Convert a table to a percent table

richgalloway — Fri, 06 Nov 2015 14:22:59 GMT

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

Re: Convert a table to a percent table

DavidHourani — Fri, 06 Nov 2015 14:29:48 GMT

yeah i figured as much.. thank you Rich 🙂

Re: Convert a table to a percent table

Runals — Fri, 06 Nov 2015 15:16:05 GMT

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

Re: Convert a table to a percent table

Runals — Fri, 06 Nov 2015 15:20:47 GMT

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

Re: Convert a table to a percent table

DavidHourani — Fri, 06 Nov 2015 15:30:05 GMT

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

Re: Convert a table to a percent table

Runals — Fri, 06 Nov 2015 15:42:37 GMT

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.

Re: Convert a table to a percent table

DavidHourani — Mon, 09 Nov 2015 08:45:09 GMT

Thank you for your time and your detailed explanation!