How do I find the highest number of days between events for a given month

[splunker1981]

Splunk experts -

Trying to figure this out, but at a point where I am stuck. I would like to come up with the largest number of days between events for a 30-day period by looking at a specific field. I've tried using running max against date_mday field but it doesn't seem to produce the results I'm looking for. I think what needs to happen is I need to somehow keep a count of number of days between events and then run max against that field?

So for example here is the output of date_mday for last month and then a manual subtraction between occurrences. So using the example below I can quickly see that we went 9 days between two sales as being the largest gap during last month. What's the best way to tackle this?

Date  Number of Days Between Sales
1      1 
2      1
3      1
6      3
9      3
16     7
25     9  This is the value I would like to display
27     2
31     4

[somesoni2 · Accepted solution]

How about this one?

searchHere location="CHI" | bucket span=1d _time  | stats count by sku, _time | streamstats current=f window=1 values(_time) as prev_time by sku | eval timeDelta=(_time-prev_time)/86400 | stats max(timeDelta) as salesDaysDiff by sku 

[niketn]

Have you already come up with a table like the one in question?

If not can you add some details around the sales field is it having values like sales="y" or sales="n"?

If you can add details about the field and it's values that will be useful.

[splunker1981]

No, the table is an example of what I am looking to accomplish.

As for the fields I'm really just focusing on 3 fields to try to figure out number of days between sales, but I would only like to focus on sku's where the total unique number of days is greater than 10. Here are the fields I'm working with as an example

date_mday, sku, location

date_mday has values: 1-31 for the day of the month
location has the stores location: NY, FL, CHI, CA
sku: has a product number

The search below gets me a count of days , but when I remove the sku filter to try to get a number across all sku's it seems to provide incorrect results

searchHere location="CHI" sku="99860001"
| delta date_mday AS timeDelta p=1 
| dedup date_mday 
| stats count(timeDelta) as salesDaysDiff by sku

So ideally this is what I'm really after. If the number of days I have a sales event for by location/sku and that number is greater than 10, then find the largest number of days between sales. Thus ignoring the days between sales for any skus, locations where we didn't have sales for at least 10 days.

[gokadroid]

Please have a look at delta command here and that is what might help you, something like:

your query to return events
| stats count by date_mday
| table date_mday
| delta date_mday as differenceDate p=1
| sort -differenceDate
| head 1

[splunker1981]

Hi gokaadroid,

I tried what you suggested which partially gets me there. If I run the following search

searchHere location="CHI" sku="99860001"
| delta date_mday AS timeDelta p=1
| dedup date_mday
| stats count(timeDelta) as salesDaysDiff by sku


I get the correct count of days, but when I remove the sku="99860001" and try to run this against all the skus then the counts are no longer accurate.

[alemarzu]

He might have to remove the table part.