topic Calculating percentage in Dashboards & Visualizations

Calculating percentage

kishen2017 — Mon, 20 Nov 2017 14:58:11 GMT

What is the spl command to calculate percentage of an field in splunk.
Using dbxquery to display field values from database table. Need to know the command to display percentage of an database table column values.

Re: Calculating percentage

somesoni2 — Mon, 20 Nov 2017 15:11:21 GMT

Could you explain more about your current output and expected output, probably with some sample data?

Re: Calculating percentage

cmerriman — Mon, 20 Nov 2017 15:44:54 GMT

if you have a column that is already a percentage, you can format the column using the UI to add a %
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/TableFormatsFormatting#Format_table_columns
or in the code of the dashboard
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/TableFormatsXML#Number_format_rules
or in the query:
|fieldformat fieldname=fieldname."%"
|eval fieldname=fieldname."%"

if you need to actually create a percentage:
|eval percentage=round((field1/field2)*100,2)."%"

Re: Calculating percentage

kishen2017 — Mon, 20 Nov 2017 16:29:45 GMT

Expected Output:

Severity Closed Deferred Fixed In UAT Returned Validated Total
1 - Critical 7 0 0 1 2 36 55
2 - High 29 11 13 5 7 57 169
3 - Medium 69 12 11 3 3 107 253
4 - Low 17 6 3 3 0 22 55
Total 122 29 27 12 12 222 532
Percentage 23% 6% 4% 2% 5% 40%

Current Output:

Query used:

| dbxquery query="SELECT BG_PROJECT 'Project', BG_USER_05 'Project_ID', BG_BUG_ID, BG_STATUS 'Status', BG_SEVERITY 'Severity', BG_DETECTION_DATE 
FROM defect connection="Test" shortnames=1             
| chart count over Severity by Status limit=0             
| addtotals labelfield="Severity" fieldname="Total"              
| addcoltotals labelfield="Severity" label="Grand Total"

Need to know how to get that percentage row as mentioned in expected output

Re: Calculating percentage

niketn — Mon, 20 Nov 2017 16:38:33 GMT

@kishen2017, You should provide more details as requested by Somesh. If possible, you should also add your current SPL with dbxquery to get the DB field for percent and also provide the field name on which to calculate percent.

| dbxquery <YourQuery>
| table Your_DB_Field
| eventstats sum(Your_DB_Field) as Total
| eval perc=round((Your_DB_Field/Total)*100,1)

Re: Calculating percentage

somesoni2 — Mon, 20 Nov 2017 17:03:05 GMT

Give this a try

| dbxquery query="SELECT BG_PROJECT 'Project', BG_USER_05 'Project_ID', BG_BUG_ID, BG_STATUS 'Status', BG_SEVERITY 'Severity', BG_DETECTION_DATE 
FROM defect connection="Test" shortnames=1 
| stats count by Severity Status
| appendpipe [| stats sum(count) as count by Status | eval Severity="5 - Total"]
| appendpipe [| where Severity="5 - Total" | eventstats sum(count) as Total | eval count=round(count*100/Total)."%" | eval Severity="6 - Percentage" | table Severity Status count]
| xyseries Severity Status count

Re: Calculating percentage

DalJeanis — Mon, 20 Nov 2017 17:52:13 GMT

Okay, you are wasting a bunch of CPU and IO with that query. You only need Status and Severity, and you are bringing back a whole bunch of other druck.

This is your SQL:

SELECT 
   BG_PROJECT 'Project', 
   BG_USER_05 'Project_ID', 
   BG_BUG_ID, 
   BG_STATUS 'Status', 
   BG_SEVERITY 'Severity', 
   BG_DETECTION_DATE 
FROM defect

This is the only SQL you need:

SELECT 
   BG_STATUS 'Status', 
   BG_SEVERITY 'Severity'
FROM defect

And really, you could have the SQL do the summary work, rather than shipping back the individual records for splunk to add up. The SQL would look like this...

SELECT 
   BG_STATUS 'Status', 
   BG_SEVERITY 'Severity', 
   Count(*) as Count
FROM defect
GROUP BY BG_STATUS, BG_SEVERITY

...and the whole thing would look like this...

| dbxquery query="SELECT BG_STATUS as 'Status', BG_SEVERITY as 'Severity', Count(*) as 'Count' 
     FROM defect GROUP BY  BG_STATUS, BG_SEVERITY" connection="Test" shortnames=1 
| table Severity Status Count
| appendpipe [
    | stats sum(Count) as Count by Status 
    | eval Severity="5 - Total"
    ]
| appendpipe [
    | where Severity="5 - Total" 
    | eventstats sum(Count) as Total 
    | eval Count=round(Count*100/Total)."%" 
    | eval Severity="6 - Percentage" 
    | table Severity Status Count]
| xyseries Severity Status Count

NOTE - There should be a closing quote after the query in the code you posted.

Re: Calculating percentage

niketn — Mon, 20 Nov 2017 18:17:08 GMT

@kishen2017, you are calculating a total of row and total of column and expect to calculate percent on the basis of Total value also as a field, which to me is a bit confusing.

If you are on Splunk Enterprise 6.5 or higher, the feature to Add Summary Total and Percent is built in to Splunk. You can do it via

1) Splunk UI > Edit Table > Format option to Add Total and/or Percent Summary row/s

Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/Viz/TableFormatsFormatting#Summary_row_examples

2) Or though Table Simple XML Configuration:

  <table>
     ...
     ...
    <option name="percentagesRow">true</option>
    <option name="totalsRow">true</option>
  </table>

Re: Calculating percentage

kishen2017 — Mon, 20 Nov 2017 18:38:19 GMT

Thank you so much for your inputs

Re: Calculating percentage

kishen2017 — Mon, 20 Nov 2017 18:38:33 GMT

Thank you so much for your inputs

Re: Calculating percentage

kishen2017 — Mon, 20 Nov 2017 18:38:46 GMT

Thank you so much for your inputs

Re: Calculating percentage

kishen2017 — Mon, 20 Nov 2017 18:40:23 GMT

Thank you somesoni2 for your inputs.

Re: Calculating percentage

niketn — Fri, 24 Nov 2017 04:19:59 GMT

@kishen2017, if one of the answers below has helped you resolve the issue you were facing, please accept the answer to mark this question as aswered. I see that you have already up voted the answers.