Count sessions in a month - rolling 30 minute sessions

[vevani]

I have an IIS log file and want to count all sessions in a month. So the simple thing to do is a distinct_count(clientip). However the caveat is that a session is track as only lasting 30 mins. So if I have a log entry for someone at 10:00am, and another entry at 11:00am, then that's two sessions.

Is a query like this possible?

[kristian_kolb]

What's wrong with just using stats?

You can create your own 'limit' on how to decide what is determined as 'one session'. In the example below, I'll say that the same IP in the same day is one session. True, some sessions might start just before midnight and end just after (and count as two sessions). But then again, sometimes you'll have a single user having two (or more) 'real' sessions per day.

... | eval sessionid = clientip . "-" . date_mday | stats dc(sessionid) | ...

If you want to have the limit a bit shorter, you can set the sessionid to the combination of clientip and date_mday and date_hour;

... | eval sessionid = clientip . "-" . date_mday . "-" . date_hour | stats dc(sessionid) | ...

---

UPDATE:

For a 30 min session-length, use a slightly modified version of the above;

... | eval date_half_hour = if(date_minute < 30, "0", "30") | eval sessionid = clientip . "-" . date_mday . "-" . date_hour . ":" . date_half_hour | stats dc(sessionid)

In this last case, you will have created a sessionid for each event which consists of clientip, day-of-month, hour-of-day and a half-hour-marker;

10.11.12.13-27-14:30     <- event timestamp is 14:48
10.11.12.13-27-15:00     <- event timestamp is 15:05
172.16.1.54-12-21:30     <- event timestamp is 21:59

Should be faster than a transaction. If you have a JSESSIONID or similar in your logs, by all means - use that.

---

UPDATE2:

hmm come to think of it, there might be a simpler (easier to read) way that might do the trick:

... | timechart span=30min dc(clientip) | addcoltotals

OR

... | bucket _time span=30min | dedup clientip _time | stats c as "Unique sessions"

/K

[vevani]

Thanks for the response here. Sorry, i'm really new to splunking. How would i set the limit to 30 mins instead of just an hour as you've shown above?

[jonuwz]

Depends on how accurate you want to be. Personally I count 2 sessions as being activity seperated by . Thats pretty hard going, and possibly easier to implement in a custom search command.

The cheap and nasty way would be :

<your search> | bin _time span=30m | stats dc(clientip) as visitors_per_half_hour by _time | stats sum(visitors_per_half_hour) as uniq_visitors

[Ayn · Accepted solution]

You could use transaction for this. It is unfortunately pretty resource intensive but does the job.

... | transaction clientip maxspan=30m | stats count

[vevani]

Yep, this works perfectly, thanks! I'll have to test it further to determine the extent of the performance impact.