topic Re: How to display percentage values in the Y-axis? in Dashboards & Visualizations

How to display percentage values in the Y-axis?

muralisushma7 — Mon, 09 Apr 2018 01:25:06 GMT

Hi,

As seen in the above image, you can see that visualization chart displays the top values of the field: Source Address.
X-axis displays the source address and Y-axis the count. I also need to include the percent value which is seen in the table( grayed out in the image) in the chart. How can I do that? Main aim is to derive the source address vs percentage use.
Kindly help me on this quickly.

Thanks & Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

niketn — Mon, 09 Apr 2018 03:57:27 GMT

@muralisushma7, please try the following:

<YourBaseSearch>
|  top 20 source_address
|  chart sum(count) as Total last(percent) as percent by source_address
|  sort - Total

Optional Suggestions 🙂
1. You should also consider option to create a Chart Overlay with percent field over the Total.
2. Another option for you would be to use your current query as the base search for post processing and use the percent field in the post process search to plot a pie chart corresponding to the Column Chart that you have.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Mon, 09 Apr 2018 06:45:44 GMT

Hi,

Thanks for the query, I ran it and i can see the graph as I would like to. Just to re-confirm, this query gives the first 20 source addresses with highest percentage usage right? Moreover if I save this as a dashboard, will the graph be dynamic always, I mean the first 20 source address and its value keeps on changing forever( getting updated) or do we need some setting to be done.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Mon, 09 Apr 2018 06:49:49 GMT

Hi,

Thanks for the query. Just to re-confirm, this query produces first 20 source addresses with highest percentage use isn't it? Moreover if I save this visualization chart as dashboard, it would be a dynamic one right( the first 20 source addresses with the corresponding % keeps on updating as and when) or do we need to some setting to be done?

Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Mon, 09 Apr 2018 12:04:15 GMT

Hi,

Along with that can you also let me know, how to include Date in the graph above. Since I created this as dashboard, go to edit dashboard and selected add input as Time range, but I dont think it is giving me the correct output.

Can you guide me on this?

Thanks & Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

niketn — Tue, 29 Sep 2020 18:57:16 GMT

@muralisushma7, Yes basically the top command gets both count and percent. You can use any appropriate method to get the same for each source_address as there will be only row per source_address. So you can also use the following:

|  top 20 source_address
|  chart last(count) as Total last(percent) as percent by source_address
|  sort - Total

Please try out and confirm. If it works don't forget to accept/up vote the answer/comment.

Re: How to display percentage values in the Y-axis?

niketn — Mon, 09 Apr 2018 13:04:44 GMT

@muralisushma7, first off instead of posting Answer, try to add a comment under previous answer/comment so that every post does not go for moderation.

Seems like you have taken right steps to add Time input. Hopefully you have added the time input tokens to your search query as well. Close out the browser and reload the dashboard in case you have changed the Time Input token name as by default it picks up tokens field1.earliest and field1.latest

Refer to documentation for details.
https://docs.splunk.com/Documentation/Splunk/latest/Viz/Buildandeditforms#Form_with_time_inputs_example

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 29 Sep 2020 18:54:31 GMT

My search query is: source="jnpr-syslog" policy_name=Internet_Lab_Policy_Gateway_Logging source | top limit=20 source_address | chart sum(count) as Total last(percent) as percent by source_address | sort - Total | timechart count by percent.

Something is wrong here?

What i just need is the source address vs percentage on a given day.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 29 Sep 2020 18:57:51 GMT

Hi,

I have two queries as below:

source="jnpr-syslog" policy_name=Internet_Lab_Policy_Gateway_Logging source | top limit=20 source_address | chart sum(count) as Total last(percent) as percent by source_address | sort - Total

eval date_wday=strftime(_time,"%u") | timechart span=1d count by source_address

How to combine both into single query.

My final requirement is to display the source address versus max percentage use for a given day.

Please help.

Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 10 Apr 2018 07:18:34 GMT

I am waiting for this.

Can you please let me know how to proceed.

Thanks & Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

niketn — Tue, 10 Apr 2018 10:17:19 GMT

@muralisushma7, please try the following search. If you are on Splunk 6.6 or higher you can use Trellis Layout to break the timechart by Time.

 source="jnpr-syslog" policy_name=Internet_Lab_Policy_Gateway_Logging source 
| bin _time span=1d
| stats count as Count by source_address _time
| eventstats sum(Count) as Total by _time
| eval "Count%"=round((Count/Total)*100,2)
| fields - Total
| sort - _time Count
| streamstats count as sno by _time
| search sno<=20
| fields - sno
| eval _time=strftime(_time,"%Y/%m/%d")
| rename _time as Time
| stats last(Count) as Count last("Count%") as "Count%" by Time source_address
| sort - Count

