The search is basically okay, though I would probably slightly shorten it as:
sourcetype=vpn <my search>
| rex "(?i)\(LOCAL\\\(?P<Username>[^)]+)\)"
| rex "(?i)\((?P<UserAcct>[^\d]+)\)"
| eval user=coalesce(Username,UserAcct)
| bucket _time span=1d
| stats dc(_time) as days_logged_in by user
| where days_logged_in > 11
| sort - days_logged_in
If this is a 100% dense search, (i.e., the events returned is pretty much all the events in the index for the time period in question) then I would expect this to run in 20 minutes 8 hours or less on a single server with the recommended CPU and disk. Probably less. However, if the events are more spare or rare within the index, then it would probably run proportionately slower. If they're 10%, then it would take about 10 times as long (i.e., 5 to 10 hours) and if they're 1 in 20, about 20 times as long, down till about 1 in 500 or 1 in 1000 when it will stabilize to a constant level. (This is known as a sparse search at this point, see http://docs.splunk.com/Documentation/Splunk/6.1.2/Installation/HowsearchtypesaffectSplunkperformance for explanation.)
If you want better performance, you can:
Try to isolate the selected events to their own index, or at least with less "other" data so that the search is more dense
Use summarization, or report acceleration, or create an accelerated data model
Check to be sure that the hardware is giving you the real CPU and disk performance required
Distribute the index over multiple indexer nodes
A combination of the above
Edit: Sorry, I thought it was a total of 50 million, not 50 million per day. So that should be 30 times what I suggested, or 7 to 10 hours, not 15-20 minutes.
... View more
