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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...