stats and stacked chart for simple event log

[jmaschle]

i have several years of daily event data in a sqlserver table i would like to stack and chart and get some good stats on each step. data is collected once per day over the course of a couple of hours. Steps have varied over time both in name and number. Also, It may retry starting steps several times, but it always ends with Done. data looks like this:

date status
2012-09-25 08:00:00.0001 Done
2012-09-25 07:30:00.0001 S3
2012-09-25 07:00:00.0001 S2
2012-09-25 06:00:00.0001 S1
2012-09-25 05:30:00.0001 Nope
2012-09-25 05:00:00.0001 S1
2012-09-25 04:31:00.0001 Nope
2012-09-25 04:30:00.0001 S1
2012-09-25 04:01:00.0001 Nope
2012-09-25 04:00:00.0001 S1
2012-09-24 07:00:00.0001 Done
2012-09-24 06:30:00.0001 S3
2012-09-24 06:00:00.0001 S2
2012-09-24 05:00:00.0001 S1
2012-09-24 04:31:00.0001 Nope
2012-09-24 04:30:00.0001 S1
2012-09-24 04:01:00.0001 Nope
2012-09-24 04:00:00.0001 S1

Thats right - two fields. The duration of each step is calculated by the difference in dates in ajacent rows.

by day, i would like to stack the duration of each step and show in a bar chart. further answer step duration average and stddev for weekdays, same stats by Day of week.
Like to ignore the nopes, and just start calculating with the first step after the nope.

Thanks!

[yannK · Accepted solution]

Do you mean something like :
From 2012-09-24 04:00:00.0001 S1 -> to 2012-09-24 04:01:00.0001 Nope took 60 seconds
and repeat for each steps ?

Check the delta or streamstats functions, and calculate the _time difference between the previous and the current events.
http://docs.splunk.com/Documentation/Splunk/4.3.4/SearchReference/Delta
http://docs.splunk.com/Documentation/Splunk/4.3.4/SearchReference/Streamstats

mysearch | delta p=1 _time AS seconds |table _time seconds _raw | rename _raw

[piebob]

i suggest you ask another question for this one 🙂

[jmaschle]

Perfect - thank you!
Next step - how do i get all the steps (except the Nopes) to chart on a stacked bar - x axis = day, y axis=seconds?color is the status name

[jmaschle]

from the table, i can calculate the duration for each step (ignoring everything before the last Nope). i would just like to visualize - by day - each step's contribution (in seconds) to the overall process duration. so a stacked chart, by day, showing the duration of each step in seconds as a piece of the stack.
newbie disclosure - new to Splunk - my first look with real data - was hoping to be able to find a ready made solution here, but still seems a little cryptic...Thanks in advance

[Ayn]

Could you tell us more precisely what the issue you are having is? Like lisa says, could you explain more clearly what you mean by 'stack' in thie context?

[lguinn2]

What exactly do you want to report? What fields do you have in Splunk - it loks like there are only 2 possible fields - "step" and "timestamp"...

Sorry, I just don't know what "stack" means