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

[pavanae]

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?

[lguinn2 · Accepted solution]

Here are my line-by-line comments on your search:

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.

[pavanae]

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

[lguinn2]

Sure, add this line at the end:

| where Yesterday > 'Previous Week'