Archive

base streamstats on timestamps in data not arrival time

Path Finder

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

Tags (1)
0 Karma

Esteemed Legend

You are missing spaces:

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

Path Finder

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 🙂

0 Karma

Champion

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

0 Karma

Path Finder

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
}
}

0 Karma

Champion

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?

0 Karma

Path Finder

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.

0 Karma

Champion

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?

0 Karma

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!