Splunk Search

How can I count occurrences of something in the 7 days before that event?

usethedata
Path Finder

I have a summary index where I record an event for each VPN session for users, tracking things like the client IP address and summaries of what was done in the VPN session. I would like to see that data along with the number of times in the 7 days (for example) preceding that vpn session. In SQL, this would be easy, e.g.

SELECT t1.event_time, t1.clientip, (SELECT count(*) FROM mytable t2 WHERE t2.clientip=t1.clientip AND t2.event_time < t1.event_time AND t2.event_time >= t1.event_time-<7days>

But, I'm not finding a way to do this in Splunk. I've got a join that's sort of close

index=mysummary  | join clientip,userid [search index=mysummary earliest=-7d | stats count as 7Day_Same_IP by clientip,userid] 

But that query gives me the count for userid,clientip across the 7 days from when the search was done, not the 7 days from the event time for each event.

It seams like streamstats would get me in the right ballpark, but the window seems to be number of events, not duration.

0 Karma

norbert_hamel
Communicator

You can use a subsearch (aka inner search) to parameterize your main search (aka outer search). This way you can detect for example the newest event which matches your criteria using the inner search and then set the earliest and latest for the outer search.

For example:

index=MyIndex  
[search index=MyIndex  ErrorCode=404 
| head 1 
| eval latest=_time 
| eval latest=relative_time(latest,"-15m") 
| eval earliest=relative_time(latest,"-30m") 
| fields + latest earliest 
| format "(" "(" "" ")" "AND" ")"]

This will search in the first run the newest event (head 1) with ErrorCode=404.
Then set the field "latest" o the _time of this event.
Then just to show the concept, re-define latest as 15 minutes back.
Then calculate the field "earliest" as 30 minutes before the new latest.
The last 2 lines give the values for earliest and latest back to the outer search using the AND as operator.
Finally the outer search will search all events in the given index for that time range.

You can play around with that and see how this works for your application.

0 Karma

norbert_hamel
Communicator

OK, if you think that streamstats will help you to get your results, and you need a time based window parameter for streamstats, you should check a combination of timechart and subsequent streamstats.
| timechart span=1m count by UserID | streamstats sum(count*) window=10080
This will count the UserIDs per minute and search with streamstats over the previous 7 days (10080 minutes).

0 Karma

usethedata
Path Finder

Thanks for the answer. This gets in the right direction, but I'm not seeing how this gets me to where I really want to go.

This will (with some work) give me the count for the 7 days of the most recent login, for example. If there are a series of 15 logins for a given IP address and UserID, I'm not seeing how I can get the count in the 7 days preceding each of those specific events. I can't pass parameters from the outer to the inner (because the inner runs first). Essentially, I need a for each row, and streamstats is the only thing I've seen for that.

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...