none
How to filter/omit user function query in Auditlog RRS feed

  • Question

  • We are trying to setup a SQL Audit logging for all Queries run by users. Unfortunately we are seeing high volume/bulge in log when a custom function is used in query. It seems if query has a UDF (say fnGetColor()) in it, the query inside the function is logged for very row of resultset - causing millions of calls logged!

    In the Audit specification we chose INSERT,UPDATE,DELETE & SELECT for logging (EXECUTE is not included).

    Note, the function itself is not logged (so I can't add filter to exclude function) but the query inside the function is logged.

    What is the way to filter this from being logged?

    Thanks


    Mahesh

    Friday, April 13, 2018 1:21 AM

All replies

  • Hi Mahesh Dasari,

    There are a few options deal with variations of filter predicates. You must disable the server audit in order to make changes, and then re-enable it.

    1. To filter out all Scalar UDFs using class_type audit filed. Syntax looks like:

    ALTER SERVER AUDIT [servAudit]
    WHERE ([class_type] <> 20038); -- EXECUTE Scalar UDF

    2. Filter out the specific function name:

    ALTER SERVER AUDIT [servAudit]
    WHERE ([object_name]<>'function_name');

    For more information, please have a check this similar discussion:

    How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

    Check if this helps.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 13, 2018 6:19 AM
  • Hi Pirlo

    I tried adding this, it did not work. Note, we are not capturing EXECUTE in our Audit specification

    ALTER SERVER AUDIT [servAudit]
    WHERE ([class_type] <> 20038); -- EXECUTE Scalar UDF

    Proof:


    Mahesh

    Friday, April 13, 2018 11:21 AM
  • A scalar function that performs data access can be described with a single word: bad.

    The optimizer does not know about, and will not be able to integrate it in the rest of the query. If often converts the query to a loop behind the curtains. And as you have noted, Audit gets wrecked to.

    Rewrite the function as an inline table function, and call it with OUTER APPLY. The query you have in your screenshot would be written as:

    SELECT TOP WN.WeekNumber, m.*
    FROM   dbo.FactHeaderMaster m
    OUTER APPLY dbo.weeknumber_inline(m.DSRDate) AS WN

    • Proposed as answer by Pirlo Zhang Monday, April 16, 2018 9:37 AM
    Friday, April 13, 2018 9:29 PM
  • Erland, agree it is bad and Thanks for the nice tip which I already passed it to team.

    However, it won't change immediately and might not stop them using another one in future. Hence, I really need a fail-proof solution to filter scalar function queries in Auditlog 

    Pirlo, Is there a straight solution you can find please :)

    Thanks


    Mahesh


    Monday, April 16, 2018 8:40 PM
  • Hi Mahesh Dasari,

    Based on my test, following filter should work:

    (NOT [statement] like '%dbo.UDFTest%')

    The dbo.UDFTest represents the custom UDF name.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Pirlo Zhang Tuesday, April 17, 2018 9:30 AM
    Tuesday, April 17, 2018 9:29 AM
  • Hi Pirlo,

    As you see in my previous message with screenshot - the first row (with 1003 record count) statement does not contain function name in it.

    Thanks


    Mahesh

    Tuesday, April 17, 2018 11:06 AM
  • Hi Mahesh Dasari,

    Yes, your statement is:

    SET @WeekNumber =(select weeknumber from DW2.dbo.DimDate WHERE StandardDate=CONVERT(datetime,Convert(char(8),@DATE,113)))

    I cannot find any UDF you used in this statement, since you choose the select action for audit, is this not your desired result?

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 6:49 AM
  • , your statement is:

    SET @WeekNumber =(select weeknumber from DW2.dbo.DimDate WHERE StandardDate=CONVERT(datetime,Convert(char(8),@DATE,113)))

    I cannot find any UDF you used in this statement, since you choose the select action for audit, is this not your desired result?

    Yes, I m not running that SET/Select statement; I m just calling the UDF in another SELECT statement (as you can see in the screenshot first 2 lines).


    Mahesh

    Thursday, April 19, 2018 4:24 PM