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
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
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
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
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
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