topic Re: ER: Remove performance issue with oracode lookup in All Apps and Add-ons

ER: Remove performance issue with oracode lookup

martin_mueller — Sat, 20 Feb 2016 16:57:10 GMT

For five sourcetypes, there's this automatic lookup defined:

LOOKUP-ORACODE = oracle_ora_code_lookup ORACODE OUTPUTNEW DESCRIPTION, CAUSE, ACTION

ACTION values returned are a textual description of what you can do to alleviate the issue around the respective code.

Conversely, several sourcetypes have this transforms-based search time field extraction:

REPORT-ACTION_text = ACTION_text

This yields a field also called ACTION, containing numerical oracle action codes, e.g. 100 for a login.

Given that these two fields share the same name, searching for ACTION=100 triggers Splunk to go through the lookup and check if there happens to be a row with ACTION=100 in case it needs to search for the corresponding ORACODE value instead. It'll never find a numerical ACTION in the textual descriptive ACTION of the lookup, so the results remain correct - however, going through 20000 lines of lookup is a needless drain on performance for Splunk to build the normalizedSearch string before executing the search. Execution itself is not affected, but I've seen up to a second of additional search startup overhead added to every search just from going through this lookup once for each of the five sourcetypes.

To alleviate this, please change the ACTION field name returned by the lookup to something else.

Re: ER: Remove performance issue with oracode lookup

ChrisG — Sat, 20 Feb 2016 17:07:40 GMT

I filed a ticket with the add-on team about this: ADDON-7882.

Re: ER: Remove performance issue with oracode lookup

ChrisG — Mon, 22 Feb 2016 15:52:50 GMT

...and I heard back from the team that there is already work underway to address these issues. They have added this Answers posting to the internal page where they are coordinating the work.

Re: ER: Remove performance issue with oracode lookup

kchen_splunk — Tue, 23 Feb 2016 03:22:06 GMT

If you can provide more information about how you build the normalizedSearch, it will be great help.

Re: ER: Remove performance issue with oracode lookup

martin_mueller — Tue, 23 Feb 2016 05:40:26 GMT

It's all in my app, the Knowledge Object Explorer at https://splunkbase.splunk.com/app/2871/ - new version soon that can detect this kind of "zero match lookup", let me know if you want to beta test.

Re: ER: Remove performance issue with oracode lookup

martin_mueller — Fri, 08 Apr 2016 21:23:01 GMT

As of 3.4.0, the lookup now produces oracle_alert_action and the eventtypes now use oracle_audit_action, so this has been fixed.

Re: ER: Remove performance issue with oracode lookup

martin_mueller — Fri, 08 Apr 2016 21:23:44 GMT

The Knowledge Object Explorer v1.1 is out at https://splunkbase.splunk.com/app/2871/ for your normalizedSearch needs 🙂