Splunk Search

## How to edit my search to calculate the average duration for last 7 days from the average duration of yesterday and display both?

Builder

I have my following search:

``````My Search earliest=-1d@d latest=@d
| convert ctime(_time) as Date_and_Time
| convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time
| eventstats range(time) as duration by user
| stats avg(duration) as avgDurationPeruser by user
| eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPeruser), "duration")
| table user Total_time_spent(out_of_collage)
``````

Which displays as follows

`````` user     yesterday_Total_time_spent(out_of_collage)
A123     19:39:35
b456     20:19:31
``````

I'm trying to get the average of Total_time_spent(out_of_collage) for last 7 days. Beside the yesterday_Total_time_spent(out_of_collage) which displays the results for yesterday?

Can anyone suggest me how to modify my search to get the result as needed?

Tags (5)
1 Solution
Legend

``````My Search earliest=-1d@d latest=@d
# why do you create a field called Date_and_Time which is never used
| convert ctime(_time) as Date_and_Time
# it is kind of strange to create a new time field, you don't need it
| convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time
# if you really want the range for each user, you should calculate it as stats range(_time) by users
# I can't figure out why you want to use the string-formatted time here or why you should use eventstats
| eventstats range(time) as duration by user
# The average may be calculated properly, which is amazing although I don't think it is a true average
| stats avg(duration) as avgDurationPeruser by user
| eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPeruser), "duration")
| table user Total_time_spent(out_of_collage)
``````

Needless to say, it sort of blew me away. I would do it this way:

``````My Search earliest=-8d@d latest=@d
| eval Timespan = if(_time > relative_time(now(),"-1d@d"),"Yesterday","Previous Week")
| eval Day = sftrtime(_time,"%y%j")
| stats range(_time) as totalDuration count  by user Day Timespan
| stats sum(totalDuration)  as totalDuration sum(count) as numEvents by user Timespan
| eval AvgTotalTimeSpent = tostring(ceil(totalDuration/numEvents), "duration")
| xyseries user Timespan AvgTotalTimeSpent
``````

Explanation of my search, line by line:
First, search over yesterday plus the prior 7 days; this is a total of 8 days.
Determine whether each event belongs to yesterday or the previous week, based on the timestamp.
Identify the day of the event - this will be used to calculate the daily timespan.
Calculate the time duration from first event to last event for each user day-by-day. (Otherwise, the weekly duration would run from the first event of the week to the last event of the week - clearly that would be weird.)
Also calculate the number of events, so we can calculate the duration per event: the average.
In the second stats command, sum the day-by-day into two groups: yesterday and the previous week.
Calculate the "average" and use the xyseries command to format the results.

Legend

``````My Search earliest=-1d@d latest=@d
# why do you create a field called Date_and_Time which is never used
| convert ctime(_time) as Date_and_Time
# it is kind of strange to create a new time field, you don't need it
| convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time
# if you really want the range for each user, you should calculate it as stats range(_time) by users
# I can't figure out why you want to use the string-formatted time here or why you should use eventstats
| eventstats range(time) as duration by user
# The average may be calculated properly, which is amazing although I don't think it is a true average
| stats avg(duration) as avgDurationPeruser by user
| eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPeruser), "duration")
| table user Total_time_spent(out_of_collage)
``````

Needless to say, it sort of blew me away. I would do it this way:

``````My Search earliest=-8d@d latest=@d
| eval Timespan = if(_time > relative_time(now(),"-1d@d"),"Yesterday","Previous Week")
| eval Day = sftrtime(_time,"%y%j")
| stats range(_time) as totalDuration count  by user Day Timespan
| stats sum(totalDuration)  as totalDuration sum(count) as numEvents by user Timespan
| eval AvgTotalTimeSpent = tostring(ceil(totalDuration/numEvents), "duration")
| xyseries user Timespan AvgTotalTimeSpent
``````

Explanation of my search, line by line:
First, search over yesterday plus the prior 7 days; this is a total of 8 days.
Determine whether each event belongs to yesterday or the previous week, based on the timestamp.
Identify the day of the event - this will be used to calculate the daily timespan.
Calculate the time duration from first event to last event for each user day-by-day. (Otherwise, the weekly duration would run from the first event of the week to the last event of the week - clearly that would be weird.)
Also calculate the number of events, so we can calculate the duration per event: the average.
In the second stats command, sum the day-by-day into two groups: yesterday and the previous week.
Calculate the "average" and use the xyseries command to format the results.

Builder

Thank You for your time. Could you also please suggest how to display only the results in which Yesterday duration is greater than the Last 7 days Average duration? @lguinn

Legend

Sure, add this line at the end:

``````| where Yesterday > 'Previous Week'
``````
Register for .conf21 Now! Go Vegas or Go Virtual!

### How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20. Learn More or Register Now >

Get Updates on the Splunk Community!