Evaluate difference in average charted value in aggregate search

[Cuyose]

I have a bar chart that I build that graphs the ave transaction response time of web pages between 2 runs. What I would ultimately like to do is calculate the difference between the average transaction times for the pages and based on a trigger for the % delta display the chart label in a different color. First off I can't find any examples on the web where someone has done this, they never use a charted aggregate search and anything I try ends up with a malformed eval string. This is the query I am trying to work with

index="perfdata" source="expense_transaction" LR_Run_Name=1206trunk80131.lrr LR_Trans_Name != *Transaction 
| chart avg(LR_Trans_Time) as 1206trunk80131.lrr by LR_Trans_Name
| appendcols 
  [search index="perfdata" source="expense_transaction" 
   LR_Run_Name=2012_06_trunk_80117.lrr LR_Trans_Name != *Transaction
   | chart avg(LR_Trans_Time) as 2012_06_trunk_80117.lrr by LR_Trans_Name]
| sort by -2012_06_trunk_80117.lrr
| head 10 

[araitz]

Let's do one thing at a time. First, this is a better search, giving you the same data

index="perfdata" source="expense_transaction" LR_Run_Name=1206trunk80131.lrr OR LR_Run_Name=2012_06_trunk_80117.lrr LR_Trans_Name!=*Transaction 
| stats avg(LR_TransTime) as avg_trans_time by LR_Trans_Name LR_Run_Name

This should give you results like:

LR_Trans_Name LR_Run_Name              avg_trans_time
trans1        1206trunk80131.lrr       42
trans1        2012_06_trunk_80117.lrr  53
trans2        1206trunk80131.lrr       14
trans2        2012_06_trunk_80117.lrr  13
...

After that, can you tell me how you intend to display this data in Splunk and what an example trigger and outcome would be?

[araitz]

Yes, it is too bad that CSS wasn't used, but I bet there was a good reason why that choice was made. You can use application.js to override the default color and range - see http://splunk-base.splunk.com/answers/3094/customised-data-overlays to get you started. I would recommend overriding decorateHeatMap rather than onResultsRendered(), but 6 of one, 1/2 dozen of another. Wow, I am really hitting all the glib coloquialisms in this thread.

[Cuyose]

I've decided to just show this supplemental graph as a simplechart heat map next to the time difference chart. Splunk apparently doesnt have any way to customize the color shading and range in heat maps though, which would be very useful.

[araitz]

I'm glad that you got it working! I was trying to avoid the append, which is less efficient than running one search, but if it ain't broke don't fix it.

[Cuyose]

I wanted to get fancy and incorporate these results from the "% difference" graph in the main bar chart so that when the % difference of any reported LR_Trans_Name is +5% or greater for the second run, that bar in the main chart would display red instead of whatever color splunk decides to make it and otherwise have it green. The other comparison ave(LR_Trans_Time) for the second run can just be a common color(doesn't matter)

I want the logic of this "% difference" data behind the scenes for the sole purpose of triggering bar color changes.

[Cuyose]

I was able to get the information I wanted in a separate chart with this:
index="perfdata" source="expense_transaction" LR_Run_Name=1206trunk80131.lrr LR_Trans_Name = APR* | chart avg(LR_Trans_Time) as avg1 by LR_Trans_Name | appendcols
[search index="perfdata" source="expense_transaction" LR_Run_Name=2012_06_trunk_80117.lrr LR_Trans_Name = APR* | chart avg(LR_Trans_Time) as avg2 by LR_Trans_Name]
| eval difference=(avg1-avg2)/avg1*100 | chart avg(difference) as "% difference" by LR_Trans_Name | sort -"% difference"

[araitz]

You should be able to do that via:
-main search from above-
| strcat LR_Trans_Name "_" LR_Run_Name run
| eventstats avg(LR_TransTime) as avg_trans range(LR_TransTime) as range by id
| stats first(range) as range avg(LR_TransTime) as trans_time by run

This will get you pretty close.

[Cuyose]

Thanks for your response, the problem with not using the appendcols is that the resulting bar chart only graphs a single bar for the ave(LR_Trans_Time) and doesn't separate them by LR_Run_Name. It averages both runs in the chart.

The chart that I want looks like this

Trans1_run1||||||||||||||||||||||

Trans1_run2|||||||||||||||||

       0   0.5   1.0    1.5   2.0
             Transaction time

I would like to be able to calculate the difference between those 2 ave(times) for each trans between its 2 runs.