Create time range for each customer from adjacent time

[xiangtaner]

Hi,

Originally I generated a table from a Splunk query in the following form:

CustomerID SeenTime
1234 8/5/2015
1234 8/19/2015
1234 9/1/2015
2345 10/3/2015
2345 10/9/2015

Now, I would like to create a time range for each row of each customer using the current row's Seen Time as min_SeenTime and the next row's seen time as max_SeenTime. If there is no next row seen time for the customer, then use the today's date.

For this specific example, I would like to generate the following result:

CustomerID Min_SeenTime Max_SeenTime
1234 8/5/2015 8/19/2015
1234 8/19/2015 9/1/2015
1234 9/1/2015 Today's Date
2345 10/3/2015 10/9/2015
2345 10/9/2015 Today's Date

Could someone please help me how to realize this? Thanks a lot!

Wayne

[sundareshr · Accepted solution]

Like this

 ... | streamstats current=f window=1 max(SeenTime) as max by CustomerID | eval Max_SeenTime=coalesce(max, strftime(now(), "%d/%m/%Y")) | table CustomerID SeenTime Max_SeenTime

[xiangtaner]

Smart solution! Thanks a lot, sundareshr!

But what if I would like to use next customer's first seen time as the max seen time for the current customer? i.e. I would like to produce the results like below, how should I change the query accordingly? Please further advise me. Thanks!

CustomerID Min_SeenTime Max_SeenTime
1234 8/5/2015 10/3/2015
2345 10/3/2015 Today

[sundareshr]

This should do it

| eval n=strftime(now(), "%m/%d/%Y") | streamstats current=f window=1 max(SeenTime) as max by CustomerID | streamstats current=f window=1 max(SeenTime) as Next_Customer_Time| eval Max_SeenTime=coalesce(max, Next_Customer_Time, n) | table cid seentime mst Next_Customer_Time max

[xiangtaner]

Thanks for the response, but it seems that it didn't result in the expected results. Thanks!

Here is what I came up with:

| sort - SeenTime
| streamstats current=f last(SeenTime) as pre_time dc(CustomerID) as count
| eval n=strftime(now(), "%m/%d/%Y")
| eval end_time = strftime(coalesce(pre_time, n), "%m/%d/%Y")
| eval start_time = strftime(SeenTime, "%m/%d/%Y")
| stats min(start_time) as start_time max(end_time) as end_time by CustomerID count
| table CustomerID start_time end_time