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?
You could use transaction
for this. It is unfortunately pretty resource intensive but does the job.
... | transaction clientip maxspan=30m | stats count
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
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?
Depends on how accurate you want to be. Personally I count 2 sessions as being activity seperated by
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
You could use transaction
for this. It is unfortunately pretty resource intensive but does the job.
... | transaction clientip maxspan=30m | stats count
Yep, this works perfectly, thanks! I'll have to test it further to determine the extent of the performance impact.