Splunk Search

How to calculate working hours between start and end DateTime

kenchisho
Path Finder

I am trying to build a working hours report with splunk...

I have a start date and an end date like so:

start_time
2012-07-03 12:56:07

end_time
2012-07-14 16:30:22

calculating calendar hours is simple but how do i get working hours?

any ideas?

Tags (1)
0 Karma

yannK
Splunk Employee
Splunk Employee

you can convert your time to epochtime, ft not already done or use the splunk timestamp (in _time as epoch time)
if you want other time calculations, use eval.

see http://docs.splunk.com/Documentation/Splunk/4.3.3/SearchReference/Convert

mysearch_for_my_task 
| convert timeformat="%Y-%M-%d %H:%M:%S" start_second=mktime(start_time) AS start_second
| convert timeformat="%Y-%M-%d %H:%M:%S" start_second=mktime(end_time) AS end_second
| eval duration_second=end_second-start_second
| eval duration_hour=round(duration_second/360,0)
| table duration_hour

if the timestamp is the timestamp of the event, you can try a transaction


mysearch | transaction mycommonfield startswith="keyword1" endswith="keyword2"
| table mycommonfield _time duration

or use stats

mysearch | stats first(_time) AS recent last(_time) AS oldest by mycommonfield
| eval duration_second=recent-oldest
| eval duration_second=end_second-start_second
| eval duration_hour=round(duration_second/360,0)
| table mycommonfield duration_hour

0 Karma

kenchisho
Path Finder

well the number of working hours between the start_time and end_time

for example i start working on a task at 09:00 AM on monday and finish at 13:00 PM on wednesday... i wish to count the number of working hours on the task...

0 Karma

Ayn
Legend

What do you mean by "calculating" hours?

0 Karma
Get Updates on the Splunk Community!

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...

[Live Demo] Watch SOC transformation in action with the reimagined Splunk Enterprise ...

Overwhelmed SOC? Splunk ES Has Your Back Tool sprawl, alert fatigue, and endless context switching are making ...

What’s New & Next in Splunk SOAR

Security teams today are dealing with more alerts, more tools, and more pressure than ever.  Join us on ...