base streamstats on timestamps in data not arrival time

[jperezes]

Hi,
I have a question I don't know if is quite possible to do. I have to calculate some data between events, but not in the order of arrival but in the order the user did the action, using a timestamp passed into the event data to splunk.
So far the only way to get time statistics between events has been only successful if based on _time. i.e:

|streamstats current=f last(_time) as next_time by userId
|eval gap = next_time - _time
| stats count, avg(gap) as avg_gap, var(gap) as var_gap by userId

What I would want is the following but not working at all:

|eval timeStamp = strptime(value.timestamp,"%Y-%m-%dT%H:%M:%S.%3N%Z")
|streamstats current=f last(timeStamp) as next_time by userId
|eval gap = next_time - timeStamp
| stats count, avg(gap) as avg_gap, var(gap) as var_gap by userId

Thanks in advance!

Juan

[woodcock]

You are missing spaces:

... | eval timeStamp = strptime(value.timestamp,"%Y - %m - %dT%H: %M: %S.%3N%Z") | ...

[jperezes]

Thanks woodcock, I think is ok now the time format doesn't have spaces:
2017-03-08T16:59:30.491Z

the trick was the tildes 'value.timestamp*'* just that 🙂

[rjthibod]

Can you provide an example raw event so we can see the fields?

[jperezes]

Sure thanks.
This is a sample of a relevant JSON data format received in the event, the action is Share so I would need, for example, to get statistics between one share and the next. But using requestTimestamp, not arrival time (_time), as this data can be sent to splunk way after it has been produced:

{
product_name: Native Client
product_version: 1.0.03
userId: serfr342-204S88T05285
value: {
errorDetail:
action: Share
mediaStatistics: {
[ + ]
}
requestTimestamp: 2017 - 03 - 08T03: 47: 49.016Z
}
}

[rjthibod]

Does the line

|eval timeStamp = strptime(value.timestamp,"%Y-%m-%dT%H:%M:%S.%3N%Z")

Actually return an epoch value that is correct or is that failing?

[jperezes]

Failing is not, I actually added timestamp for simplicity in the first question but is correct as requestTimestamp in the real search.
Not 100% sure about the correct returned format though, is there a way to easily check a time value.
Thanks a million.

[rjthibod]

The field timestampt should be a number representing the epoch seconds equivalent to the string in value.timestamp. So, are you seeing correct values in timestamp if you stop your query after that line?

[jperezes]

Hi, found the issue, second time that happens to me and drives me crazy...

instead of
strptime(value.timestamp,"%Y-%m-%dT%H:%M:%S.%3N%Z")

should be

strptime('value.timestamp',"%Y-%m-%dT%H:%M:%S.%3N%Z")

thanks for the help on finding that.
Juan