How do i Timewrap this search

[ttudor]

I have the following search. It returns the median duration for the weeks selected. I want to be able to show a week by week comparison in one chart. I am using a stacked bar chart, and would like each of the three weeks to display grouped by school name.

school name 1 week 1 hour and minutes
week 2 hours and minutes
week 3 hours and minutes

school name 2 week 1 hour and minutes
week 2 hours and minutes
week 3 hours and minutes

index=summary_lms source=2q13Summary-student earliest=-3w@w0 latest=-1w@w [inputlookup oracle.csv

| where like(DISTRICT_NAME,"$district$%") AND like(DISTRICT_STATE,"$state$") AND ACTIVE_STATUS=1
| lookup search_schid2uuid school_id OUTPUT sch_id

| return 500 sch_id]

| stats sum(problems) as problems sum(sections) as sections sum(duration) as duration by sch_id, stu_id

| stats median(duration) as medianDuration median(problems) as median-problems median(sections) as median-sections dc(stu_id) as students by sch_id

| eval hours=if(medianDuration/3600<1,0,round(medianDuration/3600))
| eval minutes=if(hours>0,(round(medianDuration/60)-(round(medianDuration/3600)60)),round(medianDuration/60))
|eval minutes=if(minutes<0,0,minutes)

| eval seconds=if(minutes<60,(round(medianDuration/60)-(round((medianDuration/60).60))),round(medianDuration/60))

| lookup search_schid2uuid sch_id OUTPUT school_id | eval ACTIVE_STATUS=1

| lookup search_oracle school_id ACTIVE_STATUS OUTPUT SCHOOL_NAME DISTRICT_NAME DISTRICT_STATE

| fields SCHOOL_NAME minutes hours

[martin_mueller]

How 'bout this then?

| stats count | eval data ="Bowman Middle School 4 0 12/08/2014
Wilson Middle School 24 0 12/08/2014
Wilson Middle School 19 0 12/09/2014
Wilson Middle School 21 0 12/10/2014
Wilson Middle School 0 0 12/11/2014
Wilson Middle School 25 0 12/12/2014
Haggard Middle School 16 5 12/07/2014
Haggard Middle School 12 0 12/08/2014
Haggard Middle School 11 0 12/09/2014
Haggard Middle School 1 0 12/10/2014
Haggard Middle School 5 0 12/11/2014
Haggard Middle School 3 0 12/12/2014
Armstrong Middle School 7 0 12/08/2014
Carpenter Middle School 14 0 12/08/2014
Carpenter Middle School 5 0 12/09/2014
Carpenter Middle School 7 0 12/10/2014
Carpenter Middle School 7 0 12/11/2014
Schimelpfenig Middle School 29 0 12/09/2014
Hendrick Middle School 0 0 12/08/2014
Renner Middle School 18 1 12/07/2014" | makemv data delim="
" | mvexpand data
| rex field=data "(?<school>[\w\s]+)\s+(?<minutes>\d+)\s+(?<hours>\d+)\s+(?<date>\S+)"
| eval _time = strptime(date, "%m/%d/%Y")
| eval duration = (hours*60 + minutes)*60
| bin span=1w _time | eval readable = strftime(_time, "%m-%d") . " to " . strftime(relative_time(_time, "+6d"), "%m-%d")
| chart median(duration) over school by readable

If you have data with the fields extracted already you can start from the | eval duration = ..., adjust field names as necessary.

[martin_mueller]

You're not going to get the numeric chart's horizontal axis in anything other than numbers. You can have the duration in seconds as it was in my example, or minutes, or hours - just change the eval duration = ... accordingly.

Your NULL suggests that your search isn't filling the readable field correctly. Tear back commands off the end one by one to look for the culprit.

[ttudor]

Thanks, but i need the duration broken down by hours and minutes. When I run the code below, it does not display the date just the schools on the right and then null for the legend. The code I ran is

index=summary_lms source=2q13Summary-student [inputlookup oracle.csv | where like(DISTRICT_NAME,"Plano Ind School District") AND like(DISTRICT_STATE,"TX") AND ACTIVE_STATUS=1 | lookup search_schid2uuid school_id OUTPUT sch_id | return 500 sch_id] | stats sum(duration) as duration by sch_id, stu_id | stats median(duration) as medianDuration by sch_id | eval hours=if(medianDuration/3600<1,0,round(medianDuration/3600)) | eval minutes=if(hours>0,(round(medianDuration/60)-(round(medianDuration/3600)60)),round(medianDuration/60)) |eval minutes=if(minutes<0,0,minutes) | eval seconds=if(minutes<60,(round(medianDuration/60)-(round((medianDuration/60).60))),round(medianDuration/60)) | lookup search_schid2uuid sch_id OUTPUT school_id | eval ACTIVE_STATUS=1 | lookup search_oracle school_id ACTIVE_STATUS OUTPUT SCHOOL_NAME DISTRICT_NAME DISTRICT_STATE | fields SCHOOL_NAME minutes hours | eval _time = strptime(date, "%m/%d/%Y")
| eval duration = (hours*60 + minutes)*60
| bin span=1w _time | eval readable = strftime(_time, "%m-%d") . " to " . strftime(relative_time(_time, "+6d"), "%m-%d")
| chart median(duration) over SCHOOL_NAME by readable

It displays this: http://workspl.blogspot.com/2015/01/blog-post.html

[ttudor]

Sample data would be :

SCHOOL_NAME minutes hours date
Bowman Middle School 4 0 12/08/2014
Wilson Middle School 24 0 12/08/2014
Wilson Middle School 19 0 12/09/2014
Wilson Middle School 21 0 12/10/2014
Wilson Middle School 0 0 12/11/2014
Wilson Middle School 25 0 12/12/2014
Haggard Middle School 16 5 12/07/2014
Haggard Middle School 12 0 12/08/2014
Haggard Middle School 11 0 12/09/2014
Haggard Middle School 1 0 12/10/2014
Haggard Middle School 5 0 12/11/2014
Haggard Middle School 3 0 12/12/2014
Armstrong Middle School 7 0 12/08/2014
Carpenter Middle School 14 0 12/08/2014
Carpenter Middle School 5 0 12/09/2014
Carpenter Middle School 7 0 12/10/2014
Carpenter Middle School 7 0 12/11/2014
Schimelpfenig Middle School 29 0 12/09/2014
Hendrick Middle School 0 0 12/08/2014
Renner Middle School 18 1 12/07/2014

[martin_mueller]

If you'd pastebin some sample data (csv, json, etc) I'd give it a whirl.

[ttudor]

When I do the eval for duration to a string, it does not display in the chart. The legend is there but no data is shown in the chart. Currently the code for each individula week displays like this,

http://4.bp.blogspot.com/-6AbZxJscWBg/VHM92NE7OsI/AAAAAAAAAAM/-NWFiWjqpto/s1600/duration.jpg

I want to display this

http://2.bp.blogspot.com/-Ybf24W8mLXE/VHM-lkhifbI/AAAAAAAAAAU/FcamPwt73Pc/s1600/splunk2.jpg

Does that help any?

[martin_mueller]

Check out http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/timechart

If you want a readable duration, consider using ... | eval readableDuration = tostring(medianDuration, "duration").

[ttudor]

I am not sure what to code for the timechart

[martin_mueller]

You need a timechart before you can timewrap.