Graph log attendancy

[danisam]

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

[somesoni2 · Accepted solution]

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")

[danisam]

Thats perfect for me , thanks you a lot.

[somesoni2]

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.

[danisam]

How could i convert 28800 in 08:00 am ?

[danisam]

Great,it's show exactly what i want , but how could i have the time showing like 09:00 , 19:00. ?