Splunk Enterprise

How do I optimize filtering of Accelerated Report?

MonkeyK
Builder

I am trying to track user/machine logons. To help with this, I created the following query as an accelerated report:

(index=windows) EventCode IN (4624,4625,4648) TargetAccountName!="-" ComputerName=*.mydomain
| eval acctN=mvindex(Account_Name,1) 
| search acctN=* 
| bin _time span=1d as date 
| eval ComputerName=replace(ComputerName,".mydomain","") 
| eval user=upper(acctN) 
| eval domain=upper(TargetAccountDomain) 
| stats values(EventCode) as EventCodes values(date) as DaysSeen earliest(_time) as earliest latest(_time) as latest by ComputerName user Logon_Type 
| sort 0 user ComputerName

As an accelerated report this runs quite quickly for most time ranges:
a month gives me 23K stats in 12 seconds
90 days gives me 55k stats in 50 seconds.
However a YTD is brutal (5 hours. not sure why I let it finish)

I figure that I could use this report to do quick research on users/logons that I might see in a new computer/logon alert (to be created). So I built a dashboard with inputs for time, user, ComputerName and tried this:

(index=windows) EventCode IN (4624,4625,4648) TargetAccountName!="-" ComputerName=.mydomain TargetAccountName=$user$ ComputerName=$computer$
| eval acctN=mvindex(Account_Name,1) 
| bin _time span=1d as date 
| eval ComputerName=replace(ComputerName,".mydomain","") 
| eval user=upper(acctN) 
| eval domain=upper(TargetAccountDomain) 
| stats values(EventCode) as EventCodes values(date) as DaysSeen earliest(_time) as earliest latest(_time) as latest by ComputerName user Logon_Type 
| sort 0 user ComputerName

But that runs slower, the one month query goes to 45 seconds. So it looks like the acceleration statistics are at a higher level than the windows index. So then I tried moving my search term to the end.

(index=windows) EventCode IN (4624,4625,4648) TargetAccountName!="-" ComputerName=.mydomain 
     | eval acctN=mvindex(Account_Name,1) 
     | bin _time span=1d as date 
     | eval ComputerName=replace(ComputerName,".mydomain","") 
     | eval user=upper(acctN) 
     | eval domain=upper(TargetAccountDomain) 
     | stats values(EventCode) as EventCodes values(date) as DaysSeen earliest(_time) as earliest latest(_time) as latest by ComputerName user Logon_Type 
     | sort 0 user ComputerName | search user=$user$ ComputerName=$computer$

This runs way better, one month in 5 seconds - which is faster than reporting on a month of everything. But that's even more confusing, since according to the query, I had to summarize a month of everything before I could filter for user and computername.

So how are Accelerated Reports indexing their summarized data? And what/why is the best way to filter that data?
(also, would this have been a better case for a summary index?)

0 Karma

mwdbhyat
Builder

I would first decide on what I think would be acceptable for search times.. Then use job inspector to see what is the most taxing part of my search, modify the search accordingly (although it looks like you may have done that?) Depending on the type of search you are running it will also change whether the search is CPU bound or I/O.

With regards to summary indexing it depends on how long a period of time you generally plan on reporting over? Check this link out for more info on report acceleration vs SI:

https://answers.splunk.com/answers/135451/summary-index-vs-report-acceleration.html

0 Karma

MonkeyK
Builder

Acceptable search times are always "as fast as possible". I can work with what I have, but the difference in position has be wondering why. I have added criteria to the end of the query and it ran faster than without the criteria. Is this just because of render time?

Thanks for the link. I think that I kind of understood those differences, which is why I was confused by the performance difference in my query.

woodcock's summary notes that Report Accleration:

Speeds up a search by creating additional TSIDX mappings into the raw data.
if this were the case, then my first query should have run faster.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...