i am going to try to chart the day time check attendance of our employee over the last week.
For example i am amble to have this table by week days :
index="asistencia" No=5 earliest=-7d@w0 latest=@w7 | eval myHour=strftime(_time, "%H") | Where (myHour >= 1) | stats values(Date) as entrada by date_mday Name | sort date_mday
1 5 Jonathan 05/05/2014 02:02:47 p.m.
05/05/2014 06:19:58 p.m.
2 6 Jonathan 06/05/2014 05:54:03 p.m.
06/05/2014 08:30:05 a.m.
3 7 Jonathan 07/05/2014 06:04:21 p.m.
07/05/2014 08:38:07 a.m.
4 8 Jonathan 08/05/2014 06:59:31 p.m.
08/05/2014 08:16:02 a.m.
5 9 Jonathan 09/05/2014 06:08:43 p.m.
09/05/2014 08:12:51 a.m.
Dont know how to graph this table for all the employees having plotting the arrival time and the end day time , also we want to be able to plot 2 lines in the chart showing the normal entrance time and end of the day time.
Thanks for your help.
Regards
Daniel
Try something like this:
index="asistencia" earliest=-7d@w0 latest=@w7 | eval myHour=strftime(_time, "%H") | Where (myHour >= 1) | eval Date=_time | bucket span=1d _time | eval Date=Date- _time| chart min(Date) as EntryTime max(Date) as ExitTime min(NormalEntryTime) as NormalEntryTime min(NormalExitTime) as NormalExitTime by _time Name | sort _time | eval NormalEntryTime=28800 | eval NormalExitTime=61200
Updated: shows duration as decimal no ("hour.minute")
index="asistencia" earliest=-7d@w0 latest=@w7 | eval myHour=strftime(_time, "%H") | Where (myHour >= 1) | eval Date=_time | bucket span=1d _time | eval Date=strftime(Date,"%H.%M") | chart first(Date) as EntryTime last(Date) as ExitTime min(NormalEntryTime) as NormalEntryTime min(NormalExitTime) as NormalExitTime by _time Name | sort _time | eval NormalEntryTime="8.00" | eval NormalExitTime="17.30" | eval _time=strftime(_time,"%Y-%m-%d")
Try something like this:
index="asistencia" earliest=-7d@w0 latest=@w7 | eval myHour=strftime(_time, "%H") | Where (myHour >= 1) | eval Date=_time | bucket span=1d _time | eval Date=Date- _time| chart min(Date) as EntryTime max(Date) as ExitTime min(NormalEntryTime) as NormalEntryTime min(NormalExitTime) as NormalExitTime by _time Name | sort _time | eval NormalEntryTime=28800 | eval NormalExitTime=61200
Updated: shows duration as decimal no ("hour.minute")
index="asistencia" earliest=-7d@w0 latest=@w7 | eval myHour=strftime(_time, "%H") | Where (myHour >= 1) | eval Date=_time | bucket span=1d _time | eval Date=strftime(Date,"%H.%M") | chart first(Date) as EntryTime last(Date) as ExitTime min(NormalEntryTime) as NormalEntryTime min(NormalExitTime) as NormalExitTime by _time Name | sort _time | eval NormalEntryTime="8.00" | eval NormalExitTime="17.30" | eval _time=strftime(_time,"%Y-%m-%d")
Thats perfect for me , thanks you a lot.
Yeah... That's the problem with visualizations. you need to have a numerica value to plot them in chart (y-axis). The best formatting which may be possible would be show as decimal no like 9.00, 17.30 etc.
How could i convert 28800 in 08:00 am ?
Great,it's show exactly what i want , but how could i have the time showing like 09:00 , 19:00. ?