topic Re: How to combine my two searches and render dynamic ranges for a Radial Gauge? in Splunk Search

How to combine my two searches and render dynamic ranges for a Radial Gauge?

rajendra_b — Sun, 22 Feb 2015 14:57:20 GMT

I have a Drop-down box from where I get the type of Device chosen and depending on the chosen device I need to dynamically get the ranges and render it in a Radial Gauge. Then calculate the total devices sold.

The query works for static values like this

source =/opt/data/splunkLogs/order_transaction.log ProductName="$DEVICE1$-$STORAGE1$-$COLOR1$" | dedup OrderId | stats sum(SKUQuantity) as TotalDevices


 <option name="charting.chart.rangeValues">["0","600000","1400000","2000000"]</option>

I can calculate the ranges dynamically using a lookup table and eval,

lookup inventorylookup Devicename as $DEVICE1$-$STORAGE1$-$COLOR1$ OUTPUT AllocTotal | eval y1=round(AllocTotal*0.5, 2) | eval y2=round(AllocTotal*0.8, 2) | eval y3=AllocTotal | gauge count 0 y1 y2 y3

Q1. Now how to I render these ranges on the gauge?
Q2. How do I combine both the search queries into one?

Tried all options to render like this.

<option name="charting.chart.rangeValues">[x, y1, y2, y3]</option>

OR

<option name="charting.chart.rangeValues">[$x$, $y1$, $y2$, $y3$]</option>

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

ramdaspr — Mon, 23 Feb 2015 04:49:37 GMT

I guess you can add the guage ranges in the main search query itself

source =/opt/data/splunkLogs/order_transaction.log ProductName="$DEVICE1$-$STORAGE1$-$COLOR1$" | dedup OrderId | stats sum(SKUQuantity) as TotalDevices | guage TotalDevices [|gentimes start=-1 | lookup inventorylookup Devicename as $DEVICE1$-$STORAGE1$-$COLOR1$ OUTPUT AllocTotal | eval y1=round(AllocTotal*0.5, 2) | eval y2=round(AllocTotal*0.8, 2) | eval y3=AllocTotal | eval rs="0,"+y1+","+y2+","+y3 | fields rs | rename rs as search ]

The subquery would return the guage limits for the main search to use.
This worked for me for a search where I am doing an eval to determine the value for the ranges so I think it should work for inputlookup as well.

Let me know how it goes.

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

rajendra_b — Mon, 23 Feb 2015 13:03:31 GMT

Thanks Ramdaspr for a quick response.

The gauge data is working but the ranges are not displaying, (its displaying default 1 to 100). The ranges are in a lookup table so it has to be picked only once based on the dropdown selection. The TotalDevices should be calculated for every event, which is it doing now.

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

ramdaspr — Mon, 23 Feb 2015 13:12:43 GMT

What is the result you get when you run the subsearch alone?

|gentimes start=-1 | lookup inventorylookup Devicename as $DEVICE1$-$STORAGE1$-$COLOR1$ OUTPUT AllocTotal | eval y1=round(AllocTotal*0.5, 2) | eval y2=round(AllocTotal*0.8, 2) | eval y3=AllocTotal | eval rs="0,"+y1+","+y2+","+y3 | fields rs

I didnt notice this earlier, but you seem to be rounding the y values to 2 decimal places.. Is that required and can you try the query by rounding it up instead?

|gentimes start=-1 | lookup inventorylookup Devicename as $DEVICE1$-$STORAGE1$-$COLOR1$ OUTPUT AllocTotal | eval y1=round(AllocTotal*0.5, 0) | eval y2=round(AllocTotal*0.8, 0) | eval y3=AllocTotal | eval rs="0,"+y1+","+y2+","+y3 | fields rs

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

markthompson — Mon, 23 Feb 2015 13:12:56 GMT

Hi Rajendra,
You could create a SearchManager in JS to set tokens, and then use these in your source code to set the ranges.

It might be worth taking a look at them.

http://dev.splunk.com/view/webframework-features/SP-CAAAEM8

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

rajendra_b — Mon, 23 Feb 2015 15:35:50 GMT

I get rs as 1, when I run the sub-search. You are correct the rounding off is not needed.

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

rajendra_b — Mon, 23 Feb 2015 15:56:21 GMT

Thanks Mark, I will take a look at this. Can you share some examples if you can?

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

ramdaspr — Mon, 23 Feb 2015 22:33:41 GMT

Hi Rajendra, can you share the final query/ setup which worked for you? I'd like to see if this answer works for my setup as well..

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

ramdaspr — Mon, 23 Feb 2015 22:39:51 GMT

you shouldnt be getting rs as 1 since its been concatenated to provide something like 0,1,200,500.
Have you replaced the tokens with the actual data for trying out this query?

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

rajendra_b — Tue, 24 Feb 2015 01:38:47 GMT

Sorry, mgmt changed the priority suddenly and I was busy the whole day fixing other stuff. But here is my whole code for this chart. The gauge count is calculated but the ranges are not.

I have tried several permutation and combinations of your answers but never got the ranges.

