Report new users each month trended - large time range

[DanielFordWA]

Hi,

I have a request to trend new users on a web application by month over a two year period and produce this report each month.

I can get the numbers for each month using the below search..

index=iis earliest=-1mon@mon latest=@mon | search cs_username="PCC*" NOT [search earliest=-24mon@mon latest=-1mon@mon cs_username="PCC*" | dedup cs_username | fields + cs_username] |  stats dc(cs_username) by date_month

The first problem I have is doing this for each month and presenting it in one chart.

The second problem is that due to the date range the search is slow even for the above search that covers new users for the previous month, let alone the entire year by month. What would be the best way to speed this up?

Should I run a monthly search which is added to a summary index, and then query the summary index to produce the chart trended over two years?

I also have issues with the sub search timing out but I can always adjust the limits.conf using maxtime = X, or is there a better way of doing this?

Any advice is much appreciated.

Thanks,

Dan

[martin_mueller · Accepted solution]

The thing that's really slow for you is searching over the past two years to determine existing users. You should maintain a lookup of when a user has first been used. Then you can quickly run excluding searches for new users... see http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/ for a little more info on maintaining lookups automatically.

[DanielFordWA]

Thanks for the reply, the blog you link to answers most my questions,

Cheers

Dan