How to compute time range spread over multiple files.

[sanjeevdixit]

Hi Everyone,
I want to calculate availability time range of each employee based on the records present in two files (say Start_Log and End_Log). Start_Log has start time and End_Log has the end time. The difference is the time in which employee is available. Each employee can have multiple records in both files. This means they can be available multiple times in a day.

Start_Log:

Event1
EmpID : 1
Start_Time : 09:00 AM

Event2
EmpID : 2
Start_Time : 10:00 AM

Event3
EmpID : 1
Start_Time : 11:00 AM

End_Log:

Event1
EmpID : 3
End_Time : 08:15 AM

Event2
EmpID : 1
End_Time : 09:20 AM

Event3
EmpID : 2
End_Time : 10:15 AM

Event4
EmpID : 1
End_Time : 11:10 AM

Expected result:

EmpID : 1
Availability_time : 09:00 AM - 09:20 AM

Event2
EmpID : 2
Availability_time : 10:00 AM - 10:15 AM

Event3
EmpID : 1
Availability_time : 11:00 AM - 11:10 AM

How can I achieve this? Is there any way to loop through the records of files?
Thanks for help in advance.

[somesoni2 · Accepted solution]

Try something like this

source=Start_Log OR source=End_Log | transaction EmpID maxevents=2 startswith=source=Start_Log endswith=source=End_Log | eval Availability_time=Start_Time." - ".EndTime| table EmpID, Availability_time

[sanjeevdixit]

Hi Somesoni2,

Thanks for your help. It solved this issue. I have 1 more query though: Now that I have the time range with me, I want to count the hits made during this time range, where the hits time is logged in another log file which does not have EmpId.

I didn't mention this in initial query as i thought i would be able to achieve this once time range is found, but now I am not able to link this result with third log. Thanks for your help in advance.