I am trying to extract the time duration in tabular format of check-in and check-out value, can someone please help. I am adding these value in my base search query:
index=* sourcetype=* host=* chkin=* chkout=* | eval TimeDifference= chkout-chkin | stats count by chkin,chkout
now there is a field called chkin
which indicate the customer check-in date and chkout
indicate customer checkout date...now I need to pull the difference of check-in and check-out to identify the stay period of customer ?
I am beginner so don't have much knowledge to use more commands in my search query.
Thanks in advance!
It is unlikely that any answer will actually work because you have not shown us sample data but maybe something like this:
index=YouShouldAlwaysSpecifyAnIndex sourcetype=AndSourcetypeToo chkin="*" chkout="*"
| rename COMMENT AS "If both fields are not in each event, total failure happened above"
| eval StayTimeSeconds = chkout - chkin
| rename COMMENT AS "if both values are not 'time_t' integers (AKA 'epoch'), total failure happened above"
| stats avg(StayTimeSeconds) AS AvgStayTime BY host
| eval AvgStayTime=tostring(AvgStayTime, "duration")
Hi iqbalintouch,
you have to use the epochtime format before calculate a time diff:
| your_search
| eval TimeDifference=strptime(chkout,"%Y-%m-%d %H:%M:%S")-strptime(chkin,"%Y-%m-%d %H:%M:%S")
| ...
obviously verify the date format of your chkout and chkin used in strptime.
I don't understand why you want to calculate Time Difference if you don't use it in the following command.
Remember that after Time Difference calculation if you run a stats count like | stats count by chkin,chkout
, you don't see Time Difference, to see it you have to add to stats command also the option values(TimeDifference) AS TimeDifference
.
Bye.
Giuseppe