Splunk Search

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

Path Finder

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
``````
Tags (4)
1 Solution
Revered Legend

``````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
``````
Revered Legend

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

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Path Finder

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.

Motivator

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
``````
Path Finder

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.

Motivator

He might have to remove the table part.

Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...