Calculating percentage

[kishen2017]

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.

[niketn]

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

[niketn]

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

[kishen2017]

Thank you so much for your inputs

[DalJeanis]

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.

[kishen2017]

Thank you so much for your inputs

[somesoni2]

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

[kishen2017]

Thank you somesoni2 for your inputs.

[cmerriman]

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

[kishen2017]

Thank you so much for your inputs

[somesoni2]

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

[niketn]

@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)

[kishen2017]

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:

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

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