create table based on end time and time limit

[Carolina]

Hi,

i need to create a table with the following conditions:
This is my log:

proceso,start,end,diferencia,tiempo
PRUEBA02,19:10:00.00,01:00:00.00,05:10:00.00,22:50:00.10
PRUEBA03,01:00:00.00,03:00:00.00,02:00:00.00,04:00:00.00
PRUEBA04,22:00:00.00,00:00:00.00,03:00:00.00,23:00:00.00
PRUEBA05,18:00:00.00,04:00:00.00,10:00:00.00,22:30:00.00
PRUEBA06,21:00:00.00,02:00:00.00,05:00:00.00,01:00:00.00

I require to alert when the tiempo field is greater the end field.
The fields have a format of 24 hours.
In addition, some processes may end earlier than planned

I use the command where but but as a result it shows me the events that ended earlier and its limit is the next day.

source="pruebajob.csv" host="LAPTOP-3UME9D0Q" index="test"
| eval time1=strftime(strptime(tiempo,"%H:%M:%S.%2N"),"%H")
| eval time2=strftime(strptime(end,"%H:%M:%S.%2N"),"%H")
| eval time3=strftime(strptime(diferencia,"%H:%M:%S.%2N"),"%H")
| eval value=time1+time3
| eval compare=if(time1>time2,value,time3)
| where time1<compare

it is possible to create create a table?

[gcusello · Accepted solution]

Hi Carolina,
you have to transform times in epochtime before comparing, something like this:

 source="pruebajob.csv" host="LAPTOP-3UME9D0Q" index="test"
| eval et_end=strptime(end,"%H:%M:%S.%2N"), et_tiempo=strptime(tiempo,"%H:%M:%S.%2N")
| where et_tiempo>et_end

Bye.
Giuseppe

[Carolina]

Thank you !

[renjith_nair]

From the above example for the process PRUEBA02, tiempo : 22:50:00.10 , end :01:00:00.00
How do you distinguish between current day or next day? Is this process completed before the limit which is 01 AM?

[Carolina]

It is correct this process ended before its limit.
Only the time distinguishes it