Splunk Search

Convert a table to a percent table

DavidHourani
Super Champion

Hello,

I have a table that looks like this :

alt text

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

0 Karma
1 Solution

Runals
Motivator

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

View solution in original post

Runals
Motivator

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
Motivator

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

DavidHourani
Super Champion

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

0 Karma

Runals
Motivator

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
Super Champion

Thank you for your time and your detailed explanation!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)."%" | ...
---
If this reply helps you, Karma would be appreciated.

DavidHourani
Super Champion

Yeah that should work! thanks mate 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If it does, please accept the answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma

DavidHourani
Super Champion

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 ?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.

DavidHourani
Super Champion

yeah i figured as much.. thank you Rich 🙂

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...