Splunk Search

How do I make a multi-dimension timechart?

Jason
Motivator

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?

Tags (3)
1 Solution

Jason
Motivator

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

  1. Get the events (successful and failure messages) that you are interested in.
  2. Round their times to a convenient time interval that is divisible by 3 (for "earlier period, later period, and blank" bars - see below)
  3. Differentiate the failure and success messages with a marker such as type
  4. Count up the events by the category (in this case, sourcetype) and success or failure (type)
  5. Give this set of data a temporal marker, such as period=today
  6. If the event is a failure message, negate the count
  7. Append a search that does the same as steps 1-6 above, over an earlier timeframe (specified in earliest and latest)
  8. 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)
  9. Hack _time again to shift any period=today events over 1/3 of the time period decided on in step 2
  10. Create a label using the time period, success/failure, and category fields to make a unique series to graph by
  11. 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!)

Triple Axis Timechart

View solution in original post

Jason
Motivator

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

  1. Get the events (successful and failure messages) that you are interested in.
  2. Round their times to a convenient time interval that is divisible by 3 (for "earlier period, later period, and blank" bars - see below)
  3. Differentiate the failure and success messages with a marker such as type
  4. Count up the events by the category (in this case, sourcetype) and success or failure (type)
  5. Give this set of data a temporal marker, such as period=today
  6. If the event is a failure message, negate the count
  7. Append a search that does the same as steps 1-6 above, over an earlier timeframe (specified in earliest and latest)
  8. 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)
  9. Hack _time again to shift any period=today events over 1/3 of the time period decided on in step 2
  10. Create a label using the time period, success/failure, and category fields to make a unique series to graph by
  11. 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!)

Triple Axis Timechart

View solution in original post

justgrumpy
Path Finder

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

0 Karma

Ayn
Legend

Amazing answer. Kudos.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!