Splunk Dev

base streamstats on timestamps in data not arrival time

jperezes
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

woodcock
Esteemed Legend

You are missing spaces:

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

jperezes
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

rjthibod
Champion

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

0 Karma

jperezes
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

rjthibod
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

jperezes
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

rjthibod
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

jperezes
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...