How to write a search to extract field values and display the results in a pie chart?

[macadminrohit]

Hi ,

I have a search which results in some events, the events will have a field "Value" which will have value 0 or 1. I want make it into a pie chart showing the percentage of 1 and 0 in the pie chart over a month.

Value is basically showing the availability of the servers. 1 means server was available and 0 means it was down. The pie chart will show availability of the server over a month.

Example event :

"09-27-2016 22:19:28" hostid="127723", ServerName="ip-10-206-116-220.dqa.capitalone.com", status="0", hostgroupid="118854", itemid="3020493", ItemName="Server Availability -Ping Check", GrpId="81", GroupName="App_Development CLO_Xdev7_Hostgroup", Event_Date_Time="2016-09-27 22:19:28.0", ***value="0",*** ItemKey="2016-09-27 22:19:28-3020493-127723"

Any help to write this search will be highly appreciated.

[msivill_splunk]

Hopefully the following will get you started.

Using csv data (written to index 456009 ) –

time,value
08-24-2016 01:00:00,1
08-25-2016 01:00:00,1
08-26-2016 01:00:00,1
08-27-2016 01:00:00,0
08-28-2016 01:00:00,1
08-29-2016 01:00:00,1
08-30-2016 01:00:00,1
08-31-2016 01:00:00,1
09-01-2016 01:00:00,1
09-02-2016 01:00:00,0
09-03-2016 01:00:00,1
09-04-2016 01:00:00,1
09-05-2016 01:00:00,1
09-06-2016 01:00:00,0
09-07-2016 01:00:00,1
09-08-2016 01:00:00,1
09-09-2016 01:00:00,1
09-10-2016 01:00:00,1
09-11-2016 01:00:00,1
09-12-2016 01:00:00,1
09-13-2016 01:00:00,0
09-14-2016 01:00:00,0
09-15-2016 01:00:00,0
09-16-2016 01:00:00,1
09-17-2016 01:00:00,1
09-18-2016 01:00:00,1
09-19-2016 01:00:00,1
09-20-2016 01:00:00,1
09-21-2016 01:00:00,1
09-22-2016 01:00:00,1
09-23-2016 01:00:00,1
09-24-2016 01:00:00,0
09-25-2016 01:00:00,0
09-26-2016 01:00:00,0
09-27-2016 01:00:00,1
09-28-2016 01:00:00,1

The following query can be written

index="456009" |  stats count(value) as status_count by value |  eval status = if( value=1, "Up", "Down") | fields status, status_count

I’ve not added a time range to the query as this could be done from the GUI using the drop down list next to the search bar.

Next go into Visualization tab and set up the Pie Chart, then save as new Dashboard Panel. Currently you can’t set percentages from the GUI for the pie chart so will need to edit the underlying source.

Mine looks like

<dashboard>
  <label>456009</label>
  <row>
    <panel>
      <chart>
        <title>Server Up vs Down</title>
        <search>
          <query>index="456009" |  stats count(value) as status_count by value |  eval status = if( value=1, "Up", "Down") | fields status, status_count</query>
          <earliest>0</earliest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.showPercent">true</option>
      </chart>
    </panel>
  </row>
</dashboard>

The key text for adding percentage is the line

 <option name="charting.chart.showPercent">true</option>

Other options for setting vizualizations can be found at http://docs.splunk.com/Documentation/Splunk/6.5.0/Viz/ChartConfigurationReference

All being well you should end up with something like -