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