Splunk Search

stats and stacked chart for simple event log

jmaschle
New Member

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!

Tags (3)
0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

yannK
Splunk Employee
Splunk Employee

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

0 Karma

piebob
Motivator

i suggest you ask another question for this one 🙂

0 Karma

jmaschle
New Member

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

0 Karma

jmaschle
New Member

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

0 Karma

Ayn
Legend

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?

0 Karma

lguinn2
Legend

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

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>