How do I make a multi-dimension timechart?

Jason — Tue, 18 Sep 2012 16:22:13 GMT

I have a need to count up both failures and successes on a chart, split them by something, and then compare these values to the same time period in the past. Is it possible to do this all on one graph?

Re: How do I make a multi-dimension timechart?

Jason — Tue, 18 Sep 2012 16:24:24 GMT

Yes. The search is a bit involved, and you will want to make sure the colors in the dashboard are set so you can clearly see current/previous times and types of failure/success messages.

Search

Get the events (successful and failure messages) that you are interested in.
Round their times to a convenient time interval that is divisible by 3 (for "earlier period, later period, and blank" bars - see below)
Differentiate the failure and success messages with a marker such as type
Count up the events by the category (in this case, sourcetype) and success or failure (type)
Give this set of data a temporal marker, such as period=today
If the event is a failure message, negate the count
Append a search that does the same as steps 1-6 above, over an earlier timeframe (specified in earliest and latest)
At the end of the appended search, add the amount of time you went back to _time (in this example, went back an hour, so added 3600 seconds)
Hack _time again to shift any period=today events over 1/3 of the time period decided on in step 2
Create a label using the time period, success/failure, and category fields to make a unique series to graph by
Graph the series using a timechart using a span of 1/3 of the time period decided in step 2. This will leave every third one blank for clarity - to show the relationship between today and the previous period.

example: run over -h@h to @h
index=_internal (sourcetype="splunkd" OR sourcetype="*_access" OR sourcetype="splunk_web_service" OR sourcetype="searches" OR sourcetype="scheduler") | bucket _time span=3m | eval type=if(searchmatch("sourcetype=splunkd OR sourcetype=splunkd_access OR sourcetype=scheduler"), "Successful", "Unsuccessful") | stats count by _time sourcetype type | eval period="Today" | eval count=if(type=="Successful", count, -1*count) | append [search index=_internal (sourcetype="splunkd" OR sourcetype="*_access" OR sourcetype="splunk_web_service" OR sourcetype="searches" OR sourcetype="scheduler") earliest=-2h@h latest=-h@h | bucket _time span=3m | eval type=if(searchmatch("sourcetype=splunkd OR sourcetype=splunkd_access OR sourcetype=scheduler"), "Successful", "Unsuccessful") | stats count by _time sourcetype type | eval period="Yesterday" | eval count=if(type=="Successful", count, -1*count) | eval _time=_time+3600] | eval _time=if(period=="Today", _time+60, _time) | eval series=type+" "+period+": "+sourcetype | timechart span=1m limit=12 first(count) as count by series

Dashboard
Use a custom palette of colors to show the relationships clearly between current/previous and types of success/failure. Here's an example - this one uses blues/greens for success and reds/purples for failures, with 50% faded versions of the color for the previous period:

<chart>
  <searchName>Triple axis timechart - chart view</searchName>
  <title>Triple Axis (success/failure, today/yesterday, sourcetype)</title>
  <option name="charting.chart">column</option>
  <option name="charting.chart.stackMode">stacked</option>
  <option name="charting.legend.placement">top</option>
  <option name="charting.axisTitleX.text"></option>
  <option name="charting.axisTitleY.text">Messages</option>      
  <option name="charting.b1">solidFill</option>
  <option name="charting.b1.color">0x961C1C</option>
  <option name="charting.b1.alpha">1.0</option>
  <option name="charting.b1t">solidFill</option>
  <option name="charting.b1t.color">0X961C1C</option>
  <option name="charting.b1t.alpha">0.5</option>
  <option name="charting.b2">solidFill</option>
  <option name="charting.b2.color">0x961C89</option>
  <option name="charting.b2.alpha">1.0</option>
  <option name="charting.b2t">solidFill</option>
  <option name="charting.b2t.color">0x961C89</option>
  <option name="charting.b2t.alpha">0.5</option>
  <option name="charting.b3">solidFill</option>
  <option name="charting.b3.color">0xD41D3B</option>
  <option name="charting.b3.alpha">1.0</option>
  <option name="charting.b3t">solidFill</option>
  <option name="charting.b3t.color">0xD41D3B</option>
  <option name="charting.b3t.alpha">0.5</option>
  <option name="charting.b4">solidFill</option>
  <option name="charting.b4.color">0x519AEC</option>
  <option name="charting.b4.alpha">1.0</option>
  <option name="charting.b4t">solidFill</option>
  <option name="charting.b4t.color">0X519AEC</option>
  <option name="charting.b4t.alpha">0.5</option>
  <option name="charting.b5">solidFill</option>
  <option name="charting.b5.color">0x32B86B</option>
  <option name="charting.b5.alpha">1.0</option>
  <option name="charting.b5t">solidFill</option>
  <option name="charting.b5t.color">0x32B86B</option>
  <option name="charting.b5t.alpha">0.5</option>
  <option name="charting.b6">solidFill</option>
  <option name="charting.b6.color">0x4A68E7</option>
  <option name="charting.b6.alpha">1.0</option>
  <option name="charting.b6t">solidFill</option>
  <option name="charting.b6t.color">0x4A68E7</option>
  <option name="charting.b6t.alpha">0.5</option>
  <option name="charting.myBrushPalette">list</option>`
  <option name="charting.myBrushPalette.brushes">[@b4,@b5,@b6,@b4t,@b5t,@b6t,@b1,@b2,@b3,@b1t,@b2t,@b3t]</option>
  <option name="charting.chart.columnBrushPalette">@myBrushPalette</option>
</chart>

Disclaimer
Using custom brushes to change colors, like above, is evidently not supported by JSchart, the HTML5 chart display mechanism. So... it's going to resort to flash.

Result
(click for full size photo!)

Re: How do I make a multi-dimension timechart?

Ayn — Tue, 18 Sep 2012 19:37:24 GMT

Amazing answer. Kudos.

Re: How do I make a multi-dimension timechart?

justgrumpy — Wed, 19 Sep 2012 20:46:57 GMT

This is an excellent example of using the "advanced" charting features and brushes. Thanks.

topic Re: How do I make a multi-dimension timechart? in Splunk Search

How do I make a multi-dimension timechart?

Re: How do I make a multi-dimension timechart?

Re: How do I make a multi-dimension timechart?

Re: How do I make a multi-dimension timechart?