topic Re: How to get data by _time in Splunk Search

How to get data by _time

Learner — Wed, 19 May 2021 11:32:06 GMT

Hi everyone,

index=xyz source="something" |stats earliest(_time) as minTime latest(_time) as maxTime values(activityName) as activityName values(accessSeekerId) as accessSeekerId values(businessChannel) as businessChannel values(status) as status values(ttStatus) as ttStatus values(feature) as feature by requestId | eval duration = maxTime - minTime | stats avg(duration) AS "AvgResponseTime" perc95(duration) AS "P95ResponseTime"

If I run this script for a day then I get some non-zero value for AvgResponseTime and P95ResponseTime also get different values for minTime and maxTime.

But if i run following script for same time, I get same values for minTime and maxTime.

index=xyz source="something" |bin _time span=1d |stats earliest(_time) as minTime latest(_time) as maxTime values(activityName) as activityName values(accessSeekerId) as accessSeekerId values(businessChannel) as businessChannel values(status) as status values(ttStatus) as ttStatus values(feature) as feature by requestId _time | eval duration = maxTime - minTime || eval Time=strftime(_time , "%d/%m/%Y %H:%M") | stats avg(duration) AS "AvgResponseTime" perc95(duration) AS "P95ResponseTime" by Time

At the end I get 0 for AvgResponseTime and P95ResponseTime which is not matching with above query as value of duration gets as 0.

I want output of based on each day AvgResponseTime and P95ResponseTime

I hope I give clear idea about my issue. What would I do to resolve this?

Re: How to get data by _time

ITWhisperer — Wed, 19 May 2021 11:37:45 GMT

bin will set all the times to the beginning of the day, so when you do stats by the same field, they will all be the same - hence 0 duration. Use bin to set up a different field (day) and do stats by that, thus leaving _time undisturbed

index=xyz source="something" |bin _time span=1d as day |stats earliest(_time) as minTime latest(_time) as maxTime values(activityName) as activityName values(accessSeekerId) as accessSeekerId values(businessChannel) as businessChannel values(status) as status values(ttStatus) as ttStatus values(feature) as feature by requestId day | eval duration = maxTime - minTime || eval Time=strftime(day, "%d/%m/%Y %H:%M") | stats avg(duration) AS "AvgResponseTime" perc95(duration) AS "P95ResponseTime" by Time

Re: How to get data by _time

Learner — Fri, 21 May 2021 07:35:28 GMT

thank you very much @ITWhisperer . it solved some part of my problem. i checked the values for "AvgResponseTime" and "P95ResponseTime" and they are not matching. Suppose i ran my code without filtering _time, i get values as 0.4 and 1.7 to "AvgResponseTime" and "P95ResponseTime" respectively. but if i use your code i get average values of "AvgResponseTime" and "P95ResponseTime" as 0.4 and 1.9 respectively. why is that so? and how to solve this?

thanks, in advance!

Re: How to get data by _time

ITWhisperer — Fri, 21 May 2021 07:58:00 GMT

What do your two searches look like?

Re: How to get data by _time

Learner — Wed, 26 May 2021 10:13:11 GMT

Query which I specified, is my actual query.