What is the best approach to running monthly reports that can contain millions of events

[cburgman]

What is the best approach to running monthly reports that can contain millions of events? We are asked to provide proxy numbers based on number of unique IP addresses and total number of connections in given month. If we run the search out right it can take over 8 hours to complete. Not opposed to scheduling the search. Just making sure there aren't better options. Thanks in advance.

Here are the 2 searches we run:

Total connections and IP:
sourcetype="mcafee:wg:kv" | stats dc(src_ip), count as connections

Malicious connections and IP:
sourcetype="mcafee:wg:kv" (category="Malicious" OR category="Spam" OR category="Exploit" OR category="Phishing" OR category="Spy" OR category="Pup") | stats dc(src_ip), count as connections

[thiagodede]

For reports with high amount of data you can try summarize the values you need in a daily event on a summarized index.

On your case, you can run a daily search, for example at 00:01, summarizing the values you want from this sourcetype for the previous day and use the summarized index on your monthly search.

1) Create a index for you summarized data.
2) Settings >> Searchs, reports and alerts and click on the "new" button
3) Configure your search :
3.1) Include a name for your search, this will be included on the SOURCE field on your summarized Index.
3.2)Include the search you need. On your case you can bin the events by day
3.3)Setup the Earliest and Latest time for the previous day.
3.4)Check the box "Schedule this search" and setup the time to run the search every day.
3.5)Check the box "Enable" on the "Summary indexing" group and choose the index you create for this porpoise.
4) Change the report search to SUM the values from the summarized index for the specific month you want.

[DalJeanis]

For both of them together, run this one as your summary statistic every 4h, 12h, 1d or whatever, using 1d for this example...

 sourcetype="mcafee:wg:kv" 
| bin time span=1d 
| eval badconnection = if(category="Malicious" OR category="Spam" OR category="Exploit" OR category="Phishing" OR category="Spy" OR category="Pup",1,0) 
| stats count as connections, sum(badconnection) as badconnections by src_ip _time

... then run this one on that index at end of month

(that index) 
| stats sum(connections) as connections, sum(badconnections) as badconnections, dc(src_ip) as src_ip_count

[richgalloway]

What version of Splunk are you running?

[cburgman]

Splunk ver 6.3.3