Calculate the difference between the event time and that in a field

[raoul]

I am trying to calculate the difference between the time of an event and the time as it exists in a field of the event.

A typical event record looks like:

2011-01-18 10:38:54 PtcId=4393749 RemoteDt="2011-01-18 10:38:40" Region="KE" MID=72331615         TID=90000662 Msg=0520 NameLoc="RECORDEZ MUSIC" Rsp=00 Amt=0 Ccy=404 Node=TAISOSrc1RB Batch=146 STAN=000417

I would like to calculate the difference between the timestamp of the event as seen by the server and that as seen by the remote device.

The server timestamp (the leading 2011-01-18 10:38:54 in the example above) I presume is in _time. The remote device timestamp is in the RemoteDt field.

I have tried:

 | eval Timediff=_time - RemoteDt

but this does not work. I have figured out that the time formats are incompatible, but I cannot find out how to convert them.

I have tried:

 | convert timeformat="%y-%m-%d %H:%M:%S" mktime(RemoteDt) as TerminalTime | eval Diff=_time - TerminalTime | table TID, Diff

but Diff remains stubbornly blank.

[MarioM]

did you try adding a convert for _time?

| convert timeformat="%y-%m-%d %H:%M:%S" mktime(RemoteDt) as TerminalTime | convert mktime(_time) as c_time  | eval Diff=c_time - TerminalTime | table TID, Diff

[raoul]

I have now tried that. I see that c_time appears as a field. TerminalTime is still absent; I can only suppose that Splunk is not happy with the convert, but without error messages it is difficult to debug...

[sumnerm · Accepted solution]

Take a look at the convert function:

http://www.splunk.com/base/Documentation/latest/SearchReference/Convert

This could be used with the mktime option to convert RemoteDt into epoch time, and then eval should work.

Note further to MarioM's answer below, the conversion of _time is not necessary as it is already an epoch time value. Once you've got the time format correct on TerminalTime it should work OK. This is what worked for me:

index=<index name> | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Session_Timestamp) as SessionCTime| eval duration =_time-SessionCTime | fields + _time duration 

With the Session_Timestamp fields being of this form

Session_Timestamp=2011-01-18 13:41:02

[raoul]

Yes, it was the %Y that was the issue. Always the little things...

Thanks

[sumnerm]

Looking at your time format I think it should be %Y not %y.

I've just tried a similar example from one of my logs and it worked OK.

[raoul]

I don't know if I am being dense, but I cannot get convert to do anything useful. See the syntax I am using in the question above.