How to fine tune audit selects and clean up old audits

helena Matos 61 Reputation points
2020-11-24T14:18:19.09+00:00

Hello
Im new to mssql - I have a mssql on linux , as per our client requirements we need audit in place. i have it running , however, i need to extract the information
when i run this
SELECT GETDATE();

SELECT
event_time AS Login_Time,
server_instance_name ,
statement AS Description
FROM sys.fn_get_audit_file('/audit/*.sqlaudit', DEFAULT, DEFAULT)
WHERE
action_id = 'LGIF'
it extracts extra stuff which is not necessary like
The login packet used to open the connection is structurally invalid; the connection has been closed
Please contact the vendor of the client library

How can these be excluded from the select?
also how can the audit logs be cleaned?

thank you!!!!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,601 Reputation points
    2020-11-25T03:13:55.047+00:00

    Hi @helena Matos ,

    >also how can the audit logs be cleaned?

    You can delete the old audit file.
    If you do not specify parameters such as MAXSIZE, MAX_FILES or MAX_ROLLOVER_FILES when creating a file target server audit, then the default attribute is UNLIMITED. In the case of using the default parameters, each time you restart the SQL Server service or disable/re-enable the server audit, a new audit file will be generated. It is recommended that you modify the appropriate parameters of this server audit to generate the limited audit files, which is more flexible for saving and deleting.

    1 person found this answer helpful.
  2. helena Matos 61 Reputation points
    2020-11-25T14:06:58.063+00:00

    Another question
    I have set
    CREATE SERVER AUDIT [audit2]
    TO FILE
    ( FILEPATH = N'/mssql/audit/'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'b71e2989-b026-4d12-86be-72ee01b9568b'
    )
    ALTER SERVER AUDIT [audit2] WITH (STATE = ON)
    GO

    CREATE SERVER AUDIT SPECIFICATION [audit2]
    FOR SERVER AUDIT [audit2]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP)
    WITH (STATE = ON)
    GO

    However when i do a select for success, returns nothing HELP

    SELECT GETDATE();

    SELECT
    event_time AS Login_Time,
    server_instance_name ,
    statement AS Description
    FROM sys.fn_get_audit_file('/mssql/audit/*.sqlaudit', DEFAULT, DEFAULT)
    WHERE
    action_id = 'LGIS'
    go