Splunk Search

Create time range for each customer from adjacent time

xiangtaner
Path Finder

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

Tags (1)
0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

0 Karma

sundareshr
Legend

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

xiangtaner
Path Finder

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

0 Karma

sundareshr
Legend

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

xiangtaner
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...