Hello guys, can anyone help me to get the lenght of each operation?
I have start datetime and end datetime, both are in brazil timestamp Day/Month/Yeah-Hour:Minutes:Seconds.
How can I do that?
Follows a piece of sample:
index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval TEMPO_EXECUCAO = (DATA_FIM - DATA_INICIO)
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
DATA_INICIO DATA_FIM TEMPO_EXECUCAO
26/02/2015-04:07:06 26/02/2015-05:01:43
I bet the problem is that your fields are being treated as strings and not timestamps. You can correct this with eval
index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
the fieldformat lines aren't strictly necessary, but make the output more readable. (With the exception of _time, all timestamp and duration values are displayed as decimals)
Relevant links:
Functions for Eval and Where (search for strptime/strftime)
Fieldformat
I successfully tested the above by using the following:
index=_internal | head 1 | eval DATA_INICIO="26/02/2015-04:07:06" | eval DATA_FIM="26/02/2015-05:01:43" | table DATA_INICIO DATA_FIM
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
output:
DATA_INICIO DATA_FIM TEMPO_EXECUCAO
26/02/2015-04:07:06 26/02/2015-05:01:43 00:54:37.000000
Convert them to time_t
values using the eval
command and its strptime
function.
index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval DATA_FIM_t = strptime(DATA_FIM,"%d/%m/%Y-%H:%M:%S")
| eval DATA_INICIO_t = strptime(DATA_INICIO,"%d/%m/%Y-%H:%M:%S")
| eval TEMPO_EXECUCAO_t = (DATA_FIM_t - DATA_INICIO_t)
| eval TEMPO_EXECUCAO = tostring(TEMPO_EXECUCAO_t,"duration")
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
I bet the problem is that your fields are being treated as strings and not timestamps. You can correct this with eval
index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
the fieldformat lines aren't strictly necessary, but make the output more readable. (With the exception of _time, all timestamp and duration values are displayed as decimals)
Relevant links:
Functions for Eval and Where (search for strptime/strftime)
Fieldformat
I successfully tested the above by using the following:
index=_internal | head 1 | eval DATA_INICIO="26/02/2015-04:07:06" | eval DATA_FIM="26/02/2015-05:01:43" | table DATA_INICIO DATA_FIM
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
output:
DATA_INICIO DATA_FIM TEMPO_EXECUCAO
26/02/2015-04:07:06 26/02/2015-05:01:43 00:54:37.000000
Thanks a lot @emiller42!
Worked pretty well now...
Hello! Just a last question...
How would I make this shows only time difference in Hours:Minutes:Seconds ( 2 characters at most for each ) example
"Lenght = 01:32:18"
It's is currently this way:
index=full sourcetype=temp DATA_INICIO=* DATA_FIM=*
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=tostring(TEMPO_EXECUCAO, "duration")
| fieldformat TEMPO_EXECUCAO=strftime(TEMPO_EXECUCAO, "%d/%m/%Y-%T")
So doing a strftime
on TEMPO_EXECUCAO doesn't make sense because it's not a timestamp. And why are you fieldformatting the other fields twice? That's entirely redundant.
I was able to generate the output you're looking for via sed replacement:
index=_internal | head 1 | eval DATA_INICIO="26/02/2015-04:07:06" | eval DATA_FIM="26/02/2015-05:01:43" | table DATA_INICIO DATA_FIM
| eval DATA_INICIO=strptime( DATA_INICIO, "%d/%m/%Y-%T")
| eval DATA_FIM=strptime( DATA_FIM, "%d/%m/%Y-%T")
| eval TEMPO_EXECUCAO=DATA_FIM-DATA_INICIO
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
| fieldformat DATA_INICIO=strftime(DATA_INICIO, "%d/%m/%Y-%T")
| fieldformat DATA_FIM=strftime(DATA_FIM, "%d/%m/%Y-%T")
| fieldformat TEMPO_EXECUCAO=replace(tostring(TEMPO_EXECUCAO, "duration"),"^(.*)(\d{2}:\d{2}:\d{2})(.*)","\2")
Hello
You need to get your time to epoch, then calculate the difference, and then convert it back to human format. Something like:
... your base search | eval DATA_FIM_EPOCH = strptime(DATA_INICIO,"%d/%m/%Y-%H:%M:%S") | eval DATA_FIM_EPOCH = strptime(DATA_FIM,"%d/%m/%Y-%H:%M:%S") | eval difference_epoch = DATA_FIM_EPOCH - DATA_FIM_EPOCH | eval TEMPO_EXECUCAO = tostring(difference_epoch,"duration") | table table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
regards
Well, I don't know what happened with the conversion, but still doesn't show anything here, look:
index=* sourcetype=* DATA_INICIO=* DATA_FIM=*
| eval DATA_INICIO_EPOCH= strptime(DATA_INICIO,"%d/%m/%Y-%H:%M:%S")
| eval DATA_FIM_EPOCH = strptime(DATA_FIM,"%d/%m/%Y-%H:%M:%S")
| eval TEMPO_EXECUCAO = (DATA_FIM_EPOCH - DATA_INICIO_EPOCH)
| eval TEMPO_EXECUCAO = strftime(difference_epoch,"%d/%m/%Y-%H:%M:%S")
| table DATA_INICIO DATA_FIM TEMPO_EXECUCAO
Shows this table:
DATA_INICIO DATA_FIM TEMPO_EXECUCAO
26/02/2015-04:07:06 26/02/2015-05:01:43