Dashboards & Visualizations

How to group events by time range and show the count in a Pie Chart?

Contributor

Hi,
I want to group events by time range like below-
1. 1-6am
2. 6-9 am
3. 9-3.30am
4. 3.30-6.30pm
5. 6.30-1am

and show count of event for these time range in pie chart. how can I group events by timerange?

0 Karma
1 Solution

Champion

3rd row you mean to say 9 am - 3:30 pm right?
try this, this will split all values into grps,verify the output and then sue further
NOTE - bin span of 1 h has been used to trim down counts for testing as long as the group split works thishas no impact on removal

| eval time =strftime(_time,"%I:%M%P")
| eval time1 =strptime(time,"%I:%M%P")| eval mrkr1=strptime("06:00am","%I:%M%P")
|  eval mrkr2=strptime("09:00am","%I:%M%P")
|  eval mrkr3=strptime("03:30pm","%I:%M%P")
|  eval mrkr4=strptime("06:30pm","%I:%M%P")
|  eval mrkr5=strptime("01:00am","%I:%M%P")
| eval grp=case(time1>mrkr5 AND time1 <=mrkr1,"grp1",time1>mrkr1 AND time1 <=mrkr2,"grp2",time1>mrkr2 AND time1 <=mrkr3,"grp3",time1>mrkr3 AND time1 <=mrkr4,"grp4",time1>mrk4 AND time1 <=mrkr5,"grp5")
| stats values(time) by grp

View solution in original post

0 Karma

Champion

3rd row you mean to say 9 am - 3:30 pm right?
try this, this will split all values into grps,verify the output and then sue further
NOTE - bin span of 1 h has been used to trim down counts for testing as long as the group split works thishas no impact on removal

| eval time =strftime(_time,"%I:%M%P")
| eval time1 =strptime(time,"%I:%M%P")| eval mrkr1=strptime("06:00am","%I:%M%P")
|  eval mrkr2=strptime("09:00am","%I:%M%P")
|  eval mrkr3=strptime("03:30pm","%I:%M%P")
|  eval mrkr4=strptime("06:30pm","%I:%M%P")
|  eval mrkr5=strptime("01:00am","%I:%M%P")
| eval grp=case(time1>mrkr5 AND time1 <=mrkr1,"grp1",time1>mrkr1 AND time1 <=mrkr2,"grp2",time1>mrkr2 AND time1 <=mrkr3,"grp3",time1>mrkr3 AND time1 <=mrkr4,"grp4",time1>mrk4 AND time1 <=mrkr5,"grp5")
| stats values(time) by grp

View solution in original post

0 Karma

Contributor

Thanks for your answer ..
but it won't satisfy all condition like If my _time is 07pm then using above epoch comparison it won't give me result though it will fall under 6.30pm to 01am but epoch value 01am is less than 06.30 pm.

0 Karma

Champion

hmm i see what you mean..the if conditions was always going to be tricky..instead of 1 am what if we make it 12:59 PM in mrkr 5?

0 Karma

Champion

my main effort has been to show that you can grp by using mrkr times, the cae/ifs are a bit tricky but i think together we can fix it, what say?

0 Karma

Champion

hi @ips_mandar
Any updates?

0 Karma

Contributor

Thanks I resolved issue with said idea

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!