Following is a run anywhere dashboard example based on Splunk's _internal index

Please find the Simple XML dashboard code for example above:

<form>
  <label>Timechart with Volume and Percent with Trellis</label>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <input type="time" token="tokTime" searchWhenChanged="true">
        <label></label>
        <default>
          <earliest>-7d@h</earliest>
          <latest>now</latest>
        </default>
      </input>
      <chart>
        <title>Daily Top 10 Errors by Component</title>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!=INFO
| bin _time span=1d
| stats count as Error by component _time
| eventstats sum(Error) as Total by _time
| eval "Error%"=round((Error/Total)*100,2)
| fields - Total
| sort - _time Error
| streamstats count as sno by _time
| search sno<=10
| fields - sno
| eval _time=strftime(_time,"%Y/%m/%d")
| rename _time as Time
| stats last(Error) as Error last("Error%") as "Error%" by Time component
| sort - Error</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">1</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.overlayFields">Error%</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">bottom</option>
        <option name="charting.lineWidth">2</option>
        <option name="height">600</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">1</option>
        <option name="trellis.scales.shared">0</option>
        <option name="trellis.size">medium</option>
        <option name="trellis.splitBy">Time</option>
      </chart>
    </panel>
  </row>
</form>

Re: How to display percentage values in the Y-axis?

niketn — Tue, 10 Apr 2018 10:18:50 GMT

@muralisushma7 please see attached an example based on Trellis Layout (will work if you are on Splunk 6.6 or higher).

Please try out and confirm!

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 10 Apr 2018 10:36:34 GMT

Hi,

We are using Splunk 6.5.1 version, so kindly let me know how to achieve the above dashboard.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Wed, 11 Apr 2018 04:59:59 GMT

Thanks Niketnilay for the code. I manipulated the above code to suit our environment and it is working as required. One small information I would like to know, how to bypass the SPLUNK login configured using LDAP authentication.

Re: How to display percentage values in the Y-axis?

niketn — Wed, 11 Apr 2018 08:19:14 GMT

@muralisushma7 is there a single Query in your Dashboard or multiple? If you create a Scheduled Report, you can embed the same in an HTML page and share HTML page with users. Other option would be PDF delivery of the report/dashboard (however, many visualizations may not work correctly with scheduled PDF delivery).

There would be an option to embed dashboard to your own webpage as well. However, it would not be a recommended approach: https://answers.splunk.com/answers/582632/how-do-you-use-custom-xml-in-reports-from-dashboar.html

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 29 Sep 2020 19:32:04 GMT

Hi Niketnilay,

For the below query, which you helped me out:

This query don't return an output when ran in fast mode. When in verbose mode it gives the output. Using this query a dashboard has been created and by default it runs in fast mode. The dashboard also has time as input. As a result, when I select a real time data i.e. 1 minute window, the query does not produce any output as it is in fast mode, when i change it to verbose mode manually it gives the output. How to make the dashboard run permanently with the verbose mode or else how to change the query so that it gives output even when ran in the fast mode.

Let me know if you need any more information.

Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

niketn — Mon, 14 May 2018 17:55:46 GMT

Are you getting data every 1 minute?
Can you take out second line | bin _time span=1d when you run in real-time mode and test?
Also if you have span=1d for your stats, what is the need for real-time 1 min window?
Instead of real-time 1 minute windows can you try adding panel refresh every 1 minute instead?

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 15 May 2018 12:18:18 GMT

Hi,

Yes we are getting data every minute and real time events exists. Previously we were not bothered about it and the query was good enough but now we even wish to see the real time events graph. As you said I tried removing the | bin_time span=1d, doing so the real time events generated are different from when time span is included. What I exactly mean is, when time span is included and time window of 1 minute is selected, output generated is different from when time span not included and time window of 1 minute is selected. Why is that difference? What can be done?
Coming to adding a panel to refresh every minute. I am not sure how to achieve this.

Kindly respond.

Thanks & Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 15 May 2018 13:59:04 GMT

Hi,

Yes we are having real time events. Previously we were not bothered about it, but now we even consider it.
As said, I removed the line |bin_time span=1d and ran the query, it does produces the output irrespective of the modes it is in. However the output generated when I remove the line time span in the query for 1 minute window in verbose mode is different from not removing the time span from the query for 1 minute window in the verbose mode. Why is that difference? Why both the outputs are not correct. Which one should i think is correct?

Please guide.

Thanks & Regards,
Sushma.

Re: How to display percentage values in the Y-axis?

muralisushma7 — Tue, 15 May 2018 14:09:32 GMT

Sorry for the double comments.

I mistakenly did it twice.

Thanks & Regards,
Sushma.