Calculate diff between two entries in a transaction?

[ericdp]

I'm trying to calculate the amount of time between two events and I'm having a lot of trouble. Because of some requirements on the logging side, I'm logging a download start as one event, and a download end as another. They are both reporting the timestamp for their event, but the client that sends up the event batches sending up the events, and thus the default timestamp that Splunk uses isn't getting me the right data. Here's the query that I run to get the events properly correlated.

(LS-XUCF OR LS-XUBF) | transaction ConsoleId LogonId ItemUrl startswith=LS-XUBF endswith=LS-XUCF

And here are some results:

1  6/14/10 11:52:48.096 AM  
2010/06/14 11:52:48.096|LS-XUBF|test|0C20241536|3|1CB0BB7F866325E
2010/06/14 11:52:48.096|LS-XUCF|test|0|0C20241536|3|1CB0BB7FB3C5623


2  6/14/10 11:52:48.096 AM
2010/06/14 11:52:48.096|LS-XUBF|foo|0C20241536|3|1CB0BB7F864141D
2010/06/14 11:52:48.096|LS-XUCF|foo|0|0C20241536|3|1CB0BB7F9F77486

3  6/14/10 11:09:55.809 AM
2010/06/14 11:09:55.809|LS-XUBF|bar|08377FDE66|3|1CB0BB1FECF39B6
2010/06/14 11:11:11.502|LS-XUCF|bar|0|08377FDE66|3|1CB0BB2437D395B

How can I get the difference between the ClientTimestamp fields (the last field in each event) calculated for each transcation? The duration autovalue uses the timestamp that the event was reported on, which as I mentioned earlier, is not useful for me. I also need a way of getting this number from hex to decimal, which right now is by way of a Python script that was written for an earlier version of Splunk - is there an easier way to do this as well?

[gkanapathy · Accepted solution]

Well, there are two things:

• Splunk will read a hex timestamp, but I believe it expects it still to be in epoch seconds. Yours are in...I don't know what those are.

• Okay, lets suppose you can write a an eval expression to convert that hex timestamp into epoch seconds. Then (making sure the field is extracted as, say, hextime:

   ... | eval epochtime=blah(hextime) | stats min(epochtime),max(epochime) by ConsoleId, LogonId,ItemUrl
  

  might be better? If the stats approach isn't right for you, i.e., the ConsoleId,LogonId,ItemUrl combination isn't unique, then you can use transaction and then

   ... | eval first=mvindex(epochtime,0) | eval second=mvindex(epoch,1) | eval d=second-first
  

as hextime and epochtime will have been placed into a multivalue field by transaction.

[ericdp]

Using mvindex was the key - thank you so much!