<panel>
          <input type="dropdown" searchWhenChanged="true" token="DEVICE1">
            <label>Device</label>
            <choice value="*">ALL</choice>
            <choice value="iPhone6">iPhone6</choice>
            <choice value="iPhone6Plus">iPhone6Plus</choice>
            <default>ALL</default>
          </input>
          <input type="dropdown" searchWhenChanged="true" token="STORAGE1">
            <label>Storage</label>
            <choice value="*">ALL</choice>
            <choice value="16GB">16GB</choice>
            <choice value="64GB">64GB</choice>
            <choice value="128GB">128GB</choice>
            <default>ALL</default>
          </input>
          <input type="dropdown" searchWhenChanged="true" token="COLOR1">
            <label>Color</label>
            <choice value="*">ALL</choice>
            <choice value="Gold">Gold</choice>
            <choice value="Silver">Silver</choice>
            <choice value="Black">Black</choice>
            <choice value="White">White</choice>
            <choice value="Gray">Gray</choice>
            <default>ALL</default>
          </input>
          <chart>
            <title>Total Device Volumes</title>
            <search>
              <query>index=order_activation sourcetype=order_trans ProductName="$DEVICE1$-$STORAGE1$-$COLOR1$" | dedup OrderId | stats sum(SKUQuantity) as TotalDevices | gauge TotalDevices [ | lookup iPhonelookup Devicename as $DEVICE1$-$STORAGE1$-$COLOR1$ OUTPUT AllocTotal | eval y1=AllocTotal*0.5 | eval y2=AllocTotal*0.8 | eval y3=AllocTotal | eval x=0 | eval range=x+" "+y1+" "+y2+" "+y3 | return $range]</query>
              <earliest>0</earliest>
              <latest></latest>
            </search>
            <option name="charting.chart">radialGauge</option>
            <option name="charting.chart.style">minimal</option>
            <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
            <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
            <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</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.scale">linear</option>
            <option name="charting.axisY.scale">linear</option>
            <option name="charting.axisY2.enabled">false</option>
            <option name="charting.axisY2.scale">inherit</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.sliceCollapsingThreshold">0.01</option>
            <option name="charting.chart.stackMode">default</option>
            <option name="charting.drilldown">all</option>
            <option name="charting.gaugeColors">[0x84E900,0xFFE800,0xBF3030]</option>
            <option name="charting.layout.splitSeries">0</option>
            <option name="charting.legend.placement">right</option>
            <option name="wrap">true</option>
            <option name="rowNumbers">false</option>
            <option name="dataOverlayMode">none</option>
            <option name="count">10</option>
          </chart>
        </panel>

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

markthompson — Tue, 24 Feb 2015 13:14:13 GMT

Hi Rajendra,
Unfortunately I don't have any examples, just know it's possible.

You could also look at using SideviewUtils - looking at it it has a ResultsValueSetter, which I believe you could use to set tokens.

Re: How to combine my two searches and render dynamic ranges for a Radial Gauge?

rajendra_b — Mon, 02 Mar 2015 16:12:14 GMT

I am going with a different approach now. Created a custom search command called dynrange and calling it like below

index=order_activation sourcetype=order_trans ProductName="$DEVICE1$-$STORAGE1$-$COLOR1$" | dedup OrderId | stats sum(SKUQuantity) as TotalDevices | gauge TotalDevices [dynrange __EXECUTE__ None None $DEVICE1$-$STORAGE1$-$COLOR1$]

The search works and does not return any errors, but it is still not rendering the dynamic range on the Radial Gauge. How do I troubleshoot the custom search command, to see if it is returning the right values.

[root@ip-172-31-26-130 bin]# cat dynrange.py
#   Version 4.0
import sys
import splunk.Intersplunk
import csv
import re

(isgetinfo, sys.argv) = splunk.Intersplunk.isGetInfo(sys.argv)

if len(sys.argv) < 3:
    splunk.Intersplunk.parseError("3 Arguments are required")

if isgetinfo:
    splunk.Intersplunk.outputInfo(False, False, False, False, None)

results = splunk.Intersplunk.readResults(None, None, True)

file2 =  '/opt/splunk/etc/apps/search/lookups/inventory.csv'

def searchText(search_text):
    #print "search_text:", search_text
    with open(file2) as fp1:
        root = csv.reader(fp1)
        result = [i for i in root  if re.findall(search_text, i[0])]

    return result

newresult = {}
offset = 0
header = []
#countDev = 0
x = 0
y1 = 0
y2 = 0
y3 = 0
totVolume = 0
search_text = ''

if len(results) > 0:
    search_text = str(sys.argv[4]).strip()
    #countDev = int(sys.argv[2])
    try:
        #tmp1, tmp2, tmp3 = search_text.split("-")
        search_text = search_text.replace("*", "[^-]*")
        result = searchText(search_text)
        #pprint.pprint(result)
        for line in result:
            totVolume = totVolume + int(line[1])
            #print line
            #print line[1]
    except:
        print "wrong search text. *-*-*"

if totVolume == 0:
    totVolume = 40000

y1 = ((totVolume * 50)/100)
y2 = ((totVolume * 80)/100)
y3 = totVolume

print "Device = %s, X = %d, Y1 = %d, Y2 = %d, Y3 = %d" % (search_text, x, y1, y2, y3)

newresult['x'] = 0
header.append('x')
newresult['y1'] = y1
header.append('y1')
newresult['y2'] = y2
header.append('y2')
newresult['y3'] = y3
header.append('y3')

newresults = []

if len(newresult) > 0:
    newresults.append(newresult)

splunk.Intersplunk.outputResults(newresults, None, header)